在Dynamics 365财务和运营中构建动态查询 / Build Dynamics Query in Dynamics 365 for Finance and Operations – Video Webcast MsDyn365FO

Hi every one, here is another video.  Perfection is enemy of best and Best is enemy of good.  I spend 3 less sleep nights but unable to attain perfection in terms of presentation, I decided to complete it in good condition. It is beginner level video for developers. Certainly It based my 6 years technical work in Dynamics Ax 2012 and Dynamics 365 for Finance and Operations. Imagine how much I explored while making this video.   I used many reference to complete the code snippet form Internet.  Ahhoo

• 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
  • Query
  • QueryBuildDataSource
  • QueryBuildFieldList
  • QueryBuildRange
  • QueryFilter
  • QueryBuildDynaLink
  • QueryBuildLink

• QueryRun

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

 QueryRun queryRun;

    queryRun = new QueryRun(query);

    if (queryRun.prompt())

    {

        while (queryRun.next())

        {

            inventTrans = queryRun.get(tableNum(InventTrans));

        }

}

• 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(InventTrans));

• 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
• GroupBy

QueryBuildDataSource queryBuildDataSourceTrans, queryBuildDataSourceDim;

queryBuildDataSourceTrans = query.addDataSource(tableNum(InventTrans));

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

  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

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

QueryBuildDynalink – Contains information regarding a relation (limitation) to an external record. When the query is run, this information is converted to additional entries in the WHERE clause of the query SQL statement. Can only exist on the parent data source of a query. The function is used by forms, when two data sources are synchronized. Then the child data source will contain a dynalink or dynalinks to the parent data source. The function is used even if the two data sources are placed in two different forms but are still synchronized.

• QueryBuildLink

QueryBuildLink  –  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));

Reference : 

http://www.axaptapedia.com/Query_class

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/query-object-model