AOT query range on dateTime fields to get filter records using (lessThanUtcNow()) and (greaterThanUtcNow()) [AX 2012]

Finance and Operations/AX Developer Forum
1

There’s a standard Query called HcmPositionLookup which has a DataSource HcmPositionDetail. It has two ranges on dateTime fields ValidFrom and ValidTo. The values are specified in Value property of the ranges. Validfrom has a value (lessThanUtcNow()) and ValidTo has a value (greaterThanUtcNow()). These are static methods of SysQueryRangeUtil class.The table HcmPositionDetail has property ValidTimeStateFieldType set to UtcDateTime.

I’m using the exact approach on my Query, which has couple dataSources. One of them is HcmWorkerTitle. This table has also value UtcDateTime in property ValidTimeStateFieldType and has dateTime fields ValidFrom and ValidTo. When I set the range on ValidFrom using (lessThanUtcNow()) I get this error after compiling the Query:

“Invalid value (<DateTimeUtil::utcNow()) specified in the query predicate”.

Also, when I try to open this Query I get the error “getCompanyRange API failed to detect permissions”. (Tried to search for these errors on the web but couldn’t find anything).

I checked how this range is interpreted in SQL-like language and got this: …OUTER JOIN * FROM HcmWorkerTitle(HcmWorkerTitle) ON HcmWorker.RecId = HcmWorkerTitle.Worker AND (((lessThanUtcNow())))

So no "(<DateTimeUtil::utcNow())" is included there.

Then I checked the correct version i.e. HcmPositionLookup’s range interpetation:

…OUTER JOIN * FROM HcmPositionDetail(HcmPositionDetail) ON HcmPosition.RecId = HcmPositionDetail.Position AND (((lessThanUtcNow()))) AND (((greaterThanUtcNow())))…

Couldn’t identify the problem because there are no apparent differences.

And only after all this I found this in the white paper “Using Date Effective Patterns AX2012”:

"AOT query
A valid time state table can be one of the data sources of an AOT query. Currently, there are no
properties in the AOT query to specify the date range or AsOfDate for the records. This can be
achieved programmatically or through form and SysQueryForm, which is demonstrated in the later
section"

And had a thought: Maybe this approach can’t be used on new Queries and work only for standard Microsoft queries? Or maybe I’m not doing something correctly.

I hope someone on this forum has tackled this before. If so, then please share your experiences.

2

Please show us how did you define the range value and explain why you’re not using the normal logic of date-effective data handled by AX kernel. Manaully adding ranges for these fields is highly unusual.