Using Jet Report NL Function

 
Returns fields or record keys from a table based on filters. Duplicates report templates.
 
Note:
If the NL function is making copies of a template, it must be the only function in the cell.
The functions =-NL("Rows") and =NL("Rows")*-1 are not valid.
 

 
Example:

=NL("Rows","Customer",,"Salesperson Code","PS","Shipping Agent Code","DHL")

This NL returns the record key for all the Customer with Salesperson Code = ‘PS’ and Shipping Agent Code as ‘DHL’
 


If you specify multiple filters, they combine in a logical AND.
 


NL Function Parameters & Arguments with brief Description.
 




Navision Cronus NL Function Examples:

This NL that returns the record key for all of the customers in the Customer table who are in the City of Boston with a Balance less than zero

=NL("Rows","Customer",,"Balance","<0","City","Boston")

This NL returns the Customer Name from sales quote number 10000. This NL can only return one record so the "What" parameter is blank

=NL(,"Sales Header","Name","No.","10000","Document Type","Quote")

This NL returns information for a company other than the default one in the Options screen

=NL("Rows","Customer",,"0","CRONUS USA, Inc.")

This NL returns information for a company other than the one in the Options screen using a connection other than the default.

=NL("Rows","Customer",,"0","CRONUS USA, Inc.","DataSource=","2")

This NL creates sheets called "US","CANADA" and "MEXICO" using an array in the table field

=NL("Sheets",{"US","CANADA","MEXICO"})

This NL creates lookup values for use with Report Options for each item in cells F5 through F15.

=NL("Lookup",F5:F15,"My Values")

 

See my upcoming posts for more details and uses of this function.