Skip to main content

Notifications

Business Central forum
Suggested answer

Relating a purchase invoice to its many purchase receipts

Posted on by 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.

pastedimage1670338900982v1.png

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:

  1. 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.
  2. 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.

  • Suggested answer
    Manan_Shah Profile Picture
    Manan_Shah 1,457 on at
    RE: Relating a purchase invoice to its many purchase receipts

    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

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 11,423 User Group Leader on at
    RE: Relating a purchase invoice to its many purchase receipts

    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?

  • Ibu Profile Picture
    Ibu 25 on at
    RE: Relating a purchase invoice to its many purchase receipts

    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".

    pastedimage1670421670444v1.png

    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.

  • Suggested answer
    Amit_Sharma Profile Picture
    Amit_Sharma 2,545 on at
    RE: Relating a purchase invoice to its many purchase receipts

    Hi,

    Use ILE and VE for achieving this.

    Regards

    Amit Sharma

    www.erpconsultors.com

  • Ibu Profile Picture
    Ibu 25 on at
    RE: Relating a purchase invoice to its many purchase receipts

    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.

  • Suggested answer
    Dallefeld Profile Picture
    Dallefeld 11,423 User Group Leader on at
    RE: Relating a purchase invoice to its many purchase receipts

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,522 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,441 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans