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”))’,

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”))’,
            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.”

Feel free to post any comment / feedback here.

If you liked the story and want to see more of this kind of content, please follow our twitter handle @CursorRun or FB page @CursorRun
Default image
Rahul Sharma
Rahul is a technology enthusiast, solutions architect, trainer, and blogger, working on various Microsoft and open source solutions with more than 18 years of industry experience. He specially takes interest in designing enterprise applications, cloud integrations, IoT, and other architecture rich business solutions. Rahul is a guest author with us and shares his knowledge through technology blog posts.
Articles: 59


Enter your email address below to subscribe to our website. We will never flood your inbox with unwanted emails & you can always unsubscribe anytime.

One comment

  1. good explanation..

Leave a Reply

%d bloggers like this: