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

Filter gallery using a combo box that allows multiple selections

I have a SharePoint list with two columns: Title and MyChoices.

Title = Text column. 

MyChoices = 'Choice' type. Allows multiple selections. Allows 'fill in' values. Pre-set values for MyChoices are A, B, C, and D.

 

The actual list sample values look like this:

Choices.png

I have a PowerApp created using this list, with a Gallery and a ComboBox. The idea is to filter the gallery using the combo box selections. I specifically have two requirements:

  1.  User be able to all distinct values of MyChoices (remember, it allows fill-in values) in the combo box.
    • This means, according to above sample data, the combo box should display A, B, C, D, Z as selection items.
  2. Gallery is filtered by multiple choices.
    • That is, for instance, if the user selected A and Z in the combo box, the Gallery should filter to show items 1, 5, 6, and 8.

My code is like this so far:

 

ComboBox.Items

 

Distinct(
    Ungroup(
        ForAll(
            _DeleteThis_MultipleChoice,
            {Item: MyChoices}
        ),
        "Item"
    ),
    Value
)

 

 

Gallery.Items

 

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.Selected.Result in MyChoices.Value
)

 

 

This works partially. The first two checks takes care of empty items, and the last one filters the list by the user choice. But the issue here is obviously, it only takes into account the last choice the user made. So if the user selected in the combo box A and Z in that order, the list would filter only to show item #8.

 

I understand that this is because I'm using ComboBox1.Selected.Result which is a single select. I've tried to do ComboBox1.SelectedItems in MyChoices.Value, but it gives me an error:

Err.png

 

How do I fix this?

1 ACCEPTED SOLUTION

Accepted Solutions
WillPage
Power Participant
Power Participant

Try this:

 

Filter(_DeleteThis_MultipleChoice, CountRows(Filter(MyChoices,Value in ComboBox1.SelectedItems)) > 0)

 

View solution in original post

7 REPLIES 7
BCBuizer
Super User
Super User

Hi @SachS ,

 

Using the Choices() function it is a lot easier to get the distinct values for the MyChoices column:

ComboBox.Items = Choices(_DeleteThis_MultipleChoice.MyChoices)

 

Still you will have to add .Value after SelectedItems to take care of the error:

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Value in MyChoices.Value
)

 

 

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

@BCBuizer 

Thanks for  the reply.

 

The use of Choice() in Combo Box won't work for me, since, as I mentioned in the post that the [ MyChoices ] is a Choice column but also allows fill in values by the user, and I need the ComboBox to present those fill in values as options to filter as well.

 

Re. the Gallery.Item, it still doesn't work even if I add .Value. Please see below:

Err.png

 

BCBuizer
Super User
Super User

Hi @SachS ,

 

In that case, replace .Value for .Result

 

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Result in MyChoices.Value
)
Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.
SachS
Helper II
Helper II

@BCBuizer it's still the same.

 

If I use Choices() function in the ComboBox, my code looks like this in Gallery.Items:

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Value) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Value in MyChoices.Value
)

 

And if I use the more elaborated function I showed earlier in the ComboBox, then I must use .Result, like so:

Filter(
    _DeleteThis_MultipleChoice,
    IsBlank(ComboBox1.SelectedItems.Result) || IsEmpty(ComboBox1.SelectedItems) || ComboBox1.SelectedItems.Result in MyChoices.Value
)

 

But in either case, while the ComboBox.SelectedItems.Value or ComboBox.SelectedItems.Result works for the IsBlank() check, it gives the above mentioned "Invalid argument type. Cannot use Table values in this context" error message for the code after the last OR in the code shown. See below:

Err.png

 

WillPage
Power Participant
Power Participant

Try this:

 

Filter(_DeleteThis_MultipleChoice, CountRows(Filter(MyChoices,Value in ComboBox1.SelectedItems)) > 0)

 

BCBuizer
Super User
Super User

Hi @SachS ,

 

So PowerApps turns out not to like an N:N search in this manner. Please check this video on how you can achieve the desired result: https://www.youtube.com/watch?v=YYizaX6gXW0

Did you like my post? Please give it a thumbs up! Did I resolve your issue? Please click Accept as Solution to close the topic and so other members of the community can find solutions more easily.

This was what I was looking for, thanks!

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (2,241)