Use Link

Overview

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

LINK= is available for the following connector types: Dynamics 365 Business Central, Dynamics NAV 2013 - 2018, Dynamics NAV 2009R2 and earlier, Dynamics GP, Other (Universal), and Jet Data Warehouse.

LINK= Examples

• 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/2019"

  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/2019","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/2019")

  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.

  

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

  In this example we linked the Territory to the Customer and the Customer to the Sales Invoice HeaderThen 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.

  

  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/2019","LINK=","Sales Invoice Line","Document No.","=No.","Type","Item")

  

LINK= does not support the use of CalcFilters.

For example:

=NL("Rows","Purch. Rcpt. Line",,"Link=","Purch. Inv. Line", "Order No.","=Order
      No.","Order Line No.","=Order Line No." ,"Posting Date",				class="wysiwyg-color-red90">"=""<>""&NF(,""Shipping
      Date"")"						)

CalcFilters require the use of the equal sign and that character can only be used in LINK= to indicate a linking field.