Hi ,
I want to Query the Items whose modelGroup are stocked products . I can able to query those items with the following query .But after getting the resultant items I need to group the records based on ItemGroup So that I can get/Retrive the distinct Item Group values . I am unable to groupby the resultant Query . whether can I able to group by the resultant query ?
Query query = new query(); QueryBuildDataSource invtbl,grp,mdl,mdlstk; InventTable invTable; InventItemGroupItem invGroup; InventModelGroupItem invModel; QueryBuildRange queryBuildRange; InventModelGroup stk; invtbl = query.addDataSource(tableNum(InventTable)); grp = invtbl.addDataSource(tableNum(InventItemGroupItem)); grp.relations(false); grp.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemGroupItem, ItemId)); grp.fetchMode(QueryFetchMode::One2One); mdl = invtbl.addDataSource(tableNum(InventModelGroupItem)); mdl.relations(false); mdl.addLink(fieldNum(InventTable, ItemId), fieldNum(InventModelGroupItem, ItemId)); mdl.fetchMode(QueryFetchMode::One2One); mdlstk = mdl.addDataSource(tableNum(InventModelGroup)); mdlstk.relations(false); mdlstk.addLink(fieldNum(InventModelGroupItem, ModelGroupId), fieldNum(InventModelGroup, ModelGroupId)); mdlstk.fetchMode(QueryFetchMode::One2One); mdlstk.addRange(fieldNum(InventModelGroup,StockedProduct)).value(queryvalue(noyes::Yes)); // query.groupByField(); // query.groupByField(fieldNum(InventItemGroupItem, ItemGroupId),grp); //QueryGroupbyfield = grp.addGroupByField(fieldNum(InventItemGroupItem, ItemGroupId)); // invtbl.addOrderByField(fieldnum(InventTable, ItemId)); //grp.addGroupByField(fieldNum(InventItemGroupItem,ItemGroupId)); //grp.addSortField(fieldnum(InventItemGroupItem, ItemGroupId)); //grp.addOrderByField(fieldnum(InventItemGroupItem, ItemGroupId)); //grp.orderMode(OrderMode::GroupBy); QueryRun qr = new QueryRun(query); while(qr.next()) { invTable = qr.get(tableNum(InventTable)); invGroup = qr.get(tableNum(InventItemGroupItem)); invModel = qr.get(tableNum(InventModelGroupItem)); stk = qr.get(tableNum(InventModelGroup)); Info(strFmt("%1 ,%2 ,%3 ,%4 ,%5",invTable.ItemId,invGroup.ItemGroupId,invModel.ModelGroupId,stk.StockedProduct,stk.ModelGroupId)); }
I have try groupby Methods but it didnt work as Expected . Am I missing anything ? Can I add subquery for the following query to groupby ?
Thanks Martin . The code which you have shared works perfectly . But In my code I have tried Groupby , since it doesnt work I have commented those lines and my logic was wrong for grouping !!
You don't do any grouping in your code, so it shouldn't be surprising that nothing gets groupped. You need to at least try it before you can say whether you're able to do it or not.
Consider looking at the code I gave you.
"not being able to group" It means I want distinct values of itemGroupId
In this distinct value of ItemgroupId
Sorry, I was only guessing - I'm not at work at the moment. And I was confused by those tables with similar names. Now your query makes more sense to me.
It seems that you want something like this:
select ItemGroupId InventItemGroupItem group by ItemGroupId join InventTable join InventModelGroupItem join InventItemGroupItem join InventModelGroup where StockedProduct = Yes
And this will be real code:
Query query = new Query(); QueryBuildDataSource groupItemDs = query.addDataSource(tableNum(InventItemGroupItem)); groupItemDs.addGroupByField(fieldNum(InventItemGroupItem, ItemGroupId)); QueryBuildDataSource inventTableDs = groupItemDs.addDataSource(tableNum(InventTable)); inventTableDs.relations(true); inventTableDs.joinMode(JoinMode::ExistsJoin); QueryBuildDataSource modelGroupItemDs = inventTableDs.addDataSource(tableNum(InventModelGroupItem)); modelGroupItemDs.relations(true); modelGroupItemDs.joinMode(JoinMode::ExistsJoin); QueryBuildDataSource modelGroupDs = modelGroupItemDs.addDataSource(tableNum(InventModelGroup)); modelGroupDs.relations(true); modelGroupDs.joinMode(JoinMode::ExistsJoin); modelGroupDs.addRange(fieldNum(InventModelGroup, StockedProduct)).value(queryValue(NoYes::Yes)); QueryRun qr = new QueryRun(query); while (qr.next()) { InventItemGroupItem groupItem = qr.get(tableNum(InventItemGroupItem)); info(groupItem.ItemGroupId); }
Does it look correct now?
And can you know finally explain your problem? You still didn't tell us what you mean by "not being able to group".
Hi Martin ,
how do you relate InventItemGroupItem with InventModelGroup ? As there is no relation between these two tables
SELECT * FROM InventItemGroupItem(InventItemGroupItem_1) GROUP BY InventItemGroupItem.ItemGroupId EXISTS JOIN * FROM InventModelGroup(InventModelGroup_1) WHERE ((StockedProduct = 1))
Sorry about the table name. Changing it sounds easy. Does it look correct now?
Query query = new Query(); QueryBuildDataSource groupItemDs = query.addDataSource(tableNum(InventItemGroupItem)); groupItemDs.addGroupByField(fieldNum(InventItemGroupItem, ItemGroupId)); QueryBuildDataSource modelGroupDs = groupItemDs.addDataSource(tableNum(InventModelGroup)); modelGroupDs.relations(true); modelGroupDs.joinMode(JoinMode::ExistsJoin); modelGroupDs.addRange(fieldNum(InventModelGroup, StockedProduct)).value(queryValue(NoYes::Yes)); QueryRun qr = new QueryRun(query); while (qr.next()) { InventItemGroupItem groupItem = qr.get(tableNum(InventItemGroupItem)); info(groupItem.ItemGroupId); }
Yes, you can group by ItemGroupId. But I can't give you a solution for "I am unable to groupby based on on the resultant query", because I don't know what you mean by not being able. I suggest you forget your code for a moment and look at mine instead.
Hi Martin,
ItemGroupId field doesnt belong to the table InventModelGroupItem ., ItemGroupId field belongs to InventItemGroupItem Table.
And yes . InventTable is not required in this case . But for understanding purpose I have added InventTable since in the Item Master Form has both ItemGroupId and ItemModelId.
And these two fields are from InventItemGroupItem(ItemGroupId) and InventModelGroupItem(ModelGroupId) . And the details about the stocked products are from InventModelGroup table .
So I have used the following Code
Query query = new query(); QueryBuildDataSource invtbl,grp,mdl,mdlstk; InventTable invTable; InventItemGroupItem invGroup; InventModelGroupItem invModel; QueryBuildRange queryBuildRange; InventModelGroup stk; invtbl = query.addDataSource(tableNum(InventTable)); grp = invtbl.addDataSource(tableNum(InventItemGroupItem)); grp.relations(false); grp.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemGroupItem, ItemId)); grp.fetchMode(QueryFetchMode::One2One); mdl = invtbl.addDataSource(tableNum(InventModelGroupItem)); mdl.relations(false); mdl.addLink(fieldNum(InventTable, ItemId), fieldNum(InventModelGroupItem, ItemId)); mdl.fetchMode(QueryFetchMode::One2One); mdlstk = mdl.addDataSource(tableNum(InventModelGroup)); mdlstk.relations(false); mdlstk.addLink(fieldNum(InventModelGroupItem, ModelGroupId), fieldNum(InventModelGroup, ModelGroupId)); mdlstk.fetchMode(QueryFetchMode::One2One); mdlstk.addRange(fieldNum(InventModelGroup,StockedProduct)).value(queryvalue(noyes::Yes)); QueryRun qr = new QueryRun(query); while(qr.next()) { invTable = qr.get(tableNum(InventTable)); invGroup = qr.get(tableNum(InventItemGroupItem)); invModel = qr.get(tableNum(InventModelGroupItem)); stk = qr.get(tableNum(InventModelGroup)); Info(strFmt("%1 ,%2 ,%3 ,%4 ,%5",invTable.ItemId,invGroup.ItemGroupId,invModel.ModelGroupId,stk.StockedProduct,stk.ModelGroupId)); }
And the result of this code is
Further I need to do some modification in the code so that I can get the distinct values for ItemGroupId which is (Kitchen) . But I am unable to groupby based on on the resultant query .Whether can I group this reults based on ItemGroupId ?
If all you want to get is a list of model group with StockedProduct = Yes, that are used by some items (and not items as such, as originally claimed), outputting any other field isn't useful and you don't need InventTable data source at all. Let me suggest this code:
Query query = new Query(); QueryBuildDataSource groupItemDs = query.addDataSource(tableNum(InventModelGroupItem)); groupItemDs.addGroupByField(fieldNum(InventModelGroupItem, ItemGroupId)); QueryBuildDataSource modelGroupDs = groupItemDs.addDataSource(tableNum(InventModelGroup)); modelGroupDs.relations(true); modelGroupDs.joinMode(JoinMode::ExistsJoin); modelGroupDs.addRange(fieldNum(InventModelGroup, StockedProduct)).value(queryValue(NoYes::Yes)); QueryRun qr = new QueryRun(query); while (qr.next()) { InventModelGroupItem modelGroupItem = qr.get(tableNum(InventModelGroupItem)); info(modelGroupItem.ItemGroupId); }
Isn't it what you want?
Hi Girish and Martin,
After Running the above Query which I have shared , I am getting the following output since I have added the range to the stocked products field , I can able to see whether the Item is stocked or not based on this result ,
But I want the ItemGroup(Kitchen) to be Grouped/Distinct When I try to add the groupby the ItemGroupid field I am getting other results where the ranges doesnt work and Item field model group field appers blank in the result . so I have commented those lines.
And I need only the ItemgroupId field after grouping because I need to use this query in the lookup
That would mean that only ItemGroupId has a value. Only fields that are either grouped by or aggregated have a value defined, therefore we would need to decide what to do with all the other fields.
I wonder whether groupping by ItemGroupId makes sense at all if the goal is getting "items whose modelGroup are stocked products". If we don't group by item ID, we need to aggregate item ID, such as just getting the highest one in the given group. This seems to go against the requirement. And if we group by item ID, I don't see a reason for grouping by ItemGroupId.
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,817
Most Valuable Professional
nmaenpaa
101,156