Building a query object

Query objects in Dynamics 365 for Finance and Operations are used to build SQL statements for reports, views, forms, and so on. They are normally created in the AOT using the drag and drop functionality and by defining various properties. Query objects can also be created from the code at runtime. This is normally done when AOT tools cannot handle complex and/or dynamic queries.

In this recipe, we will create a query from the code to retrieve project records from the Project management module. We will select only the projects of the type Time & material, starting with 00005 in its number and containing at least one hour transaction. The project list will be sorted by project name.

How to do it...

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

1. Open the project area, create a runnable class named ProjTableQuery, and enter the following code snippet:

         class ProjTableQuery 
       {         
         /// <summary> 
         /// Runs the class with the specified arguments. 
         /// </summary> 
         /// <param name = "_args">The specified arguments.</param> 
         public static void main(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::TimeMaterial)); 
 
          qbr2 = qbds1.addRange(fieldNum(ProjTable,ProjId)); 
          qbr2.value( 
           SysQuery::valueLike(queryValue('00005'))); 
 
          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. Run the class and you will get a screen similar to the following screenshot:

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 the project name.

The following two blocks of code create two ranges. The first block of code shows only the projects of the time & material type and the second one lists only the records where the project number starts with 00005. These two filters are automatically added together using SQL's AND operator. The QueryBuildRange objects are created by calling the addRange() member method of the QueryBuildDataSource object with the field ID number as the argument. The range value is set by calling value() on the QueryBuildRange object itself. We use the queryValue()function from the Global class and the valueLike() function from the SysQuery class to prepare the values before applying them as a range. More functions, such as queryNotValue() and queryRange(), can be found in the Global application class by navigating to AOT | Classes. Note that these functions are actually shortcuts to 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 the data sources using SQL's 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 relationships between the tables. If relationships are not defined on tables, we will have to use the addLink() method with relation field's ID numbers. In this example, relations in the tables are already defined, so you only need to enable them by calling the relations() method with true as an argument.

Calling joinMode() with JoinMode::ExistsJoin as a parameter ensures that only the projects that have at least one hour transaction will be selected. In situations like this, where we do not need any data from the second data source, performance-wise it is better to use an exists join instead of the inner join. This is because the inner join fetches the data from the second data source and, therefore, takes longer to execute.

The last thing that needs to be done 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 the 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 SQL's AND operator. In most cases, this is fine, but sometimes complex user requirements demand ranges to be added using SQL's OR operator. There might be a number of workarounds, such as using temporary tables or similar tools, but we can use the Dynamics 365 for Operations feature that allows you to pass a part of a raw SQL string as a range.

In this case, the range has to be formatted in a manner similar to a fully-qualified SQL where clause, including field names, operators, and values. The expressions have to be formatted properly before you use 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 within parentheses
• Each subexpression must also be enclosed within parentheses
• String values have to be enclosed within double quotes
• For enumerations, use their numeric values

For value formatting, use various Dynamics 365 for Operations functions, such as queryValue() and date2StrXpp(), or methods from the SysQuery class.

Let's replace the code snippet from the previous example with the following lines of code:

    qbr2.value(SysQuery::valueLike (queryValue('00005'))); 
    with the new code: 
    qbr2.value(strFmt('((%1 like "%2") || (%3 = %4))', 
     fieldStr(ProjTable,ProjId),queryvalue('00005*'), 
      fieldStr(ProjTable,Status),ProjStatus::InProcess+0)); 

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. The strFmt() output should be similar to the following line:

    ((ProjId like "00005*") || (Status = 3)) 

Now if you run the code, besides all the projects starting with 00005, the result will also include all the active projects, as shown in the following screenshot:

See also

• The Creating a custom filter recipe in Chapter 3, Working with Data in Forms
• The Using a form for building a lookup recipe in Chapter 4, Building Lookups