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

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.

 

View solution in original post

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
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,888)