In the first example below, I am pulling a customer number, "No.", from the Customer table that has an entry in the Sales Invoice Line table with an Item No. = 40* and a Posting Date = 10/13/2011 in the Sales Invoice Header table.
The second example is the same except it is looking for the item number and date filters in the Sales Cr.Memo Line and Sales Cr.Memo Header tables.
In both examples I am matching "No." with "Sell-to Customer No." in the other tables.
Is there a way to combine the two. In other words, can I pull "No." from the Customer table that has entries in Sales Invoice Line/Sales Invoice Header or
Sales Cr.Memo Line/Sales Cr.Memo Header. I would need to pull customer numbers that have entries in the invoice tables even if they don't have entries in the Cr Memo tables. I would also need customer numbers that have entries in the Cr Memo tables even if there are no entries in the invoice tables. Plus, I would need all customer numbers with entries in both groups.
This would not be a difficult query in SQL. Can I do it with Jet Reports?=NL("Rows=5","Customer","No.","Link=Customer","Sales Invoice Line","Sell-to Customer No.","=No.","Item No.","40*","Link=Customer","Sales Invoice Header","Sell-to Customer No.","=No.","Posting Date","10/13/2011")=NL("Rows=5","Customer","No.","Link=Customer","Sales Cr.Memo Line","Sell-to Customer No.","=No.","Item No.","40*","Link=Customer","Sales Cr.Memo Header","Sell-to Customer No.","=No.","Posting Date","10/13/2011")
Thanks for any help.
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi David,
Yes you can do that with Jet. You just need to use the NP(Union) function. First change both your existing functions from "Rows=5" to "Filter". Then create a third function that looks like this (assuming your 2 previous functions are in C2 and D2):=NL("Rows=5",NP("Union",C2,D2))
That should replicate the union of those 2 functions. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Belated thanks, Hughes. I'm just now getting back to the report. That does seem to work. The NP(Union) function will be a big help in some other reports too.
Please sign in to leave a comment.