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.Prerequisites:
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
while(qr.next())
{
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:
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.Prerequisites:
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
while(qr.next())
{
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:
