sum function in Query object

Finance and Operations/AX Technical Forum
1

Hi everyone,

Can any one tell me how can i use sum function in Query object.

Ex:

while select sum(salesQty) from salesline
group by ItemId
{
info(strfmt(‘Item Id =%1, Qty = %2’,salesLine.ItemId,salesLine.SalesQty));
}

by using the above select statement i can get the item wise Sales quantity values.

qbds = q.addDataSource(tablenum(SalesLine));
qbds.addGroupByField(fieldnum(SalesLine,ItemId));

qr = new QueryRun(q);
while(qr.next())
{
salesLine = qr.get(tablenum(SalesLine));
info(strfmt(‘Item Id =%1, Qty = %2’,salesLine.ItemId,salesLine.SalesQty));
}

here my doubt is where can i put sum function in the above query.

Please guide me…

2

You can use the following code:

QueryBuildFieldList qbfl = qbds.fields();

qbds = q.addDataSource(tablenum(SalesLine));

//QBFL
qbfl.addField(fieldNum(SalesLine,SalesQty),SelectionField::Sum);

qbds.addSortField(fieldnum(SalesLine,ItemId));
qbds.orderMode(OrderMode::GroupBy);

qr = new QueryRun(q);
while(qr.next())
{
salesLine = qr.get(tablenum(SalesLine));
info(strfmt(‘Item Id =%1, Qty = %2’,salesLine.ItemId,salesLine.SalesQty));
}

3

You get te same query with this:

q = new Query();
qbds = q.addDataSource(tablenum(SalesLine));
qbds.addSelectionField(fieldNum(SalesLine,SalesQty),SelectionField::Sum);
qbds.addGroupByField(fieldnum(SalesLine,ItemId));
qr = new QueryRun(q);

4

Hi Vinay/Fabricio,

Thanks for your reply. [:D]