D365 Finance & Operations and Dynamics AX Forum

Inquiry Form - How to Reference the Current Cost of an Item

  • 1.  Inquiry Form - How to Reference the Current Cost of an Item

    Posted Feb 14, 2020 08:40 AM
    Good Morning,

    In the Inquiry Form, how would we reference the current cost of an item?

    let's start by checking my understanding that i can find the current cost of an item by this SQL:
    Select ItemId, UnitId, Price
    From InventTableModule
    Where ModuleType = 0
    AND DataAreaId = 'Company1'
    AND Itemid In ( 'Item1' )

    Assuming that this is correct, i am not sure how to express that in the user interface Inquiry form.
    Starting with an Inquiry form based on Purchase Order Lines ( PurchLine ) I can add additional tables for items ( InventTable ) and then from there add a table for "Inventory Module Parameters" ( InventTableModule )
    the problem seems to be to reference only the inventory module.

    The end result of what we are trying to do is find PO lines where the PO price is very different from the current cost
    Something like:
    Abs(PurchLine.PurchPrice / CurrentCost - 1 ) > 0.25

    The problem is finding how to express CurrentCost.

    Thank you,

    Barclay Hershey
    Financial Analyst
    Sugar Creek Packing Co
    Cincinnati OH
    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready

If you've found this thread useful, dive deeper into User Group community content by role