Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Feedback

Using Link=


Overview

LINK= gives users advanced filtering capabilities in Jet Professional.  Using LINK= allows users to tie together information from different tables.

LINK= is available for the following connector types: Dynamics NAV 2013 or later, Dynamics NAV 2009R2 and earlier, Dynamics GP, Other (Universal), and Jet Data Warehouse.

 

Simple Linking

 

The following formula will return the document number for each Sales Invoice Header in the system.

=NL("Rows","Sales Invoice Header","No.")

 

The following formula will return only sales invoices where the posting date is within a specific data range.

=NL("Rows","Sales Invoice Header","No.","Posting Date",">1/1/2009"

 

LINK= can be used to filter on a field that is not in the Sales Invoice Header table (such as the "Type" field in the Sales Invoice Line table) as follows:

=NL("Rows","Sales Invoice Header","No.","Posting Date",">1/1/2009","LINK=","Sales Invoice Line","Document No.","=No.","Type","Item")

 

In this example, we used LINK= and specified the Sales Invoice Line table because this is where the “Type” field that we want to filter exists. We linked to the Sales Invoice Header by specifying that the “Document No.” field on the Sales Invoice Line matches the “No.” field on the Sales Invoice Header. We then specified the “Type” filter on the Sales Invoice Line table.

Please note that some relationships between tables may require more than a single field to define the link:

=NL("Rows","Purch. Rcpt. Line",,"Link=","Purch. Inv. Line", "Order No.","=Order No.","Order Line No.","=Order Line No." ,"Posting Date",">1/1/2009")

 

Nested Linking

 

LINK= statements can be combined to fulfill more complex filtering requirements. For example, assume that you would like to see the territories with sales during a given period.

A simple formula could be used if the territory is available on the table that contains the historical sales information and if you wanted the territory assignment at the time that the sale was made.

If, on the other hand, the territory is not available, or you want the currently assigned territory, you can do this by combining LINK= statements as follows:

=NL("Rows","Territory","Code","Link=","Customer","Territory Code","=Code","Link=","Sales Invoice Header","Sell-to Customer No.","=No.","Posting Date",">1/1/2009")

 

In this example we linked the Territory to the Customer and the Customer to the Sales Invoice Header. Then we filtered by the “Posting Date” field to get territories with sales for a specific period.

 

 

Linking Multiple Tables

 

LINK= statements can also be combined to handle situations where a single table is linked to multiple tables. For example, want to see sales invoices where the territory is “SouthEast” and the vendor is “30000”. To do this we can link from the Sales Invoice Line table to the Customer table with the “ field filtered for “SouthEast” and, in addition, link from the Sales Invoice Line table to the Item table with the “Vendor No. filtered for “30000”.

=NL("Rows","Sales Invoice Line",,"Type","Item","Link=Sales Invoice Line","Customer","No.","=Sell-to Customer No.","Territory Code","SouthEast","Link=Sales Invoice Line","Item","No.","=No.","Vendor No.","30000")

 

Note that the Link= statements include the primary table (Sales Invoice Line) which indicates that links should restart from the primary table rather than linking in the nested fashion demonstrated in the previous section. It is possible to mix these models and have multiple links as well as nested links.

One other thing to note is that filters applied to the primary table (like the filter on the "Type" field in this example) should occur before any LINK= statements.


NL("Link")

The NL(Link) function can be used to specify linked tables when more than 10 parameters are needed for a linking statement.

This example formula was used in the nested link section and links from the Territory table to the Customer table and then from Customer table to the Sales Invoice Header table.

=NL("Rows","Territory","Code","Link=","Customer","Territory Code","=Code","Link=","Sales Invoice Header","Sell-to Customer No.","=No.","Posting Date",">1/1/2009")

  A B C D
1 Auto+Hide      
2   =NL("Rows","Territory","Code","Link=",C2) =NL("Link","Customer",,"Territory Code","=Code",Link=",D2) =NL("Link","Sales Invoice Header",,"Sell-To Customer No.","=No.","Posting Date",">1/1/2009")
3        

 

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

Comments

  • Avatar
    Jouhayna Zeidan

    Good explanation