Sorting on an aggregate with a group by

Erik-Jan Bulthuis

Solved

Question

I have an aggregate with a group by and I'm not able to have it sorted. Before I made the group by I was allowed to sort the results on something like "{User}.[Name]" but that doesn't work anymore.

Based on this topic I tried this dynamic sort which refers to my group by column name but it doesn't work either:

List_SortColumn_GetOrderBy(UserTable.Id, "Name")

Anyone an idea how to proceed?

12 Jul 2016

Paulo Duarte Ferreira

Staff

Solution

Hi Erik,

I suspect the problem is caused by a non-obvious detail that definitely needs an usability improvement. Similarly to filters, which are split in "standard" Filters and Group Filters, there's also the notion of Group Sorts and "standard" Sorts, although that's not reflected in the editor. And when an Aggregate has groups, "standard" Sorts are completely ignored...

To see if that's your scenario, try this:

Set a test value for the List_SortColumn_GetOrderBy(UserTable.Id, "<Name>") expression (e.g. "Name ASC")
Check the Executed SQL property of the Aggregate and see if the SQL has an ORDER BY clause

If it doesn't, then your sort is actually a "standard" Sort and is being ignored.

To fix it, just delete the Sort and add a new Dynamic Sort with the same expression as before. Since the Aggregate is grouped, this will create a Grouped Sort.

Let me know if this helped.

Regards,

Paulo Ferreira

See solution in context

18 Jul 2016

1 reply

14 May 2020

Show thread

Hide thread

Balaji Ravikumar

Paulo Ferreira wrote:

Hi Erik,

To see if that's your scenario, try this:

Set a test value for the List_SortColumn_GetOrderBy(UserTable.Id, "<Name>") expression (e.g. "Name ASC")
Check the Executed SQL property of the Aggregate and see if the SQL has an ORDER BY clause

If it doesn't, then your sort is actually a "standard" Sort and is being ignored.

To fix it, just delete the Sort and add a new Dynamic Sort with the same expression as before. Since the Aggregate is grouped, this will create a Grouped Sort.

Let me know if this helped.

Regards,

Paulo Ferreira

Thank you Paulo its working as you said,

Regardsm

Balaji R

14 May 2020

Daniel de Witte

Erik-Jan Bulthuis wrote:

List_SortColumn_GetOrderBy(UserTable.Id, "Name")

Anyone an idea how to proceed?

In the GetOrderBy function you refer to your table in the first parameter, the second parameter is the default column to sort on (used if the user did not click on any of the column headers to dynamic sort yet) What if you put the second parameter to "{User}.[Name]" ? Will it then sort by name by default?

12 Jul 2016

Shashi Kant Shukla

Hi Erik,

Can you please share the oml so that I can understand the exact problem.

Regards
SK

12 Jul 2016

Erik-Jan Bulthuis

@Daniel: I've tried "{User}.[Name]" but that doesn't work either.

@Sahski: I've uploaded the .oml in the attachment. You can find the problem occuring on the Users page.

hb.oml

12 Jul 2016

1 reply

12 Jul 2016

Show thread

Hide thread

Daniel de Witte

Erik-Jan Bulthuis wrote:

@Daniel: I've tried "{User}.[Name]" but that doesn't work either.

@Sahski: I've uploaded the .oml in the attachment. You can find the problem occuring on the Users page.

Did you test it by deploying and actually running the application? (your design-time previeuw might not give the correct result when using the

List_SortColumn_GetOrderBy

function.
As it should be working (i use many aggregates with grouping on multiple entity sources that i sort without problems) i suspect, if you tested it by deploying and really visiting the website, you might have something interfering with your sorting (or sorting it another way) before the page load ends.

12 Jul 2016

Erik-Jan Bulthuis

Done. I suppose you'll nee the new .oml?

hb.oml

12 Jul 2016

Shashi Kant Shukla

Hi Erik,
Please remove Grouping from username & email columns(highlited in red)then it should work as expected:

After removing it will look like:

12 Jul 2016

Erik-Jan Bulthuis

So, I only need to have a grouping on User.Id and User.Name? I tried that but the sorting still doesn't work while another problem occurs as well: I'm not able anymore to use GetUsers.List.Current.Username in an expression. It seems I can only use those variables in the expression which I grouped by.

12 Jul 2016

Erik-Jan Bulthuis

I'm able to deploy because in some expressions I used  GetUsers.List.Current.Username which made my application invalid if the username wasn't grouped by. So, maybe it's a good idea to say a few things about the process I went through:

1. I'd like to have an overview of all the users with role with access to my application
2. If i aggregate only the users everything is fine and I can use  GetUsers.List.Current.Username and I had no problems sorting the aggregate
3. However, I then also have all the users with roles that are not aligned to my application, so therefore I made a joint to the roles (and espace, application, etc.)
4. I do have a few users with more than 1 application role which had the result that I needed to group by on unique users. I now faced two problems:
=> a. The sorting didn't work anymore
=> b. The variable  GetUsers.List.Current.Username or GetUsers.List.Current.User.Username wasn't available anymore, unless I made a group by of this variable.

Does this help with understanding my problem?

12 Jul 2016

Paulo Duarte Ferreira

Staff

Solution

Hi Erik,

To see if that's your scenario, try this:

Set a test value for the List_SortColumn_GetOrderBy(UserTable.Id, "<Name>") expression (e.g. "Name ASC")
Check the Executed SQL property of the Aggregate and see if the SQL has an ORDER BY clause

If it doesn't, then your sort is actually a "standard" Sort and is being ignored.

To fix it, just delete the Sort and add a new Dynamic Sort with the same expression as before. Since the Aggregate is grouped, this will create a Grouped Sort.

Let me know if this helped.

Regards,

Paulo Ferreira

18 Jul 2016

1 reply

14 May 2020

Show thread

Hide thread

Balaji Ravikumar

Paulo Ferreira wrote:

Hi Erik,

To see if that's your scenario, try this:

Set a test value for the List_SortColumn_GetOrderBy(UserTable.Id, "<Name>") expression (e.g. "Name ASC")
Check the Executed SQL property of the Aggregate and see if the SQL has an ORDER BY clause

If it doesn't, then your sort is actually a "standard" Sort and is being ignored.

To fix it, just delete the Sort and add a new Dynamic Sort with the same expression as before. Since the Aggregate is grouped, this will create a Grouped Sort.

Let me know if this helped.

Regards,

Paulo Ferreira

Thank you Paulo its working as you said,

Regardsm

Balaji R

14 May 2020

Erik-Jan Bulthuis

Thanks Paulo! It worked. I had to delete the sort and add it again, but then it was in the executed SQL.

18 Jul 2016

Stanley Mok

I have the same problem and found Paulo's solution works for me too.

However, where can I find the Executed SQL property of the Aggregate?

I am on version 9.1.603.0

Thanks,

Stanley

16 Jan 2017

1 reply

16 Jan 2017

Show thread

Hide thread

Justin Babel

Stanley Mok wrote:

I have the same problem and found Paulo's solution works for me too.

However, where can I find the Executed SQL property of the Aggregate?

I am on version 9.1.603.0

Thanks,

Stanley

Hi Stanley,

When inside the aggregates editor (Double clicking on aggregate), you should see the following:

Let me know if you have any trouble,

Justin

16 Jan 2017

Stanley Mok

Go t it.

Thanks Justin.

16 Jan 2017

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines