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

Filtering gallery by multiple dropdowns AND search box

Hello,

 

I am attempting to filter a gallery sharepoint list by numerous drop downs and a search box. I needed to add an option in the filter for "ALL" values. I would like these to work simultaneuously but I am unable to get a single drop down to filter correctly. Here is the furthest i have gotten but am receiving delgation warnings.

 

Filter(Project, If((phasedrop.Selected.Value="all"), true, Phase.Value = phasedrop.Selected.Value))

 

The gallery filters by the other selections but when all is selected, the gallery is blank. How can I pair each of these if statements together to display ALL if the selection is made?

 

Capture.JPG

 

EDIT!!: If((phasedrop.Selected.Value = "All"), Project, Filter(Project, Phase.Value = phasedrop.Selected.Value))

 

I have been able to get all to filter but I need to combine these if statements so that the drop down boxes work together

1 ACCEPTED SOLUTION

Accepted Solutions
Helper III
Helper III

Try Creating Collections for each drpDown.You wll have to add text columns for it to work with the drop down columns in sharepoint. Then create a collection for each field you will be using in a dropdown. First use this below for Onstart or onvisible of the screen:

ClearCollect(
colProject,
AddColumns('DataSource',"PhaseTxT",Phase.Value,"PlantTxt",Plant.Value,"ProjectTypeTxt",ProjectType.Value));
ClearCollect( colPhase, Distinct(colProjects,PhaseTxt)); Collect(colPhase,{Result:"* All"});
ClearCollect(
colPlant,
Distinct(colProjects,PlantTxt));
Collect(colPlant,{Result:"* All"});
ClearCollect(
colProjectType,
Distinct(colProjects,ProjectTypeTxt)):
Collect(colProjectType,{Result:"* All"});
ClearCollect(
colFilterProjects,colProjects);
Reset(drpPhase);Reset(drpPlant);Reset(drpProjectType)

 
 

 

 

Next use this for the onchange for each of your drpdown controls (They should all contain the same command):

 

ClearCollect(
    colFilterProjects,
    Filter(colProjects,
   PhaseTxt = drpPhase.Selected.Result ||drpPhase.Selected.Result = "* All",
PlantTxt = drpPlant.Selected.Result ||drpPlant.Selected.Result = "* All",
ProjectTypeTxt = drpProjectType.Selected.Result || drpProjectType.Selected.Result = "* All"))




 

Then for the gallery. items = colFilterProjects

 

And Dropdown Items:

 

drpPhase.items= Sort(colPhase,Result)

drpPlant.Items - Sort(colPlant,Result)

etc...

 

 

Hope this helps. 

View solution in original post

7 REPLIES 7
Super User
Super User

Try something like this to get all your filters working together:

 

Filter(Project, 
	If(quarterdrop.Selected.Value = "All", true, Quarter.Value = quarterdrop.Selected.Value),
	If(plantdrop.Selected.Value = "All", true, Plant.Value = plantdrop.Selected.Value),
	If(phasedrop.Selected.Value = "All", true, Phase.Value = phasedrop.Selected.Value),
	If(typedrop.Selected.Value = "All", true, Type.Value = typedrop.Selected.Value),
	If(statusdrop.Selected.Value = "All", true, Status.Value = statusdrop.Selected.Value)
)


--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

Hi Jeff,

 

Thank you for the reply. I was able to get that working, but even with 5-6 records there, I was able to find the delegation issue upon running through the filters. What would be a good way to correct so that delegation warnings go away?

Helper III
Helper III

Try Creating Collections for each drpDown.You wll have to add text columns for it to work with the drop down columns in sharepoint. Then create a collection for each field you will be using in a dropdown. First use this below for Onstart or onvisible of the screen:

ClearCollect(
colProject,
AddColumns('DataSource',"PhaseTxT",Phase.Value,"PlantTxt",Plant.Value,"ProjectTypeTxt",ProjectType.Value));
ClearCollect( colPhase, Distinct(colProjects,PhaseTxt)); Collect(colPhase,{Result:"* All"});
ClearCollect(
colPlant,
Distinct(colProjects,PlantTxt));
Collect(colPlant,{Result:"* All"});
ClearCollect(
colProjectType,
Distinct(colProjects,ProjectTypeTxt)):
Collect(colProjectType,{Result:"* All"});
ClearCollect(
colFilterProjects,colProjects);
Reset(drpPhase);Reset(drpPlant);Reset(drpProjectType)

 
 

 

 

Next use this for the onchange for each of your drpdown controls (They should all contain the same command):

 

ClearCollect(
    colFilterProjects,
    Filter(colProjects,
   PhaseTxt = drpPhase.Selected.Result ||drpPhase.Selected.Result = "* All",
PlantTxt = drpPlant.Selected.Result ||drpPlant.Selected.Result = "* All",
ProjectTypeTxt = drpProjectType.Selected.Result || drpProjectType.Selected.Result = "* All"))




 

Then for the gallery. items = colFilterProjects

 

And Dropdown Items:

 

drpPhase.items= Sort(colPhase,Result)

drpPlant.Items - Sort(colPlant,Result)

etc...

 

 

Hope this helps. 

View solution in original post

The delegation warning is happening because the fields in SharePoint are choice or lookups and those complex field types are not delegable. If your list is under 2,000 then you could increase the default non-delegable row limit from 500 to 2,000 (max size).

 

If this is a new site and you are going to be using the PowerApps custom forms instead of the standard SharePoint form then you could redesign your form so that it uses text fields instead of Choice/Lookups. Then in the PowerApps custom form you would use a combo box or drop box control instead of the text field. To the end user it would work the same but since under the covers it is a text field you won't be getting those delegation warning.



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

This one is working, my only issue now is that I can not select ALL for some reason. It does not allow me to revert the filter back to "*ALL" it only allows me to select the items that were collected for each drop down. Any clue how I can resolve? I always want to be able to revert back to the full list without backing out and entering the screen again.

Thank you both very much for the replies, I have never utilized clearcollect prior to now and see massive benefits with it. Thank you!!

you can always put the clear collect code on a button on select to reset, or a refresh onselect.

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Kudoed Authors
Users online (71,795)