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

Comments

Post a Comment

Popular posts from this blog

Dynamics Ax 2012 Interview Questions:

Interview Questions: 1.       Difference between following condel   :- Use condel to delete one or more items from a container. confind :- Use confind to locate a sequence of items in a container.  conins   :- Use conins to insert some items into a container. conlen   :- Use conlen to find out how many items there are in a container. connull  :- Use connull to explicitly dispose of the contents of a container. conpeek :- Use conpeek to extract an item from a container, and to convert it into another data type   conpoke :- Use conpoke to replace (poke) an item in a container. 2.       Difference between edit and display method Display  Indicates that the method's return value is to be displayed on a form or a report. The value cannot be altered in the form or report Edit  Indicates that the method's return type is to be used to provide information for a field that is used     in  in a form. The value in the field can be edited.       3.       Diff
Read more

Oops concept in AX

Class : Class is the 1st OOPs concept .Class defines the characteristics of objects which includes its attributes , fields  properties and behavior . Let us say we have a class called car , then the color , model number , top speed can be its attributes and properties . Accelerating , breaking , turning will be its behavior Objects: Objects can be considered as a thing  that performs a set of related functions .Programming objects are used to model real worlds objects. An object is also an instant of a class . For our class Car , Ferrari  will be our object Instance : One can have an instance of a class; the instance is the actual object created at runtime.  The set of values of the attributes of a particular object is called its state. The object consists of state and the behaviour that’s defined in the object’s class. Method :Also called as  functions in some programming languages , methods defines the  behavior of particular objects . For our Car class , turning() , breakin
Read more

X++ code to create a customized lookup on form

Image
Override the lookup method on Formdatasource field(on which you want to show lookup) , and copy the following code to your method. Comment the super() method in the lookup. public void lookup(FormControl _formControl, str _filterStr) { SysTableLookup sysTableLookup; // systemclass to create //customlookup Query query; QueryBuildDataSource qbd; ; sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable),_formcontrol); // Construct query on the table, // whose records you want to show as lookup. query = new Query(); qbd = query.addDataSource(tablenum(InventTable)); qbd.addRange(fieldnum(InventTable,ItemType)).value(SysQuery::value(enum2str (ItemType::Item))); // add the fields to the lookup list sysTableLookup.addLookupfield(fieldnum(InventTable,ItemId)); sysTableLookup.addLookupfield(fieldnum(InventTable,ItemName)); // pass the query as parameter // system will show the records in the lookup // as per your query sysTableLookup.parmQuery(query); sysTableLookup.performFormLo
Read more