web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Is the position of Add...
Microsoft Dynamics AX (Archived)

Is the position of AddGroupByField?

(0) ShareShare
ReportReport
Posted on by Community Member

I've some issues with grouping. Consider the code below which joins two tmp-tables (but it's the same for regular tables).
There are two yellow groupby's. If the first one is enabled the code does not work.

It seems that the position of group by does change the behavior.

Can someone confirm this, and is this correct behavior?

 

 

 

TmpIdRef tmp1, tmp1_res;    
TmpIdRef tmp2, tmp2_res;
Query                   query;
QueryRun                queryRun;  
QueryBuildDataSource    qbdsTmp1;   
QueryBuildDataSource    qbdsTmp2;

//Fill table1 with one entry
tmp1.Id = 1;
tmp1.insert();

//Fill table 2 with one entry related to table one
tmp2.Id = 2;
tmp2.Parent = tmp1.RecId;
tmp2.insert();

query = new Quey();
qbdsTmp1 = query.addDataSource(tableNum(TmpIdRef));
//qbdsTmp1.addGroupByField(fieldNum(TmpIdRef, RecId)); //This is the problematic line (here it doesnt work);

qbdsTmp1 = qbdsTmp1.addDataSource(tableNum(TmpIdRef));
qbdsTmp1.joinMode(JoinMode::InnerJoin);
qbdsTmp1.addLink(fieldNum(TmpIdRef, RecId), fieldNum(TmpIdRef, Parent));
qbdsTmp1.addGroupByField(fieldNum(TmpIdRef, RecId)); //This is the problematic line (here it works)        

 

queryRun = new QueryRun(query);    
queryRun.setCursor(tmp1,1);    
queryRun.setCursor(tmp2,2);

 while(queryRun.next())    
{
        tmp1_res = queryRun.getNo(1);
        tmp2_res = queryRun.getNo(2);

        breakpoint;    
}

*This post is locked for comments

I have the same question (0)
  • Tommy Skaue Profile Picture
    Tommy Skaue Moderator on at

    Whenever you are grouping, you always need to be explicit about what fields you want to select. If you are selecting fields that are not in the "group by" list, you need to be explicit about how you want to aggregate these fields (avg, min, max, min).

    Make sure you select only the fields you group by and see if that works.

  • Community Member Profile Picture
    Community Member on at

    Hi Tommy, yes I know that. That is a simple sql rule, but I only use RecId here for the join and I did group by that field so this should be the issue.

  • Tommy Skaue Profile Picture
    Tommy Skaue Moderator on at

    Hi

    Consider this example instead:

    static void GroupByExample(Args _args)
{
    SalesTable  salesTable;
    SalesLine   salesLine;
    
    Query                   query;
    QueryRun                queryRun;
    QueryBuildDataSource    qbds1, qbds2;
    int i;
    
    query = new Query();
    
    qbds1 = query.addDataSource(tableNum(SalesTable));
    qbds1.fields().clearFieldList();    
    qbds1.fields().addField(fieldNum(SalesTable, LanguageId));
    qbds1.addGroupByField(fieldNum(SalesTable, LanguageId));
    
    qbds2 = qbds1.addDataSource(tableNum(salesLine));
    qbds2.relations(true);
    qbds2.fields().clearFieldList();    
    qbds2.fields().addField(fieldNum(salesLine, CustGroup));
    qbds2.addGroupByField(fieldNum(salesLine, CustGroup));
    
    queryRun = new QueryRun(query);
    
    info(strFmt('%1', qbds1.toString()));
    
    while(queryRun.next())
    {
        i++;
        
        salesTable  = queryRun.get(tableNum(SalesTable));
        salesLine   = queryRun.get(tableNum(SalesLine));
        
        info(strFmt('%1 - %2', salesTable.LanguageId, salesLine.CustGroup));       
        
    }
}
  • Community Member Profile Picture
    Community Member on at

    Hi Tommy. In your example the grouped field isn't the one necessary for the join. As you can see in my example the group-by field of the first datasource is the join condition.

    For clarification: The question in behind is the ReqCalc.newQueryInventTrans method which creates the query for the items to be calculated. In that query you can see the qbsTransOrigin.addGroupByField(fieldNum(InventTransOrigin,RecId)) which is later used for the join.

    I am just searching a bug why the InventDim is copied incorrectly from a purchline to the reqpo. This is where I came to this code and I could track it down to the group by which seems to not work here.

  • Verified answer
    Tommy Skaue Profile Picture
    Tommy Skaue Moderator on at

    Ok. Maybe it is a known bug and it is already fixed. I wouldn't be surprised if Microsoft Support already have a solution - no guarantees though.

    As for your original question, I could not see where you explicitly selected what columns to retrieve. It looked like "SELECT * FROM tmpIdRef GROUP BY RecId", which wouldn't work.

  • Community Member Profile Picture
    Community Member on at

    Oh, I finally found the bug in master planning, which was a type of our own development. Nevertheless I find the group by thing interesting. I will close the thread for now.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans