AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 11.10.2018, 08:12   #1  
Blog bot is offline
Blog bot
Участник
 
21,073 / 721 (67) +++++++
Регистрация: 28.10.2006
alirazazaidi: Build Dynamics Query in Dynamics 365 for Finance and Operations – Video Webcast MsDyn365FO
Источник: https://www.tech.alirazazaidi.com/bu...st-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/dyn...y-object-model



Источник: https://www.tech.alirazazaidi.com/bu...st-msdyn365fo/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
alirazazaidi: Upgrade from Ax 2012 to Dynamics 365 Finance and Operations – Ignite Video Blog bot DAX Blogs 0 03.10.2018 08:12
survivingcrm: What’s An “App” in Dynamics 365 Anyway? Blog bot Dynamics CRM: Blogs 0 06.01.2018 22:14
alirazazaidi: Dynamics 365 For Finance And Operations On Perm Demo Video Blog bot DAX Blogs 0 14.09.2017 13:11
jaestevan: Microsoft Dynamics 365 for Operations Blog bot DAX Blogs 0 02.11.2016 01:11
Platform updates overview - 3.70.B - NAV2009 R2 Blog bot Dynamics CRM: Blogs 0 07.02.2011 22:06
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 09:12.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.