QueryBuildDataSource SalesTable SalesLine Join Check Dynamics AX X++

 

static void POR_AxCheck(Args _args)

{

    Query                       q;

    QueryRun                    qr;

    QueryBuildDataSource        qbdSalesTable,qbdSalesLine,qbdItem;

    SalesTable                  SalesTable;

    SalesLine                   SalesLine;

    TransDate                   FromDate,ToDate;

    TPI_ItemTest                ItemTest;

    ;

    FromDate    = Mkdate(01,09,2022);

    ToDate      = Mkdate(01,09,2022);

    /*

    InnerJoin       - creates INNER or CROSS JOIN

    OuterJoin       - creates LEFT JOIN

    ExistsJoin      - creates WHERE EXISTS (...)

    NotExistsJoin   - creates WHERE NOT EXISTS (...)

    */

    q               = new Query();

    qbdSalesTable   = q.addDataSource(TableNum(SalesTable));

    qbdSalesLine    = qbdSalesTable.addDataSource(TableNum(SalesLine));

    qbdItem         = qbdSalesLine.addDataSource(TableNum(TPI_ItemTest));

    qbdSalesTable.addGroupByField(fieldnum(SalesTable,TPI_SPO));

    qbdSalesTable.addGroupByField(fieldnum(SalesTable,SalesId));

    qbdSalesTable.addGroupByField(fieldnum(SalesTable,PostingProfile));

    qbdSalesLine.addGroupByField(fieldnum(SalesLine,Itemid));

    qbdItem.addGroupByField(fieldnum(TPI_ItemTest,Itemid));

    qbdSalesLine.addGroupByField(fieldnum(SalesLine,SalesPrice));

    qbdSalesLine.addGroupByField(fieldnum(SalesLine,SalesUnit));

    qbdSalesLine.addSelectionField(fieldnum(SalesLine,SalesQty),SelectionField::Sum);

    qbdSalesLine.addSelectionField(fieldnum(SalesLine,TPI_LineAmountTotalDisplay),SelectionField::Sum);

    qbdSalesTable.addRange(FieldNum(SalesTable, SalesStatus)).value(strfmt('!=%1',SalesStatus::Canceled));

    qbdSalesTable.addRange(FieldNum(SalesTable, PostingProfile)).value(strfmt("!4,!9"));

    qbdSalesTable.addRange(FieldNum(SalesTable, SalesType)).value(enum2str(SalesType::Sales));

    qbdSalesTable.addRange(FieldNum(SalesTable, TPI_dpbusDate)).value(queryRange(FromDate,ToDate));

    qbdSalesTable.addRange(FieldNum(SalesTable, TPI_DP_NO)).value(sysquery::valueNotEmptyString());

    qbdSalesTable.addRange(FieldNum(SalesTable, TPI_SPO)).value('2208051702');

    qbdSalesLine.addLink(FieldNum(SalesTable, Salesid), FieldNum(SalesLine,Salesid));

    qbdSalesLine.joinMode(JoinMode::OuterJoin);

    qbdItem.addLink(FieldNum(SalesLine, Itemid), FieldNum(TPI_ItemTest,Itemid));

    qbdItem.joinMode(JoinMode::OuterJoin);

    qr  = new QueryRun(q);

    while(qr.next())

    {

        SalesTable          = qr.get(tablenum(SalesTable));

        SalesLine           = qr.get(tablenum(SalesLine));

        ItemTest            = qr.get(tablenum(TPI_ItemTest));

        if(!ItemTest.ItemId)

        {

            info(strfmt('SPO %1 ,Item %2 %3 ,Qty %4,Amount %5 , SalesType %6',SalesTable.TPI_SPO,SalesLine.ItemId

            ,SalesLine.itemName(),SalesLine.SalesQty,SalesLine.TPI_LineAmountTotalDisplay,SalesTable.PostingProfile));

        }

    }

}