Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Using NL( Lookup )


The NL(“Lookup”) function can be used to allow users of the report (either Designers or Viewers) to select from a specified list of values when setting their report filters. This will allow them to see the specific options available to them in regards to a particular report filter.
The most common (and basic) use of the NL(“Lookup”) function is to simply pull a list of values from the database. For example, if a list of customer numbers (“No.”) from the “Customer” table is desired then the function would look something like this:
=NL(“Lookup”,”Customer”,”No.”)
The resulting lookup that the user sees would be:

It is also possible to allow the user to see more than one value from a particular table. This can help the user to make a choice more easily by displaying additional details about the values returned. Multiple fields can be displayed by placing them in an array. This is accomplished by placing the list of fields to be displayed, separated by commas, in the Field parameter and surrounding it with curly braces. If the fields to be shown are the “Name” and the “State” associated with each customer “No.”, the resulting function would look something like this:
=NL(“Lookup”,”Customer”,{“No.”,”Name”,”State”})
The resulting lookup would be:

NOTE: The first field in the array is the field that will be returned by the Lookup form.  (In this case the value returned would be '10000'.)
In addition to selecting multiple fields to be displayed in the list, it is also possible to customize the headers, that appear at the top of the Lookup window. This can be done by placing “Headers=” in one of the FilterField parameters of the NL(“Lookup”) function. For example, instead of the field names appearing as “No.”, “Name”, and “State”, it has been decided that they should be displayed as “Cust. No.”,”Cust. Name”, and “Cust. State” to the user. To do this, “Headers=” will be added in one of the FilterField parameters, and the desired names will be placed in the associated Filter parameter. The function should look like this:
=NL("Lookup","Customer",{"No.","Name","State"},"Headers=",{"Cust. No.","Cust. Name","Cust. State"})
The resulting Lookup window would now appear as:

In addition to returning a list of values from the database, it is also possible to manually specify the values that will be returned. This allows you to present the user with a list of values that are not stored in your database. The syntax that will be used is slightly different. Since data is no longer being returned directly from the database, the Table parameter is no longer used to specify the table that the information will pull from. Instead, an array containing the values to be displayed is placed in the Table parameter of the Lookup function. The Field parameter will then contain the list of column headers of the Lookup window. To create an NL(“Lookup”) function that will display “N”, “S”, “E”, and “W” for directions, the function will be:
=NL("Lookup",{"N","S","E","W"},,"Headers=","Direction")
The resulting lookup window would now be:

NOTE : It is important to place the field name that will be displayed at the top of the Lookup window (in this example “Direction”) in the Field parameter. Omitting this will not display any values in the Lookup Window.
In addition to placing the values in the NL(“Lookup”) function itself, a cell reference can also be used. The following example will display the exact same result as the previous example, but the values are sprcified by a cell reference instead of text.  The formula is now:
=NL("Lookup",F5:F8,"Direction")

It is also possible to display multiple columns in the Lookup Window by utilizing cell references. To achieve this, another column of values will need to be inserted next to the values that we are already displaying. Once this is done, the cell reference in the NL(“Lookup”) function will also need to be expanded to encompass both columns of data. Since multiple columns are now being specified, names for these columns will also need to be defined in the NL(“Lookup”) function as well. This is done by using the array syntax that was described above in order to specify the field names in the Field parameter, in this case “Direction” and “Description”.
Below is an example of what this would look like:

The resulting Lookup window would now be displayed as:

NOTE : This feature applies to Jet Reports 2009 and later.
In some instances it is also desirable to base the values that are displayed in one NL(“Lookup”) function on the results that were selected in another NL(“Lookup”) function. An example of this could exist in a Sales Report. The viewer will have the ability to select a Salesperson Code to run the report for, and will also be able to specify Customer Numbers in order to filter the report further. If only one Salesperson Code is selected, however, it may be undesirable to display Customer Numbers that are associated with other Salesperson Codes. In this instance, two NL(“Lookup”) functions will be used, with the Customer Number filtered by the Salesperson Code so that the values are related. The first NL(“Lookup”) function, which will allow the selection of the Salesperson Code, will look like this:

The next NL(“Lookup”) function will give the viewer the ability to select from a list of Customer Numbers, but it will be filtered based on the Salesperson Code that was previously selected. This is done but inserting a normal filter into the function and referencing the cell containing the Salesperson Code that was previously selected by the viewer.
This addition would make the report look like this:

If the viewer selects all Salesperson Codes in the report (by placing an asterisk in the filter), then the resulting list would be:

However, if a particular Salesperson Code is selected, then the resulting list of Customer Numbers will be much smaller. This is because the NL(“Lookup”) function that is returning the list of Customer Numbers is now filtering on the Salesperson Code that was selected, and thus, only returns Customer Numbers associated with that Salesperson Code.

Another useful feature of the NL(“Lookup”) function is the ability to specify how many records Jet Reports will go through in order to create a list of values. By default, Jet Reports uses the value that is set for Maximum Lookup Records Scanned on the Jet Reports Options form. The default is 1,000 records. If the number of desired records to be searched is larger than this setting, the “ScanLimit=” keyword can be utilized. To apply a scan limit, “ScanLimit=” must be placed in one of the FilterField parameters and then the desired number of records to be searched will be placed in the associated Filter parameter. To create a Lookup function that will return all of the G/L Account Numbers in the first 5,000 records of a G/L transaction table, the function would look like this:
=NL(“Lookup”,”G/L Entry”,”G/L Account No.”,”ScanLimit=”,”5000”)

The NL("Lookup") function normally returns all values (for the particular field) that are present in the table specified.  For fields defined in NAV as "Option" fields, it may sometimes be desirable to display *all possible* value - regardless as to whether those values are present in the table or not.  For this, a useful feature is the "SmartLookup=" option (available in Jet Essentials 2012 R2 and later).

For example, the function:

=NL("Lookup","Item Ledger Entry","Entry Type")

might provide a Lookup window that looks like this:


By adding the "SmartLookup" option [ =NL("Lookup","Item Ledger Entry","Entry Type", "SmartLookup=","TRUE" ) ], we could get a list of all options:




**NOTE: NL("Lookup") is only available in Jet Reports version 5 and later.

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

Comments