Using a combobox to filter choices column

(0) Share

Report

Posted on by MH-02091453-0

125

Hi,

I have seen similar post but for choice columns

I have a choices column and want to be able to filter using multi-select on a combo box. I don't want to create tick boxes as shown in this video Power Apps filter multiple selections - Exact or All options - Try again - Bing video as the list of selections will be too long on the screen.

So I have a choices column of USStates and several states can be collected but I also want to search for several different states being chosen by using a combobox that has the same choices (USStates).

At the moment I have

Filter(MyTable, USStates in ComboBox4.SelectedItem.Value) - This only filters by the last(most recently) selected item and not all sleected. So I tried,

ForAll (ComboBox.SelectedItems, Filter(MyTable, USStates in ComboBox4.SelectedItem.Value)) mbut then I just get true/false values.

Any ideas? I am really new to powerapps and still figuring out the filtering.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@MichelleH

Yes, please consider changing your Formula to the following:

Filter(MyTable, USStates in ComboBox4.SelectedItems.Value)

SelectedItem - is a single selected record

SelectedItems - is a table of all the selected records.

I hope this is helpful for you.

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the reply. yes, I tried this and just tried again to check...unfortunately it shows an error over USStates saying invalid argument: cannot use table values in this context. My USStates is a CHOICES column? any ideas?

Was this reply helpful? Yes No
v-yujincui-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MichelleH,

Do you want to use Combo Box to filter the Choice columns that allow multiple selections?
I have made a test for your reference.

1.Here is my SharePoint list.

2.Add a Text Input Control.
3.Add a Gallery Control and apply the following formula on its items property as:
If(!IsEmpty(ComboBox1.SelectedItems),Filter(Filtertest,"yes" in ForAll(ComboBox1.SelectedItems,If(Value in Category.Value,"yes","no"))),Filtertest)
Result Screenshots:

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like
a
(0)

Report
Copy link

Link copied!

That is amazing thank you, before it was giving me true/false values in the gallery now it gives the actual values. Is there anyway to make combobox filters as AND so to only retrieve the results where all they have ALL selecteditems?

Thanks

Was this reply helpful? Yes No
Verified answer

v-yujincui-msft Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @MichelleH ,

Do you want to only retrieve the results where all they have ALL selecteditems?
I have made a test for your reference. This formula can meet your matching needs in the case of multiple selection. Do you also want an exact match in the case of single selection?

Select the Gallery Control and apply the following formula on its items property as:
Filter(Filtertest,CountIf(ForAll(ComboBox2.SelectedItems As CS,If(CS.Value in Category.Value,"yes","no")),"yes" = Value )>=CountRows(ComboBox2.SelectedItems))
Result Screenshots:

Best Regards,
Charlie Choi

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Charlie,

I was wondering if there is some way to find if the length of the combo box selected items was greater or same as the number of values (choices), that solution is perfect. Wish I new more of the syntax like you!🙂 I will keep learning. Thank you again

Was this reply helpful? Yes No
MH-02091453-0 125 on at

Like
a
(0)

Report
Copy link

Link copied!

I am wondering how apply the same type formula in a situation where you are using a combo box to filter in a custom many to may situation?

So I have

ClearCollect(fourthCollection, ForAll(Filter(Skills, Skills.SkillName in ComboBox6.SelectedItems.SkillName), Student));

This collects all the students who have ANY of the skill with the name in selectedin the items in the combo box.

There are tables student (1 - many|) Student_skills (many to one) SkillNames (the name column is a text column) I used the SkillNames to populate the combox by having its items as SkillNames ( table name)

Thanks

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yujincui-msft ,

I was stuck in a similar situation and your solution was really helpful but I need to use multiple combo boxes as filters for my gallery. I tried using the same formula with little tweaks as listed below.

If(!IsEmpty(ComboBox1.SelectedItems),Filter(Filtertest,"yes" in ForAll(ComboBox1.SelectedItems,If(Value in Category.Value,"yes","no"))),
If(!IsEmpty(ComboBox2.SelectedItems),Filter(Filtertest,"yes" in ForAll(ComboBox2.SelectedItems,If(Value in 'Category2'.Value,"yes","no"))),
If(!IsEmpty(ComboBox3.SelectedItems),Filter(Filtertest,"yes" in ForAll(ComboBox3.SelectedItems,If(Value in 'Category3'.Value,"yes","no"))),
If(!IsEmpty(ComboBox4.SelectedItems),Filter(Filtertest,"yes" in ForAll(ComboBox4.SelectedItems,If(Value in 'Category4'.Value,"yes","no"))),
Filtertest))))

The filters worked well, but my gallery shows nothing when I initially enter the screen and none of the filters are selected. Can you point me in the right direction? I am not sure where did I go wrong!

Thanks

Was this reply helpful? Yes No
TanajiChavan 4 on at

Like
a
(0)

Report
Copy link

Link copied!

This has worked perfect for me. Thanks for sharing this solution.

Was this reply helpful? Yes No
cammic 6 on at

Like
a
(0)

Report
Copy link

Link copied!

This is a great solution BUT I replicated this example exactly and still cannot get the filter results I need. For example, in my Combobox I select "Fruit" and "Cheap" I got SharePoint items where the Choices have Fruit + Cheap BUT how can I filter and find records where the Category contains "Fruit" and/or "Cheap". Thanks

Was this reply helpful? Yes No