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

[SeanTambling]

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.

[v-monli-msft]

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

[SeanTambling]

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?

[SeanTambling]

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.

[SeanTambling · Accepted solution]

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.