Building a query object

Building a query object

Query objects are used to visually build SQL statements. They are used by Dynamics AX

reports, views, forms, and other objects. Normally queries are stored in AOT, but they can also

be created from code dynamically. This is normally done when visual tools cannot handle

complex and dynamic queries. In this recipe, we will create one dynamically from code.

As an example, we will build a query that selects all active customers who belong to group 10

and have at least one sales order.

How to do it…

1. Open AOT, create a new job called CustTableSales, and enter the following code:

static void CustTableSales(Args _args)

{

Query query;

QueryBuildDataSource qbds1;

QueryBuildDataSource qbds2;

QueryBuildRange qbr1;

QueryBuildRange qbr2;

QueryRun queryRun;

CustTable custTable;

;

query = new Query();

qbds1 = query.addDataSource(tablenum(CustTable));

qbds1.addSortField(

fieldnum(CustTable, Name),

SortOrder::Ascending);

qbr1 = qbds1.addRange(fieldnum(CustTable,Blocked));

qbr1.value(queryvalue(CustVendorBlocked::No));

qbr2 = qbds1.addRange(fieldnum(CustTable,CustGroup));

qbr2.value(queryvalue(’10′));

qbds2 = qbds1.addDataSource(tablenum(SalesTable));

qbds2.relations(false);

qbds2.joinMode(JoinMode::ExistsJoin);

qbds2.addLink(

fieldnum(CustTable,AccountNum),

fieldnum(SalesTable,CustAccount));

queryRun = new QueryRun(query);

while (queryRun.next())

{

custTable = queryRun.get(tablenum(CustTable));

info(strfmt(

“%1 – %2″,

custTable.Name,

custTable.AccountNum));

}

}

2. Run the job, and the following screen should appear:

How it works…

First, we create a new query object. Next, we add a new CustTable data source to the query

by calling its addDataSource() member method. The method returns a reference to the

QueryBuildDataSource object—qbds1. Here, we call addSortField() to enable sorting by

customer name.

The following two blocks of code creates two filter ranges. The first is to show only active

customers and the second one is to list only customers belonging to a single group 10. Those

two filters are automatically added together using the SQL AND operator. QueryBuildRange

objects are created by calling the addRange() member method of the QueryBuildDataSource

object with the field ID number as argument. Range value is set by calling value() on the

QueryBuildRange object itself. It is a good practice to use queryvalue() or a similar function

to process values before applying them as a range. More functions like querynotvalue(),

queryrange(), and so on can be found in the Global application class. Note that these

functions actually process data using the SysQuery application class, which in turn has even

more interesting helper methods that might be handy for every developer.

Adding another data source to an existing one connects both data sources using the SQL

JOIN operator. It this example, we are displaying customers that have at least one sales

order. We start by adding the SalesTable table as another data source. We are going to

use custom relations between those tables, so we need to disable standard relations by

calling the relations() method with false as an argument. Calling joinMode() with

JoinMode::ExistsJoin as a parameter ensures that a record from a parent data source

will be displayed only if the relation exists in its attached data source. And finally, we create a

relation by calling addLink() and passing the field ID number of both tables.

Last thing to do is to create and run the queryRun object and show the selected data on

the screen.

There’s more…

It is worth mentioning a couple of specific cases when working with query objects from code.

One of them is how to use the OR operator and the other one is how to address array fields.

Using the OR operator

As you have already noted, regardless of how many ranges are added, all of them will be

added together using the SQL AND operator. In most cases, it is fine, but sometimes complex

user requirements demand ranges to be added using SQL OR. There might be a number of

work-arounds, like using temporary tables or similar, but I use the Dynamics AX feature that

allows passing raw SQL as a range.

In this case, the range has to be formatted like the fully qualified SQL WHERE clause including

field names, operators, and values. Each separate clause has to be in brackets. It is also very

important that filter values, especially if they are specified by the user, have to be properly

formatted before using them in a query.

Let’s replace the code from the previous example:

qbr2.value(queryValue(’10′));

with the new code:

qbr2.value(strfmt(

‘((%1 = “%2″) || (%3 = “%4″))’,

fieldstr(CustTable,CustGroup),

queryvalue(’10′),

fieldstr(CustTable,Currency),

queryvalue(‘EUR’)));

Now, the result would also include all the customers having the default currency EUR.

Using arrays fields

Some table fields in Dynamics AX are based on extended data types, which contains more

than one array element. An example in a standard application could be financial dimensions

based on the Dimension extended data type or project sorting based on ProjSortingId.

Although such fields are very much the same as normal fields, in queries, they should be

addressed slightly different. To demonstrate the usage, let’s modify the example by filtering

the query to list only customers containing a specific Purpose value. In the standard

application, Purpose is the third financial dimension, where the first is Department and the

second is Cost centre.

First, let’s declare a new QueryBuildRange object in the variable declaration section:

QueryBuildRange qbr3;

Next, we add the following code right after the qbr2.value(…) code:

qbr3 = qbds1.addRange(

fieldid2ext(fieldnum(CustTable,Dimension),3));

qbr3.value(queryvalue(‘Site1′));

Notice that we use the global fieldid2ext() function, which converts the field ID and

the array number into a valid number to be used by addRange(). This function can also be

used anywhere, where addressing the dimension fields is required. The value 3 as its second

argument here means that we are using a third dimension, that is, Purpose. In my application, I

have purposes defined as Site1, Site2, and Site3, so I simply use the first one as filter criteria.

Now, when we run this job, the customer list based on previous criteria will be reduced even

more to match customers having only a specific Purpose set.

-Harry