Using dynamic query values (SysQueryRangeUtil) in Dynamics AX
2 minutes to read
Whenever I setup Cues for customers in AX 2012 I use dynamic filtering to create actionable cues representing relevant data. I do tend to forget the precise name of the methods and number of parenthesis etc. so here is the full list of end-user available methods based on the SysQueryRangeUtil class:
(dayRange(-1,2)) gives a query result of all records from yesterday until the day after tomorrow.Note that it is unfortunately not possible to use decimals, only full days work.
(day(-1)) gives all records of yesterday, (day(1)) gives records of tomorrow.Remember that options such as <(day(-1)) works for identifying for example any date before yesterday
(monthRange(-1,0)) last month and this month
(yearRange(-1,0)) last year and this year
(lessThanDate(-1)) (calculated from the AX session date)(lessThanUtcDate(-1)The date represented on the AOS(lessthanUtcNow())Calculates from “Now”
greaterThanDate(5)) (calculated from the AX session date)(greaterThanUtcDate(-1)The date represented on the AOS)(greaterthanUtcNow())Calculates from “Now”
(currentuserid())Take the user id and filter on for example “created by”Good for making a generic query relevant for the individual users, forexample a Cue along the lines of "Sales orders I have created"
(currentWorker()) A somewhat advanced option but nice for filtering on records where you have assigned for example a sales responsible.Note that the value returned is a record IDFor example for a cue along the lines of "Sales orders I am responsible for"
(currentparty()) Similar to the currentWorker but could potentially be used if the user was something other than a Worker.Note the returned value is a record id.
(currentcompany())For filtering on “system” tables such as the worker table
Remember also the ability to compare two fields on the same table using the AOT names within a set of parenthesis. The system names can be found on the personalization screen. Just recently I have used it forexample in the below example to compare reequested ship date and confirmed ship date on a sales order (back) lines.