Skip to main content
insightsoftware Docs insightsoftware Docs
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

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=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.

Published:

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=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.

For an optimal Community experience, Please view on Desktop