Jet Professional adds the |

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

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

The function tells Jet Professional to return the Balance field in the Customer table for the record that has a customer No. of "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.

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")**

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

Here is the function.

** =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")**

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.

## Comments