Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / multiple lines on INVE...
Microsoft Dynamics AX (Archived)

multiple lines on INVENTTRANS Table and SALELINES table

(0) ShareShare
ReportReport
Posted on by mangosteenlu 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,645 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,645 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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans