Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Groupby based on result query / SubQuery

(0) ShareShare
ReportReport
Posted on by 2

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 ?

  • NoobGamer Profile Picture
    NoobGamer 2 on at
    RE: Groupby based on result query / SubQuery

    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 !!

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Groupby based on result query / SubQuery

    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.

  • NoobGamer Profile Picture
    NoobGamer 2 on at
    RE: Groupby based on result query / SubQuery

    "not being able to group"  It means I want distinct values of itemGroupId

    pastedimage1673382071460v1.png

    In this distinct value of ItemgroupId

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Groupby based on result query / SubQuery

    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".

  • NoobGamer Profile Picture
    NoobGamer 2 on at
    RE: Groupby based on result query / SubQuery

    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))

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Groupby based on result query / SubQuery

    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.

  • NoobGamer Profile Picture
    NoobGamer 2 on at
    RE: Groupby based on result query / SubQuery

    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 

    pastedimage1673371927846v1.png

    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 ?

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Groupby based on result query / SubQuery

    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?

  • NoobGamer Profile Picture
    NoobGamer 2 on at
    RE: Groupby based on result query / SubQuery

    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

    pastedimage1673362500385v1.png

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Groupby based on result query / SubQuery

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,817 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans