Empower Community

Build Dynamics Query in Dynamics 365 for Finance and Operations:

In this blog about all Query in D365 F&O in Details.

• Build Query in AOT

  you can build a query in the AOT using Visual Studio. An advantage of creating the query in the AOT is that it can be reused in various places, saving lines of identical code, and making wide-reaching query adjustment easier.

• Build Query in X++

  Queries can also be built dynamically with X++ code. Both approaches are used in the standard application. One advantage of making the query dynamic is that it is not public in the AOT and is protected against unintentional AOT changes.

• X++ Query Component

  • QueryRun:

    Use the queryRun object to execute the query and fetch data.

    QueryRun queryRun;
    CustTable custTable;
    queryRun = new QueryRun(query);
    
    if (queryRun.prompt())
    
    {
    
    while (queryRun.next())
    
    {
    
    custTable = queryRun.get(tableNum(CustTable));
    
    }
    
    }

  • Query:

    The query object is the definition master. It has its own properties and has one or more related data sources.

    Query query;
    
    query = new Query();
    
    query.addDataSource(tableNum(CustTable));

  • QueryBuildDataSource:

    Using QueryBuildDataSources you add all the tables you want joined (just one in this example). This is also where you define how the resultset is to be sorted. The orderMode() method lets you define:

    OrderBy:

    QueryBuildDataSource queryBuildDataSourceTrans, queryBuildDataSourceDim;
    
    queryBuildDataSourceTrans = query.addDataSource(tableNum(CustTrans));
    
    queryBuildDataSourceDim = queryBuildDataSourceTrans.addDataSource(tableNum(InventDim));
    
    queryBuildDataSourceDim.addGroupByField(fieldNum(InventDim, InventBatchId));
    
    queryBuildDataSourceDim.relations(true);

  • QueryBuildFieldList

    The queryBuildFieldList object defines which fields to fetch from the database. The default is a dynamic field list that is equal to a “select * from …”. Each data source has only one queryBuildFieldList object which contains information about all selected fields. You can also specify aggregate functions like sum, count, and avg with the field list object.
    

    QueryBuildFieldList qbfl = qbds.fields();
    
    qbfl.addField(fieldNum(CustTable,CreditMax),SelectionField::Sum);
    
    qbfl.addField(fieldnum(CustTable,RecId),SelectionField::Count);

  • QueryBuildRange

    The queryBuildRange object contains a limitation of the query on a single field.
    

    QueryBuildDataSource custTableQBDS, custTransQBDS;
    
    QueryBuildRange qbr1, qbr2;
    
    custTableQBDS = query.addDataSource(tablenum(custTable));
    
    qbr1 = query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, AccountNum));
    
    qbr2 = query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, InvoiceAccount));
    
    qbr1.value(SysQuery::value(‘4011’));
    
    qbr2.value(SysQuery::value(‘4010’));

  • QueryFilter

    The queryFilter object is used to filter the result set of an outer join. It filters the data at a later stage than the queryBuildRange object and filters the parent table based on the child table results.
    

    QueryBuildDataSource custTableQBDS, custTransQBDS;
    
    QueryFilter qFilter1, qFilter2;
    
    custTableQBDS = query.addDataSource(tablenum(custTable));
    
    qFilter1 = query.addQueryFilter(custTableQBDS,”AccountNum”);
    
    qFilter1.value(“4011″);
    
    qFilter2 = query.addQueryFilter(custTableQBDS,” InvoiceAccount”);
    
    qFilter1.value(“4010″);

  • QueryBuildDynalink

    Specifies the relation between the two data sources in the join. Can only exist on a child data source.
    

    QueryBuildLink qbl;
    
    QueryBuildDataSource custTableQBDS, custTransQBDS;
    
    custTableQBDS = query.addDataSource(tablenum(custTable));
    
    custTransQBDS = custTableQBDS.addDataSource(tableNum(CustTrans));
    
    qbl = custTransQBDS.addLink(fieldNum(CustTable, AccountNum), fieldNum(CustTrans, AccountNum));