Matching values in multiselect combobox to Sharepoint Column Names to sort by text value

[Miss_Zo]

Ok. I'm trying again because my original post did not seem to get accross what I'm trying to do. 

I have the following:

Sharepoint list

Product	Metals	Steel	Tin	Copper
A	Steel	Protected	Compatible	Compatible
B	Tin, Steel	Protected	Protected	Not protected

Screen 1: Gallery of checkboxes from the "Metals" in sharepoint list. These values match titles of other columns in the sharepoint list. These checkboxes create/update a collection as they are checked/unchecked.

Screen 2: Gallery of the sharepoint list that is being filtered by multiple factors. I have the collection from the first screen populating a comobobox so it can be used to the filter the products in the gallery. 

I want to be able to sort the products by their protection ability. So if the user selects Tin and Steel, the gallery will show product B first because Tin and Steel are both Protected. It would show product A second because Tin is only compatible. 

From looking around, I think what I have to do is a IsMatch scenario. I think what I need is something like this, but I don't know how to get it to work.

IsMatch(Concat(combobox1.SelectedItems, Value, ","), "Steel", MatchOptions:Beginswith)

    SortByColumns('Sharepointlist', "Steel", "Protected", "Compatible") 

|| IsMatch(Concat(combobox1.SelectedItems, Value, ","), "Tin", MatchOptions:Beginswith)

    SortByColumns('Sharepointlist', "Tin", "Protected", "Compatible") 

Right now I have a formula that will sort the results but by all columns and not only the ones listed in the ComboBox (cbmetals). This currently doesn't help sort the results in a useable way. I want to sort the results to have those with "Protected" in the columns listed first, followed by "Compatible".

Current Formulas Part 1:

Part 2:

Thanks everyone for all your help or just taking a look. I've been working on this project and learning so much. Powerapps is so tricky and this community is really amazing at helping to understand it. 

[WiZey]

Hello @Miss_Zo ,

"Sort()" works if you're expecting to sort a table in ascending or descending order, you can't force it to sort on another criteria.

That's why what can be done is to create a dynamic column storing a numeric value equals to the record "priority" for the sorting. Let's say we add a new column with "AddColumns()" named "Score".

Let's say you want product B to appear first, his "Score" must be higher than product A.

To do this, we'll add a number everytime the record validate a condition :

Score = 
Sum(
  If(Steel was checked && Steel Protected, 2, 0),
  If(Steel was checked && Steel Compatible, 1, 0),
  If(Tin was checked && Tin Protected, 2, 0),
  If(Tin was checked && Tin Compatible, 1, 0),
  If(Copper was checked && Copper Protected, 2, 0),
  If(Copper was checked && Copper Compatible, 1, 0),
  ...
)

The example above would give your product B a score of "4" and your product A a score of "3".

Steel = checked
Tin = checked
Copper = not checked

Product B Score = Steel Protected + Tin Protected + Copper Not protected
                = 2 + 2 + 0
                = 4

Product A Score = Steel Protected + Tin Compatible + Copper Compatible
                = 2 + 1 + 0
                = 3

With this, you have a sortable column based on your criteria.

Hope this was helpful to you.

[Miss_Zo]

@WiZey 

This makes a lot of sense in therory and I need a little help implementing it. I tried a few metals and I'm getting an error. I used the cbmetals because that's on the same page as the gallery I'm filterind and sorting. It can have one or more values in the combobox. I also have a collection that populates the combobox that I could use. 

I did the addcolumns to my sharepoint list. 

[WiZey]

"AddColumns()" should be done in the "source" parameter, not in the "logical_test" parameter of the "Filter()".

"AddColumns()" return a table with the source plus one or more additional columns. By putting it in the "source" parameter you can then use the additional columns for either filtering or sorting.

[Miss_Zo]

@WiZey 

I tried several different variations with AddColumns in the formula and all gave me an error. This is probably just my inexperience and lack of knowledge and misunderstanding of what you were trying to say. 

Most of the errors I'm getting are that the column I'm adding doesn't exist. Even when I run the app to create the column, it doesn't do it and cannot find the column to sort by. 

I tried several attempts. The following attemps were what seemed closest:

1. After Filter
SortByColumns(
    Filter(
        'Product Family Grouping',
        AddColumns(
        'Product Family Grouping', "Score",
            Sum(
                If("Mild carbon steel" in colmetalsfilter && 'Mild carbon steel' = "Protected", 2, 0),
                If("Mild carbon steel" in colmetalsfilter && 'Mild carbon steel' = "Compatible", 1, 0),       

2. Before Filter

SortByColumns(
    AddColumns(
        'Product Family Grouping', "Score",
            Sum(
                If("Mild carbon steel" in colmetalsfilter && 'Mild carbon steel' = "Protected", 2, 0),
                If("Mild carbon steel" in colmetalsfilter && 'Mild carbon steel' = "Compatible", 1, 0),
                Etc...
        ),
    Filter(
        'Product Family Grouping',

3. At the end of Filter

 IsBlank(cbtype.Selected.Value) || IsEmpty(cbtype.SelectedItems) || Paper_Film.Value = cbtype.Selected.Value,
        AddColumns(
        'Product Family Grouping', "Score",
            Sum(
                If("Mild carbon steel" in colmetalsfilter && 'Mild carbon steel' = "Protected", 2, 0),
                If("Mild carbon steel" in colmetalsfilter && 'Mild carbon steel' = "Compatible", 1, 0),

I also tried doing it a different way by adding the AddColumns to the Search button on the first page and using the collection instead of the combobox. The error I kept getting was that the added column didn't exist. 

[WiZey · Accepted solution]

Have you tried those combinations of functions? I think they should give you a valid result. 

Sort(
  Filter(
    AddColumns(
      source,
      "Score",
      Sum(...)
    ),
  ,//filter conditions
  ),
  Score,
  Descending
)

or

Sort(
  AddColumns(
    Filter(
      source,
      //filter conditions
    ),
    "Score"
    Sum(...)
  ),
  Score,
  Descending
)

If it's still not working, I can try giving different formulas. 

[Miss_Zo]

@WiZey 

If I could hug you I would. THANK YOU SO SO MUCH. I used your first format. I was putting in more code than needed. It does seem to have messed up my form view for some reason, but I think I can figure that out! Thank you!