Josh,
Adding to this thread to help...
=NL (What, Table, Field, FilterField1, Filter1, ..., FilterField10, Filter10)
Purpose: Retrieves individual fields or record keys from your database, based on filters that you provide.
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.
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".
This NL 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","Customers",,"Balance","<0","City","Boston")
Record keys are then referenced by NF() functions to retrieve individual field values
This NL returns the Customer Name from sales quote number 10000. This NL can only return one record, so the 'What' parameter of "First" is used:
=NL("First","Sales Header","Name","No.","10000","Document Type","Quote")
This NL returns information for a company (regardless of the default company shown on the Jet ribbon):
=NL("Rows","Customers",,"Company=","CRONUS USA, INC.")
This NL returns information for a specific company and data source (ignoring the default company & data source):
=NL("Rows","Customers",,"Company=","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"})
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")
Hope this helps.
Thanks,
Steve
