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


Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: