none
Query on AX SQL database

    Question

  • Hello Friends,

    I need some help on running a query that return, sales order number, batch number, picked quantity and customer number. 

    The query really is to help me know the customer who order what, in what quantity, which batch the good were taken from and what sales order it was applied to.

    When I right click on the lines for picking list registration i see the line am interesting it but it doesn't tell me which table i can use. 

    Friday, July 13, 2018 4:17 PM

Answers

  • Hi Lionelf07,

    Thanks for your question.

    >>>The query really is to help me know the customer who order what, in what quantity, which batch the good were taken from and what sales order it was applied to.
    According to your description, it seems like an issue of T-SQL. If it is, please post your question in T-SQL forum ,you will get a more professional support in there.

    >>>When I right click on the lines for picking list registration i see the line am interesting it but it doesn't tell me which table i can use.
    Would you please elaborate more information about this? It is not clear what you are after for this question.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 16, 2018 5:14 AM
    Moderator

All replies

  • Hi Lionelf07,

    Thanks for your question.

    >>>The query really is to help me know the customer who order what, in what quantity, which batch the good were taken from and what sales order it was applied to.
    According to your description, it seems like an issue of T-SQL. If it is, please post your question in T-SQL forum ,you will get a more professional support in there.

    >>>When I right click on the lines for picking list registration i see the line am interesting it but it doesn't tell me which table i can use.
    Would you please elaborate more information about this? It is not clear what you are after for this question.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 16, 2018 5:14 AM
    Moderator
  • Thanks for your help Willson. I have learnt that if you want to know which tables are affected when AX 2012 present data to a grid, form etc, just right click in the control(where the data is) select personalize then in the "System name" its likely you will see the table where the data comes from. I used this to get the tables that have all the fields I am interested in. Then do some join and get the data thanks much.

    select DISTINCT 
    WMSPICKINGROUTE.TRANSREFID AS SALESORDERID,
    WMSORDERTRANS.ITEMID,
    INVENTTABLE.NAMEALIAS,
    WMSORDERTRANS.QTY,
    inventdim.INVENTBATCHID,
    WMSPICKINGROUTE.CUSTOMER,
    WMSPICKINGROUTE.DELIVERYNAME

    from WMSPICKINGROUTE
    JOIN WMSORDERTRANS on INVENTTRANSREFID=WMSPICKINGROUTE.TRANSREFID
    JOIN INVENTDIM on [INVENTDIM].INVENTDIMID=WMSORDERTRANS.INVENTDIMID
    JOIN [INVENTTABLE] on [INVENTTABLE].ITEMID=WMSORDERTRANS.ITEMID
    where TRANSREFID IN ('salesORDERID1', 'salesORDERID2', 'salesORDERID3')
    and WMSPICKINGROUTE.DATAAREAID='AAA'

     
    Monday, July 16, 2018 2:24 PM