We all know that the =NL (Lookup) function is used to pull a list of values from a database, for example:
=NL("Lookup","Customer","No.")but there's a few features of it that are probably not as often used. Let's take a look.
Multiple-field Lookup with Custom Headers
To help users make choices more easily, it's possible to display multiple values from a table. This can be achieved by creating an array of fields to be displayed, separated by commas, and enclosing the list with curly braces in the Field parameter.
For instance, if the information to be shown includes the Name and State associated with each customer number, the resulting function would resemble:
=NL("Lookup","Item",{"No.","Description","Unit Cost"})Moreover, it's feasible to customise the column headers that appear at the top of each column in the Lookup window, in addition to selecting several fields to be displayed. To accomplish this, include "Headers=" in one of the FilterField parameters of the NL("Lookup") function.
=NL("Lookup","Item",{"No.","Description","Unit Cost"},"Headers=",{"Item Code.","Item Description","Unit Cost"})Scan Limit
The NL("Lookup") function comes with a nifty feature that lets users determine the number of records to scan and create the list of values to display. The Lookup function usually uses the 'Maximum Lookup Records Scanned' value, which is set in the Jet Application Settings and is typically 1,000 records.
However, if you need to search for a more extensive set of records, you can utilise the ScanLimit= keyword. To specify a scan limit, input ScanLimit= in one of the FilterField parameters and type in the desired number of records to be searched in the corresponding Filter parameter.
For instance, if you want to display all G/L Account Numbers included in the first 5,000 records of a G/L transaction table, you can use the Lookup function like this:
=NL("Lookup","G/L Entry","G/L Account No.","ScanLimit=","5000")Lookups filtered by other Lookups
Sometimes, we may need to display values in one NL("Lookup") function based on the results selected in another NL("Lookup") function in Business Central.
For example, let's say we have a table of sales orders and a table of items. We want to allow users to select a sales order and then see only the items associated with that order. In this case, we can use two NL("Lookup") functions, with the items filtered by the sales order to create a relationship between the values.
The first NL("Lookup") function will allow the selection of the sales order and might look something like this:
=NL("Lookup", "Sales Header", "No.")Once the user selects a sales order from this function, we can use the value in another NL("Lookup") function to show only the items associated with that order:
=NL("Lookup", "Sales Line", "Item No.", "[REFERENCE CELL CONTAINING ABOVE FUNCTION]")SmartLookup
When using the NL("Lookup") function to display values from an "Option" field, it will normally only display values that are currently present in the table. However, in some cases, we may want to display all possible values of the "Option" field, regardless of whether or not they are currently present in the table.
In Business Central, "Option" fields are commonly used to define fields that have a fixed set of values. These values are typically defined by the system administrator or the application developer, and users can select from these values when entering data in the system.
For example, in the case of items, the "Item Type" field may be defined as an "Option" field with possible values such as "Inventory", "Non-Inventory", "Service", "Charge", and "Resource".
This is where the SmartLookup= option comes in handy. When this option is used, the NL("Lookup") function will display all possible values of the "Option" field, even if some of these values are not currently present in the table.
For example, let's say we want to display all possible values of the "Item Type" field in the "Item" table. We can use the following function:
=NL("Lookup", "Item", "Item Type", "SMARTLOOKUP=", "TRUE")This will display a Lookup window that contains all possible values of "Item Type", including "Inventory", "Non-Inventory", "Service", "Charge", and "Resource", even if some of these values are not currently present in the "Item" table.
❤️ Enjoyed this article?
Forward to a friend and let them know where they can subscribe (hint: it's here).
