Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / Linking Sales Order Li...
Finance forum
Answered

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

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

  • Suggested answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at
    RE: Linking Sales Order Lines to Sales Invoice Lines derived from them?

    Yes, Data Lake exposes complete tables.

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    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 Profile Picture
    Ian Waring 436 on at
    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 Profile Picture
    Alex VN 1,987 on at
    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 Profile Picture
    Alex VN 1,987 on at
    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 Profile Picture
    Sheikh Sohail 6,125 on at
    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 Profile Picture
    Ian Waring 436 on at
    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 Profile Picture
    Sheikh Sohail 6,125 on at
    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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,835 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,526 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans