Using dynamic query values (SysQueryRangeUtil) in Dynamics AX

  • Article

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:

 

Most used:

Name Used how Example
dayRange (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. image001
day (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 image003
monthRange (monthRange(-1,0)) last month and this month image005
yearRange (yearRange(-1,0)) last year and this year image007
lessThanDatelessThanUtcDatelessThanUtcNow (lessThanDate(-1)) (calculated from the AX session date)(lessThanUtcDate(-1)The date represented on the AOS(lessthanUtcNow())Calculates from “Now” image009
greaterThanDateGreaterThanUtcDategreaterThanUtcNow greaterThanDate(5)) (calculated from the AX session date)(greaterThanUtcDate(-1)The date represented on the AOS)(greaterthanUtcNow())Calculates from “Now” image011
currentUserId (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" image013
currentWorker (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" image015
currentParty (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. image017
currentCompany (currentcompany())For filtering on “system” tables such as the worker table image019

 

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.

image021