Using NL( Lookup )

Simple Field Lookup

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:

Only the one field is displayed and that field name is used as the column header

Multiple-Field Lookup

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 creating the list of fields to be displayed, separated by commas, in the Field parameter and surrounding the list with curly braces.

For example, 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"})

and the resulting lookup window would look like this:

Only the first field in the array is the field that will be returned by the Lookup form.

Custom Column Headers

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 each column in 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 would look like this:

=NL("Lookup","Customer",{"No.","Name","State"},"Headers=",{"Cust. No.","Cust. Name","Cust. State"})

and the resulting Lookup window now appears as:

Hard-coded Lookups

It is also possible to manually specify the values that will be displayed in and returned by the Lookup window.  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, Headers= must be used in the FilterField parameter, and the Field parameter will 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 would be:

=NL("Lookup",{"N","S","E","W"},,"Headers=","Direction")

The resulting lookup window would now be:

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 same result as the previous example, but the values are specified by a cell reference instead of text.

The formula is now:

=NL("Lookup",F5:F8,"Direction")

Cell Reference Lookups

We can also utilize cell references to display multiple columns in the Lookup window.  To achieve this, we'll include another column of values next to the values that we are already displaying.  Once this is done, we'll expand the cell range in the NL("Lookup") function to encompass both columns of data.

Since multiple columns are now being specified, our NL("Lookup") function will also need to include the names for these columns.  This is done by using the same array syntax that was described above in order to specify the field names in the Field parameter.

Below is an example of what this would look like - using "Direction" and Description" as our column headings:

The resulting Lookup window would now be displayed as:

Lookups filtered by other Lookups

In some instances. we may want to have the values which are displayed in one NL("Lookup") function to be based 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 and will also be able to specify Customer Numbers in order to filter the report further.  If only one Salesperson Code is selected, we'll want to display only those Customer Numbers that are associated with that Salesperson.

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.

ScanLimit

Another useful feature of the NL("Lookup") function is the ability to specify how many records the Lookup function will go through in order to create the list of values that will be displayed. 

By default, the Lookup function uses the value that is set for Maximum Lookup Records Scanned in the Jet Application Settings.  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.  So, to create a Lookup function that will return all of the G/L Account Numbers contained 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")

SmartLookup

The NL("Lookup") function normally returns all values (for the particular field) that are found in the table specified (based on the limit discussed above).

For fields defined in NAV as "Option" fields, it may sometimes be desirable to display *all possible* values - regardless as to whether those values are present in the table or not.  For this, a useful feature is the SmartLookup= option.

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 that could be used in that field: