Dynamic Queries in Ax 2012

To Select All Customers from Custtable

static void CON_DynamicQuery(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbd;

    CustTable               custTable;

   

    q   = new Query();

    qbd = q.addDataSource(tableNum(custTable));

    qbd.addSortField(fieldNum(CustTable,AccountNum),SortOrder::Ascending);

    qr = new QueryRun(q);

    while(qr.next())

    {

      custTable = qr.get(tableNum(custTable));

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

       

    }

   

}

   To Select Customers Between Range

static void CON_DynamicQuery(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

   

    q       = new Query();

    qbds    = q.addDataSource(tableNum(custTable));

    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);  // order by

    qbr     = qbds.addRange(fieldNum(custTable,AccountNum)); // For all customers

    qbr.value(queryRange('US-001','US-009')); // for particular range

  

    qr = new QueryRun(q);

    while(qr.next())

    {

        custTable = qr.get(tableNum(custTable));

        info(custTable.AccountNum);

       

    }

}

                                Where Condition

static void Job34(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

   

    q   = new Query();

    qbds = q.addDataSource(tableNum(custTable));

    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    qbds.addSortField(fieldNum(custTable,CustGroup),SortOrder::Descending);

    qbr = qbds.addRange(fieldnum(custTable,CustGroup));

    qbr.value(queryRange('10','30'));

   

    qr  = new QueryRun(q);

    while(qr.next())

    {

        custTable   = qr.get(tableNum(custTable));

        info(strFmt("%1--%2",custTable.CustGroup,custTable.AccountNum));

    }

}

                                CrossCompany

static void CON_DynamicStatement(Args _args)

{

    CustTable       custTable;

   

    Query                   query;          

    QueryBuildDataSource    qbds;

    QueryRun                qr;

   

    query   = new Query();

    query.allowCrossCompany(true);

   // query.addCompanyRange('USMF');

   // query.addCompanyRange('IBM');

    qbds  = query.addDataSource(tableNum(custTable));

    qr    = new QueryRun(query);

    while(qr.next())

    {

      

            custTable = qr.get(tableNum(custTable));

            info(strFmt('%1-%2',custTable.AccountNum,custTable.dataAreaId));

    

    }

   

}

                                Aggregate Function -- min,max,sum,count,computedcolumn,database

static void CON_Dynamic(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q = new Query();

    qbds = q.addDataSource(tableNum(custTrans));

    qbds.addSelectionField(fieldNum(custTrans,AmountMST),Selectionfield::Min);

    qbds.addSelectionField(fieldNum(custTrans,RecId),SelectionField::Count);

   

    qr      = new QueryRun(q);

    while(qr.next())

    {

        custTrans = qr.get(tableNum(custTrans));

        info(strFmt("%1",custTrans.AmountMST));

    }

   

}

                                first only,first fast

static void CON_Dynamic(Args _args)

{

    int                     i;

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q   = new Query();

    qbds= q.addDataSource(tableNum(custTable));

    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    qbds.firstOnly(true);  //firstonly --firstfast

    qr = new QueryRun(q);

  

    while(qr.next())

    {

        custTable = qr.get(tableNum(custTable));

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

    }

}

                                forUPdate

static void CON_DynamicforUpdate(Args _args)

{

    int                     i;

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

    Con_Calc                obj;

    q   = new Query();

    qbds= q.addDataSource(tableNum(Con_Calc));

    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    //qbds.firstFast(true);

    qbds.addSortField(fieldNum(Con_Calc,Name));

    qr = new QueryRun(q);

    while(qr.next())

    {

        obj = qr.get(tableNum(Con_Calc));

        obj.selectForUpdate(true);

        ttsBegin;

        if(obj.Name =="Siva")

        {

            obj.Name="Sivakumar";

            obj.update();

        }

        ttsCommit;

        info(strFmt("%1",obj.Name));

    }

}

                                                GroupBy

static void CON_DynamicGroupBy(Args _args)

{

    Query           q;

    QueryRun        qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q = new Query();

    qbds = q.addDataSource(tableNum(custTrans));

    //qbds.addSortField(fieldNum(custTable,AccountNum));

    qbds.addSelectionField(fieldNum(custTrans, AmountMST),SelectionField::Max);         //Aggregate

    qbds.addGroupByField(fieldNum(custTrans,AccountNum),OrderMode::GroupBy);     //group by

      

    qr = new QueryRun(q);

  

    while(qr.next())

    {

        custTrans = qr.get(tableNum(custTrans));

        info(strFmt("%1--%2",custTrans.AccountNum,custTrans.AmountMST));

    }

   

}

                                                Joins

static void CON_DynamicJoin(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbdsCustTable,qbdsCustTrans;

    QueryBuildRange         qbr;

    CustTable               custTable;

    CustTrans               custTrans;

    CON_Car                 car;

    CON_RentCar             rent;

    q   = new Query();

    qbdsCustTable = q.addDataSource(tableNum(CON_Car));                     //Data Source 1

    //qbdsCustTable.addSortField(fieldNum(CON_Car,CarId),SortOrder::Descending);

    qbdsCustTrans = qbdsCustTable.addDataSource(tableNum(CON_RentCar));    //Data Source 2

    qbdsCustTrans.relations(false);

    qbdsCustTrans.joinMode(JoinMode::InnerJoin);                          //Join Type

    qbdsCustTrans.addLink(fieldNum(CON_Car,CarId),fieldNum(CON_RentCar,CarId));

   

    qr = new QueryRun(q); 

    while(qr.next())

    {

      

        car = qr.get(tableNum(CON_Car));

        rent = qr.get(tableNum(CON_RentCar));

        info(strFmt("%1--%2",car.CarId,rent.CarId));

    }

}

                                Multiple Tables Join

static void CON_DyamicQUeries(Args _args)

{

    Query                   query = new Query();

    QueryBuildDataSource    salesTableDS;

    QueryBuildDataSource    salesLineDS;

    QueryBuildDataSource    custTableDS;

    QueryRun                qr;

    CustTable               custTable;

    SalesTable              salesTable;

    SalesLine               salesLine;

  

    salesTableDS    = query.addDataSource(tableNum(SalesTable));

   

    salesLineDS     = salesTableDS.addDataSource(tableNum(SalesLine));

    salesLineDS.relations(true);

    //salesLineDS.fetchMode(QueryFetchMode::One2One);

    salesLineDS.joinMode(JoinMode::InnerJoin);

    salesLineDS.addLink(fieldNum(SalesTable, SalesId), fieldNum(SalesLine, SalesId));

    custTableDS     = salesTableDS.addDataSource(tableNum(CustTable));   

    custTableDS.relations(false);   

    custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));

    //custTableDS.fetchMode(QueryFetchMode::One2One);

    custTableDS.joinMode(JoinMode::InnerJoin);

    //info(salesTableDS.toString());.

    qr = new QueryRun(query);

    while(qr.next())

    {

        salesTable= qr.get(tableNum(salesTable));

        salesLine = qr.get(tableNum(salesLine));

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

   

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

    }

}

Validtimestate

static void QueryCurrent(Args _args)
{
     CustInterestVersion interestVersion;
     CustInterest interest;
     Date asOfDate = 1\1\2002;

 while select validtimestate(asOfDate) * from interestVersion join interest
       where interestVersion.CustInterest == interest.RecID
      {
          info(strFmt("%1, %2, %3, %4", interest.InterestCode, interestVersion.GraceDays,
        interestVersion.ValidFrom, interestVersion.ValidTo));
      }
}

In AsOfDate or AsOfDateRange mode, a new keyword ValidTimeState is introduced.
ValidTimeState(date1) can be used to query the records that are effective at a specific time.
ValidTimeState(date1, date2) can be used to query the records that are effective during a time
period. Date1 and Date2 can be the type of Date or UtcDateTime, depending on the ValidFrom and
ValidTo type of the valid time state table. For example, the following code sample returns the records
that are effective on 1/1/2002:

                                                Full Text Index

static void CON_FullTextIndex(Args _args)

{

    Query           q;

    QueryRun        qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    Con_Calc                obj;

    q       = new Query();

    qbds    = q.addDataSource(tableNum(Con_Calc));

    qbr     = qbds.addRange(fieldNum(Con_Calc,Name));

    qbr.rangeType(QueryRangeType::FullText);

    qbr.value("Raj kumar");

    qr      = new QueryRun(q);

  

    while(qr.next())

    {

        obj = qr.get(tableNum(Con_Calc));

        info(obj.Name);

    }

}

Example

https://gfeaxblog.wordpress.com/2018/04/09/d365-full-text-search/

Path
Sales and Marketing >> Setup >> Search

1. Search parameter
2. Search criteria

All sales orders

Add invalid Item on sales line and then enter Tab

Product search form will be opened

Technical 

Form MCRSalesQuickQuote

Methods --> prepareSearch

DS     InventItemDimTmpFilter

Methods --> excuteQuery

Class   MCRInventSearch.executeSearch()

How to add new field in Product search form

Steps 

1. Add new field in MCRProductSearchView  view
2. Add newly added field in Search criteria form (Sales and Marketing >> Setup >> Search)
3. Click on update search data button on Search criteria form
4. Add new datasource on MCRSalesQuickQuote form
5. In the Datasource init method add below code
6. Add required field on grid

//In my case, I have added CustVendExternalItem table as DS

public void init()

{

       QueryBuildDataSource    qbds    = this.queryBuildDataSource();

        qbds.addLink(fieldNum(InventItemDimTmpFilter, ItemId), fieldNum(CustVendExternalItem, ItemId));

       

        qbds.addGroupByField(fieldNum(CustVendExternalItem, ExternalItemId));

}

Greater than or euqal to / Less than or equal to (<= or >=) in Query class in AX 2012 or D365

Greater than or equal to:

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value(SysQuery::range(today(), dateMax()));

or

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value((queryRange(today(), dateMax()));

Less than or equal to:

 queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value(SysQuery::range( dateNull(), today()));

or

queryBuildDataSource.addRange(fieldNum(HcmPositionWorkerAssignment,ValidTo));
        queryBuildRange.value((queryRange(dateNull(), today()));