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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / "Group by" cla...
Microsoft Dynamics AX (Archived)

"Group by" clause forgotten ?

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I created a query on a custom table that filters using the current user id and then group by the projid column. This query is displayed by a form with just the projid column and it works fine. Now if I click the column header to change the sort order, I'll start to see numerous duplicate records. It looks like changing the sort order removes the group by clause ?!

How could I fix this ? TIA

*This post is locked for comments

I have the same question (0)
  • Ahamed Minhaj Profile Picture
    Ahamed Minhaj 250 on at
    Re: "Group by" clause forgotten ?

    Hi,

    Can you give some more info reg your issue, like where you are filtering your records?
     and where you set the groupby field to the datasourse?

    if you are not doing those steps in the executeQuery() method of the form datasourse, then you need to place the code in executeQuery method before calling super().


    br,
    Ahamed

  • Community Member Profile Picture
    Community Member on at
    Re: Re: "Group by" clause forgotten ?

    Hi,

    For now the query is entirely created in the AOT. This is a custom table with a ProjId field used in the GroupBy node. I don't have any code and was puzzled to see first the expected result (one line per ProjId values) but then to see duplicated values as soon as I click on the header field to reverse the sort order.

    I was about about to check in code if the structure of the query is altered.

    Thanks...

  • Denis Patrakov Profile Picture
    Denis Patrakov on at
    Re: "Group by" clause forgotten ?
    What AX version are you using? In versions prior to AX 2009 a group by clause implicitly adds an order by clause with the same set and sequence of fields as in the group by (except for those where an aggregation is used). In fact the kernel used to use a single memory structure associated with a query to keep list of fields for both grouping and sorting - that's why in AX 4.0 if you change sorting in a query with grouping you affect grouping as well.
  • Community Member Profile Picture
    Community Member on at
    Re: Re: "Group by" clause forgotten ?

    I'm using AX 2009 SP1. It seems to be exactly what you describe i.e. if the sort order is changed by clicking the header column, the group by clause is not used any more.

     What was the preferred way to workaround this issue with AX 4 ? In case you didn't I'm still quite a beginner with the AX UI (I've done mostly some SSRS reporting for now).

     Thanks for your help.

     

  • Denis Patrakov Profile Picture
    Denis Patrakov on at
    Re: Re: Re: "Group by" clause forgotten ?
    If you somehow tweak a query on a form then sometimes the only way to enforce your tweaks is to apply them in the FormDS.executeQuery() before calling super() - for both FormDS.query() and FormDS.queryRun().query(). You need to alter FormDS.queryRun().query() to apply your "tweaks" with the ones that a user does (in that case executeQuery() is called from FormDS.research()) and you need to alter FormDS.query() to keep you "tweaks" in case a user resets the query - in the latter case FormDS.queryRun() object is recreated and hence all FormDS.queryRun().query() tweaks are lost.
    In this case your code will be executed every time a select statement is issued and that also means that you should write the code carefully to make it idempotent.
  • Community Member Profile Picture
    Community Member on at
    Re: Re: Re: Re: "Group by" clause forgotten ?

    Thanks a lot. They wanted a presentation change that make using "group by" not needed anymore but I keep your detailed response for further use. Thanks very much !

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#1
Community Member Profile Picture

Community Member 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans