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.
Carry out the following steps in order to complete this recipe:
- 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)); } } }
- Run the class and you will get a screen similar to the following screenshot:
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.
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.
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: