You’re offline. This is a read only version of the page.
4,359
Hi Copmmunity,
May I know in Jet report, what does this means?
Thank you!
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
Hi, Just to add a little information, there are some examples in the link below for reference.
https://support.jetglobal.com/hc/en-us/articles/218953168-NL-Function-Reference
Hope this helps.
Thanks.
ZHU
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (