Implement query objects and query builder

  • 10 minutes

In Dynamics 365, you can use query objects in the development platform to build SQL statements. You can use these queries to render data into forms, reports, views, and more. Additionally, you can write SQL statements by using select queries. The query object helps you create the SQL dynamically at runtime.

We recommend that you use query objects in forms instead of using tables directly. By using query objects, you can select those fields that you want to display in the form, unlike tables. This approach will help reduce the size of the SQL statement that's running behind the form, and it helps improve performance. The same notion applies for reports and views also.

QueryBuildDataSource class

The QueryBuildDataSource class is the building block for creating the query. It defines the data source to be added to the query. It can specify the table fields to be added, sort order of the records, and so on. Additionally, it can establish the relationship by applying joins between data sources.

Example

You have a TrainingMaster table with two fields: TrainingID and TrainingDate. You need to display both fields in the ascending order of TrainingID by building a query object. The coding pattern is as follows:

 class TrainingMasterQuery
 {
    public static void main(Args _args)
    {
        QueryBuildDataSource    qbds;

        Query query = new Query();
 
        qbds = query.addDataSource(tableNum(TrainingMaster));
        qbds.addSortField(fieldNum(TrainingMaster, TrainingID), SortOrder::Ascending);

        QueryRun queryRun = new QueryRun(query);
 
        while (queryRun.next())
        {
            TrainingMaster trainingMaster = queryRun.get(tableNum(TrainingMaster));
            info(strFmt("%1, %2", trainingMaster.TrainingID, date2Str(trainingMaster.TrainingDate, 123, DateDay::Digits1or2, DateSeparator::Hyphen, DateMonth::Digits1or2, DateSeparator::Hyphen, DateYear::Digits4)));
        }
    }
}

QueryBuildRange class

The QueryBuildRange class defines filtering for the records that are fetched from the data source.

Example

In the TrainingMaster table, a new field named TrainingType has been added. This new field has two values: Online and Classroom. You need to filter all online trainings by building a query object. The coding pattern is as follows:

class TrainingMasterQuery
{
    public static void main(Args _args)
    {
        Query query = new Query();
 
        QueryBuildDataSource qbds = query.addDataSource(tableNum(TrainingMaster));
        qbds.addSortField(fieldNum(TrainingMaster, TrainingID), SortOrder::Ascending);

        QueryBuildRange qbr = qbds.addRange(fieldNum(TrainingMaster, TrainingType));
        qbr.value(queryValue(TrainingType::Online));

        QueryRun queryRun = new QueryRun(query);
 
        while (queryRun.next())
        {
            TrainingMaster trainingMaster = queryRun.get(tableNum(TrainingMaster));
            info(strFmt("%1, %2", trainingMaster.TrainingID, date2Str(trainingMaster.TrainingDate, 123, DateDay::Digits1or2, DateSeparator::Hyphen, DateMonth::Digits1or2, DateSeparator::Hyphen, DateYear::Digits4)));
        }
    }

}

Advanced query syntax

A range of advanced query syntax exists for string, number, or date fields. For string fields, you can filter by equal, not equal, range, wildcard, and so on. For number fields, the filter options can be equal, not equal, greater than, less than, and so on. For date fields, the filter options can be day-based, day range, month range, year range, greater/less than dates, and so on.

Example

A new field called NoofDays has been added to the TrainingMaster table. An advanced filter will be applied to the query to show all trainings that have a number of days greater than 2. The coding pattern is as follows:

class TrainingMasterQuery
{
    public static void main(Args _args)
    {
        Query query = new Query();
 
        QueryBuildDataSource qbds1 = query.addDataSource(tableNum(TrainingMaster));
        qbds1.addSortField(fieldNum(TrainingMaster, TrainingID), SortOrder::Ascending);

        QueryBuildRange qbr1 = qbds1.addRange(fieldNum(TrainingMaster, NoofDays));
        qbr1.value(queryValue(">2"));

        QueryRun queryRun = new QueryRun(query);
 
        while (queryRun.next())
        {
            TrainingMaster trainingMaster = queryRun.get(tableNum(TrainingMaster));
            info(strFmt("%1, %2", trainingMaster.TrainingID, date2Str(trainingMaster.TrainingDate, 123, DateDay::Digits1or2, DateSeparator::Hyphen, DateMonth::Digits1or2, DateSeparator::Hyphen, DateYear::Digits4)));
        }
    }

}

Build a query that includes a relational table

By using a query object, you can build a query that includes related tables. You can define all types of joins as required by your query.

Example

A new table named TrainerTable has been created with three fields: TrainerID, TrainerName, and TrainerType (enum values: Functional, Technical, and Softskill). Additionally, the TrainerID field has been added to the TrainingMaster table, establishing a foreign key relationship between the tables. You need to filter all online trainings that are delivered by technical trainers by building a query object.

The coding pattern is as follows:

class TrainingMasterQuery
{
    public static void main(Args _args)
    {
        Query query = new Query();
 
        QueryBuildDataSource qbds1 = query.addDataSource(tableNum(TrainingMaster));
        qbds1.addSortField(fieldNum(TrainingMaster, TrainingID), SortOrder::Ascending);

        QueryBuildRange qbr1 = qbds1.addRange(fieldNum(TrainingMaster, TrainingType));
        qbr1.value(queryValue(TrainingType::Online));

        QueryBuildDataSource qbds2 = qbds1.addDataSource(tableNum(TrainerTable));
        qbds2.relations(true);
        qbds2.joinMode(JoinMode::ExistsJoin);

        QueryBuildRange qbr2 = qbds2.addRange(fieldNum(TrainerTable, TrainerType));
        qbr2.value(queryValue(TrainerType::Technical));

        QueryRun queryRun = new QueryRun(query);
 
        while (queryRun.next())
        {
            TrainingMaster trainingMaster = queryRun.get(tableNum(TrainingMaster));
            info(strFmt("%1, %2", trainingMaster.TrainingID, date2Str(trainingMaster.TrainingDate, 123, DateDay::Digits1or2, DateSeparator::Hyphen, DateMonth::Digits1or2, DateSeparator::Hyphen, DateYear::Digits4)));
        }
    }

}

Learn more

For more information about query objects and the query builder, see the following sites: