Overview
NL Function
=NL (What, Table, Field, FilterField1, Filter1, …, FilterField10, Filter10)Purpose: Retrieves individual fields or record keys from the database, based on your filters.
Structure of the NL function
The first parameter of the NL function specifies what to retrieve from your database. The second and third parameter specifies the name of the table and its field respectively. The remaining parameters specify the filters. An NL Function would appear like this:
Parameter Options
NL function is one of many Jet functions like NF, NP and GL etc. It has the following parameter options.
1. What
- “BinaryText”
Retrieves a text or rich text value from a binary database field. Uses the current Windows ANSI codepage to perform the conversion.
- “Caption”
Retrieves the caption in the current language for a table or field.
- “Columns”
Inserts a copy of the current column for each value in the resulting list. 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, you will use “Columns=3”
- “Count”
Count the number of records that match the filters. Ignores the Field parameter.
- “Filter”
Creates a filter from the resulting data to be used in another NL function. It is intended for filtering the contents of one table based on the contents of another. It can also be used with array operations such as NP(“Union”) or NP(“Intersect”).
- “First”
Returns the first record or field that matches the filter.
- “FlowField“
Creates a FlowField() string for use in retrieving flow field values from an NL (Table) function.
- “Last”
Returns the last record or field that matches the filter.
- “Link”
Creates a link for use in another function. Intended for filtering the contents of one table based on the contents of another.
- “LinkField”
Returns a string used to retrieve a field from a linked table in an NL(Table) function.
- “LinkSum”
Creates a string for use in retrieving sums from linked tables in an NL(Table) function.
- “Lookup”
Define a lookup function for use with the Report Options feature.
- “Picture”
Loads a bitmap (bmp) from a file or from a BLOB in Dynamics NAV/Business Central.
- “Rows”
Inserts a copy of the current row for each value in the resulting list. 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.
- “Sum”
Sum a numeric field for every record that matches the filters.
- “Table”
Inserts an Excel table object that contains the resulting record set.
2. Table
In the table field of NL Function, we can specify the name, number, or caption of the table. We can also use an Excel array in the table argument when the “What” argument is “Rows”, “Columns”, “Sheets”, or “Lookup”. This will use the array values instead of database values.
Excel arrays can be created using a range of cells like D4:D8, by typing {“element1″,”element2″,”element3”} or using one of the Jet Reports Array Calculations. This will create rows, columns or sheets for each element of the array.
To execute an SQL query in the Table argument, the argument should begin with SQL=. If your Table starts with SQL=, Jet Reports expects the rest of the parameter to be a valid SQL query. Leave the table argument blank in order to load a picture from a file.
3. Field
- In the field argument of the NL Function, we can specify the name, number, or caption of the field to return. In order to return a record key (for use with the NF Function), we have to leave the Field parameter blank. You can select a single field or an array of fields for e.g., {“No.”,”Name”,”Balance”,”Address”,”City”,”County”,”Country/Region Code”} from this argument.
- 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 we have to filter the data using the NL Function. For Dynamics NAV and Business Central users, this can also be an Advanced Dimension. The following special values are allowed as FilterField arguments:
- “CacheTables=”
When the value of the Filter argument is TRUE, table data for this function will be pre-fetched and cached before expanding replicators.
- “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. It is used to provide more than 10 filters in an NL function.
- “InclusiveLink=”
Links the base 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.
- “ShowQuery=”
When the value of the filter argument is TRUE, specifies that the function should return the SQL query or Dynamics NAV drilldown query.
5. Filter #1
The value of the filter to apply to FilterField1 in the NL Function.
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 fields and filter pairs can be specified in the NL Function. If you specify multiple filters, they combine using a logical AND.
7. Field #n
Same as Filter #1, but applies to FilterField #n.
