There’s no mystery here but a misperception.
Recently, a colleague found a little issue when using an AOT query to feed a view with a range dynamically filtered using a SysQueryRangeUtil method.
Recreating the issue
The query is pretty simple, only showing ledger transaction data from the GeneralJournalEntry and GeneralJournalAccountEntry tables. A range in the Ledger field from the current company was added as you can see in the pic below:
We created a new range method by extending the SysQueryRangeUtil class. Using the Ledger::current() to filter the active company.
The we used the query to feed data to the view and added two fields just for testing purposes:
Everything quite straightforward. Let’s check the view in the table browser…
No data! And I can tell there’s data in here:
What’s going on in here? If we use the query in a job (yeah, I know, Runnable Class…) the range is filtering the data as expected.
So… let’s see the view design in SSMS:
Well, it definitely looks like something’s being filtered in here. The range is working! Is it? Sure? Which company does that Ledger table RecId corresponds to?
What’s going on?
There’s an easy and clear explanation but one doesn’t think of it until he faces this specific issue. While the view* is a Data Dictionary object, and when the project is synchronized the view is created in SQL Server, the query* is a X++ object and only exists within the application. The view is created in SQL and we can see and query it in SSMS. The AOT query doesn’t. It feeds the view and provides a data back end, but all X++ added functionality stays in 365, including the SysQueryRangeUtil filters.
The solution is an easy one. Removing the range in the query and adding it in the form data source will do the trick (if this can be considered a trick…).
(*) Note: the links to the docs point to AX 2012 docs but should be valid.
