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

Опции темы Поиск в этой теме Опции просмотра
Старый 26.05.2016, 18:11   #1  
Blog bot is offline
Blog bot
25,475 / 846 (79) +++++++
Регистрация: 28.10.2006
stoneridgesoftware: How to use a View to Simplify Working with Query Classes in Dynamics AX

Have you ever used a View to simplify working with Query classes in Dynamics AX? I have worked on a couple projects where my lookups were not working quite like I wanted them to when working with the Query classes. The problems specifically happened when I wanted to see data from a child table that was two, three or four levels deep. The lookup wouldn’t run or the data coming back into the lookup form would say ‘Unknown’. I thought to myself, it would be nice if the data I wanted to see was in one table and I would not need to do any joins using the Query classes. This is when I realized that using a custom View created in the AOT would be handy.

Let’s say I wanted to see data related to Project Contracts and their Customers that are associated with Projects in my lookup. (Physical table and field names in parentheses):

Project ID (ProjTable.ProjId) Project Name (ProjTable.Name) Project Contract ID (ProjTable.ProjInvoiceProjId) Project Contract Name (ProjInvoiceTable.Description) Customer ID (ProjTable.CustAccount) Customer Name (DirpartyTable.Name)

The customer name lives in DirPartyTable so this will require joining three tables to get to it:

ProjTable.CustAccount -> CustTable.AccountNum -> CustTable.Party -> DirPartyTable.RecId To create the view in the AOT, you can choose a table based one or a query based one. If you want a query based one, then you create the query in the AOT first and then select it using the Properties window of the view object. This is an example of the CustBankAccountCrossCompanyView that is query based:

I used a table based query for this solution. The ProjTable will be the parent table and will join the other three tables I listed above. The view looks like this:

Now that my view is defined in the AOT, I can now test it in a job or a custom lookup with X++. The following is a job that displays the data for a certain Customer number:

static void ProjectContractInfoViewTest(Args _args)
    ProjectContractInfoView         projectContractInfoView;
    Query                           query;
    QueryRun                        qRun;
    QueryBuildDataSource            qBDSource;
    QueryBuildRange                 querybuildRange;
    CustAccount                     customerID;
    int i = 0;
    customerID = "1010-1010123";    
    query = new Query();
    qBDSource = query.addDataSource(tablenum(ProjectContractInfoView));
    querybuildRange = qBDSource.addRange(fieldnum(ProjectContractInfoView, CustAccount));
    qRun = new QueryRun(query);

    while (
        projectContractInfoView = qRun.get(tablenum(ProjectContractInfoView));
        info("ProjId: " + projectContractInfoView.ProjId + " Contract Id: " + projectContractInfoView.ProjInvoiceProjId 
                + " Contract Desc: " + projectContractInfoView.ProjContractDesc + " Customer Name: " + projectContractInfoView.CustomerName);

    info(int2str(i) + " Records found using range on CustAccount.");
Note: There is only one QueryBuildDataSource required since the view joins the underlying tables behind the scenes.

Here would be a similar example of the job code in a method that would be used in a lookup filtering on a Customer number:

public static void lookupProjectCustomerId(FormControl _formControl, CustAccount _customerID)
    SysTableLookup sysTableLookup =  SysTableLookup::newParameters(tablenum(ProjectContractInfoView), _formControl);
    Query query = new Query();
    QueryBuildDataSource qBDSource;
    QueryBuildRange querybuildRange;

    qBDSource = query.addDataSource(tableNum(ProjectContractInfoView));

    // Filter on the Customer ID
    if (_customerID)
        querybuildRange = qBDSource.addRange(fieldnum(ProjectContractInfoView, CustAccount));

    sysTableLookup.addLookupfield(fieldnum(ProjectContractInfoView, ProjId));
    sysTableLookup.addLookupfield(fieldnum(ProjectContractInfoView, ProjInvoiceProjId));
    sysTableLookup.addLookupfield(fieldnum(ProjectContractInfoView, ProjContractDesc));
    sysTableLookup.addLookupfield(fieldnum(ProjectContractInfoView, CustomerName));
    sysTableLookup.addLookupfield(fieldnum(ProjectContractInfoView, ProjectName));


Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Последний раз редактировалось mazzy; 26.05.2016 в 18:55.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
atinkerersnotebook: Using the Dynamics AX Excel Add-In Blog bot DAX Blogs 1 25.09.2013 07:11
dynamicsaxbi: Better together: Microsoft Dynamics AX 2012 R2 and SQL Server Power View Blog bot DAX Blogs 0 12.12.2012 13:11
ax-erp: Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports Blog bot DAX Blogs 0 18.07.2012 12:11
rumicrosofterp: Dynamics AX на Convergence 2012 Blog bot Microsoft и системы Microsoft Dynamics 0 13.01.2012 11:11
CRM DE LA CREME! Configuring Microsoft Dynamics CRM 4.0 for Internet-facing deployment Blog bot Dynamics CRM: Blogs 0 18.08.2009 11:05
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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