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:
- An order line was received in 2+ parts, and each receipt line still has its own invoice line. In this case, the [Receipt No_] column is empty, but I can backfill it by looking in the
Purch_ Rcpt_ Line table based on the [Order No_] value.
- An order line was received in 2+ parts, but 2+ receipt lines share the same invoice. In this case, it makes sense that there is no single value for [Receipt No_]. Yet, viewing the receipt line in BC and clicking "Item Invoice Lines" correctly presents the invoice. Likewise, viewing the invoice line and clicking "Item Receipt Lines" also presents the multiple receipts.
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.
