Sign Up for Training |
Jet Global Company Site
Community
Downloads
Submit a Request
Give Feedback

Introducing the NL Function


Related Articles...

Overview

Jet Reports adds the NL worksheet function to Excel. This function retrieves data from your database based on the function parameters that you set.

Examples of the NL function

Retrieve balance of customer "10000"

=NL("First","Customer","Balance","No.","10000")

The function tells Jet Reports to return the Balance field in the Customer table for the record that has a customer No. of "10000".

Structure of the NL function

The first three parameters of the NL function specify what to retrieve, the table, and the field.  The fourth, fifth and following parameters specify the filters. 

=NL("what to retrieve","table name","field name","filter field","filter value")

For each filter, you include two parameters: the filter field and the filter value.

If the first parameter (the "what" parameter) is blank, Jet retrieves the first record that matches the filters. You can also use "Sum" to sum up values in a field or "Count" to count records in a table.

The following example of an NL function retrieves the phone number for a customer with the CustomerID "ALFKI"

=NL(,"Customers","Phone","CustomerID","ALFKI")

The function tells Jet Reports to return the Phone field in the Customers table for the record that has a CustomerID of "ALFKI".

In this example, the first parameter is blank ("First" is implied) so you enter a comma to move to the next parameter, which is the table name. The third parameter is the field to return. The fourth is a filter field, and the last parameter is the filter value. The easiest way to get this function is to use the Jet Browser to drag the Phone field out of the Customers table with the Browser drag-and-drop keyword option set to First. You now have an NL function without the filters. You can add the filters using the Jet Function Wizard (Jfx).

Since only one record in the table matches the filter set, Jet Reports knows exactly what to return.

What do you think the following functions would return?

=NL(,"Customers","ContactName","Region","CA")
=NL(,"Customer","Balance","State","CA")

Since you might have more than one customer in the state of California, Jet will return the name of the first customer it finds in California.

If you omit the first parameter, the NL always returns the field of the first record it finds that matches the filters.  Returning the first record is usually used when the filters specify exactly one record.

Dynamics NAV Examples

How would you get the sum of the balances for all customers in Georgia?

=NL("Sum","Customer","Balance","State","GA")

This function will find all customers in Georgia and return the sum of their balances.

If you only wanted customers in Atlanta, you could use:

=NL("Sum","Customer","Balance" ,"State","GA" ,"City","Atlanta")

Can you guess how to write the function for the sum of all positive balances?

=NL("Sum","Customer","Balance","Balance",">0")

You can specify up to ten filters. If you wanted the sum of the balances of all customers with positive balances in Atlanta, GA, you could use:

=NL("Sum","Customer","Balance","Balance",">0","City","Atlanta","State","GA")

If you wanted to know how many customers had positive balances in Atlanta, GA you could use

=NL("Count","Customer","Balance","Balance",">0","City","Atlanta","State","GA")

 

More Examples

How would you get the Sum of the Quantities for all Invoices for customers in London? Here is the function.

=NL("Sum","Invoices","Quantity","City","London")

This function will find all Invoices that have a City of London and return the Sum of their Quantities.

If you only wanted quantities for Invoices in London for the product named "Sir Rodneys Scones", you could use:

=NL("Sum","Invoices","Quantity","City","London","ProductName", "Sir Rodneys Scones")

Can you guess how to write the function for the Sum of all Quantities greater than 5?

=NL("Sum","Invoices","Quantity","Quantity",">5")

You can specify up to ten filters. If you wanted the sum of the Invoice Quantities for the product named "Sir Rodneys Scones" with Quantity greater than 5 in London, you could use:

=NL("Sum","Invoices","Quantity","City","London","ProductName","Sir Rodneys Scones", "Quantity",">5")

If you wanted to know how many Invoices had Quantities greater than 5 in London you could use:

=NL("Count","Invoices","Quantity","City","London","Quantity",">5")

 


Was this article helpful?
3 out of 3 found this helpful
Have more questions? Submit a request

Comments