Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Introducing the NL Function


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

Help Topics

NL Function Ref
Filtering

 

With Dynamics NAV, the following example of an NL function retrieves the balance of customer "10000".

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

With Northwind, 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 Professional 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 not used, so you enter a comma to move to the second 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 Designer to drag the "Phone" field out of the "Customers" table with the Designers Return option set to "One". You now have an NL function without the filters. You can add the filters using the Excel Function Wizard (Jfx).

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

What do you think the following function 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 Professional will return the name of the first customer it finds in California. If you omit the first parameter, 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.

 
More Northwind 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")

 
 
More Dynamics NAV Examples

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

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

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

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

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

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. 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.

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

Comments