- Overcoming Single-Field Limits: Standard
QueryBuildRangeobjects are tied to a single field; expressions allow multi-field Boolean logic. - Complex WHERE Clauses: Writing dynamic SQL-like conditions combining
ANDandORlogic across multiple columns. - Proper String Formatting: Utilizing
strFmtand parentheses syntax to ensure clean SQL generation and prevent syntax runtime errors. - Date & Enum Conversions: Safely converting dates and enumeration values into literal expressions using
date2StrXpp()andany2Int(). - AOT & Runtime Performance: Maximizing query engine performance without falling back to slow client-side filtering.
Overview: When Standard Ranges Fall Short
In standard X++ development, adding a range via queryBuildDataSource.addRange(fieldNum(Table, Field)) applies a straightforward equality or simple range condition against that single field.
However, enterprise business scenarios frequently require complex Boolean logic spanning multiple fields—such as evaluating ((FieldA == Value1 && FieldB == Value2) || (FieldC == Value3)). In these scenarios, X++ supports embedding expressions directly into QueryBuildRange.value().
“Query expressions unlock full SQL-grade filtering power in X++ without abandoning the security and query-building capabilities of the AOT Query framework.”
Complete X++ Query Expression Implementation
The following sample demonstrates initializing an advanced query range with dynamic expressions:
void initQuery()
{
Query query;
QueryRun queryRun;
QueryBuildDataSource qbdsProjPeriodLine;
QueryBuildRange rangePeriodId;
query = queryRun.query();
qbdsProjPeriodLine = query.dataSourceTable(tableNum(ProjPeriodLine));
if (periodFrom && periodTo)
{
qbdsProjPeriodLine.addRange(fieldNum(ProjPeriodLine, PeriodFrom)).value(strFmt('((%1.%2 >= %4) && (%1.%3 <= %5))',
qbdsProjPeriodLine.name(),
fieldStr(ProjPeriodLine, PeriodFrom),
fieldStr(ProjPeriodLine, PeriodTo),
date2StrXpp(periodFrom),
date2StrXpp(periodTo)));
}
}
Expression Formatting Rules & Best Practices
- Enclose in Parentheses: The entire expression string passed to
range.value()must be enclosed in outer parentheses, e.g.strFmt('((%1 == "%2") || (%3 == "%4"))', ...). - Use fieldStr() and tableStr(): Never hardcode field names as string literals; use compile-time metadata intrinsics
fieldStr()to prevent silent breaks during refactoring. - Literal Value Escaping: String comparisons must wrap values in internal quotes to compile valid SQL WHERE clauses.