Monday 7 May 2018

Simple Dynamic Query in Ax 2012

static void Na_DynamicQuery(Args _args)
{
    VendTable                        vendTable;
    VendTrans                        vendTrans;
    Na_vendtrans                    na_vendtrans; 
    Query                         query;
    QueryBuildDataSource         queryBuildDataSource;
    QueryBuildDataSource         queryBuildDataSource1;
    QueryBuildRange         queryBuildRange;
    QueryRun                  queryRun;
    //CustTable                                 custTable;
    //DirPartyTable                           DirPartyTable;

    ;
    query=new query();      //Initialize Query
    queryBuildDataSource=query.addDataSource(tableNum(vendTrans));   //Add Query
    queryBuildDataSource.addSortField(fieldNum(vendTrans,AccountNum),SortOrder::Descending); //Add sorting
    queryBuildRange=queryBuildDataSource.addRange(fieldNum(vendTrans,AccountNum)); // Add Range
    queryBuildRange.value(queryValue('3001')); // Add Query Value
    queryBuildRange=queryBuildDataSource.addRange(FieldNum(vendTrans,TransDate));
    queryBuildRange.value(SysQuery::range('7/1/2006','8/20/2010'));

    queryBuildDataSource1=queryBuildDataSource.addDataSource(tableNum(VendTable)); //Add other Data Source for Joining
    queryBuildDataSource1.relations(false);// Enable Relation
    queryBuildDataSource1.joinMode(JoinMode::InnerJoin);//Define Join Mode
    queryBuildDataSource1.addLink(fieldnum(VendTable,AccountNum),fieldnum(vendTrans,AccountNum));
   
    querybuildrange=querybuilddatasource1.addrange(fieldnum(VendTable,vendgroup));
    querybuildrange.value(queryvalue('30'));


    queryRun= new QueryRun(query);   //Pass query to run it
    while(queryRun.next())
    {
        vendTrans=queryRun.get(tableNum(VendTrans));
        //print(strFmt("Customer Name, Account num and Currency are  %1 , %2 and %3",DirPartyTable::findRec(CustTable.Party).Name,CustTable.AccountNum,CustTable.Currency));
        info(strFmt("%1-%2-%3-%4",vendTrans.AccountNum,vendTrans.TransDate,vendTable.AccountNum,vendTable.VendGroup));
        na_vendtrans.AccountNum = VendTrans.AccountNum;
        na_vendtrans.Voucher    = VendTrans.Voucher;
        na_vendtrans.TransDate  = VendTrans.TransDate;
        //na_vendtrans.insert();
       
        //pause;
    }


}

No comments:

Post a Comment