Hi All, I'm new here, i will need create a sales data customer with delivery man, but I'm not sure how to join both Sales Invoice Line and Sales Cr.Memo Line table. Anyone can help? The format i need is:
B2 = Delivery Man Code
C2 = Name of Delivery Man
D2 = Customer No. (Filtered delivered by B2)
E2 = Sum of Amount (Filtered Customer delivered by B2)
Date
Votes
4 comments
-
Jet Reports Historic Posts Hello Kyorene,
I am a little unclear as to how you need to link the Sales Invoice Line to the Sales Cr.Memo Line table. There isn't a direct link. Are you actually trying to get a combined list from both tables? Like all the sales and credit memos for a period? If so, you simply list the sales and then the credits with 2 NL(Rows) functions.
Is this what you are looking for? -
Jet Reports Historic Posts Dear The Kloser,
Thanks for your reply, i actually need to get the data from both sales invoice line and sales cr.memo line, i need to pull the delivery man's sales from this two table, i not sure how to combine two table's data, if using 2 NL(Row), i can get the figure correctly. As in the same customer may deliver by different delivery man. -
Jet Reports Historic Posts I had a similar need (but for Vendors). I used a Report instead of a Table, then I use a Pivot Table to summarize the data.
1st Row (pulls Vendors rows) =NL("Rows=3","Vendor Ledger Entry",,"Filters=",$D$4:$E$6) [note that my filters are in $D$4:$E$6]
2bd Row (pulls Invoice rows) =NL("Rows","Purch. Inv. Line",,"Document No.",$H11,"No.",$E$7) [The first Row had the Document No. in cell $H11]
3rd Row (pulls Cr Memo rows) =NL("Rows","Purch. Cr. Memo Line",,"Document No.",$H11,"No.",$E$7) [The first Row had the Document No. in cell $H11]
I use "IFERROR" functions to blank out cells that return errors (it seems to help run speeds).
Hope this helps! -
Jet Reports Historic Posts Hi njvolz,
Thanks for your reply, but what should i input the filter? As our data is really big, if pull the document no. may need take up a lot of running time.I had a similar need (but for Vendors). I used a Report instead of a Table, then I use a Pivot Table to summarize the data.
1st Row (pulls Vendors rows) =NL("Rows=3","Vendor Ledger Entry",,"Filters=",$D$4:$E$6) [note that my filters are in $D$4:$E$6]
2bd Row (pulls Invoice rows) =NL("Rows","Purch. Inv. Line",,"Document No.",$H11,"No.",$E$7) [The first Row had the Document No. in cell $H11]
3rd Row (pulls Cr Memo rows) =NL("Rows","Purch. Cr. Memo Line",,"Document No.",$H11,"No.",$E$7) [The first Row had the Document No. in cell $H11]
I use "IFERROR" functions to blank out cells that return errors (it seems to help run speeds).
Hope this helps!
Please sign in to leave a comment.