You’re offline. This is a read only version of the page.
25
I'm querying BC for a report presenting all Purchase Orders with their Receipts and Invoices. I cannot find how to relate an invoice line to its receipt lines, when that invoice lines pays for multiple receipt lines.
I built the following data model to connect order lines, receipt lines & invoice lines together. Note the PurchaseOrderRowKey, PurchaseReceiptRowKey columns that serve as foreign keys and enabling the 1:many relationships.
I then noticed that not every invoice line has a value for [Receipt No_] in the Purch_ Inv_ Line table. This means the report shows orders & receipts without their corresponding (but existing) invoices. I identified 2 cases when this happens:
Share
ReportPurch_ Rcpt_ Line table based on the [Order No_] value.I am looking for how to correctly establish the relationship between an invoice line and its multiple receipt lines for case (2), like BC appears able to do.
A potential approach could be to duplicate the invoice line into as many lines as there are corresponding receipt lines. The quantity of each invoice line would need to be adjusted to reflect the corresponding receipt line quantity, until the invoiced quantity is exhausted (i.e., there is not necessarily enough invoiced quantity to cover all receipt line quantities). That said, it leaves up to chance the way to select which receipt lines get chosen first. Is there a better way?
Alternatively, I can change the data model to have Purchase Receipt and Purchase Invoice both relate directly to Purchase. With this, every receipt and invoice get counted, although I am not able to relate Invoices to Receipts which is highly desirable.
Hi IBU,
To get Purchase Order of Posted Purchase Invoice.
Please follow below steps.
1) On Value Entry Page.
-> For Document type as "Purchase Invoice" on this line you can fine Item Ledger entry no.
-> Use that no. and put filter on Item Ledger entry table on Entry no. field you will get line where Document type as "Purchase Receipt".
-> On this Posted Purchase Receipt header you will get Purchase Order no.
Hope this will help.
Many Thanks,
Manan
Build a list of POs numbers from Posted Purchase Receipts, then to connect the receipts to the invoices you have to go through the value entry table. Value Entry table filtering for the item ledger entry type of Purchase, Document Type of Purchase Receipt.Value Entry table filtering for the item ledger entry type of Purchase, Document Type of Purchase Invoice.
What is the purpose of such a report?
Kim Dallefeld Amit_Sharma I took a look at ILE and VE and I don't see how they can be used here. As a cursory check, I queried ILE for [Document Type] = 6 (Purchase Invoice), and there are no rows. There's also no Document Type] enum value for Purchase/Order.
To restate my goal, it is to build a visual like this, where for each Vendor it shows the list of Purchase Orders (CAxxx), and for each Purchase Order, the list of Receipts (RCxxx, if any), and for each Receipt, the list of Invoices (FAxxx, if any). This is after filtering all 3 types of documents based on their "Document Date".
So far the Purchase Line/Purchase Receipt Line/Purchase Invoice Line tables have gotten me 99% there, I just noticed some Invoice Lines don't have a relation to the receipt, and as such are excluded from the report.
Hi,
Use ILE and VE for achieving this.
Regards
Amit Sharma
www.erpconsultors.com
Thanks Kim Dallefeld, I will take a look at those tables and reply back.
I must be able to present the Ordered / Received / Invoiced data from the point of view of any date in the past. Will using those tables you mention allow to reconstruct totals as of a specific date?
When I did this for Accounts Receivable/Payable, I had to use the Detailed Customer/Vendor Ledger Entry tables to recalculate totals from any point in time (vs. using the "current" calculated amounts in the regular C/V Ledger Entry tables). Wondering if there's a similar distinction here.
I would suggest using item ledger and value entries. Using receipt lines and invoice lines is virtually impossible. With Business Central automatically doing the matching at the item ledger and associated value entries, why reinvent the wheel.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,522
Super User 2024 Season 2
#2
Martin Dráb
228,441
Most Valuable Professional
#3
nmaenpaa
101,148
All responses (
Answers (