Friday 12 January 2024

Using Expressions in Query Ranges

 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.

This example can be useful when there is a scenario of adding OR conditions on to query build data source ranges.

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;

    }