NL Function Reference

Related Articles...

Overview

=NL("what to retrieve", "table name", "field name", "filter field", "filter value")

Parameter Options

1. What

   • Blank or Omitted

     Same as NL("First").

     Returns the Field or record key from the first record that matches the other parameters in the NL function (based on the database table's default index).

     To override the table's default index, you can force the function to sort by a specific field.

   • "AllUnique"

     Returns an array of unique values for the field.

     For all operations except those in which you pass an array to use an Excel function, it is now recommended that you use "Filter" instead of "AllUnique"

   • "BinaryText"

     Retrieves a text or rich text value from a binary database field. Uses the current Windows ANSI codepage to perform the conversion.

   • "Caption" (Dynamics NAV/Business Central only)

     Returns the NAV/Business Central caption in the current language for the specified table or field.

   • "Columns"

     Copies the current column and all of its contents for each unique value of the Field parameter in the records that match the filter.

     The values returned are sorted.

     To copy more than one column, put "Columns=n" (where n is the number of columns to copy). For example, to copy the current column and the next two columns, use "Columns=3"

   • "Count"

     Returns the count of all records that match the filters. Ignores the Field parameter.

   • "CountUnique"

     Returns the count of all of the unique values of the specified field that matches the filters.

   • "CubeValue"

     Returns a single measure value from a Cube data source.

   • "Filter"

     Returns a string value that can be used as a filter in another NL function.  Intended for filtering the contents of one table based on the contents of another.

     Can also be used with array operations such as NP("Union") or NP("Intersect"). 

   • "First"

     Returns the first record or field that matches the filters (based on the tables default index) that matches the filters.

     To override the table's default index, you can force the function to sort by a specific field.

   • "FlowField"

     Returns a string used to retrieve a FlowField from the primary table in an NL(Table) function.

     FlowFilters in this function are only applied to the specified FlowField and not the parent query.

   • "Last"

     Returns the last record or field (based on the table's default index) that matches the filters.  To override the table's default index, you can force the function to sort by a specific field.

     It is recommended that you use the NL("First") function instead of the NL("Last").  Using the NL("First") function with a negative sort will return the same value was the NL("Last") function - but will do so without having to read in all other values.  In tables with a large number of records, NL("First") can be significantly faster.

   • "Link"

     Returns a string value that can be used as a filter in another NL function.  Intended for filtering the contents of one table based on the contents of another.

     See Using Link= for more information.

   • "LinkField"

     Returns a string used to retrieve a field from a linked table in an NL(Table) function.

   • "LinkSum"

     Returns a string used to retrieve a sum of a field from a link table in an NL(Table) function

   • "Lookup"

      

     Creates a formula that will perform the database lookup for Report Options.

     This function must be used with the Option tag in column A and the Lookup tag in row 1. See the various Report Options topics for more details.

   • Number (negative)

     -1 returns the last record or field that matches the filters (based on the table's default index)

     -2 returns the second to last record or field, etc.

   • Number (positive)

     1 returns the first record or field that matches the filters [same as NL("First")]

     2 returns the second record or field, etc.

      

   • "Picture"

     Loads a bitmap (bmp) from a file or from a BLOB in Dynamics NAV/Business Central.

   • "Rows"

     Copies the current row and all of its contents for each unique value of Field in the records that match the filter.

     The values returned are sorted.

     To copy more than one row, put "Rows=n" where n is the number of rows to copy. For example, to copy the current row and the next two rows, use "Rows=3".

   • "Sheets"

     Like "Rows" and "Columns" but copies the entire current worksheet.  The name of the copy sheet is set to the value returned by the function. 

     "Sheets=n" is not supported.  Only the current worksheet can be copied.
     If the name is too long or already exists, Jet will create a new name.

   • "Sum"

     Returns the sum of the Field for all records that match the filters.  To use Sum, the field type must be numeric.

   • "Table"

     Creates an Excel table object based on the field values returned.  Leaving the Field argument blank returns all fields.  Use a Field Cache to return multiple fields.

2. Table

   The name, number, or caption of the table.

   When the "What" argument is "Rows", "Columns", "Sheets", or "Lookup" you can also use an Excel array in the table argument, which will use the array values instead of database values.

   Excel arrays can be created in a number of ways, including typing {"element1","element2","element3"}, using a range of cells like E8:E16, or using one of the Jet Reports Array Calculations. This will cause rows, columns or sheets to be created for each element of the array.

   To execute a SQL query, begin the Table argument with SQL=. 
   If your Table starts with SQL=, Jet Reports expects the rest of the parameter to be a valid SQL query.

   If you want to load a picture from a file, leave the table blank.

3. Field

   • The name, number, or caption of the field to return.

     For Dynamics NAV and Business Central users, this can also be an Advanced Dimension.

   • To return a record key (for use with the NF function) leave the Field parameter blank.

   • A Field Cache is required when a table does not have a primary key. If a Field Cache is used, every field that will be retrieved with NF functions must be in the Field Cache.

     To return a Field Cache, specify an array of fields.  e.g., {"No.","Name","Balance","Address","City","County","Country/Region Code"}

   • A Field Cache can be used with NL(Table) when you only want to see some of the fields from the table rather than all of them. An NL function with a Field Cache will return only the unique combinations of the values of the fields in the Field Cache.

   • When the What argument is "Picture", the Field parameter becomes the full path for the file or the name of the Dynamics NAV or Business Central binary field ("Blob") in the specified table containing an image.

4. FilterField #1

   The name of the first field by which to filter.  For Dynamics NAV and Business Central users, this can also be an Advanced Dimention.  

   The following special values are allowed as FilterField arguments:  

   • "Company=" or 0

     Overrides the default company with the one specified by the Filter argument.

   • "DataSource="

     Overrides the default data source with the one specified by the Filter argument.

   • "Filters="

     Specifies a set of filters for the query with an array of filters specified by the Filter argument.  Typically used when wanting more than 10 filters in your NL function

   • "Headers="

     Overrides field headers with the array of headers specified by the Filter argument. For use with an NL(Table) or NL(Lookup) function.

   • "HideTotals="

     When the value of the Filter argument is TRUE, hides the totals row created by NL(Table).

   • "IncludeDuplicates="

     When the value of the Filter argument is TRUE, specifies that all matching records from the source data will be included in the NL(Table) results.

   • "InclusiveLink="

     Links the primary table to the one specified by the Filter argument for the purpose of retrieving data.

   • "Key="

     Overrides the Dynamics NAV/Business Central key to use for the query with the one specified by the array of fields in the Filter parameter.

   • "Limit="

     Limits the number of records or values returned to the number specified by the Filter argument.

   • "Link="

     Links the primary table to the one specified by the Filter argument for the purpose of filtering and retrieving data.

   • "Measures="

     In a Cube data source, specifies the measures to use in returning only the items containing the measure value.

   • "ScanLimit="

     Specifies the number of records to scan in a Dynamics NAV 2009R2 (or earlier) query with the number specified by the Filter argument.

   • "Schema="

     Overrides the database schema used in the query with the one specified by the Filter argument.

     The special filter "Schema=" is designed to be used with NL() functions that return specific fields.  In NL() functions where the "Field" parameter is either left blank or contains a field cache, the function will return the correct data, but any NF() functions that reference that field cache will not.  See our article Using "Schema=" to return specific data

   • "ShowQuery="

     Displays the query that will be sent to the database.

   • "TableName="

     Specifies the name to use for the Excel table object created by NL(Table) with the name in the Filter parameter. Use this to refer to the table by name from a Pivot table.

   • "Type="

     Specifies the type of image being inserted by the NL(Picture) function.

   • "UseLocalFormulas="

     For use with an NL(Table) function. Specifies that any resulting Excel formulas will be written in the language currently set in windows, otherwise English formulas will be returned.

   • "Width=" and "Height="

     Overrides the width and height of the picture being inserted by the NL(Picture) function with the value in the Filter parameter.

5. Filter #1

   The value of the filter to apply to FilterField1.

   If "Company=" is in the corresponding FilterField, put the company name here.

   If "DataSource=" is in the corresponding FilterField, put the connection name as defined in Jet Options here.

6. FilterField #n 

   Same as FilterField #1.

   Up to 10 field and filter pairs can be specified.  If you specify multiple filters, they combine using a logical AND. 

7. Field #n 

   Same as Filter #1, but applies to FilterField #n. 

   ---

Examples of the NL function

Retrieve balance of customer "10000":

=NL("First","Customer","Balance","No.","10000")

The function tells Jet Reports to return the Balance field in the Customer table for the record that has a customer No. of "10000".

This NL returns the record key for all of the customers in the Customer table who are in the City of Boston with a Balance less than zero: 

=NL("Rows","Customers",,"Balance","<0","City","Boston")

Record keys are then referenced by NF() functions to retrieve individual field values

This NL returns the Customer Name from sales quote number 10000. This NL can only return one record, so the 'What' parameter of "First" is used:

=NL("First","Sales Header","Name","No.","10000","Document Type","Quote")

This NL returns information for a company (regardless of the default company shown on the Jet ribbon):

=NL("Rows","Customers",,"Company=","CRONUS USA, INC.")

This NL returns information for a specific company and data source (ignoring the default company & data source):

=NL("Rows","Customers",,"Company=","CRONUS USA, Inc.","DataSource=",2)

This NL creates sheets called "US","CANADA" and "MEXICO" using an array in the table field:

=NL("Sheets","{"US","CANADA","MEXICO"})

How would you get the sum of the balances for all customers in Georgia?

=NL("Sum","Customer","Balance","State","GA")

This function will find all customers in Georgia and return the sum of their balances.

If you only wanted customers in Atlanta, you could use:

=NL("Sum","Customer","Balance" ,"State","GA" ,"City","Atlanta")

Can you guess how to write the function for the sum of all positive balances?

=NL("Sum","Customer","Balance","Balance",">0")

You can specify up to ten filters. If you wanted the sum of the balances of all customers with positive balances in Atlanta, GA, you could use:

=NL("Sum","Customer","Balance","Balance",">0","City","Atlanta","State","GA")

If you wanted to know how many customers had positive balances in Atlanta, GA you could use:

=NL("Count","Customer","Balance","Balance",">0","City","Atlanta","State","GA")

=-NL("Rows","table1","field1")

 =NL("Rows","table1","field1")*-1

=IF($D$7>0,NL("Rows","table1","field1"),"")