Dynamics Search Engine

Tuesday, November 25, 2008

How to write Queries in Microsoft Dynamics Ax

This article is for beginner level.

This article explains:
How to write a query in Microsoft Dynamics Ax
How to add a DataSource to a Microsoft Dynamics Ax query
How to add a range to a Microsoft Dynamics Ax query
How to use Sum clause to a Microsoft Dynamics Ax query
How to use GroupBy clause to a Microsoft Dynamics Ax query

Instead of write a select statement it's better write queries using query objects.
1. You should be familiar with Microsoft Dynamics Ax

2. You should be familiar with X++ language

Following is an example of a query.
Create a new job and test it.

I used a table called Table1 where EmplId(string), BalanceHours(real),TransDate(date) and few more fields are available. I want to write a query where record will be shorted by EmplId in ascending order, sum of BalanceHours to be calculated by EmplId, a range should be on TransDate.

The job is written below:

static void job1(Args _agrs)

Query q;

queryRun qr;

QueryBuildRange qbr;

QueryBuildDataSource qbd;

EmplTable emplTableLoc;

Table1 table1Local;


q = new Query();

qbd = q.addDataSource(tableNum(Table1));//Table1 is being added to the datasource

qbd.addSortField(fieldNum(Table1,EmplId),SortOrder::Ascending);//Records will be shorted by EmplId in Ascending order

qbd.addSelectionField(fieldNum(Table1,BalanceHours),SelectionField::Sum);//Sum of balance hours to be calculated.

qbd.orderMode(OrderMode::GroupBy);//Records will be groupby EmplId. If you don't do this then you will get the result of BalanceHours only.

qbr = qbd.addRange(fieldNum(Table1,TransDate));//For Date range. In a normal select statement we use where clause in place of this

qbr.value(SysQuery::range(systemdateget()-29,systemdateget()));//date range values

qr = new queryRun(q);//Assigning query to query run


table1Local = qr.get(tableNum(Table1));//Assigning the selected records to an instance of Table1 called table1Local

info(strfmt('Employee Id: %1 Balance Hours: %2',table1Local.EmplId,table1Local.BalanceHours));



After execution the job I got the result as shown below:

No comments:

Post a Comment