Filter gallery based on multiple dropdowns (Data in Google Sheets)

[ek21]

Hi everyone,

Having an issue with my Filters for my Gallery. Basically, I have six Dropdown boxes and I want my users to be able to select as many or as few as they need. My Dropdowns are as follows: YearDrop, TermDrop, SectionDrop, RegionDrop, SchoolsDrop, and ActivityDrop.

However, I'm coming unstuck because I need to have the gallery return all records if any of these are left blank. Using the first two drops as an example I've got the following formula:

Filter(StudentActivity,YearDrop.Selected.Value in Year && If(TermDrop.Selected.Value = "",Term,TermDrop.Selected.Value in Term))

This works if the TermDrop has a selected value (either 1, 2, 3, or 4), but not if it's left blank. I'm obviously having the same problem in the If statements for the rest of the dropdowns too.

Any help much, much appreciated!

[v-micsh-msft]

Hi @ek21,

I don't think the Dropdown could be able to be left blank, could you please share some details about how you configrued the items property of the Dropdown?

By default, the Dropdown.Selected.Value would be set to the Dropdown.Default if User doesn't make any changes, and Dropdown.Default would be the first item of the records in the items property if user has no interactions.

In other words, the Dropodwn.Selected.value will never equals to "".

If here you would like to check if the Dropdown value has been updated by the user, then please consider take use of Variable and the OnChange property to deal with current situation.

For example, for the TermDrop Dropdown, configure the OnChange property to:

UpdateContext({UserTerm:true})

Then modify the formula of the filter as below:

Filter(StudentActivity,
       YearDrop.Selected.Value in Year && 
       If(UserTerm,TermDrop.Selected.Value in Term,true)
      )

Another thing that need to be done is to reset the Variable to false, Please add the following code of the under the OnVisible property:

UpdateContext({UserTerm:false})

For other Dropdown, you may need to create the corresponding Variable and follow the step to configure the property.

See if this would work.

Regards,

Michael

[ek21]

Hi @v-micsh-msft,

Thanks so much for your reply, this was really helpful. However, when I tried to configure my following Dropdowns it stopped working.

My final gallery items formula after following your advice ended up as follows:

Filter(StudentActivity,YearDrop.Selected.Value in Year && If(TermDrop.Selected.Value in Term,true) && If(SectionDrop.Selected.Value in Section,true) && If(RegionDrop.Selected.Value in Region,true) && If(SchoolsDrop.Selected.Value in School,true) && If(ActivityDrop.Selected.Value in Activity,true))

I'll provide a bit more context to help understand:

My other Dropdowns are called SectionDrop, RegionDrop, SchoolDrop, and ActivityDrop. Ideally, I wanted all of these Dropdowns to be blank upon visible, as some are automatically populated due to their datasources. As a workaround, I had added a blank row into the datasource so as to be the first "selection" per se on the dropdown. I've also set up cascading Dropdowns so that only the relevant data comes up upon selection (e.g., only the regions in the selected section are displayed, and then only the relevant schools within the selected region are displayed). Hope this makes sense. Even when I removed the blank rows in the datasources the formula didn't work with all of the Dropdowns. As with my previous message, I'd like users to be able to select as many or as few of the Dropdowns as required to show the data, but if a Dropdown is left blank to show all of the data within the parameters (e.g., all of the data from one region within 2017 while leaving TermDrop, SchoolDrop, and ActivityDrop blank).

My datasource inputs for each Dropdown are as follows:

SectionDrop = Distinct(Regions,Section)

RegionDrop = Filter(Regions,SectionDrop.Selected.Value in Section)

SchoolsDrop = Filter(Schools,RegionDrop.Selected.Value in Region)

ActivityDrop = Activities

Really appreciate your help on this!

[ek21]

Hi @v-micsh-msft,

Any chance you can still help with this??

Thanks a bunch,
Ellie

[Meneghino]

@ek21, you say "I need to have the gallery return all records if any of these are left blank"

This would suggest something like this:

Filter(StudentActivity, If( IsBlank(YearDrop.Selected.Value) || IsBlank(TermDrop.Selected.Value), true,  Year=YearDrop.Selected.Value && Term=TermDrop.Selected.Value))

I have taken your statement literally, so that if any of the dropdown are blank then ALL records are returned, i.e. no filter is applied.

[ek21 · Accepted solution]

Hi @Meneghino,

I understand where you're coming from, thanks for your response. However, I need the filter to be able to return all data based on multiple dropdowns. For example, the dropdown "SectionDrop" might be blank, but I need a specific activity from "ActivityDrop" to be the filtered on for 2017.

