Dynamics Ax Query Extended Range Value Expression

In this article we will see how to define extended query ranges using expressions. These expressions can be used in any query where you need to express a range that is more complex than is possible with the usual range value notations.

The rules for creating query range value expressions are:

• Enclose the whole expression in parentheses.
• Enclose all subexpressions in parentheses.
• Use the relational and logical operators available in X++.
• Only use field names from the range's data source.
• Use the dataSource.field notation for fields from other data sources in the query.
• Values must be constants in the expression, so any function or outside variable must be calculated before the expression is evaluated by the query. This is typically done by using the strFmt function.

Let's do it with some examples:

OR clause on same field:
Query q;
QueryBuildDataSource qbd;
QueryBuildRange qbr;
q = new Query();
qbd = q.addDataSource(TableNum(CustTable));
qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
qbr.value('4005, 4006');

Result: SELECT * FROM CustTable WHERE ((AccountNum = N'4005' OR AccountNum = N'4006'))

or use this expression to achieve the same...

qbr.value(strFmt('((AccountNum == "%1") || (AccountNum == "%2"))',
QueryValue('4005'),
QueryValue('4006')));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4005") || (AccountNum == "4006"))))

OR clause on different fields: In this example we are using DataAreaId field to obtain range object but actual range is on AccountNum and Name. This means when you use range value expressions you can use any field to obtain range object and use it to insert your range in the query. The field you use to get range object is not included in the query. I usually use DataAreaId to denote that this is a special range.

qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));

qbr.value(strFmt('((%1 == "4000") || (%2 == "The Bulb"))',

fieldStr(CustTable, AccountNum),

fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((AccountNum == "4000") || (Name == "The Bulb"))))

The above result can also be obtained with this below example. The only difference is we are using DataSource name as well. This will also give you some idea on how to use expressions when more than one DataSources are involved.

qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));

qbr.value(strFmt('((%1.%2 == "4000") || (%1.%3 == "The Bulb"))',

qbd.name(),

fieldStr(CustTable, AccountNum),

fieldStr(CustTable, Name)));

Result: SELECT * FROM CustTable WHERE ((((CustTable_1.AccountNum == "4000") || (CustTable_1.Name == "The Bulb"))))

Query range value expressions are evaluated only at run time, so there is no compile-time checking. If the expression cannot be understood, a modal box will appear at run time that states "Unable to parse the value."