Sorting on an aggregate with a group by

Sorting on an aggregate with a group by

  
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?


Erik-Jan Bulthuis wrote:
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?

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?
Hi Erik,

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

Regards
SK
@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. 
Done. I suppose you'll nee the new .oml?
Hi Erik,
Please remove Grouping from username & email columns(highlited in red)then it should work as expected:

After removing it will look like:

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.
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.
 
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?
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:

  1. Set a test value for the List_SortColumn_GetOrderBy(UserTable.Id, "<Name>") expression (e.g. "Name ASC")
  2. 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

Solution


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