RSMUS.comThe query statement in X++ is a primary method of retrieving and manipulating data in Microsoft Dynamics AX 2012. A query can be created in the AOT using the graphical user interface. The AOT query has an advantage of a quick and visual design, however, it may not be able to do what the more serious programmer needs. This article examines the components of query design in X++. The illustration builds from the Sales Table using the Contoso database set. The goal is to find the sales items from a specific confirmation date. The finished class is included at the end of the article so that you can simply cut and paste to test the code.
Query Components
The basic query builds the model through a series of required steps. First is the definition of the class variables, where you setup the name declarations for the classes to be used, in this case the query framework, run method, data source, and range. Second, build out the framework classes defining the query, datasource, and the range. Third, instantiate or actually create an active query for use. Fourth, you can now run the query, using the created query instantiated in step three.
Defining the Classes
The following variables are used when creating a query. Their name declarations need to occur at the top of the class. Notice the naming convention used for it is common in much of existing code within AX, however it may vary.
// The first step is to define the query variables that we will be using. This is building the framework. Query query; QueryRun queryRun; QueryBuildDataSource qbds; QueryBuildRange qbr;The Build
The build of these objects is the next step. First, instantiate the query class to build the framework. Second, call to the query build data source class and specifically the addDatatsource method. Finally, define the range to set the query scope.
// Instantiates or creates the query framework. query = new Query(); // Links the table to the datasource definition. qbds = query.addDataSource(TableNum(SalesTable)); // Sets the range to be the ShippingDateConfirmed. qbr = qbds.addRange(FieldNum(SalesTable, ShippingDateConfirmed));The Instantiation
Instantiate the query using a three step statement combined in to one statement. This is taking the framework definition from previous steps, creating a new instance, and activating it for use.
queryRun = new QueryRun(query);The Action
Queries wouldn’t be any fun unless there was some sort of action. In this case, the query will display a query form for the user. The user can then add their own number sequences or sorting preferences. This is ideal to create much more dynamic queries for form information or reporting.
// the If condition checks to see that the query is running. If not, it will not execute. if (queryRun.prompt()) { //The while loops over the SalesTable in search of the information needed. while (queryRun.next()) { salesTable = queryRun.get(tableNum(SalesTable)); info(salesTable.SalesID); } } }Completed Query
The following three methods of the class ExaminingQueryBasic complete the query, with a few more extra commands to set the date and increase end user interactivity by adding a sorting field.
Declaration Method
public class ExaminingQueryBasic { SalesTable salesTable; } Main method public static void main(Args args) { ExaminingQueryBasic eqb = new examiningQueryBasic(); eqb.queryfun(); }Queryfun Method
public void queryfun() { Query query; QueryRun queryRun; QueryBuildDataSource qbds; QueryBuildRange qbr; query = new Query(); qbds = query.addDataSource(TableNum(SalesTable)); qbr = qbds.addRange(FieldNum(SalesTable, ShippingDateConfirmed)); // notice the addition of a specific value for date. qbr.value(’05/12/2008′); // this creates an additional setup that the end user can change within the query form. qbds.addSortField(FieldNum(SalesTable,SalesID)); queryRun = new QueryRun(query); if (queryRun.prompt()) { while (queryRun.next()) { salesTable = queryRun.get(tableNum(SalesTable)); info(salesTable.SalesID); } } }Conclusion
The X++ query is a robust tool to retrieve data. Along with the while and select statements, virtually any piece of the data in the system can be extracted for use in either forms or reports. This article examined the four components that are needed for a query to operate. With this primer, you too can create your own dynamic queries for use with Dynamics AX.
