Linking Sales Order Lines to Sales Invoice Lines derived from them?

(0) Share

Report

Posted on by Ian Waring

436

I'm looking at SalesOrderHeaderV2/SalesOrderLinesV2 and SalesInvoiceHeaderV2/SalesInvoiceLinesV2. I'm trying to work out if I can link a Sales Invoice Line back to the Sales Order Line that gave rise to it.

The InvoiceHeader contains the Sales Order Reference. Sales Invoice Lines contains a field "LineCreationSequenceNumber" but it's not clear if this is the same LineCreationSequence number in the original sales order, or one on the invoice itself (I seem to recall purchase orders have fields for both the PO line and the Req line that it came from).

I'm attempting to append the LineSequence number to the SalesOrderNumber on both sides in the vain hope I can give a Power BI report that can track payments against sales order lines all the way back :-)

Any ideas if i'm attempting the impossible?

All responses (8)

Answers (4)

Suggested answer

Sheikh Sohail 6,125 on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

Yes, Data Lake exposes complete tables.
ergun sahin 8,816 Moderator on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

community.dynamics.com/.../automated-solution-to-find-table-relations-in-ax-2012-and-d365fo
Ian Waring 436 on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

Fantastic help, thank you.

Another case of entities not exposing useful fields to us poor Power BI users.I'm just having a look at whether our Data Lake setup can expose the full table (as well as other useful tables, like Customer and Vendor Transactions - which allow us to map payment events back to invoice vouchers).

Probably also a good time to download Alex Meyers ERD discovery tool as well.
Verified answer

Alex VN 1,994 on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

In addition to my previous answer, you will get 1-1 relationship unless you have partial invoice on the line. For the partial invoice part, I believe it will also give you the connection to the same SalesLine from different SalesInvoiceLines.

Hope this helps.
Verified answer

Alex VN 1,994 on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

Hi,

I'm not so sure but can you try to check for the SalesLine.InventTranID and CustInvoiceTrans.InventTransID to see if you can establish the connection?
Verified answer

Sheikh Sohail 6,125 on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

Hi

these fields are not part of the data entities, these fields you can find in the sales line and cust Invoice trans table, If you want we can connect to understand what exactly you want to achieve or you can involve a technical developer with you to evaluate the issue
Ian Waring 436 on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

Thank you. On my D365FO 10.0.22 setup, I have no Invent Trans field in either SalesLine nor OrderLine tables (at least as part of the standard entites).

I've created orderline numbers with a concatenation of Order number, "-" and then the LineCreationSequence numbers, but for some reason that only particially works on SalesInvoiceHeaders and Lines - the relationship between the two being a recid field.

Sales Order Headers and Sales Invoice Headers do appear to link using SalesOrderNumber. However, it's the lines I need to match :-)
Verified answer

Sheikh Sohail 6,125 on at

Like (0)

Report

RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

Hi Ian

You can engage any technical with you to link the sales line with the invoice line again, there is a field invent Trans in both tables, and with this relation, you can link the sales line with the invoice line.

If the Sales order header does also not link with the invoice header the there is one OOTB table available to maintain their relation, You need to put the record on it.