How to add a query range through code to an AOT query which is used in a view?

Finance and Operations/AX Developer Forum
1

I want to add a range to an AOT query MyQuery through the code. The reason why I can’t use AOT node “ranges” is that I need to set ranges on ValidFrom and ValidTo dateTime fields. I simply need to check if utcDateTimeNow falls between ValidFrom and ValidTo. I know that setting ranges manually on these fields is highly unusual but I don’t see any other way. It’s not done automatically in this case. I couldn’t find any way to set ranges using node “ranges”, if that’s possible please tell me how that can be done.

I’m using the query MyQuery in a View MyView. This view is used in another union query. The union query is used in a view and lastly, the view is used as a form datasource.

I tried writing code in init and run methods but the code written there isn’t executed when MyView is opened. So I guess it won’t be executed when I open my form either.

2

If your view contains a date-effective table, wouldn’t setting view’s property ValidTimeStateEnabled to Yes do what you want?

3

I’ve tried that, but it also hides some extra records which need to be shown. I’m using Outer Join in my query so some records don’t have a corresponding record in HcmWorkerTitle table (where ValidFrom and ValidTo fields are). I need to show these records also.

I think I’ll have to remove HcmWorkerTitle table from my query at all and create another query & view where I’ll set ValidTimeStateEnabled to Yes. Then I’ll merge this query also in my union query.

4

I created a duplicate of the query and edited JoinMode property of HcmWorkerTitle datasourse from OuterJoin to NoExistsJoin. In both views I set ValidTimeStateEnabled to Yes. I added the new view to my union query and it solved the problem. Of course I had to add dummy ValidFrom and ValidTo fields to match the fieldlists of other views in a union query. I filled them with DateTimeUtil::utcNow() and DateTimeUtil::maxValue().

I had one workaround in mind but for some reason I couldn’t make it work. I was going to check whether RecId of HcmWorkerTitle table was empty.

rangeWorkerTitle.value(strFmt(’(((ValidFrom <= %1) && (ValidTo >= %1)) || (HcmWorkerTitleRecId == %2)))’,
DateTimeUtil::toStr(DateTimeUtil::utcNow()), SysQuery::value(0) ));

But the problem with this code was that AX was unable to filter HcmWorkerTitleRecId successfully. There was no problem when I checked whether HcmWorkerTitleRecId was equal to some positive number, but whenever I tried to check whether it was 0, AX wasn’t returning any result. AX was unable to find records with value 0 in HcmWorkerTitleRecId even in a view, using Ctrl+G, I don’t know why. After spending too much time to find the cause of this behaviour I decided to use the above-mentioned solution.