cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AFernandez
Level: Powered On

Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

Hello, 

 

I am trying to filter a gallery by a searchbox and multiple dropdown fields. I have done this before entering manually the values for the dropdown fields, but now I am using a sharepoint list to populte the dropdowns and I used a clearcollect / collect option to add a "All" or blank field to the dropdowns and I cant get the filter to work.

 

1. I created several dropdown fields and populated each using the formula: 

    On select: ClearCollect(collection1, {Name: "All"}); Collect(collection1, Distinct(mySharePointList, MySharePointColumn.Value))

    Item: Collection1

    I repeated these formulas for each dropdown, changing the collection number (collection2, collection3...) and the columns in SP.

 

2. I tried filtering the gallery the way I done it before when I populated the dropdown fields manually:

   Items: Filter(MySharePointList, If(Dropdown1.SelectedText.Value <> "All", MySharePointColumn.Value =                                                        Dropdown1SelectedText.Value, True)... 

   Repeat for each dropdown and add a Search(Searchbox1.Text, "MySharePointColumn") to the formula to search by the               searchbox.

 

What I want the formula to do is filter the gallery by the items selected on the dropdown fields or show all items if the Dropdown field is blank (" ") or "All".  Right now I can get it to filter, but it does not show all items if blanck (" ") or "All" is selected.

 

From what I have seen the problem has to do with with the Value of the dropdown field.  I see that the value can be from the Name created with the ClearCollect (Blank " " or "All") or the result of the Distinct in the Collect Formula (for example, a project list). Each is a Column of the collection.  When I choose Name as a the Value for the DropDown field it shows only the Column Name, so it can't filter by the values from the SharePoint list and when I choose Result as the Value for the DropDown it shows the values from the SharePoint List and I can filter the gallery by this values, but I can't show all items. 

 

Any help is appreciated!  I tried looking at other posts but could not find any exactly related to this problem (sorry if I missed one).

 

Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

@AFernandez 

I wonder if this generic example would be helpful.  It would be placed in the Items property of the gallery.

Filter(
    your_datasouce_name,
    IsBlank(Dropdown1.Selected.Text)=true Or Column1 = Dropdown1.Selected.Text,
    IsEmpty(Combobox1.Selected)=true Or Column2 = ComboBox1.Selected,
    IsBlank(TextInput1.Text)=true Or Column3 = TextInput1.Text
)

 

You would have to change the "All" to Blank() instead in the Dropdown though.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

Try something like:

Filter(MySharePointList, IsBlank(Dropdown1.Selected) || Dropdown1.Selected.Value = "All" || MySharePointColumn.Value = Dropdown1.Selected.Value)

Dropdown1.Selected.Value might need to be replaced by another column name. See what suggestions you get after you type in the dot.

Let me know if this works.

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

Hardit Bhatia
https://thepoweraddict.com
Super User
Super User

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

@AFernandez 

I wonder if this generic example would be helpful.  It would be placed in the Items property of the gallery.

Filter(
    your_datasouce_name,
    IsBlank(Dropdown1.Selected.Text)=true Or Column1 = Dropdown1.Selected.Text,
    IsEmpty(Combobox1.Selected)=true Or Column2 = ComboBox1.Selected,
    IsBlank(TextInput1.Text)=true Or Column3 = TextInput1.Text
)

 

You would have to change the "All" to Blank() instead in the Dropdown though.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

AFernandez
Level: Powered On

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

Thank you so much for your quick response. It filters  by the value selected on the dropdown field, but it does not show all ítems in the gallery if nothing is selected in the dropdown (same as with what I tryed). 

 

I am going to keep triying with your formula to see if something Works.

Super User
Super User

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

@AFernandez 

Which one of these represents "nothing" in your dropdown?

 

  • "" a zero character string?
  • Blank() a null value?

 

I believe the solutions above would only work with the null value.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

AFernandez
Level: Powered On

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

Thank you so much, this worked! 😀.

 

Could you explain the logic behind the formula just to make sure I undertand it correctly? 

 

If the dropdown field is blank = True: Shows all ítems in the gallery.

Or

SharePoint Columns = value selected in the Dropdown.

 

I only tryied it with one dropdown, I hope I can make it work with all the dropdown fields and the search box. 

Super User
Super User

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

@AFernandez and @mdevaney just one small suggestion:

IsEmpty(Combobox1.Selected)=true

Can be replaced with

IsEmpty(Combobox1.Selected)

The result of IsEmpty will either be true or false, there is no need to compare it to true (or false).

Just trying to make that code as efficient as possible 🙂

Thanks,
Hardit Bhatia
https://thepoweraddict.com
Super User
Super User

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

@PowerAddict
Agreed. It’s a good suggestion. I only tend to put “=True” since not everyone knows that the equals sign is not necessary.
AFernandez
Level: Powered On

Re: Filtering Gallery by Multiple DropDown Fields Populated with Data from SharePoint list adding a blank - All Option.

I think thats a good practice. When people like me, that are not from tech or programning backgrounds are trying to understand or figure out this types of situations it can be confusing if we  do not see a part of a formula or the explanation for when something can be used or not. 

 

Thanks for everything everone!  👏🏆!

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,954)