Search code examples
sqlaxaptax++dynamics-ax-2012

Dynamics AX 2012 Complex QueryBuild Object

Trying to filter a specific data range in AX and having trouble.

This is what I am trying to do, in pseudo code..

(start >= now-30 && (end == null || end >= now-30))
||
(end >= now-30 && (start == null || start >= now-30))
||
(start >= now-30 && end >= now-30)

Here is how I tried to do it

CLASS DECLARATION

QueryBuildRange filterDates;

DS INIT METHOD

filterDates=this.query().dataSourceName('LogTable').addRange(fieldNum(LogTable,startDateTime));    
filterDates=this.query().dataSourceName('LogTable').addRange(fieldNum(LogTable,endDateTime));

DS EXECUTEQUERY METHOD

filterDates.value(strFmt("(%1 >= %3 && (%2 == %4 || %2 >= %3)) || (%2 >= %3 && (%1 == %4 || %1 >= %3)) || (%1 >= %3 && %2 >= %3)", fieldStr(LogTable, startDateTime), fieldStr(LogTable, endDateTime), currentTimeMinus30Mins, DateTimeUtil::minValue()));

AX seems to ignore pretty much any completex query I enter.

Thanks

Solution

  • Provided that the logic in your pseudo-code is correct, I still can see a few flaws in your DS EXECUTEQUERY METHOD:

    • The entire expression must be enclosed in parenthesis.
    • Each sub-expression must be enclosed in its own set of parenthesis.
    • DateTimeUtil::toStr(...) is missing.

    Try changing your code as follows:

    filterDates.value(strFmt("(((%1 >= %3) && ((%2 == %4) || (%2 >= %3))) || ((%2 >= %3) && ((%1 == %4) || (%1 >= %3))) || ((%1 >= %3) && (%2 >= %3)))",
                        fieldStr(LogTable, startDateTime),
                        fieldStr(LogTable, endDateTime),
                        DateTimeUtil::toStr(currentTimeMinus30Mins),
                        DateTimeUtil::toStr(DateTimeUtil::minValue())));

    You should be able to simplify it a bit:

    filterDates.value(strFmt("(((%1 >= %3) || (%1 == %4)) && ((%2 >= %3) || (%2 == %4)) && !((%1 == %4) && (%2 == %4)))",
                        fieldStr(LogTable, startDateTime),
                        fieldStr(LogTable, endDateTime),
                        DateTimeUtil::toStr(currentTimeMinus30Mins),
                        DateTimeUtil::toStr(DateTimeUtil::minValue())));

    Also, I don't see any reason for initialising filterDates twice in DS INIT METHOD. You can actually use a different field in this range, it doesn't have to be startDateTime or endDateTime:

    filterDates = SysQuery::findOrCreateRange(this.query().dataSourceTable(tableNum(LogTable)), fieldNum(LogTable, RecId));

    P.S. I don't remember very well how utcDateTime values should be used in such expressions - as far as I remember, DateTimeUtil::toStr(...) should work.