Using dynamic query values in Dynamics AX
Bloody hell... MSDN just decided to delete my blog posts ... The pictures are gone and Linked does not support tables.. sigh.. so this is a copy paste of the text until I get around to update it with new D365 pictures
I keep forgetting the syntax of the dynamic ranges in Dynamics AX, especially the lesser used ones. here is an overview of them all ready for cues or "Favourized" list pages with persoanlized queries.
I recently needed some more advanced date/time query functionality, a little searching and a couple of pages assisted getting my immediate questions answered. I was however left with the vague feeling that the underlying SysQueryRangeUtil class could provide additional possibilities. Here is the result of that deep dive. Where I have copied the text on the AX2009 methods from Peter Chan’s excellent blog post and added the AX2012 (R2) methods
The SysQueryRangeUtil contains in AX 2012 R2 47 relevant methods. That breaks down into 15 “developer” methods returning values such as “Current user” or “Current user language”, 7 methods about returning information about the current user, 13 methods about very specific filters on single tables/forms. The remaining 12 methods are about advanced query functionality. I hope the below overview and examples will be of assistance:
Method
Description
Takes values
Example – todays date is April 3, 2014
Create a date query string that is relative to the session date.
day(int relativeDays = 0)
AX2009-AX2012:
Relative days to today's date. Default to 0 when the 'relativeDays' optional parameter is ignored. Can be offset + or -.
Eg.
(day(-1)) will gives you 02/04/2014
(day(0)) will gives you 03/04/2014
(day(1)) will gives you 04/04/2014
Creates a query string that represents a date range that is based on days.
dayRange(int relativeDaysFrom = 0, int relativeDaysTo = 0)
AX2009-AX2012:
Relative day range from and to given today's date.
To get past 1 week + next 1 week date, you can use (-7, 7).
Eg.
(dayRange(0,0)) gives you 03/04/2014 to 03/04/2014
(dayRange(-2,2)) gives you 01/04/2014 to 05/04/2014
(dayRange(0,2)) gives you 03/04/2014 to 05/04/2014
(dayRange(-2,0)) gives you 01/04/2014 to 03/04/2014
Creates a query string for all dates that are after the specified date.
greaterThanDate(int relativeDays = 0)
AX2009-AX2012:
Similar to (day()), but give the date of after the offset.
Uses the Session date to find “Now”
Eg.
(greaterThanDate(0)) gives you "> 03/04/2014"
(greaterThanDate(2)) gives you "> 05/04/2014"
(greaterThanDate(-2)) gives you "> 01/04/2014 "
Creates a query string for all dates that are after the specified date.
greaterThanUtcDate(int relativeDays = 0)
AX2012:
Is almost the same as greaterThanDate() except that this uses the UTC now to find “Now” with the time offset by the users time zone
Creates a query string for all dates that are after the current time.
greaterThanUtcNow()
AX2012:
Creates a query string for all dates that are less than the specified date.
lessThanDate(int relativeDays = 0)
Creates a query string for all dates that are before the specified date.
lessThanUtcDate(int relativeDays = 0)
Creates a query string for all dates that are before the current time.
lessThanUtcNow()
Creates a query string that represents a range of dates that uses months.
monthRange(int relativeMonthsFrom = 0, int relativeMonthsTo = 0)
Creates a query string that represents a date range that uses years.
yearRange(int relativeYearsFrom = 0, int relativeYearsTo = 0)
Retrieves the session date that can be used in queries.
currentSessionDate()
Retrieves the session date and time that can be used in queries.
currentSessionDateTime()
Great article David - thanks!