cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kriggo15
Post Patron
Post Patron

Filtering gallery based on both, text input and dropdown selection

I have a gallery based on a collection that I want to be able to use multiple dropdowns to filter the list and also have the ability to search using a textbox. Also, I need the dropdowns to contain distinct values.  I have both functions (search & filtering on dropdown) working separately, but cannot seem to get them to work together. How do I combine these 2 functions?

 

Filtering gallery based on Search box (works by itself):
Filter(StewardList2,TextSearchBox1_1.Text in 'Steward User - Subject Area'||TextSearchBox1_1.Text in 'Asset Name'||TextSearchBox1_1.Text in 'Steward User - Steward Type')

 

Filtering gallery based on dropdowns (works by itself):

Filter(StewardList2, 'Steward User - Steward Type' = Dropdown1.Selected.'Steward User - Steward Type' && 'Steward User - Onboarded?' = Dropdown2.Selected.'Steward User - Onboarded?')

 

Lastly, I need my dropdowns to contain only distinct values. I tried setting a variable using distinct on the fields but when I set the dropdown source as that variable, my filtering would not work. 
Formula: Set(DistinctAuthor,Distinct(StewardList2,'Steward User - Onboarded?'))

 

Need for applying distinct to the dropdowns:

ss1.png                    ss2.png

1 ACCEPTED SOLUTION

Accepted Solutions

Here's how you can do it with dropdowns. Items property of the gallery: 

 

Search(Filter(Steward,If(IsBlank(Dropdown3.Selected.Result)&&IsBlank(Dropdown4.Selected.Result), true ,If(!IsBlank(Dropdown3.Selected.Result),Type=Dropdown3.Selected.Result, true )&&If(!IsBlank(Dropdown4.Selected.Result),Completed=Dropdown4.Selected.Result, true ))),TextSearchBox5.Text,"Name","Subject","Type")

 

Other details in the attached document. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

View solution in original post

11 REPLIES 11
PowerAddict
Super User
Super User

Can you share your collection format? Both the things you are looking for should be easy to implement. I can give you sample formulae but knowing your collection will make sure I give you the formulae that would make the most sense. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

 

Let me know if this is not what you were looking for.

 

Data Source: Excel file with Steward Export table.

 

Collection Building:

ClearCollect(StewardList2,[@Steward_Export]);

 

Gallery Fields:

ss3.png

I have attached a document that shows I have done it using some sample data similar to yours. 

 

Basically, this is the Items property of my gallery: 

 

Search(Filter(Steward, Type = Dropdown3.Selected.Result && Completed = Dropdown4.Selected.Result), TextSearchBox5.Text, "Name", "Subject", "Type")

 

Dropdown3 displays Steward Type

Dropdown4 displays Completed Author Training? 

TextSearchBox5 is where the user can input text that will be searched against Steward Type, Asset Name, and Subject Area. 

 

You can further enhance it by using combo boxes instead of dropdown to allow for blank values in the filters and then when nothing is selected in the dropdown, you can show all the items. 

 

Let me know if this works and if you need any more info. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Did this solve your problem? If so, please accept that reply as the solution so others can find it easily.

Thanks!

Thanks for the detailed response. I got it working but based on your example. I guess the only tweak I need to solve my issue is for it to show all items until a selection is made. Based on your response, I would have to use a combo-box for that? If so, I've never used one of those before. How would I go about configuring it?

Yup, that's what I had thought. Let me put together an example with combo boxes and I will share that with you.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Thanks @PowerAddict , if it can be achieved using individual dropdowns, that is preferable, but if not, i'll try the combo box.

Here is how you can do it using combo boxes. Gallery item set to: 

 

Search(Filter(Steward, If(IsBlank(ComboBox11.Selected) && IsBlank(ComboBox10.Selected), true, If(!IsBlank(ComboBox11.Selected),Type = ComboBox11.Selected.Result, true) && If(!IsBlank(ComboBox10.Selected),Completed = ComboBox10.Selected.Result, true))), TextSearchBox5.Text, "Name", "Subject", "Type")

 

Attached document gives you all the details. There is a way to do it using dropdowns too. I will show that in a diff document. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Here's how you can do it with dropdowns. Items property of the gallery: 

 

Search(Filter(Steward,If(IsBlank(Dropdown3.Selected.Result)&&IsBlank(Dropdown4.Selected.Result), true ,If(!IsBlank(Dropdown3.Selected.Result),Type=Dropdown3.Selected.Result, true )&&If(!IsBlank(Dropdown4.Selected.Result),Completed=Dropdown4.Selected.Result, true ))),TextSearchBox5.Text,"Name","Subject","Type")

 

Other details in the attached document. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,686)