Dynamics AX 2012 Complex QueryBuild Object

Question

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

Answer 1

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.

Answer 2

I think your logic is flawed which may be why it is not working!

I guess you would like to do a point-in-time search with two date-time fields start and end, and end is blank if not ended.

To search for records valid 30 days ago the only needed condition is:

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

Implementented in executeQuery of the datasource:

public void executeQuery()
{
    QueryBuildDataSource qds = this.query().dataSourceTable(tableNum(LogTable));        
    SysQuery::findOrCreateRange(qds, fieldNum(LogTable,StartDateTime)).value('..'+queryValue(currentTimeMinus30Mins));
    SysQuery::findOrCreateRange(qds, fieldNum(LogTable,EndDateTime)).value('"",'+queryValue(currentTimeMinus30Mins+'..');
    super();
}

The above works for date fields, and I think it will work for date-time fields also.