NL Function
=NL (What, Table, Field, FilterField1, Filter1, …, FilterField10, Filter10)Purpose: Retrieves individual fields or record keys from the database, based on your filters.
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:
NL function is one of many Jet functions like NF, NP and GL etc. It has the following parameter options.
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”
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”.
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.
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:
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.
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.
Same as Filter #1, but applies to FilterField #n.