When writing reports, you may find that you need to filter your data based on information stored in a separate table. Essentially, there are two ways in Jet Reports to do this; by using the Jet function NL(Filter) or by using LINK=
Let's explore how both these options work, how to use them, and advice on which one to use.
NL(Filter)
The NL(Filter) function returns a string that can be used as a filter in another NL function.
The following formula returns a list of records from the Sales Line table:
=NL("Rows","Sales Line")NL(Filter) can be used to link this to the Customer table in order to filter based on information within that table. Let's filter to only show sales lines for a particular market sector:
=NL("Rows","Sales Line",,"Sell-to Customer No.",NL("Filter","Customer","No.","Market Sector","MEDICAL"))
The inner NL(Filter) function returns an array of the values of the Sell-to Customer No. field where the market sector field is "MEDICAL". This information isn't in the Sales Line table, but is stored on the customer card. We're using NL(Filter) to return this information as a string and feed it into the filter part for our NL(Rows) formula.
If you'd prefer, this can be written as two separate NL formulas like below:
LINK=
Let's say that we want to display a list of all invoice numbers since 2020 that have G/L entries rather than just item sales entries. The invoice number is stored in the Sales Invoice Header table, but the entry type is stored in the Sales Invoice Line table. We can use LINK= in our NL(Rows) formula to be able to link these two tables:
=NL("Rows","Sales Invoice Header","No.","Posting Date","1/1/2020..","LINK=","Sales Invoice Line","Document No.","=No.","Type","G/L")Note - LINK= requires a unique value connection in order to link two tables together. This is similar to the Primary Key/Foreign Key relationship when working with SQL statements.
There's a LOT more that LINK= can do but there's a separate blog post coming for that. For now, we'll continue on how this differs from NL(Filter) and how to choose which one you should be using.
Which one to choose?
Both of these functions result in essentially the same desired outcome. The most important deciding factor on which to choose usually comes down to performance. The general rule to go by is:
- NL("Filter") should be used to filter one table based on data from another table that is of smaller or equivalent size
- "Link=" should be used to filter one table based on data from a larger table
In the LINK= example in this post, the Sales Invoice Header table is being filtered by results from the Sales Invoice Line table. The Sales Invoice Line table is larger than the Sales Invoice Header table, so Link= is the right way to filter here, rather than NL(Filter).
The case where NL(Filter) is faster would be where the set returned by the NL(Filter) was only a few items, say a 15 or less. In this case, it's probably faster to compare each record being returned to those agaisnt those 15 items than to do a subquery to a different table. This is still dependant on the setup of your ERP, what the table structure is like, the other filters you have in the formula, etc. As you can see, it's not neccessarily a straight answer and is worth thinking about carefully when writing filters in your Jet Reports.
❤️ Enjoyed this article?
Forward to a friend and let them know where they can subscribe (hint: it's here).
