Hello,
I am fairly new to to JET and am trying to figuer out how to do a simple join in JET. I have read several posts on the topic but did not understand as they seemed specific to the case. I just need to join data from two tables. One table called "Shipment" the other "Vendors" joined with the field "Booking Agent Code" in "Shipment" and "No." in the "Vendor"table. Is it really that difficult to "Join" in JET? Sure will be glad when I am back on a SQL project~! Any help appreciated!!!
thanks!
K
Date
Votes
9 comments
-
Jet Reports Historic Posts Hello,
To join tables in Jet Reports, you use "Link=". The structure of this formula could vary, depending on how you want to display the data. If you are looking to return a list of vendors and their shipment records, you would set up separate NL functions like so:
In cell B2: =NL("Rows=2","Vendors",,"Link=","Shipment","Booking Agent Code","=No.")
–this will retrieve a list of vendor records which have entries in the Shipment table; add "Hide" in cell A2 to hide this column from display–
In cell C2: =NF($B2,"Name")
–this will display the name of the vendor–
In cell D2: =NF($B2,"No.")
–this will display the vendor number, you may want to put "Hide" in Cell A3 to hide this column if you don't want the number to display–
In cell D3: =NL("Rows","Shipment",,"No",$D2)
–this will retrieve a list of shipment records for that particular vendor, again, hidden from view due to the "Hide" entered in A3–
In cell E3: =NF($D3,"Field1FromShipmentTable")
–this will display the field referenced from the Shipment table; add more NF functions in cells F3..x3 you wish to display–
For more details on this technique, please reference the Jet Reports Help under "Structuring Reports" –>"Grouping and Subtotaling Tutorial".
Hope this helps. Please let me know if you have any other questions. -
Jet Reports Historic Posts Thanks for the help. However I am unable to use the fields from both tables for my query? I need to use the "created date" from the "shipments" table and "state" from the vendor table to get my list.
It is a simple list that will display a list of shipments from the shipments table where the "Booking Vendor" is located in "State" "X, Y and Z" where the "Created Date" is > 1/1/2011
the problem is the "Created Date" is in the "shipments" table and the "State" is in the "vendors" table. I hate to ask for more help but………….how do I use both to compile my list?
thanks for any you can offer!
regards,
K -
Jet Reports Historic Posts You can include filters for both the primary and the linked table in the first NL function. Any filter declared after the "Link=" will apply to the linked table.
That means that for a grouping report including the vendor name, your first NL function would become:
=NL("Rows=2","Vendors",,"State","X|Y|Z","Link=","Shipment","Booking Agent Code","=No.","Created Date","1/1/2011..")
This should limit the data to only the records you are interested in seeing.
If you only interested in displaying records from the "Shipment" records (meaning without any fields from the "Vendors" table such as vendor name), you could do all your filtering in a single NL function. Your primary and linked table and their filters would simply switch places, since the information you want to display will come from the "Shipment" table rather than the "Vendors" table:
=NL("Rows","Shipment",,"Created Date","1/1/2011..","Link=","Vendors","No.","=Booking Agent Code","State","X|Y|Z")
From there, you would use NF functions to display the fields from the "Shipment" table you want to include in your report.
Please note that in order to return fields from both tables, you will still need to include two NL functions in your report (with the additional filter applied in my first example above).
Please let me know if you need any further information, I'm happy to help! -
Jet Reports Historic Posts Is it possible to link three tables? ie;
=NL("Rows","Shipment",,"Global Dimension 1 Code","20","Service Type","<>Storage","Company=",$B$2,"Link=","Shipment Ledger Entry","Shipment No.","=No.","Posting Date",Options!$D$7,"Link=","Vendor","No.","=Booking Agent Code","State",Options!$D$5) -
Jet Reports Historic Posts Yes, it's definitely possible. Normally, a "Link=" argument specifies a link on the most recently declared table. So the way you have it structured:
=NL("Rows","Shipment",,"Global Dimension 1 Code","20","Service Type","<>Storage","Company=",$B$2,"Link=","Shipment Ledger Entry","Shipment No.","=No.","Posting Date",Options!$D$7,"Link=","Vendor","No.","=Booking Agent Code","State",Options!$D$5)
Will link the "Vendor" table from the "Shipment Ledger Entry" table. This is how you specify nested links.
If you would like to link the Vendor table back to the Shipment table, you would specify the Shipment table directly after the "Link=" for the "Vendor" table:
=NL("Rows","Shipment",,"Global Dimension 1 Code","20","Service Type","<>Storage","Company=",$B$2,"Link=","Shipment Ledger Entry","Shipment No.","=No.","Posting Date",Options!$D$7,"Link=Shipment","Vendor","No.","=Booking Agent Code","State",Options!$D$5)
Hope this helps! -
Jet Reports Historic Posts It says "Invalid Field "Link=Shipment"" It must be something with the syntax? I even tried pasting in the example you gave, any ideas? thanks for your help.
K -
Jet Reports Historic Posts What version of Jet Reports are you running? Multi-linking is only available in Jet Reports 2009 and above.
-
Jet Reports Historic Posts ver. 7.1.2 Is that compatible?
-
Jet Reports Historic Posts Unfortunately, only one Link= argument is allowed in Jet Reports version 7.1.2. Multi-links and nested links were both introduced in Jet Reports 2009.
I'm sorry I didn't ask you your version information before tempting you with those features!
Please sign in to leave a comment.