Working with the Query ranges

The standard filter functionality in Ax forms is very powerful and use full way to filter records. (Cntrl + G) is the shortcut for the filter functionality. Using this filter functionality in your code is something you’ll definitely use at some point in time as a programmer.

Technically this filter functionality is called adding ranges (range = where clause of the query). This is the very details topic which includes scenarios from Simple to very advance level.

Some time you need to show filtered records on the form (adding ranges to form datasource). This is very simple, you just need write some code.

this.query().dataSourceName(‘CustTable’).addRange(fieldnum(CustTable,Currency)).value(queryvalue(‘USD’)); http://dynamics-ax-live.blogspot.com/2010/03/how-to-filter-records-in-form-by-code.html

Some time you need to use expressions or if you need OR clause in the query. Support of expressions is very good to implement the complex queries containing some complex joins and conditions. if you need to implement OR clause then again you need to use the expressions. If you add ranges to two different fields then it default supports the “And &&” clause, the “OR ||” clause is only supported if you add ranges to same field with different value. In all other cases we need to use the expressions to implement the OR clause. For details on implementing the expressions

this.query().documentaries(‘InventTable’).addRange(fieldnum(InventTable,RecId)).value(strFmt(‘(ItemId == “%1”)’, queryValue(“B-R14”))); http://www.axaptapedia.com/Expressions_in_query_ranges

if you need to add range on the utcdateTime field. you can add range to dateTime field, but only date value is pass to range to make it working, query will not work on time part. for details on adding the utcdateTime field as a range see below links
Complete details on utcdateTime from Ivan (Vanya) Kashperuk blog
https://community.dynamics.com/blogs/axvanyakashperuk/comments/63774.aspx
Example from Radjip’s link: https://community.dynamics.com/blogs/axrajdipdas/comments/51852.aspx

This is another blog digging in the details of ranges.
http://www.artofcreation.be/2010/04/09/split-and-add-ranges-on-datasource/

Other than that there are two important classes in Dynamics ax 2009 which deals with the query ranges.
SysQueryRangeUtil
The SysQueryRangeUtil class is just the great enhancement to the AOT queries made in AX 2009. It is possible now to specify a range on the AOT query with the value taken from a method’s return. The requirements for such range method are quite simple: the method should be static, return string, be a member of the SysQueryRangeUtil class and not be related on the data fetched by the query
Microsoft Dynamics AX Toolbox: https://community.dynamics.com/blogs/daxit/archive/2010/01/13/dynamics-ax2009-8211-useful-utility-8220-sysqueryrangeutil-8221.aspx
MSDN Details about the class: http://msdn.microsoft.com/en-us/library/cc618616.aspx

SysQuery
The SysQuery class is another useful class for working with the ranges, it has numerous method related to Query.
MSDN Details about the class: http://msdn.microsoft.com/en-us/library/aa866017.aspx

%d bloggers like this: