cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cah2035
Helper III
Helper III

Filter SharePoint list where an item has a specific value in their multi-choice column

I have a list of products and one of the columns is a multi-choice column for "category", and a product can be put into multiple categories. In PowerApps, I want to populate a dropdown with these products filtered by a specific category (i.e. filter for items where "CategoryA" exists as one of the items categories). How can I accomplish this (preferably without doing a collection)? 

 

This is just an example of the issue and not reflective of my actual data. I am only going to have 20 or so "products" so delegation will not be an issue. 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @cah2035 ,

 

According to your description, I've made a test for your reference:

1\ This is my list 'LIST68'. 'Product' is a Single line of text column. 'Category' is a Choice column.It allows multiple selections.

v-xiaochen-msft_0-1612321703868.png

 

2\ Add a combo box control 'ComboBox2' and set its Items property to:

Choices(LIST68.Category)

3\ Add a gallery control and set its Items property to:

Filter(ForAll(LIST68,If(CountRows(Filter(ForAll(ComboBox2.SelectedItems,If(ThisRecord.Value in LIST68[@Category].Value,ThisRecord)),!IsBlank(Value)))>0,ThisRecord)),!IsBlank(Category.Value))

 

4\ The result is as follows:

v-xiaochen-msft_1-1612321863131.png

v-xiaochen-msft_2-1612321889332.png

 

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

View solution in original post

3 REPLIES 3
v-xiaochen-msft
Community Support
Community Support

Hi @cah2035 ,

 

According to your description, I've made a test for your reference:

1\ This is my list 'LIST68'. 'Product' is a Single line of text column. 'Category' is a Choice column.It allows multiple selections.

v-xiaochen-msft_0-1612321703868.png

 

2\ Add a combo box control 'ComboBox2' and set its Items property to:

Choices(LIST68.Category)

3\ Add a gallery control and set its Items property to:

Filter(ForAll(LIST68,If(CountRows(Filter(ForAll(ComboBox2.SelectedItems,If(ThisRecord.Value in LIST68[@Category].Value,ThisRecord)),!IsBlank(Value)))>0,ThisRecord)),!IsBlank(Category.Value))

 

4\ The result is as follows:

v-xiaochen-msft_1-1612321863131.png

v-xiaochen-msft_2-1612321889332.png

 

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

 

Hi @v-xiaochen-msft 

 

Thanks for the help. The situation you set up is similar to what I am trying to accomplish, but not exact. My input in powerapps will not be a combobox, but will be a single value being passed along ("CategoryB" for example), and my output will be a dropdown (not combobox) with Products that contain Category B. The example belong uses the same list you had put together, but my dropdown is not filtered yet. Since I am only passing one value into the formula, there is probably a ForAll in your example that can be removed since I don't need to loop through multiple values. Can your formula be simplified to account for this?

cah2035_0-1612372159133.png

 



@v-xiaochen-msft I played around with the formula and believe I got what I need. 

 

Filter(
  ForAll(
    LIST68,
    If(
      varSelectedCategory in ThisRecord.Category.Value,   
      ThisRecord.Product
    )
  ),
  !IsBlank(Value)
)

//Where varSelectedCategory is the single value that I am passing to the formula i.e. CategoryA

 

So this seems to work for me when I only need to check for a single Category value and not more than one. 

Helpful resources

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

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (3,372)