Connect Gallery to search field and combo box which is linked to SharePoint list which allows multiple selections

[Treble009]

Hi, 

I just recently got into world of PowerApps and I'm running into an issue. I tried to search for related messages in this form, but could not find something related.

What I'm tryin to accomplish

SharePoint

• I have created a SharePoint list for all my future suppliers
• This list shows what kind of brands the suppliers sell, contact persons, etc. (the columns)
• One of the columns is a "category", which allows to select multiple. (e.g. clothing, toys, accessories, etc.)

PowerApps

• I want to have a search field which allows me to search on supplier name, contact person name and brands
• I want to have a combo box which allows me to select 1 or more "categories"
• Depending on the selections in the combo box and the search field, I want the gallery to show the correct information  
  • When multiple categories are selected it should match to one of the categories selected (or function in excel)

What I got so far

• I was able to connect a gallery to a search field via the following code:
  • Search(Suppliers,TextSearchBox1.Text,"Contactperson","Brands","Title") (title=supplier name)
  • 
• I was able to create a drop down which shows all the possible categories via the following code
  • Choices(Suppliers.Category)
  • 

This is where I get stuck

• As an intermediary step I wanted to check if I where able to connect the category combo box to a gallery. So far I'm only getting errors or nothing returned
• I use the following code:
  • Filter(Suppliers,Category = ComboBoxCategory.Selected)
  • 
• I did not even bother trying to knot the two together.

Is there anyone who can help me out with this problem at hand?

Thanks!

[RandyHayes]

@Treble009 

Since Category is a Choices column, it will be a record.  So, you need to reference the column of that record to compare.

    Filter(Suppliers, Category.Valuye = ComboBoxCategory.Selected)

I hope this is helpful for you.

[Treble009]

Hi @RandyHayes,

I tried your solution, but i did not work. With me the = gets red underlined. Stating: incompatible types for comparison.

[RandyHayes]

@Treble009 

Opps, I see there was a typo in my formula and a missing column name.

Should be:

    Filter(Suppliers, Category.Value = ComboBoxCategory.Selected.Value)

[Treble009]

@RandyHayes, updated the code, but still i get the same message: incompatible types for comparison, these types cannot be compared: table, text. (later past was different before)

[Treble009]

When i adapt the formula to: 

• Filter(Leveranciers;Categorie.Value = ComboBoxCategory.SelectedItems.Value)

is still get an error stating:

• incompatible types for comparison, these types cannot be compared: table, table 😑

[BoHamad]

• Hi ,
• Filter(Suppliers,Category in ComboBoxCategory.SelectedItems)
• hope this work

[RandyHayes]

@Treble009 

Yes, you will get an error with that formula!  Totally glossed over the multiple selections!

    Filter(Leveranciers; Categorie.Value in ComboBoxCategory.SelectedItems.Value)

Is what you want.  

Your SelectedItems is a table.  When you put a column name behind it, it just makes it a table with just that column...but it's still a table.  And you can't compare a text value to a table.  

The in operator will resolve that.

Keep in mind that the in operator is not delegable, so if you have more than the record limit items in your datasource, this will be an issue.

[Treble009]

Hi @RandyHayes,

Updated the formula with your suggestion, still getting an error, this time the first value gets underlined with the message:

• Invalid argument type. Cannot use table values in this context.
• Filter(Leveranciers,Categorie.Value in ComboBoxCategory.SelectedItems.Value)

PowerApps only allows me to select Value in the first case. So not sure what else there can be?

[RandyHayes · Accepted solution]

@Treble009 

Ah, had to re-read your original post that you have a choice column with a multi-select option turned on.

So, you need to compare all the choice options against all of the combobox options.

The formula would be:

Filter(Leveranciers; 
    Sum(ForAll(Categorie, If(Value in ComboBoxCategory.SelectedItems.Value, 1, 0)), Value)>0
)

This is one of several ways to go about this.  Essentially what happens is we use a ForAll table function to iterate the Categorie values.  A simple If statement will compare each iteration value to determine if it is in the selected combobox items.  If so, then that row of the forall table will be 1.  Finally, we sum all the Value columns of the ForAll (the value columns in this case are all the 1's and 0's just determined).  If the sum is more than 0, then there was a match and the result will be true and thus that row included in your resultant filter.