Sign Up for Training |
Jet Global Company Site
Community
Downloads
Submit a Request
Give Feedback

Using InclusiveLink=


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= or InclusiveLink=.  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 InclusiveLink=, it is valid.

 
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments