Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet Reports Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give 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.

LINK= Examples

  • Simple Linking

    Simple Linking

     

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

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

    This function 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 by) 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")

    In this example, we need both the Order No. fields and the Order Line No. fields to define the link between our two tables. 

  • Nested Linking

    LINK= statements can be combined to fulfill more complex filtering requirements.

    Nested Linking

    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.

    Linking Multiple Tables

    Let's assume that we 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.

    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")

    NL_Link.png

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

Comments

  • Avatar
    Jouhayna Zeidan

    Good explanation