Building a query object

Query objects are used to visually build SQL statements, which can be used by Dynamics AX reports, views, forms, and other objects. Normally, queries are stored in the AOT, but they can also be dynamically created from code. This is normally done when visual tools cannot handle complex and dynamic queries.

In this recipe, we will create a query dynamically from the code to retrieve project records from the project management module. We will select only the projects of type fixed price, starting with 2 in its number and containing at least one hour transaction.

How to do it...

Carry out the following steps in order to complete this recipe:

1. 1. Open the AOT, create a new job named ProjTableQuery, and enter the following code:

   static void ProjTableQuery(Args _args)
   {
   Query query;
   QueryBuildDataSource qbds1;
   QueryBuildDataSource qbds2;
   QueryBuildRange qbr1;
   QueryBuildRange qbr2;
   QueryRun queryRun;
   ProjTable projTable;
   query = new Query();
   qbds1 = query.addDataSource(tableNum(ProjTable));
   qbds1.addSortField(
   fieldNum(ProjTable, Name),
   SortOrder::Ascending);
   qbr1 = qbds1.addRange(fieldNum(ProjTable,Type));
   qbr1.value(queryValue(ProjType::FixedPrice));
   qbr2 = qbds1.addRange(fieldNum(ProjTable,ProjId));
   qbr2.value(queryValue('2') + '*');
   qbds2 = qbds1.addDataSource(tableNum(ProjEmplTrans));
   qbds2.relations(true);
   qbds2.joinMode(JoinMode::ExistsJoin);
   queryRun = new QueryRun(query);
   while (queryRun.next())
   {
   projTable = queryRun.get(tableNum(ProjTable));
   info(strFmt(
   "%1, %2, %3",
   projTable.ProjId,
   projTable.Name,
   projTable.Type));
   }
   }
   

2. 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 ProjTable data source to the query object by calling its addDataSource() member method. The method returns a reference to the QueryBuildDataSource object—qbds1. Here, we call the addSortField() method to enable sorting by project name.

The following two blocks of code create two ranges. The first is to show only projects of type fixed price and the second one is to list only records, where the project number starts with 2. 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. The range value is set by calling value() on the QueryBuildRange object itself. It is a good practice to use the queryValue() function to process values before applying them as a range. More functions such as queryNotValue(), queryRange(), and so on can be found in the Global application class. Note that these functions are actually shortcuts to the SysQuery application class, which in turn have 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. In this example, we are displaying projects that have at least one posted hour line. We start by adding the ProjEmplTrans table as another data source.

Next, we need to add relations between the tables. If relations are not defined on tables, we will have to use the addLink() method with relation field ID numbers. In this example, relations on the tables are already defined so it is enough only to enable them by calling the relations() method with true 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 the attached data source.

The 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 workarounds, such as using temporary tables or similar tools, but we can use the Dynamics AX feature that allows passing a part of raw SQL string as a range.

In this case, the range has to be formatted in a similar manner as a fully qualified SQL where clause, including field names, operators, and values. The expressions have to be formatted properly before using them in a query. Here are some of the rules:

• The expression must be enclosed within single quotes.

• Inside, the whole expression has to be enclosed in parenthesis.

• Each subexpression must be enclosed in parentheses too.

• String values have to be enclosed within double quotes.

• For enumerations use their numeric values.

• For value formatting use various Dynamics AX functions, such as queryValue(), Date2StrXpp(), or methods from the SysQuery class.

Let us replace the code from the previous example:

qbr1.value(queryValue(ProjType::FixedPrice));

with the new code:

qbr1.value(strFmt(
'((%1 = %2) || (%3 = "%4"))',
fieldStr(ProjTable,Type),
ProjType::FixedPrice+0,
fieldStr(ProjTable,ProjGroupId),
queryValue('TM1')));

Notice that by adding zero to the enumeration in the previous code, we can force the strFmt() function to use the numeric value of the enumeration.

Now, the result will also include all the projects belonging to the group TM1 regardless of their type:

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 project sorting based on a ProjSortingId extended data type. Although such fields are very much the same as normal fields, in queries they should be addressed in a slightly different manner. In order to demonstrate the usage, let us modify the example by filtering the query to list only those projects containing the value South in the field labelled Sort field 2, which is the second value in the array.

First, let us 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(ProjTable,SortingId),2));
qbr3.value(queryValue('South'));

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 the addRange() method. This function can also be used anywhere, where addressing the dimension fields is required.

Now, we can run this job, as the project list based on previous criteria will be reduced even more to match projects having only a specific Sort field 2:

See also

See Chapter 3, Working with Data in Forms:

• Creating a custom filter

See Chapter 4, Building Lookups:

• Using a form for building a lookup