Filter(StudentActivity, If(IsBlank(YearDrop.Selected.Value) || IsBlank(TermDrop.Selected.Value) || IsBlank(SectionDrop.Selected.Value) || IsBlank(RegionDrop.Selected.Value) || IsBlank(SchoolsDrop.Selected.Value) || IsBlank(ActivityDrop.Selected.Value), true, Year=YearDrop.Selected.Value && Term=TermDrop.Selected.Value && Section=SectionDrop.Selected.Value && Region=RegionDrop.Selected.Value && School=SchoolsDrop.Selected.Value && Activity= ActivityDrop.Selected.Value))

 I'll try provide a bit more of a background. The app is to quickly show programme activities run in schools in particular regions within a particular time. For example, I might want to know all of the activities that are run in the first term of 2017 (i.e., YearDrop and TermDrop). I may also want to see all of the activities run in Southland (YearDrop and RegionDrop). What I hope I'm explaining well, is that I need to return ALL values within a set of criteria based on the combinations of selections from my dropdown boxes.

Thanks again for all your help!

[ek21 · Accepted solution]

Hi again @Meneghino,

I altered your code slightly and got it to work - final filter below:

Filter(StudentActivity, If(IsBlank(YearDrop.Selected.Value), true, Year=YearDrop.Selected.Value) && If(IsBlank(TermDrop.Selected.Value), true, Term=TermDrop.Selected.Value) && If(IsBlank(SectionDrop.Selected.Value), true, Section=SectionDrop.Selected.Value) && If(IsBlank(RegionDrop.Selected.Value), true, Region=RegionDrop.Selected.Value) && If(IsBlank(SchoolsDrop.Selected.Value), true, School=SchoolsDrop.Selected.Value) && If(IsBlank(ActivityDrop.Selected.Value), true, Activity=ActivityDrop.Selected.Value))

Thanks again for everyone's help on this!

[Nikhil2]

Hello,

Could anyone help me on this issue, Below is the powerapp which i am using to filter the gallery(documents) from the sharepoint library.

I have tried with many filter possibilites but it works either ways but i am looking for 2 sort of filters at a same time and also to download the multiple documents using checkbox.

1. Is it possible to filter the gallery by selecting values from the dropdown individually, For Ex: if i select training level and as a option "anything", i need to see or filter only training level documents which was defined in the sharepoint doc library and same way goes to all the dropdown. Well i am using Distinct(Table,Column name) for the all the drop downs to fetch the values.

2. Second approch, If i set values to all the dropdowns according to the selection i need see the results in the gallery.

3. There is a displaymode set to the below 3 dropdowns, i have value in training level when i select "-" other two dropdowns will be invalid.

[Nikhil2]

To Add on, For example:

1. This has to work like actual filter, When i select Training level "-" the result should pop up in gallery and show all documents which there under "-". And when actual filter applies to all the dropdowns it should provide the result.

2. And when i select Training level "-" and Application "Any Option" it should filter matching the variables and it should keep filtering accordingly. As many as filter if i apply it should provide the matching documents.

3. On Items for gallery, like i said it works either ways(since i am using "And"/"Or"). So it is either working for "And" otherwise "Or " Sort(Filter('Test Document',And(Training_x0020_Level=Dropdown1.Selected.Value,Application.Value=Dropdown2.Selected.Value,Package.Value=Dropdown3.Selected.Value,Language.Value=Dropdown4.Selected.Value,Medium.Value=Dropdown5.Selected.Value)),Modified,Descending). In my case it should work for both.

Please contact me for more info, Thanks in Advance and appreciated !

[MattB_EC]

I have been searching for a few hours on this problem and yours was the first one that worked! Woohoo!

The "true" statement was all I needed thanks!

---

Filter(StudentActivity, If(IsBlank(YearDrop.Selected.Value), true, Year=YearDrop.Selected.Value) && If(IsBlank(TermDrop.Selected.Value), true, Term=TermDrop.Selected.Value) && If(IsBlank(SectionDrop.Selected.Value), true, Section=SectionDrop.Selected.Value) && If(IsBlank(RegionDrop.Selected.Value), true, Region=RegionDrop.Selected.Value) && If(IsBlank(SchoolsDrop.Selected.Value), true, School=SchoolsDrop.Selected.Value) && If(IsBlank(ActivityDrop.Selected.Value), true, Activity=ActivityDrop.Selected.Value))

---