Exploring SysQuery class

SysQuery is a helper class provided by Microsoft for working with queries dynamically. It has all its methods set as ‘static.’ It provides numerous functionalities that allow one to add data source in a query, add range, apply sort, etc. in a user-friendly system. This article will provide demonstrations for the most commonly used methods from SysQuery:

findOrCreateDataSource()

FindOrCreate is mainly used to check whether or not the query already contains the data source. If it does not, a data source will be created. FindOrCreate is most efficient when an event developer must write a code in which the event must be fired multiple times. This function allows developers to prevent adding additional elements to data sources. This is the most common situation in which findOrCreateDatasource() is used to avoid multiple addition of data sources in a query.

Because this method is static, it uses Query as the first parameter. The next parameter will be the table id of the table that is being used as the data source in the query. Below is the method signature:

public static QueryBuildDataSource findOrCreateDataSource(Query _query, tableId _tableId, tableId _parentTableId = 0)

An alternative option, findOrCreateDataSourceByName() works in a similar fashion, but uses the table name for table identification in parameters.

findOrCreateRange()

This method is used to check whether or not a range already exists. If the range exists, the object will either be returned or a range according to the provided parameter will be created and returned. Using the findOrCreateRange() method, allows developers to avoid the possibility of adding a single range multiple times. The below is an example of the method:

range = SysQuery::findOrCreateRange(query.dataSourceName("CustTable"), fieldNum(CustTable, AccountNum));

range.value(SysQuery::value("123"));

There are other methods for specifying value for conditions in a range below:

SysQuery::range()

Range() method is used to specify value ranges of any type. This is most commonly used for specifying date ranges.

Queries in Dimension data sources

AX 2012 is more efficient when working with financial dimensions in comparison to previous versions. In AX 2012, the schema of dimensions’ framework has been normalized. However, this change has made querying in dimension data model more complex and harder to understand. Microsoft has provided helper classes for developers working with dimension data sources. They have also provided guidance in SysQuery class to help developers write queries in a more simplistic way—they do not need to have in-depth knowledge of the data model behind financial dimensions. There is generally only one reference field (recld) that references whole dimension value combination in the table DimensionAttributeValueCombination. It then becomes difficult to apply range on a specific dimension using one reference field (usually LedgerDimension in tables). This type of action requires complex joins in the dimension value tables hierarchy, but SysQuery class makes the process much simpler.

Below are a few methods to use when working with dimension data source:

1. addDimensionDataSource() – To add dimension data source
2. addDimensionAttributeRange() – To add range on dimension data source
3. addOrderByDimensionAttribute() – To add an order by clause on specific dimension