Fork me on GitHub

Building a Query in X++ [AX2012]

Solution

An example of Query to find InventSerials, when Works Order is known.

An example of achieving same result using While/Select statements.

Class declaration

public class YourClass
{
    // Query objects
    Query                   query;
    QueryRun                queryRun;
    QueryBuildDataSource    qbdsInventTransOrigin;
    QueryBuildDataSource    qbdsInventTrans;
    QueryBuildDataSource    qbdsInventDim;
    QueryBuildDataSource    qbdsInventSerial;
    QueryBuildRange         qbr;

    // Declare a table buffer
    InventTransOrigin       inventTransOrigin;
    InventTrans             inventTrans;
    InventDim               inventDim;
    InventSerial            inventSerial;
}

A Method

public void getInventSerials(InventRefId _inventRefId, InventTransType _inventTransType)
{
    query = new Query();
    // Data sources
    qbdsInventTransOrigin = query.addDataSource(tableNum(inventTransOrigin));

    qbdsInventTrans = qbdsInventTransOrigin.addDataSource(tableNum(inventTrans));
    qbdsInventTrans.relations(true);
    qbdsInventTrans.joinMode(JoinMode::InnerJoin);

    qbdsInventDim = qbdsInventTrans.addDataSource(tableNum(inventDim));
    qbdsInventDim.relations(true);
    qbdsInventDim.joinMode(JoinMode::InnerJoin);

    qbdsInventSerial = qbdsInventDim.addDataSource(tableNum(inventSerial));
    qbdsInventSerial.relations(true);
    qbdsInventSerial.joinMode(JoinMode::OuterJoin);

    // Ranges and Values
    qbr = qbdsInventTransOrigin.addRange(fieldNum(inventTransOrigin, ReferenceCategory));
    qbr.value(enum2str(_inventTransType));
    qbr.status(RangeStatus::Locked);

    qbr = qbdsInventTransOrigin.addRange(fieldNum(inventTransOrigin, ReferenceId));
    if(_inventRefId != "")
    {
        qbr.value(_inventRefId);
    }
    else
    {
        error("InventRefId value is empty");
    }
    qbr.status(RangeStatus::Locked);

    // Results sorting
    qbdsInventTransOrigin.addSortField(fieldNum(inventTransOrigin, InventTransId));

    queryRun = new QueryRun(query);

    if(queryRun.prompt())   // Comment 'if' statement if you don't want
    {                       // to see query dialogue at runtime
        while (queryRun.next())
        {
            inventTransOrigin = queryRun.get(tableNum(inventTransOrigin));
            inventTrans = queryRun.get(tableNum(inventTrans));
            inventDim = queryRun.get(tableNum(inventDim));
            inventSerial = queryRun.get(tableNum(inventSerial));

            // Print out results received
            info(strFmt("RecId: %1, InventTransId: %2, InventDimId: %3, InventSerialId: %4",
                        inventTransOrigin.RecId,
                        inventTransOrigin.InventTransId,
                        inventTrans.inventDimId,
                        inventDim.inventSerialId)
                );
        }
    }
}

Main method to run

public static void main (Args _args)
{
    YourClass yourClass = new YourClass();

    // Provide existing Works Order number and InventTransType as parameters
    yourClass.getInventSerials("DON1-000034", InventTransType::Production);
}

Query dialogue (if implemented)

Query dialogue

Query output to Infolog

InventSerials Output

Comments !

links

social