Hello Everyone.
Thanks for your time.
Last two days I am pushing my Jet Reports knowledge and imagination to the limits but still can't accomplish what I am trying to do. My feeling is that it is possible but not sure how. Can you help?
The report is for company that mostly does drop shipping. So they nave matching Sales Invoices (say, Item A, 2kg) and Purchase Invoices (Item A, 2kg). The "connecting" link between Sales and Purchase Invoices is the PO number that is present on both.
The report I built (enclosed) lists those AP invoices, where related AR invoices have been paid in full but the AP invoice hasn't. Everything works fine if the PO number is not shared between two Sales Invoices and two Purchase Invoices. The problem is that sometimes the PO is created but several Sales and Purchase Invoices are created based on that. in that case the PO number is shared and report gives wrong results.
I want to bring "Quantity" to equation, but not sure how. My report lists invoices from Sales and Purchase Invoice Header tables, and I need somehow to filter the results based on Invoice Line Tables and match the quantities. To make it clearer, here is an example:
PO # is 55 and has only one related Sales and Purchase Invoice. My report will scan the Sales Invoice Header to find if the Sales Invoice with PO 55 has "Remaining amount = 0" and search if corresponding Purchase Invoice is still unpaid (Vendor Ledger Entry, Open=TRUE)
But if PO # 55 has more than one related Sales and Purchase invoices, my report doesn't work. That is why I also want to match quantities, because quantities will be unique to each Sales/Purchase Invoice pairs, even though they share the same PO#.
Please let me know if you need more details. Thank you!
Date
Votes
3 comments
-
Jet Reports Historic Posts Bump :)
-
Jet Reports Historic Posts Just taking a stab at answering this, but it seem to me that part of your issue is that your formula for Sales Invoice Header is only looking for the "First" record. I think in this case that you will need to do multi-level grouping so that the report will return a separate list of the rows from the sales invoice header that meet the criteria rather than just the first record that meets the criteria.
That way, you can do have the report display rows where the AP invoice has not been paid, followed by a second level of rows of AR Invoices where the AR remaining amount is 0 and the PO number matched the Order No. in the AP invoice. This will result in however many AR invoices apply to the original PO.
If you are not familiar with using multi-level grouping, there are some tutorials in the Knowledgebase.
Hope this helps somewhat.
Jill -
Jet Reports Historic Posts Thank you, Jill.
Yes, I am familiar with multi-level grouping. I will look into your suggestion and let you know the results!
Please sign in to leave a comment.