Use Inclusive Link
Overview
The InclusiveLink= filter for the NL(Table) function allows you to retrieve fields or sums from a linked table, while not forcing the primary table to be filtered by the linked records.
Using InclusiveLink
For example, suppose you had the following tables:
Customer
| No. |
|---|
| 1 |
| 2 |
| 3 |
Cust. Ledger Entry
| Entry No. | Customer No. | Amount |
|---|---|---|
| 1 | 1 | 42 |
| 2 | 1 | 38 |
| 3 | 3 | 100 |
If you wanted to retrieve customers and get the sum of amounts for each customer from the Cust. Ledger Entry using Link= , you could create this formula:
=NL("Table","Customer",{"No.","LinkSum([Cust. Ledger Entry],[Amount])"},"Link=","Cust. Ledger Entry","Customer No.","=No.")
In this case you would receive the following results:
| No. | Cust. Ledger Entry - Amount |
|---|---|
| 1 | 80 |
| 3 | 100 |
However, you may want to view all customers regardless of whether they have a ledger entry. In this case, you could use InclusiveLink= since you want to retrieve sums from the linked table, but not filter by it :
=NL("Table","Customer",{"No.","LinkSum([Cust. Ledger Entry],[Amount])"},"InclusiveLink=","Cust. Ledger Entry","Customer No.","=No.")
| No. | Cust. Ledger Entry - Amount |
|---|---|
| 1 | 80 |
| 2 | 0 |
| 3 | 100 |
Notice that Customer 2 was included in the results even though it did not have any records in the Cust. Ledger Entry table.
Default Values when using InclusiveLink=
If a record does not exist on a linked table when using InclusiveLink= , you will receive a default value for linked fields or sums. For fields from a linked table, the default value is always blank (an empty string). For sums from linked tables, the default value is 0.
Limitations of InclusiveLink=
InclusiveLink= is only valid in NL(Table) , NL(Lookup) , and NL(Link) functions. There is no reason to ever use InclusiveLink= unless values are being retrieved from a linked table (using LinkSum() or LinkField() ).
Since LinkSum() and LinkField() can only be retrieved using NL(Table) and NL(Lookup) , these are the only functions where you can use InclusiveLink=
NL(Link) functions can also contain InclusiveLink= when they are being referenced by NL(Table) and NL(Lookup)
InclusiveLink= cannot be nested inside Link= and vice versa. The entirety of a link chain must be either Link=orInclusiveLink= . However, Link= and InclusiveLink= can appear in the same NL function if the function contains multiple links that are not nested.
For example, the following function is valid:
=NL("Table","Customer",{"No.","LinkSum([Cust. Ledger Entry],[Amount])"}, "InclusiveLink=","Cust. Ledger Entry","Customer No. ,"=No.", "Link=Customer","Customer Posting Group","Code","=Customer Posting Group")
Because the Link= is linking from the base table (Customer), rather than being nested inside the I nclusiveLink= , it is valid.