I am trying to use Jet Reports to access data from multiple tables with a linked column. For example, the table “Aircraft” has the column, “Aircraft ID” and the table “Quotes” has the column “Aircraft ID”. I would like to pull “Tail Number” from Aircraft and “Confirmation Number” from Quotes. Using sql it would be similar to “Select ‘Tail Number’, ‘Confirmation Number’ from Aircraft a inner join Quotes q on a.aircraft ID = q.aircraft ID”
Using Jet Reports documentation I created this query:
=NL("Table","Aircraft",{"Tail Number","LinkField([Quotes - Original],[Confirmation Number])"},"InclusiveLink=","Quotes - Original","Aircraft ID","=Aircraft ID")
But received the following error:
“Inclusive Links are not supported in the Universal data source”
Can you not pull data from multiple tables with a Universal Data Source or is there another way?
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment Hi,
LinkField is not yet implemented in the Universal data source, so you can't do this with an NL(Table) yet with a Universal data source. Probably this will happen in the future, but for now that funcitonality is NAV only. You can of course pull data from multiple tables in a Universal data source using NL(Rows) and NL(First) formulas. You could do something like this:
C3:=NL("Rows","Aircraft",{"Aircraft ID","Tail Number"})D3:=NF(C3,"Aircraft ID")E3:=NF(C3,"Tail Number")F3:=NL("First","Quotes - Original","Confirmation Number","Aircraft ID",D3)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks that goes a long way. Jet Reports doesn't work like any other reporting tool that I have used before.
However first wouldn't work. Specifically in this case it would only return the first confirmation number it came across. Aircraft
I changed "First" to "Rows" and it seems to have given me the one to many relationship I was looking for.
I should probably add a date filter shortly so that the reports do not take long to run as I am still learning/testing various code.
I assume it Jet Reports processes functions left to right? -
Jet Reports Historic Posts Aaah, yes sounds like you figured it out. With a one to many relationship like that you would need to use NL(Rows) so that for each row of the outer (left) replicator it will replicate multiple rows of the inner (right) replicator. You are correct in saying Jet expands the replicators left to right. You can also use Rows=x where x is the number of Excel rows to copy for each database result (e.g. =NL("Rows=3",…) to copy 3 Excel rows, the current row and the 2 below it). Then your inner replicator doesn't have to be on the same row as the outer one.
Regards,
Hughes
Please sign in to leave a comment.