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.
If your view contains a date-effective table, wouldn’t setting view’s property ValidTimeStateEnabled to Yes do what you want?
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.
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.