Dynamic query creation in Ax2012 using Join table

Dynamic query creation in Ax2012 using Join table

Sub: Dynamic query creation using multiple table

static void DynamicQuery(Args _args)

{

//Author : Rajaram E
//Title : Dynamic query creation using multiple join table

    Query                   query;

    QueryBuildDataSource    queryBuildDataSource, queryBuildDataSource1;

    QueryBuildRange         queryBuildRang;

    QueryRun                queryRun;

    SalesTable              salesTable;

    CustTable               custTable;

    ;

    query                   = new Query();//create new query

    queryBuildDataSource    = query.addDataSource(tableNum(CustTable));// adding datasource - table

    queryBuildRang          = queryBuildDataSource.addRange(fieldNum(CustTable,AccountNum));// adding range

    queryBuildRang.value("Cognizant");   // filter value or assign range value

    queryBuildDataSource1   = queryBuildDataSource.addDataSource(tableNum(SalesTable));// adding second datasource inside first datasource

    queryBuildDataSource1.relations(false);// set defualt relation false if needed

    queryBuildDataSource1.addLink(fieldNum(CustTable,AccountNum),fieldNum(SalesTable,custaccount));// create own new relation

    queryBuildDataSource1.joinMode(JoinMode::InnerJoin);// adding join mode

    queryRun                = new QueryRun(query);// calling query

    while(queryRun.next())

    {

       custTable = queryRun.get(tableNum(CustTable));

       salesTable = queryRun.get(tableNum(SalesTable));

       info(strFmt("%1 %2",custTable.AccountNum, salesTable.SalesId));

    }

}