I have been trying to merge two tables that have the same fields into one output. I know I can create two NL Filters and then use the NP Union but this would only combine one field from both tables into a list. I would like to merge (not link) multiple fields from two tables so that it would be something similar to the TSQL Union query. Is this possible in JET?
For example I would like to merge the following fields from the [Sales Line] table and the [Sales Invoice Line] table:
Type
No.
Sell-to Customer No.
SalesPerson Code
Amount
Productline Code
Date
Votes
13 comments
-
Jet Reports Historic Posts Hi,
you can reach that with "Link=" in the NL-function.
I can give you an example, when you edit, to which table the fields are belonging (for each field), and maybe add some filters…
regards
jetsetter -
Jet Reports Historic Posts Hello JetSetter,
If you can I would like to see an example. I thought that link would only bring back records from the main table but perhaps I am mistaken. Thanks! -
Jet Reports Historic Posts Hi, no Problem…
but please send the filters you want to set. That is important to get the right tables to start with…
regards
jetsetter -
Jet Reports Historic Posts I would like to merge the following fields from the [Sales Line] table and the [Sales Invoice Line] table:
No.
Sell-to Customer No.
SalesPerson Code
Amount
Productline Code
WHERE
Type = "Item"
Document Type = "Order" -
Jet Reports Historic Posts Hi,
Some more questions.
Do you want to use any filters?
Where is the Field "No" from => Sales Line or Header?
Is the Product Line Code part of the sales lines?
regards
jetsetter -
Jet Reports Historic Posts Hi JetSetter,
The only filter I need is where Type = Item. I don't need any links as I am simply trying to combine two tables. I may just need to do an NL Rows on two different sheets and then combine them on a third sheet.
Sorry, we are using Shortcut Dimension 2 Code as the ProductLine Code in the Sales Line table.
Thanks for the help. -
Jet Reports Historic Posts hi,
you can build rows from the sales line, with filter on "type"Item" and "document type" order". in the row you can show the fields you want to show from the sales line, like item.no, dimenion2 and so on with the NF function.
also give out the "documenttype" and the" document no ".
then, in the same row, make a "NL"first" oh the sales Header table with filtering on "documenttype" and the "document no" with the cells of you sales line.
then, with "NF" get out the fields you need from the sales Header….
is that ok for you?
regards,
jetsetter -
Jet Reports Historic Posts Hi JetSetter,
That is not exactly what I am looking for. Sorry if I muddied up the water and added confusion. Lets forget about the tables I mentioned and all the filtering and linking…
Suppose table A has the same fields as table B. Also, suppose that table A has 10 rows and table B has 5 rows. I simply want to combine those two tables and return 15 rows.
Does that sound doable?
I appreciate all your help. -
Jet Reports Historic Posts Hi,
OK, what about this:
eg, in row 5=>NL(rows=2) with table salesline and (NF) with saleslines-fields, including "doc.Type" and "doc.no" in eg cells B5 and C5
then in row 6 => get in cell B6 and C6 from row above, then NL (first) with the table salesheader, and filter from B6 and C6 and (NF) with the sales Header fields..
you can also Change the rows (first Header with rows=2) then row with sales lines ….
is this the right direction?
regards -
Jet Reports Historic Posts Hi JetSetter,
No it is not the correct direction as I am looking at the Sales Line and Sales Invoice Line tables. Not the Header. I just want to combine two tables that have the same fields.
Here is the SQL equivalent of what I am asking…
SELECT [Sell-to Customer No_] as "Customer No",
(SELECT Name From [Customer] Where [No_] = [Sell-to Customer No_]) As "Customer Name", No_ as "Item No.",
(SELECT Name From [Salesperson_Purchaser] Where Code = [Salesperson Code] ) As "Sales Person",
[Shortcut Dimension 2 Code] as "PL Code"
FROM [Sales Line]
Where [Type] = 2 AND [Document Type]=1 –ITEM(2)
UNION ALL
SELECT [Sell-to Customer No_] as "Customer No",
(SELECT Name From [Customer] Where [No_] = [Sell-to Customer No_]) As "Customer Name", No_ as "Item No.",
(SELECT Name From [Salesperson_Purchaser] Where Code = [Salesperson Code] ) As "Sales Person",
[Shortcut Dimension 2 Code] as "PL Code"
FROM [Sales Invoice Line]
Where [Type] = 2 -
Jet Reports Historic Posts hi,
please check the example ;)
(in the sales invoice lines, there is no doc.-type "order", so i left the filter to that field in that table on blank)
regards
jetsetter -
Jet Reports Historic Posts Hi JetSetter,
Thanks for taking a stab at this however you are simply adding two NL Rows beneath one another. That is not exactly merging the two tables into one NL Row. -
Jet Reports Historic Posts hi,
you can put the code in one row, no problem…
change the (rows=2) to (row) an put the code from the second row in the first row.
(I don't have access to jetreports the next days, so i can't edit my example.)
regards
jetsetter
Please sign in to leave a comment.