Using Expressions in Query Ranges

Sometimes, it’s hard to create a query that exactly matches what we need. For instance, using OR statements can be tricky. But we can tackle this challenge by utilizing expressions as query range values. Also, there are various methods available to assist us in building ranges. These methods are found in the SysQuery and SysQueryRangeUtil classes and most of the time we are using these methods in grids and with other objects.  

In this article, we will use expression in query to filter out the from-date and to-date columns which are based on different fields as shown in the example below:  

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)));
    } 
}