Answered

Filtering gallery by multiple dropdowns AND search box

(0) Share

Report

Posted on by Pseifert

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?

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

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!
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) )

Was this reply helpful? Yes No
Pseifert 42 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Verified answer

Rebeccak 113 on at

Like (2)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Pseifert 42 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Pseifert 42 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Rebeccak 113 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No