Thursday, May 14, 2015

AOT and X++ queries and ranges in AX 2012

Let's say we have a query in AOT \Queries\ProjTable














And now we want to add more datasources into this query's parent datasource (ProjTable) and some ranges etc.

Let's assume we have a class (SRS report data provider class or a dialog class) which is using above query and during processing in this class we want to add more datasources and so on.

I created a SRS report data provider class for this example;

Class declaration
[
    SRSReportQueryAttribute (querystr(MarginAnalysisReportV2Sw)),
    SRSReportParameterAttribute(classstr(MarginAnalysisReportContractV2Sw))
]
public class MarginAnalysisReportDPV2Sw extends SRSReportDataProviderBase
{
    Query                       query;
    TempTable               tempTable;
    RecordInsertList       recordInsertListTmpTable;
    ProjTable                  projTable;
    TransDate                 AsOfDate;

}

ProcessReport
[SysEntryPointAttribute]
public void processReport()
{
    // Query variable declaration
    QueryRun                            queryRun;

    // Contract class declaration
    MarginAnalysisReportContractV2Sw    dataContract;

    // Contract class parameters
    ProjDateCriteriaSw                  dateType;

    // Views declaration
    ProjTableDateViewSw                 projTableDateViewSw;

    // This will retrieve the above ProjTable query
    // Select * from ProjTable
    query = this.parmQuery();

    dataContract  = this.parmDataContract();
    dateType        = dataContract.parmDateType();
    asOfDate        = dataContract.parmAsOfDate();

    this.addParameterRanges();

    queryRun = new QueryRun(query);

    while(queryRun.next())
    {
        projTable = queryRun.get(tablenum(ProjTable));
        this.insertTmpTable(ProjTable);
    }
}

addParameterRanges() Method
private void addParameterRanges()
{
    QueryBuildDataSource    qbds1;
    QueryBuildRange            qbr1;

// add ProjTableDate table into query's parent datasource projtable
qbds1= query.dataSourceTable(tableNum(projTable)).addDataSource(tableNum(ProjTableDate));
// defining relation based on ProjId
qbds1.addLink(fieldNum(ProjTable, ProjId), fieldNum(ProjTableDate, ProjId));
// defining join mode
qbds1.joinMode(JoinMode::ExistsJoin);
// adding range on ActualEndDate field of ProjTableDate table
// setting value <= asOfDate which is considered as per date
qbds1.addRange(fieldNum(ProjTableDate,ActualEndDate)).value(queryRange(dateNull(), asOfDate));
}

Resultant X++ Query
SELECT * FROM ProjTable(ProjTable) 
EXISTS JOIN * FROM ProjTableDateViewSw(ProjTableDateViewSw_1) 
WHERE ProjTable.ProjId = ProjTableDateViewSw.ProjId AND 

      ((ActualEndDate<={ts '2015-05-14 00:00:00.000'}))


Adding OR and AND in query
QueryBuildRange                 qbr;
qbr = query.dataSourceTable(tableNum(Table)).addRange(fieldNum(Table, TransDate));
qbr.value(strFmt('((%1 != %2) || (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));

qbr.value(strFmt('((%1 != %2) && (%3 == %4))',
                      fieldStr(Table, ModelId),
                      queryvalue("Latest"),
                      fieldStr(Table, TransDate),
                      asOfDate
                      ));
Using enum value in query as a range
qbr.value(strFmt('(Status == %1)', any2int(ProjStatus::active)));

Check modifiedDate value of the table
qbr.value(strFmt('(ModifiedDate > ProjTable.ModifiedDate)'));

Using wildcards in query
You can use LIKE keyword for wildcards in ranges
qbr.value(strFmt('(ProjName LIKE "*Builder*")'));