News and Announcements icon

Dynamics 365 CommunityForums Small and medium business | Business Central, N... Join purchase invoices...

Small and medium business | Business Central, N...

Suggested answer

Join purchase invoices to purchase orders

(0) Share

Report

Report

Posted on by garydee

86

I've pulled down all purchase invoices and purchase orders via the standard api's (endpoints purchaseOrders and purchaseInvoices), I used the ?$expand = purchaseOrderLines/purchaseInvoiceLines in order to get the respective lines. Is there a field that joins these two datasets? I can join on vendor,postingDate,itemNumber and variantCode but I'm left with some duplicates. I could identify these and weed them out with some SQL code, however I would suspect that there is a field in BC somewhere that allows me to join these two sets?

All responses Img

All responses (2)

Answers Img

Answers (0)

garydee 86 on at

Like (0)

Report

RE: Join purchase invoices to purchase orders

Thanks ZHU,

That blog was very useful, will bookmark for sure. It seems that I was mistaken however. There is a field in the purchaseInvoices service called orderNumber which links to the purchase order. I missed it because most of the records for this field are blank! I'm not sure how that could be as I thought there was almost always a purchase order associated with every purchase invoice.

cheers,

garydee
Suggested answer

YUN ZHU 73,696 Super User 2024 Season 2 on at

Like (0)

Report

RE: Join purchase invoices to purchase orders

Hi, Could the following blog help you?

https://www.kauffmann.nl/2021/01/06/batch-calls-with-business-central-apis-3-tips-and-tricks/

When encountering such complex API processing, a relatively simple way is to create a new table (temporary) in BC, create a new page, and collect all the required data when it is opened. Then access this data in Odata.

Hope this will help.

Thanks.

ZHU

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2

Martin Dráb 230,214 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans