HOW TO ADD RANGE USING EXPRESSIONS IN QUERY (strFmt('((%1.%2 ==) IN MICROSOFT DYNAMICS AX X++ CODE

HOW TO ADD RANGE USING EXPRESSIONS IN QUERY  strFmt('((%1.%2 ==

IN MICROSOFT DYNAMICS AX X++ CODE

static void AddRangeToQuery(Args _args)
{
    Query q = new Query();  // Create a new query.
    QueryRun qr;
    CustTable ct;
    QueryBuildDataSource qbr1;
    str strTemp;
    ;

    // Add a single datasource.
    qbr1 = q.addDataSource(tablenum(CustTable));
    // Name the datasource 'Customer'.
    qbr1.name("Customer");

    // Create a range value that designates an "OR" query like:
    // customer.AccountNum == "4000" || Customer.CreditMax > 2500.

    // Add the range to the query data source.
    qbr1.addRange(fieldNum(CustTable, AccountNum)).value(
    strFmt('((%1.%2 == "4000") || (%1.%3 > 2500))',
        qbr1.name(),
        fieldStr(CustTable, AccountNum),
        fieldStr(CustTable, CreditMax)));

    // Print the data source.
    print qbr1.toString();
    info(qbr1.toString());

    // Run the query and print the results.
    qr = new QueryRun(q);

    while (qr.next())
    {
        if (qr.changedNo(1))
        {
            ct = qr.getNo(1);
            strTemp = strFmt("%1 , %2", ct.AccountNum, ct.CreditMax);
            print strTemp;
            info(strTemp);
        }
    }
    pause;
}

CREATE ADD MODIFY AND DELETE AX x++ QUERY IN MICROSOFT DYNAMICS AX

CREATE ADD MODIFY AND DELETE AX x++ QUERY IN DYNAMICS AX

Static void CreateQueryJob(Args _args)
{
    TreeNode                treeNodeObj;
    Query                   queryObj; // Extends TreeNode class.
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    QueryRun                qr;
    CustTable               xrecCustTable;
    str                     queryName = "MyQuery1";
    
    // Macro.
    #AOT

    // Delete the query from the AOT, if the query exists.
    treeNodeObj = TreeNode::findNode(#QueriesPath);
    treeNodeObj = treeNodeObj.AOTfindChild(queryName);
    if (treeNodeObj) { treeNodeObj.AOTdelete(); }

    // Add the query to the AOT.
    treeNodeObj = TreeNode::findNode(#QueriesPath);
    treeNodeObj.AOTadd(queryName);
    queryObj = treeNodeObj.AOTfindChild(queryName);
    
    // Further define the query.
    qbds  = queryObj.addDataSource(tablenum(CustTable));
    qbr   = qbds.addRange(fieldnum(CustTable, DlvMode));
    qbr.value(">10");

    // Compile the query.
    queryObj.AOTcompile(1);
    queryObj.AOTsave();

    // Run the query.
    qr = new QueryRun("MyQuery1");
    while ( qr.next() )
    {
        xrecCustTable = qr.GetNo(1); // 1 means first data source.
        Global::info(strFmt("%1 , %2",
            xrecCustTable.AccountNum, xrecCustTable.DlvMode));
    }        

    // Delete the query from the AOT.
    treeNodeObj = TreeNode::findNode(#QueriesPath);
    treeNodeObj = treeNodeObj.AOTfindChild(queryName);
    treeNodeObj.AOTdelete();
}

CREATE DYNAMIC QUERY IN AX X++ CODE ON THE FLY FILTER: DYNAMICS AX X++ CODE

CREATE DYNAMIC QUERY IN AX X++ CODE WITH FILTER

public void qUERYdYNAMICS()
{
    Query q;
    QueryRun qr;
    QueryBuildDataSource qbd;
    QueryBuildRange qbr;

    q = new Query();
    qbd = q.addDataSource(TableNum(CustTable));

    qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
    qbr.value(">=4000"); // Default operator is ==.

    qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
    qbr.value("<=4022");

    qbd.addSortField(FieldNum(CustTable, DlvMode));

    qr = new QueryRun(q);
    qr.prompt();

    pause;
}

MICROSOFT DYNAMICS AX 2012 : HOW TO USE QUERYHAVINGFILTER IN AX 2012: QUERY WITH GROUP CONDITION

How to use QueryHavingFilter in AX 2012

 QueryHavingFilter:

Consider the following scenario. The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them.
Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE

        GROUP BY CUSTGROUP

        HAVING COUNT(*) < 4

In AX you can count, group by, but you'll need to loop over the results and check the counter manually if you want to filter values out. So, in AX 2012, a new query class was added: QueryHavingFilter, that lets you do just that:

static void QueryHaving(Args _args)

{

    Query                   query;

    QueryBuildDataSource    datasource;

    QueryBuildRange         range;

    QueryHavingFilter       havingFilter;

    QueryRun                queryRun;

    int                     counter = 0, totalCounter = 0;

    CustTable               custTable;

   

    query = new Query();

    datasource = query.addDataSource(tableNum(CustTable));

    datasource.addSelectionField(fieldNum(CustTable, RecId),

            SelectionField::Count);

    datasource.orderMode(OrderMode::GroupBy);

    datasource.addGroupByField(fieldNum(CustTable, CustGroup));

   

    havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),

            AggregateFunction::Count);

    havingFilter.value('<4 o:p="">

   

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        custTable = queryRun.getNo(1);

        info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));

    }

}

Note that in this code example, I added a selection field on RecId and used SelectionField::Count. This is not necessary for the having filter to work, the only reason it is in the code example is to be able to show it in the infolog (ie to have the count value available). So it is independent of the HavingFilter!

Enterprise Portal DATASET LOOKUP CODE X++

Enterprise Portal DATASET LOOKUP X++

Query to create a dataset lookup in AX Enterrprise portal .

void dataSetLookup(SysDataSetLookup sysDataSetLookup)

{

    List                    _list;

    Query                   query = new Query();

    QueryBuildDataSource    queryBuildDataSource1, queryBuildDataSource2, queryBuildDataSource3 ;

    QueryBuildRange         queryBuildRange,queryBuildRange2,queryBuildRange3;

    DirpartyTable           dirpartyTable;

    ;

    _list = new List(Types::String);

    _list.addEnd(fieldstr(EmplTable, EmplId));

    _list.addEnd(fieldstr(EmplTable, EmplName));

    queryBuildDataSource1 = query.addDataSource(tablenum(EmplTable));

    queryBuildDataSource2 = queryBuildDataSource1.addDataSource(tablenum(Dimensions));

    queryBuildDataSource2.joinMode(JoinMode::InnerJoin);

    queryBuildDataSource2.addLink(fieldnum(EmplTable,EmplId),fieldnum(Dimensions,Num));

    queryBuildRange       = queryBuildDataSource2.addRange (fieldnum (Dimensions, DimensionCode));

    queryBuildRange.value(queryvalue(sysdimension::Purpose));

    sysDataSetLookup.parmLookupFields(_list);

    sysDataSetLookup.parmQuery(query);

}