Get group by field in query run

(0) Share

Report

Posted on by Community Member

Hi,

I have been trying to create a query where I am grouping it by item id from invent trans and batch id from inventDim. Now, when I try to get the table buffer of invent Dim, it is empty with no batch it present in it. Please help me with it.

qbds= query.addDataSource(tableNum(InventTrans));
qbdsInventDim = qbds.addDataSource(tableNum(InventDim));
qbdsInventDim.relations(true);
qbds.fetchMode(QueryFetchMode::One2One);
qbdsInventTable = qbds.addDataSource(tableNum(InventTable));
qbdsInventTable.relations(true);
qbds.fetchMode(QueryFetchMode::One2One);
qbdsEcoResCategory = qbdsInventTable.addDataSource(tableNum(EcoResProductCategory));
qbdsEcoResCategory.relations(true);
qbdsEcoResColor = qbdsInventDim.addDataSource(tableNum(EcoResColor));
qbdsEcoResColor.relations(true);
qbdsDSRCode = qbdsEcoResColor.addDataSource(tableNum(SLD_DSRCodeSetup));
qbdsDSRCode.relations(true);

qbds.addGroupByField(FieldNum(InventTrans, ItemId));
qbdsInventDim.addGroupByField(fieldNum(InventDim, InventBatchId));

queryRun = new QueryRun(query);

while(queryRun.next())
{
inventTransQuery = queryRun.get(tablenum(InventTrans));
InventDimQuery = queryRun.get(tablenum(InventDim)); // this line gives nothing in the buffer

}

I have the same question (0)

All responses (7)

Answers (0)

Community Member on at

Like (0)

Report

Please help me reslove the issue.

Was this reply helpful? Yes No
Martin Dráb 237,961 Most Valuable Professional on at

Like (0)

Report

Your query returns just two fields - InventTrans.ItemId and InventDim.InventBatchId. All other fields will have default values.

If InventBatchId is empty as well, maybe the inventory transaction simply doesn't have any associated batch ID.

By the way, please use Insert > Insert Code (in the rich-formatting view) to paste source code.

And which version of AX are you talking about? If AX 2012, you should choose Microsoft Dynamics AX 2012 in the version selector. If it's D365FO, you should ask in one of D365FO forums instead.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

No InventBatchId is present against this record. Seems like there is something logically wrong.

Was this reply helpful? Yes No
ergun sahin 8,826 Moderator on at

Like (0)

Report

Does the result change when you close the datasource codes other than InventTrans and InventDim and try to run the code?

Was this reply helpful? Yes No
Abdulaziz Albloshi 365 on at

Like (0)

Report

I would suggest to create a query and view first and than use that query/view in your code instead of adding the relations at run-time.

This way you will have more visibility of records before start writing your code.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

No, it doesn't.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Okay, let me try that.

Was this reply helpful? Yes No