Using Expressions in Query Ranges in D365 FO
Query range value expressions can be used in any query where you need to express a range that is more complex than is possible with the usual dot-dot notation (such as 5012..5500).
For example, to create a query that selects the records from the table MyTable where field A equals x or field B equals y, do the following.
The example above will then look like the following code example.
strFmt('((A == %1) || (B == %2))',x,y)
To get complete compile-time stability, use intrinsic functions to return the correct field names, as shown in the following code example.
strFmt('((%1 == %2) || (%3 == %4))',
fieldStr(MyTable,A), x,
fieldStr(MyTable,B), y)
static void AddRangeToQuery3Job(Args _args)
{
Query q = new Query(); // Create a new query.
QueryRun qr;
CustTable ct;
QueryBuildDataSource qbr1;
str strTemp;
;
// Add a single datasource.
qbr1 = q.addDataSource(tablenum(CustTable));
// Name the datasource 'Customer'.
qbr1.name("Customer");
// Create a range value that designates an "OR" query like:
// customer.AccountNum == "4000" || Customer.CreditMax > 2500.
// Add the range to the query data source.
qbr1.addRange(fieldNum(CustTable, AccountNum)).value(
strFmt('((%1.%2 == "4000") || (%1.%3 > 2500))',
qbr1.name(),
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, CreditMax)));
// Print the data source.
print qbr1.toString();
info(qbr1.toString());
// Run the query and print the results.
qr = new QueryRun(q);
while (qr.next())
{
if (qr.changedNo(1))
{
ct = qr.getNo(1);
strTemp = strFmt("%1 , %2", ct.AccountNum, ct.CreditMax);
print strTemp;
info(strTemp);
}
}
pause;
}
[DataSource]
class HSSalesTableCharges
{
/// <summary>
///
/// </summary>
public void executeQuery()
{
Query query = new Query();
QueryBuildDataSource qbd1, qbd2;
qbd1 = query.addDataSource(tableNum(HSSalesTableStaging));
qbd1.name("HSSalesTableStagingHeader");
qbd2 = qbd1.addDataSource(tableNum(HSSalesOrderChargesStaging));
qbd2.joinMode(JoinMode::InnerJoin);
qbd2.name("HSSalesTableCharges");
qbd2.addRange(fieldNum(HSSalesTableStaging, RecId)).value(strFmt('%1.%2 == %3.%4',qbd1.name(), fieldStr(HSSalesTableStaging, RecId), qbd2.name(), fieldStr(HSSalesOrderChargesStaging, RefRecId)));
super();
}
}
Happy DAXing...
For example, to create a query that selects the records from the table MyTable where field A equals x or field B equals y, do the following.
- Add a range on field A.
- Set the value of that range to the expression (if x = 10 or y = 20), as a string: ((A == 10) || (B == 20))
- 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.
The example above will then look like the following code example.
strFmt('((A == %1) || (B == %2))',x,y)
To get complete compile-time stability, use intrinsic functions to return the correct field names, as shown in the following code example.
strFmt('((%1 == %2) || (%3 == %4))',
fieldStr(MyTable,A), x,
fieldStr(MyTable,B), y)
Note: 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."
Example of adding a range to a query
The following code programmatically adds a range to a query and uses string substitution to specify the data source and field name. The range expression is associated with the CustTable.AccountNum field; however, because the expression specifies the data sources and field names, the expression can be associated with any field in the CustTable table.
static void AddRangeToQuery3Job(Args _args)
{
Query q = new Query(); // Create a new query.
QueryRun qr;
CustTable ct;
QueryBuildDataSource qbr1;
str strTemp;
;
// Add a single datasource.
qbr1 = q.addDataSource(tablenum(CustTable));
// Name the datasource 'Customer'.
qbr1.name("Customer");
// Create a range value that designates an "OR" query like:
// customer.AccountNum == "4000" || Customer.CreditMax > 2500.
// Add the range to the query data source.
qbr1.addRange(fieldNum(CustTable, AccountNum)).value(
strFmt('((%1.%2 == "4000") || (%1.%3 > 2500))',
qbr1.name(),
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, CreditMax)));
// Print the data source.
print qbr1.toString();
info(qbr1.toString());
// Run the query and print the results.
qr = new QueryRun(q);
while (qr.next())
{
if (qr.changedNo(1))
{
ct = qr.getNo(1);
strTemp = strFmt("%1 , %2", ct.AccountNum, ct.CreditMax);
print strTemp;
info(strTemp);
}
}
pause;
}
Another Example of adding a range to a query
The following code programmatically adds a range to a query and uses string substitution to specify the data source and field name. The range expression is associated with the HSSalesTableStaging.RecID field which is linked with HSSalesOrderChargesStaging.RefRecID; however, because the expression specifies the data sources and field names, the expression can be associated with any field in the HSSalesTableStaging table.
[DataSource]
class HSSalesTableCharges
{
/// <summary>
///
/// </summary>
public void executeQuery()
{
Query query = new Query();
QueryBuildDataSource qbd1, qbd2;
qbd1 = query.addDataSource(tableNum(HSSalesTableStaging));
qbd1.name("HSSalesTableStagingHeader");
qbd2 = qbd1.addDataSource(tableNum(HSSalesOrderChargesStaging));
qbd2.joinMode(JoinMode::InnerJoin);
qbd2.name("HSSalesTableCharges");
qbd2.addRange(fieldNum(HSSalesTableStaging, RecId)).value(strFmt('%1.%2 == %3.%4',qbd1.name(), fieldStr(HSSalesTableStaging, RecId), qbd2.name(), fieldStr(HSSalesOrderChargesStaging, RefRecId)));
super();
}
}
Happy DAXing...
