Is it possible to use two dropdowns, and a search box as filters?

[Moe69]

Hello all,

I've watched many videos on filters and I'm still no better or wiser regarding using two dropdowns, and a search box.

I'm using the dropdown created by @RandyHayes using the ALL function, it works great when using one Dropdown but when i add a second one another category , it fails. 

I would like to use two dropdowns, and a search box if possible, this will give me and other users a wider filtering options. 

I'm using SharePoint as Data Source, and the columns being used are of choice selection and numbers between 1 to 52.

Any advise or help out there who help me with my question please?

Thanking you all in advance.

[RandyHayes]

@Moe69 

What is your formula that you are having problems with?

[Moe69]

@RandyHayes 

Hello Randy Hayes,

As per below screen shot, the gallery Items works fine with one dropdown when using choice selection control.

However, when i add the second Dropdown which is using numbers, i get thing to filter on!

See below screen shot.

any help or advise please?

[Moe69]

@RandyHayes 

Would my app be able to use the two dropdowns, and a search box combination as filters? 

I've seen other videos using these combination so where have i gone wrong here, please do understand, I'm new to Power Apps, so any advise or directions to how i can either rectify or where i can find solutions to how to fix please.

[Ethan_R]

Hi @Moe69 ,

Try formulating your Filter code as,

Filter
(
   'Access Change Request Form',
   ( IsBlank(RequestStatusDropdown.Selected) || RequestStatusDropdown.Selected.Value = "All" || RequestStatusDropdown.Selected.Value = <'RequestStatus Column Name (Access Change Request Form)'.'Work For'>)
   ||
   ( IsBlank(RequestTypeDropdown.Selected) || RequestTypeDropdown.Selected.Value = "Week Number" || RequestTypeDropdown.Selected.Value = <'RequestType Column Name (Access Change Request Form)'.'Week Number'>)
   ||
   ( IsBlank(SearchBox.Text) || SearchBox.Text in <ColumnName> )
)

There is changes in the code, since I don't know the column names and choices name.

Modify as per your understanding.

Note: Its better to use below comparison for choices in the query than hard-coded text as "All"

'RequestStatus Column Name (Access Change Request Form)'.'Work For'

Here, within your single quotes resides column or choice name related to data-source/table name in brackets

' Field Name (Data-Source) '

Hope this helps 

[Moe69]

@Ethan_R 

Good morning,

Thank you for you for your response, very much appreciated.

Apologies in advance, still learning the Power Apps ropes, and my knowledge within this area is is bad. 

I have tried your suggestion but still getting errors and now I'm confused. i have highlighted in red the areas where I'm having trouble with and also listed the formula below. 

You mentioned within your single quotes resides column or choice name related to data-source/table name in brackets  ' Field Name (Data-Source) '  Would you be able to elaborate a bit more please?

I'm very close and don't want to give up   

Filter
(
'Access Change Request Form',
( IsBlank(RequestStatusDropdown.Selected) || RequestStatusDropdown.Selected.Value = "All" || RequestStatusDropdown.Selected.Value = 'Work For')
||
( IsBlank(RequestTypeDropdown.Selected) || RequestTypeDropdown.Selected.Value = "Week Number" || RequestTypeDropdown.Selected.Value = 'Week Number')
||
( IsBlank(TextSearchBox1.Text) || TextSearchBox1.Text in Title )
)

[RandyHayes]

@Moe69 

The problem in your formula is that you are not providing any level of precedence. 

Since there are no parens around the OR and AND parts of the formula, how would PowerApps know how to evaluate the proper condition you want?

Filter('Access Change Request Form',
    (RequestStatusDropdown.Selected.Value="All" || 'Work For'.Value = RequestStatusDropdown.Selected.Value) &&
    (RequestTypeDropdown.Selected.Value="All" || 'Week Number' = RequestTypeDropdown.Selected.Value)
)

In the above the OR of the two conditions is evaluated first and the results of those are ANDed together for the final result.

[Moe69]

To Whom who can help or assist me please,

i'm very close and near to getting the filtering to work correctly, but still not able to get the filtering combination to work correctly using the dropdown and a search text box combination.

Below is the formula, could someone kindly tell me where i'm going wrong here please? i have received feedback  on this but found it a bit confusing due to my limited understanding. just need to get all 3 to work when one is selected and further filtering can be conducted. At the moment only one filter works. I would like if i select the Week Number, then the other dropdown box Work For should be filtered which then would allow me to select from the filtered week or vice versa   

thanking you in advance, your help, advise is very much appreciated. 

SortByColumns(
Filter(
'Access Change Request Form',
StartsWith(
Title,
TextSearchBox1.Text
) && WeekNumberDropdown.Selected.Value = "All" || 'Work For'.Value = WeekNumberDropdown.Selected.Value And WorkForDropdown.Selected.Value = "All" || Title = WorkForDropdown.Selected.Value
),
"ID",
If(
SortDescending1,
Descending,
Ascending

)
)

Screen shot of the filters in my gallery

[Moe69]

@RandyHayes 

Hey RandyHayes,

Thank you for responding back, very much appreciated your time spent on my formula issue.

taken on board your positive feedback and will take on board going forward.

However, but the formula you wrote didn't work! i'm not able to filter either dropdowns. it all works on one filter when selecting All but not when i select anything else.

is this right?

[RandyHayes]

@Moe69 

Did you not read my last post??

You are not specifying the precedence of your AND's and OR's in your formula.  So PowerApps has no way of knowing when to OR two things together and then AND the results.  It will just go through and evaluate based on position in the formula - which will not yield the results you want.

Your formula should be: 

SortByColumns(
    Filter('Access Change Request Form',
        StartsWith(Title, TextSearchBox1.Text) && 
        (WeekNumberDropdown.Selected.Value = "All" || 'Work For'.Value = WeekNumberDropdown.Selected.Value) &&
        (WorkForDropdown.Selected.Value = "All" || Title = WorkForDropdown.Selected.Value)
    ),
    "ID",
    If(SortDescending1, Descending, Ascending)
)