Filtering Based on Data from Another Table

In this example, the Order Subtotals table does not have the OrderDate in it, but the Orders table does.  The OrderID is common to both tables, so if you wanted to list Order Subtotals based on an OrderDate, you would start out with an NL function like:

=NL("Rows","Order Subtotals","Subtotal","OrderID",... {List of OrderIDs with OrderDates between 7/1/19 and 7/31/19} ...)

In the function above, you need an NL function which will replace the description of the OrderID list.

You can use NL("Filter") to create the OrderID filter from the Orders table as shown here:

NL("Filter","Orders","OrderID","OrderDate","7/1/19..7/31/19")

Finally, you need to replace the description in the first function with the second function:

=NL("Rows","Order Subtotals","Subtotal","OrderID",NL("Filter","Orders","OrderID","OrderDate","7/1/19..7/31/19"))

The inner NL function returns a list of OrderIDs that the Jet Excel add-in can use as a filter for the Order Subtotals table.

Dynamics NAV and Business Central

With Dynamics NAV and Business Central, the Jet Excel add-in has two mechanisms to help you do this, "Filter" and "Link=".  While both mechanisms return the same result.  Which one you should use depends on what tables you are using.

"Filter" should be used in situations where the primary table is larger than (or of equivalent size to) the secondary table (e.g., the Sales Line table filtered by the Sales Header table).

"Link=", on the other hand, will greatly increase performance when the primary table is smaller than the secondary table (e.g., the Dimension table filtered by the G/L Entry table).

When performing cross-table filtering, you may not be able to drill down on the cell. That is because NAV and Business Central will not accept a list as a filter, so the Jet Excel add-in attempts to create a set of filters that will uniquely select the exact same records.  This is often possible when there are only a few records selected.  However, if there are too many records, the Jet add-in will report that drilldown is not possible.

As stated above, NL("Filter") should be used when you would like to filter one table based on data from another table that is of smaller or equivalent size (if this is not the case, please use "Link=").

For example, the Sales Line table is generally larger than the Sales Header table, since each Document can have several lines associated with it but only one header.

Typically, the Sales Line does not have the Posting Date in it, but the Sales Header does.  The Document Number is common to both tables, so if you wanted to list Sales Lines based on Posting Dates, you would start out with an NL function:

=NL("Rows","Sales Line",,"Document No.",... {List of Document Numbers with Posting Dates between 1/1/20 and 1/31/20}...)

You need to replace the description of the list in the function above with an NL function.  The field in the Sales Header that holds the document number is "No.", so the NL function that generates a document number filter is:

=NL("Filter", "Sales Header", "No.", "Posting Date", "1/1/20..1/31/20")

Finally, you need to replace the description in the first function with the second function:

=NL("Rows","Sales Line",,"Document No.",NL("Filter","Sales Header","No.","Posting Date", "1/1/20..1/31/20"))

 The inner NL function returns the Document No. filter that is then used in the Sale Line table.

"Link=" is another mechanism that can be used to filter data in one table based on data in a related table.

Specifically, "Link=" should be used when the primary table is smaller than the secondary table.

For example, lets say you would like to create a list of invoice numbers that contain item sales. You can list the invoice numbers from the Sales Invoice Header table, but need to use the Sales Invoice Line table to ensure that each invoice contains an item sale.

Since all you would like to do is create a list of invoice numbers, you do not need a complete list of Sales Lines for each invoice.  Rather, all you want to know is whether an entry containing an item sale exists.  To do this, your function would look this:

=NL("Rows","Sales Invoice Header","No.","Posting Date","7/1/20..7/31/20","Link=","Sales Invoice Line", "Document No.","=No.","Type","Item")