Using NL( Lookup ) in Jet Reports Part-3

We have discussed regarding Lookup in my previous post. If you missed can find link here.

Using NL( Lookup ) in Jet Reports Part-1.

Using NL( Lookup ) in Jet Reports Part-2.

Continuing with more advanced usage I am here below.

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 may not list all options depending upon data in the table.

By adding the "SmartLookup" option we could get a list of all options defined as option to that field.

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

Stay tuned for more details in my upcoming posts