Filtering browsegallery by two dropdowns

(0) Share

Report

Posted on by wonka1234

920 Super User 2024 Season 1

Hi,

I am trying to have two filters for my browsegallery but cant seem to get it to work, some records get dropped when im filtering . I want the filters to be combined, ie Filter 1 AND Filter 2 or vice versa will drill down records nicely.

The filters work but when I reset it to either "Filter for Request Status" or "Filter for GCO Lead" records for missing.

Am I doing this correctly?

SortByColumns(
	Filter(
		[@'Data Source'],
		If(
			Dropdown3.Selected.Value = "Filter for Request Status", 
			Status_two.Value = "New Request" Or Status_two.Value = "GCO Assessment" Or Status_two.Value = "Pending SM Review of Date" Or Status_two.Value = "Complete", 
			Status_two.Value = Dropdown3.Selected.Value
		)
 And
 If(
 Filter_for_GCO_Lead.Selected.Value = "Filter for GCO Lead",
 'GCO Lead_three' = "email1@gmail.com" Or 'GCO Lead_three' = "email2@gmail.com",
 'GCO Lead_three' = Filter_for_GCO_Lead.Selected.Value
 )
		

	),
	"Title",
	If(
		SortDescending1,
		SortOrder.Descending,
		SortOrder.Ascending
	)
)

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (2)

Sort by

Verified answer

LaurensM 12,516 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi @wonka1234,

You can avoid the If() statements within your Filter (causing a non-delegable query) by making the following adjustment:

SortByColumns( Filter( [@'Data Source'], (Dropdown3.Selected.Value = "Filter for Request Status" && (Status_two.Value = "New Request" || Status_two.Value = "GCO Assessment" || Status_two.Value = "Pending SM Review of Date" || Status_two.Value = "Complete")) || (Dropdown3.Selected.Value <> "Filter for Request Status" && Status_two.Value = Dropdown3.Selected.Value), (Filter_for_GCO_Lead.Selected.Value = "Filter for GCO Lead" && ('GCO Lead_three' = "email1@gmail.com" || 'GCO Lead_three' = "email2@gmail.com")) || (Filter_for_GCO_Lead.Selected.Value <> "Filter for GCO Lead" && 'GCO Lead_three' = Filter_for_GCO_Lead.Selected.Value) ), "Title", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) )

Additionally, make sure that all available options are in the condition (you have 2 emails for GCO and 4 statusses). When e.g. Filter for Request Status or Filter for GCO Lead is selected, only a few option will be displayed. Make sure those options are written correctly - pay close attention to spaces and capitalization.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
wonka1234 920 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@LaurensM thanks... seems to be working okay, however I am missing two records ...i am trying to verify spelling but everything seems to be okay. Any reason as to why some would be filtered out without me knowing? if I remove these filters I can see them.

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like (1)

Report
Copy link

Link copied!

@wonka1234,

I would expect it to be something related to spelling or a missing option. Do those records have something in common regarding status or GCO Lead? Or are you missing a certain status or GCO Lead in your gallery?

Was this reply helpful? Yes No
wonka1234 920 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@LaurensM

Hi Laurens, wanted to ask how to append another search box to the above statement.
I want to add -

(StartsWith( Control_two.Value, 'search box'.Text ) Or StartsWith( ID_three, 'search box'.Text ))

How can I add this to the above statement? Ive tried it as is and the filters dont really seem to work that well. It is a text input search box i am trying to filter by and also the dropdowns from above.

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like (1)

Report
Copy link

Link copied!
Hi @wonka1234,

I would use nested filtering since the StartsWith functions will make our filter query non-delegable (Delegable StartsWith expects a Text field). Our original code will remain delegable and afterwards we will filter that pre-filtered dataset with our StartsWith functions:

SortByColumns( Filter( Filter( [@'Data Source'], (Dropdown3.Selected.Value = "Filter for Request Status" && (Status_two.Value = "New Request" || Status_two.Value = "GCO Assessment" || Status_two.Value = "Pending SM Review of Date" || Status_two.Value = "Complete")) || (Dropdown3.Selected.Value <> "Filter for Request Status" && Status_two.Value = Dropdown3.Selected.Value), (Filter_for_GCO_Lead.Selected.Value = "Filter for GCO Lead" && ('GCO Lead_three' = "email1@gmail.com" || 'GCO Lead_three' = "email2@gmail.com")) || (Filter_for_GCO_Lead.Selected.Value <> "Filter for GCO Lead" && 'GCO Lead_three' = Filter_for_GCO_Lead.Selected.Value) ), StartsWith(Control_two.Value, 'search box'.Text) || StartsWith(ID_three, 'search box'.Text) ), "Title", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) )

I hope this helps!

Was this reply helpful? Yes No