Tuesday, December 13, 2011

Dates In An Extended Query Range

Let's say we need to range all the BOM versions that are active as of today. For this purpose we can use an extended range in the query.

{
    QueryBuildRange qbr;
    ;
    if(!_args.record())
        return;
    // there is an active caller!
    switch (_args.record().TableId)
    {
        case tablenum(InventTable):
            inventTable = element.args().record();
            this.query().dataSourceTable(tablenum(InventTable)).addRange(fieldnum(InventTable, ItemId)).value(inventTable.ItemId);
            qbr = this.query().dataSourceTable(tablenum(BOMVersion)).addRange(fieldnum(BOMVersion, RecId));
            qbr.value('(fromDate <= '+date2StrXpp(today())+') && (toDate >= '+date2StrXpp(today())+')');
            break;

The final SQL query will look like this (a fragment):


= BOMVersion.ItemId AND ((Active = 1)) AND (((fromDate <=13\12\2011) && (toDate >= 13\12\2011))) JOIN * FROM BOM(BOM_1) ORDER BY BOM.LineNum ASC ON BOMVersion.BOMId = BOM.BOMId

The idea was taken from the AX forum (in Russian).