How to Write Efficient Queries in Dynamics AX 2009, 2012 and 365

One of the things an AX developer commonly does is fetch data from tables. In Dynamics AX, irrespective of we are working on AX 2009, 2012 or 365, we can do that by writing a select statement or by building a Query object. Every Dynamics AX developer is fairly familiar with that, but is everyone aware of how efficiently we can use it? That’s what we’ll be going through in this blog post.

For this blog post, I’ll be writing all the code in an AX job. Let’s have a look at a basic query object to fetch AccountNum field from the CustTable.

static void F3_FetchCustomers(Args _args)
{
    Query                   query;
    QueryBuildDataSource    qbdsCustTable;
    QueryRun                queryRun;
    
    CustTable               tCustTable;
    ;
    
    query = new Query();
    qbdsCustTable = query.addDataSource(tableNum(CustTable), 'DS_CustTable');
    
    queryRun = new QueryRun(query);
    
    while (queryRun.next())
    {
        tCustTable = queryRun.get(tableNum(CustTable));
        info(strFmt("%1", tCustTable.AccountNum));
    }
}

This does the job perfectly, but what if we need to fetch some other field from a different table. What if we want to make the selection depending on a user input? Surely we’d just add a bunch of if-else statements and get the job done, but there must be a better way. There’s a better way

This is where the Common table comes in. According to MSDN:

“The Common table is the base class for all tables. It does not contain any data. It is primarily used in X++ code to refer to any table in a polymorphic way.”

What this means is that all the tables we use are inherited from one table, and that is the Common table. We can cast any table object to a Common table and it’d do just fine. However, we’d need reference to the field(s) we need to access.

Let’s start rewriting the code above without the CustTable object. The change in declarations is as follows:

 Query                   query;
    QueryBuildDataSource    qbdsCommon;
    QueryRun                queryRun;
    
    Common                  tCommon;
    
    FieldId                 fieldId;
    TableId                 tableId;
    ;

You’d notice that we’ve declared two more variables named fieldId and tableId. These variables will hold the reference to the table and the table-field we need to access. Let’s see how we will do that.

tableId = tableNum(CustTable);
	fieldId = fieldNum(CustTable, AccountNum);

Whenever we add a new data source to our query, we need to pass TableId of the table we’re adding. We retrieve that by calling the tableNum function and by passing the table name. So here I have stored it beforehand. fieldNum does the similar but for a field and with an extra parameter containing the name of the field we need reference to.

Tip: You can get/set these variables by passing in parameters to make your code reusable.

Moving on to creating the query object.

query = new Query();
	qbdsCommon = query.addDataSource(tableId);
	
	queryRun = new QueryRun(query);
	
	while (queryRun.next())
	{
	    tCommon = queryRun.get(tableId);
	    info(strFmt("%1", tCommon.(fieldId) ));
	}

Here, we have replaced the code for using tableNum to fetch TableId (for the table in action) with our tableId variable. The interesting part is retrieving the field data for AccountNum. As Common does not contain that field so we can’t access it the way we would on a CustTable object. Therefore, we need to pass in the fieldId to access its value.

Tip: Using this approach, one can also assign values to a field, apply ranges and what not.

Hopefully this would help you in managing your AX code. I would recommend you to play around with it and see how well you can utilize this feature in Dynamics AX 2009, 2012 as well as Dynamics 365.

Till next time!