How to create a Query(dynamically) and in Axapta in Ax

We are trying to join two tables dynamically and adding a link type between the two tables. Open the AOT and in jobs write the following code

staticvoidCustTableSales(Args _args)

{

    Query       query;

    QueryRun    queryrun;

    QueryBuildDataSource    qbds1;

    QueryBuildDataSource    qbds2;

    QueryBuildRange         qbr1;

    QueryBuildRange         qbr2;

    CustTable               custTable;

    ;

query   = new query();

    qbds1   =   query.addDataSource(tablenum(CustTable));

qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);

    qbr1    = qbds1.addRange(fieldnum(custTable,custGroup));

qbr1.value(queryvalue('10'));

    qbr2    =  qbds1.addRange(fieldnum(custTable,Blocked));

qbr2.value(queryvalue(CustVendorBlocked::No));

    qbds2   = qbds1.addDataSource(tablenum(SalesTable));

qbds2.relations(false);

qbds2.joinMode(joinmode::ExistsJoin);

qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));

queryrun    = newqueryrun(query);

while(queryrun.next())

    {

custTable   = queryrun.get(tablenum(custTable));

info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name())); // to check your result

    }

}

Author : Umesh Patel

Related Posts