Skip to main content

Notifications

Microsoft Dynamics AX forum

multiple lines on INVENTTRANS Table and SALELINES table

Posted on by 15

Hi Everyone,

I am very new to this community. thank you very much in advance if you can enlighten me with your knowledge.

My boss asks me to create a Margin report for each product ( ITEMID). my thinking was to get invoice value from SALESLINE table then get cost from INVENTTRANS table, in which case I could calculate margin.  I took one of the sales orders as an example trying to work out how to get Invoice amount (LINEAMOUNT) as well as cost (COSTAMOUNTSETTLED). It turns out multiple sales line and cost line. does anyone know how to combine multiple lines into one?

pastedimage1564660802208v1.png

*This post is locked for comments

  • Satish Panwar Profile Picture
    Satish Panwar 14,643 on at
    RE: multiple lines on INVENTTRANS Table and SALELINES table

    Glad it did. Try to look at SQL syntax in general so you have better idea until you work with AX2009. Once you move to D365 there are other tools available that you can use.

  • Suggested answer
    mangosteenlu Profile Picture
    mangosteenlu 15 on at
    RE: multiple lines on INVENTTRANS Table and SALELINES table

    Hi Satish,

    I don't know how, but it works :) brilliant. thank you ever so much.

  • Verified answer
    Satish Panwar Profile Picture
    Satish Panwar 14,643 on at
    RE: multiple lines on INVENTTRANS Table and SALELINES table

    Hi mangosteenlu ,

    The ItemId is present in CustInvoiceTrans table. The only issue is you can calculate the margin only for invoiced orders and not for open orders.

    In general, single sales line can have multiple inventTrans. The tables are joined on item id and inventTransId field. You need to do something like this. Below is

    Select itemid, SALESQTY, lineamount, (select sum(costamountposted) from inventtrans IT where IT.Itemid = SL.itemid and IT.dataAreaId = SL.DataAreaId and

    IT.InventTransId = SL.InventTransId) from salesline SL

    Once you have your numbers, you need to look at this query again to make sure you add any other adjustments etc. and start comparing the numbers with what you see in AX.

    Thanks,

    Satish Panwar

    Please take time to click 'Yes' against the answers that help you guide in right direction to help other community members.

  • mangosteenlu Profile Picture
    mangosteenlu 15 on at
    RE: multiple lines on INVENTTRANS Table and SALELINES table

    Hi Rustem,

    thank you very much for your prompt response. CUSTINVOICETRANS table does not have ITEMID attached to each invoice.

    On my screenshot, the first 6 columns were from SALESLINE table, the last column was from INVENTTRANS table.

    In order to create a Margin report for each ITEMID, I need ITEMID, SHIPPING DATE, SALES ORDER ID, and INVOICE AMOUNT from SALESLINE table, then the COST AMOUNT from INVENTTRANS table.  

    however there are multiple lines for each tables, and I am not sure how to get rid of multiple lines as I only need one line for each sales orders.

    Thank you

  • Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: multiple lines on INVENTTRANS Table and SALELINES table

    Hi mangosteenlu!

    Why you are trying to get invoice value from the SalesLine table instead of CustInvoiceTrans (invoice line table) ?

    From what table are records on screenshot ?

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

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,129 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans