Home > Blog > How to use a View to Simplify Working with Query Classes in Dynamics AX

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));
    querybuildRange.value(customerID);
    qRun = new QueryRun(query);

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

    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));
        querybuildRange.value(_customerID);
    }

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

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

I hope this technique helps if you run across the same problems joining multiple QueryBuildDataSource objects when you need data from child tables.

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.