cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SeanTambling
Helper IV
Helper IV

How to sort a gallery by a choice field in a Canvas App Gallery connected to SP list

I have a gallery that has Title,SubTitle,Body. For Title I used Title from the SP list. For Subtitle I used a choice field called BillTo, and for body i have another choice field called Status.

For the items of the gallery i have tried sorting the gallery by the choice field BillTo but I'm getting either errors or the gallery goes blank. This is what i currently have after searching for solutions here:

SortByColumns(AddColumns(PrintRequest,"SortPriority", BillTo.Value), SortPriority, If(SortOrder, Ascending, Descending))

 

SortPriority is a variable set by clicking 1 of three buttons:

Sort By Title: UpdateContext({SortPriority:"Title", SortOrder:!SortOrder})

Sort By BillTo: UpdateContext({SortPriority:"BillTo", SortOrder:!SortOrder})

Sort By Status:UpdateContext({SortPriority:"Status",SortOrder:!SortOrder})

 

I would like to be able to click one of the 3 buttons and have the gallery sorted by those columns, but as of yet, i'm not having any luck.

 

All help much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions

After more research reading the documentation, and really finally understanding it, I've managed to get the filter to work like i had wanted.

Here is the formula: 

SortByColumns(Filter(PrintRequest, TextSearchBox1.Text in Title || TextSearchBox1.Text in BillTo || TextSearchBox1.Text in Status || TextSearchBox1.Text in DueDate), SortPriority, If(SortDescending1, Descending,Ascending))

I thought I would post this for others that were having trouble with filters and sorting. SortPriority is a context variable set by clicking one of 3 buttons that sorts the list by one of 3 columns.

 

The documentation for Filter, and Search is located here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

I've read that page several times, but today it finally clicked for me. 

View solution in original post

4 REPLIES 4
v-monli-msft
Community Support
Community Support

Hi @SeanTambling ,

 

You mentioned that you want to "sort the gallery by the choice field BillTo" and you also said that "I would like to be able to click one of the 3 buttons and have the gallery sorted by those columns". You cannot have one data source to be sorted with multiple fields, that's just inconsistent and cannot be achieved.

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I apologize for not being clear. So i do realize you can't sort by multiple columns all at the same time (even though there could be made a good case on why that could be helpful if there was a way to make priority lvls on the colums, but back to the question). I would like the gallery to be sorted one way when the screen first appears. Then I'd like to have 3 buttons that could toggle how the gallery is sorted when pressed. I've seen Laura Rogers post videos on this, but when I tried her solution, I'm getting errors. I believe it has something todo with the fact i'm trying to sort on a column that are choice columns. If that is the reason, is there a work around?

So, after reconfiguing the BillTo field to a text field and the Status field to a text field (both formerly choice fields from sharepoint list) and replacing the corresponding data cards in the NEW/EDIT screen forms, the sort buttons now work. I am having trouble trying to figure out how to filter the the gallery. 

I have 1 gallery that is supplied data from a SP list.

on the start screen i have the 3 buttons that sort the gallery by "Title", "Bill To", and "Status". I'm using a context variable to change the sorts. The variable is: "SortPriority"

SortByColumns(Filter(PrintRequest, StartsWith(SortPriority, TextSearchBox1.Text)), SortPriority, If(SortDescending1, Descending, Ascending))

 

I don't get errors with this, but when i search for anything regardless of which of the 3 buttons was pushed, the search returns no results so the gallery is empty.

I hate being such a noob at this, but i'm still struggling with know exactly where the controls are causing the problem.

 

Ultimately, i would like to push one of the 3 buttons and the gallery sort by that column and then when text is entered into the search box, the filter affect the column of the current sort button. 

 

Hope that makes sense. Thanks for any and all help.

After more research reading the documentation, and really finally understanding it, I've managed to get the filter to work like i had wanted.

Here is the formula: 

SortByColumns(Filter(PrintRequest, TextSearchBox1.Text in Title || TextSearchBox1.Text in BillTo || TextSearchBox1.Text in Status || TextSearchBox1.Text in DueDate), SortPriority, If(SortDescending1, Descending,Ascending))

I thought I would post this for others that were having trouble with filters and sorting. SortPriority is a context variable set by clicking one of 3 buttons that sorts the list by one of 3 columns.

 

The documentation for Filter, and Search is located here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

I've read that page several times, but today it finally clicked for me. 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,591)