cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Sortby with group by

Hi All,

 

I have a GroupBy function working, but wanted to add a sort by as I have an item order field I'd like to use. I tried to add the item id field into the GroupBy to sort, but I'm getting the error "Cannot sort on the expression type". Anyone know what I am doing wrong?

 

SortByColumns(
GroupBy(
Filter(Mapping, crb20_package = Dropdown2.SelectedText.Value),
"crb20_package",
"crb20_module",
"crb20_item",
"crb20_item_order"
),"crb20_item_order" , Ascending) 

6 REPLIES 6
Highlighted
Super User III
Super User III

Re: Sortby with group by

@Kathryn_Marie 

This error is occurring because you are trying to sort on the grouped column "crb20_item_order".  The last argument to GROUPBY is always the grouped column.  You can sort on any other column.

 

To get around this simply add another argument to GROUPBY called "myGroupedColumn".

 

SortByColumns(
GroupBy(
Filter(Mapping, crb20_package = Dropdown2.SelectedText.Value),
"crb20_package",
"crb20_module",
"crb20_item",
"crb20_item_order",
"myGroupedColumn"
),"crb20_item_order" , Ascending) 

 

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Helper III
Helper III

Re: Sortby with group by

Thanks, that doesn't quite do what I want though now. Adding the ID in the group by is now giving me duplicates. The ordering is based on 1 - the module (so modules will be numbered 1, 2, 3, 4, 5), then item. So within any module the item order will be 1, 2, 3, 4, 5.  Hence now getting the wrong results. So I'm thinking I need to use group by, and have sort a completely different action within the listbox.

Would I add that as a separate code to my listbox code?

Filter(Items, crb20_item_id in Filter(Mapping, crb20_package = Dropdown2.SelectedText.Value && crb20_module = LookUp(Modules, crb20_module = Label1.Text).crb20_module_id).crb20_item)

Highlighted
Super User III
Super User III

Re: Sortby with group by

@Kathryn_Marie 

I am already confused on what must be done here.  Any chance you could provide a sample of what your data looks like and what you want it to look like after the GROUPBY and SORT?

Highlighted
Helper III
Helper III

Re: Sortby with group by

I've included a shot of the data source, and what it needs to look like on the screen. The last step I need is to sort the items in the listbox based on the Item Order field. Does that help?RemoveFilter.png

Highlighted
Super User III
Super User III

Re: Sortby with group by

@Kathryn_Marie 

I do not see any relationship between your Excel data and the ListBoxes to the right-side.  Where is the text in the ListBoxes coming from?

 

Your screenshots are good.  I feel we are getting closer to having the same understanding.  I think you want to have some grouping of the records in each ListBox.

Highlighted
Helper III
Helper III

Re: Sortby with group by

The text is coming from a lookup table, and in the mapping table it only has Id's, which is why I need the lookup function as text does not equal what is in the mapping table.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (4,848)