cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Moe69
Resolver I
Resolver I

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

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

@Moe69 

We cross-posted!!

 

Try the formula I just sent and see what the results of that are.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

11 REPLIES 11
RandyHayes
Super User
Super User

@Moe69 

What is your formula that you are having problems with?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Moe69
Resolver I
Resolver I

@RandyHayes 

Hello Randy Hayes,

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

Moe69_3-1647617140162.png

 

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

See below screen shot.

 

Moe69_4-1647617485311.png

 

any help or advise please?

 

 

 

 

 

 

 

Moe69
Resolver I
Resolver I

@RandyHayes 

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

 

Moe69_0-1647619196537.png

 

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.

 

 

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
Resolver I
Resolver I

@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   

 

Moe69_0-1647852495705.png

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 )
)

 

Moe69_1-1647853816844.png

 

@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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Moe69
Resolver I
Resolver I

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_0-1647875040524.png

 

@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
Super User
Super User

@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)
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,736)