Group-by fails when adding a linked data source to a query

Question

I have the following code which builds a query on the Req Trans table to show me consolidated quantities by Item/Site;

query = new Query();
dsReqTrans = query.addDataSource(tablenum(ReqTrans));

dsReqTrans.addRange(fieldnum(ReqTrans, ReqPlanId)).value(_reqPlanId);
dsReqTrans.addRange(fieldnum(ReqTrans, RefType)).value(enum2str(_reqRefType));

dsReqTrans.addGroupByField(fieldnum(ReqTrans, ItemId));
dsReqTrans.addSelectionField(fieldnum(ReqTrans, Qty),  SelectionField::Sum);

dsInventDim = dsReqTrans.addDataSource(tablenum(InventDim));
dsInventDim.joinMode(JoinMode::InnerJoin);
dsInventDim.relations(false);
dsInventDim.addLink(fieldnum(ReqTrans, CovInventDimId), fieldnum(InventDim, InventDimId));

dsInventDim.addGroupByField(fieldnum(InventDim, InventSizeId));
dsInventDim.addGroupByField(fieldnum(InventDim, InventSiteId));

rangeInventSiteId = dsInventDim.addRange(fieldnum(InventDim, InventSiteId));
rangeInventSiteId.value(_parmSiteId);

This works very well. I get the sum of the qty column by item/site.

However, if I want to also add a range on ItemGroupId. For this i need to link to the InventTable like so;

dsInventTable = dsReqTrans.addDataSource(tablenum(InventTable));
dsInventTable.joinMode(JoinMode::InnerJoin);
dsInventTable.relations(false);
dsInventTable.addLink(fieldnum(ReqTrans, ItemId), fieldnum(InventTable, ItemId));

dsInventTable.addRange(fieldnum(InventTable, ItemGroupId)).value(_parmItemGroupId);

When I add this data source, the link to InventDim is broken. I get a sum of all item qty for all sites grouped by item only, rather than grouped per item/site.

Why could this be?

Jan B. Kjeldsen Jan B. Kjeldsen · Accepted Answer · 2012-04-24T11:37:46

Basically you cannot have two inner joins to the same data source (you can, but it will not work).

If using AX 2012 you can add the InventTable data source to the InventDim instead:

dsInventTable = dsInventDim.addDataSource(tablenum(InventTable));
dsInventTable.joinMode(JoinMode::ExistsJoin);
dsInventTable.relations(false);
// OBS 3 arguments next:
dsInventTable.addLink(fieldnum(ReqTrans, ItemId), fieldnum(InventTable, ItemId), dsReqTrans.name()); 
dsInventTable.addRange(fieldnum(InventTable, ItemGroupId)).value(_parmItemGroupId);

In earlier versions you put InventTable first (remember to group by one field from InventTable):

query = new Query(); 
dsInventTable = dsReqTrans.addDataSource(tablenum(InventTable)); 
dsInventTable.addGroupByField(fieldnum(InventTable, ItemId)); // You have to group/sum something
dsReqTrans = dsInventTable.addDataSource(tablenum(ReqTrans)); 
dsReqTrans.joinMode(JoinMode::InnerJoin); 
dsReqTrans.relations(false); 
dsReqTrans.addLink(fieldnum(InventTable, ItemId).fieldnum(ReqTrans, ItemId));
...

Group-by fails when adding a linked data source to a query

1 Answers