Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

NL Function Reference


=NL (What,Table,Field,FilterField1,Filter1,Filter,...,FilterField10,Filter10)

 

Purpose:  Returns fields or record keys from a table based on filters.

For more general information about the NL function, see Introducing the NL Function

Help Topics

NF Function
NP Function
GL Function
Filtering

 

Parameter Argument Description
What Blank or omitted Returns the Field or record key from the first record that matches the other NL arguments
  "Sum" Returns the sum of the Field for all records that match the filters.  To use Sum, the field type must be numeric.
  "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.
  "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".

  "Columns"
Just like "Rows", but copies columns.
  "Sheets"

Like "Rows" and "Columns" but copies the current worksheet.

"Sheets=n" is not supported. Only the current worksheet can be copied.

The name of the sheet is set to the value returned.
If name is too long or already exists, Jet will create a new name.

  Positive Number

1 returns the first record or field that matches the filters.

2 returns the second record or field, etc.

  Negative Number

-1 returns the last record or field that matches the filters.

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

  "First" Returns the first record or field that matches the filters
  "Last" Returns the last record or field that matches the filters
  "AllUnique"

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

Returns an array of unique values for the field.

  "Picture" Loads a bitmap (bmp) from a file or from a BLOB in Dynamics NAV.
  "Caption" Returns the NAV caption in the current language for the specified table or field.
  "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.

  "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").

  "CubeValue"

Returns a single measure value from a Cube data source.

  "BinaryText"

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

  "Link"

Returns a string value that can be used as a filter in another NL function.

See Using Link= for more information.

  "LinkField"

Returns a string used to retrieve a field from a link 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.
  "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.

  "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.
     
 Table The name, number, or caption of the table.

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

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.

 Field

The name, number, or caption of the field to return. For Dynamics NAV users, this can also be an Advanced Dimension.

To return a record key, leave the Field parameter blank.

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

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.

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", this parameter becomes the full path for the file or the name of the binary field on the specified table containing an image.

 FilterField1

The name of the first field by which to filter.  For Dynamics NAV 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.
  "Link=" Links the primary table to the one specified by the Filter argument for the purpose of filtering and retrieving data.
  "InclusiveLink=" Links the primary table to the one specified by the Filter argument for the purpose of retrieving data.
  "Limit=" Limits the number of records or values returned to the number specified by the Filter argument.
  "Filters=" Specifies a set of filters for the query with an array of filters specified by the Filter argument.
  "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).
  "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.
  "DataSource=" Overrides the default data source with the one specified by the Filter argument.
  "Schema=" Overrides the database schema used in the query with the one specified by the Filter argument.*
  "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.
  "Type=" Specifies the type of image being inserted by the NL(Picture) function.
  "ScanLimit=" Specifies the number of records to scan in a Dynamics NAV 2009R2 (or earlier) query with the number specified by the Filter argument.
  "ShowQuery=" Displays the query that will be sent to the database.
  "Key=" Overrides the Dynamics NAV key to use for the query with the one specified by the array of fields in the Filter parameter.
  "Measures=" In a Cube data source, specifies the measures to use in returning only the items containing the measure value.
  "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.
  "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.
Filter1

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.

FilterFieldN Same as FilterField1. Up to 10 field and filter pairs can be specified. If you specify multiple filters, they combine in a logical AND. 
FilterN Same as Filter1, but applies to FilterFieldN. 

 

EXAMPLES

 

Navision Cronus NL Function

This NL that 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")


This NL returns the Customer Name from sales quote number 10000. This NL can only return one record so the "What" parameter is blank
=NL(,"Sales Header","Name","No.","10000","Document Type","Quote")

 

This NL returns information for a company other than the default one in the Options screen
=NL("Rows","Customers",,"0","CRONUS USA, INC.")


This NL returns information for a company other than the one in the Options screen using a connection other than the default.
=NL("Rows","Customers",,"0","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"})


This NL creates lookup values for use with Report Options for each item in cells F5 through F15.
=NL("Lookup",F5:F15,"May Values")

 

Access Northwind NL Function 

This NL returns the record key for all the Customer in the City of Portland with a 503 area code
=NL("Rows","Customers",,"Phone","503-???-????","City","Portland")

 

This NL returns the number of units in stock for product number 26. Since the NL can only return one record, the "What" parameter is blank.
=NL(,"Products",UnitsInStock","ProductID","26")

 

This NL returns information using a connection other than the default.
=NL("Rows","Customers",,"DataSource=",2)

 



* 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, "Schema=" will return the correct data, but any NF() functions that reference that field cache do not. See our article Using "Schema=" to return specific data

 

Note: If the NL function is making copies of a template, it must be the only function in the cell. The functions =-NL("Rows") and =NL("Rows")*-1 are not valid.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments