cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Treble009
Regular Visitor

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

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)
    • Treble009_2-1638461779575.png

       

  • I was able to create a drop down which shows all the possible categories via the following code
    • Choices(Suppliers.Category)
    • Treble009_1-1638461744683.png

       

 

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)
    • Treble009_0-1638461716405.png

       

  • 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!

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

10 REPLIES 10
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes,

 

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

RandyHayes
Super User
Super User

@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)

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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
Regular Visitor

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
Helper I
Helper I

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

@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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,285)