cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Pseifert 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
Rebeccak Helper III
Helper III

Re: Filtering gallery by multiple dropdowns AND search box

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

Re: Filtering gallery by multiple dropdowns AND search box

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.
Pseifert Helper II
Helper II

Re: Filtering gallery by multiple dropdowns AND search box

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?

Rebeccak Helper III
Helper III

Re: Filtering gallery by multiple dropdowns AND search box

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

Super User
Super User

Re: Filtering gallery by multiple dropdowns AND search box

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.
Pseifert Helper II
Helper II

Re: Filtering gallery by multiple dropdowns AND search box

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.

Pseifert Helper II
Helper II

Re: Filtering gallery by multiple dropdowns AND search box

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

Rebeccak Helper III
Helper III

Re: Filtering gallery by multiple dropdowns AND search box

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

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (9,562)