cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Marktvc
Frequent Visitor

Drop-Down Items not Showing with Filter

Hi All,

 

I have pushed my sharepoint list (Client Jobs) to PowerApps.  I'm looking to filter the BrowseGallery by a Status Drop down.  The current list of options are:

 

10 - New
20 - Approved
30 - Scheduled
40 - In Progress
50 - Completed
99 - Cancelled

 

 

The current code is:

 

SortByColumns(Filter('Client Jobs', StartsWith(Title, TextSearchBox1.Text)),"Title" , If(SortDescending1, Descending, Ascending))

 

I can see this allow filters based on the Search Text box (I would like to keep this functionality) but first exlude data where the Status field is:

 

50 - Completed
99 - Cancelled

 

This should then display jobs with status of

10 - New
20 - Approved
30 - Scheduled
40 - In Progress

1 ACCEPTED SOLUTION

Accepted Solutions

A work around I have found.

I created a caluclualted field in the sharepoint list called active. If status =

10 - New
20 - Approved
30 - Scheduled
40 - In Progress

Then I set the field to 1 else 0

Then using solution provided, tuned it to be filter where Active = 1

SortByColumns(Filter(Filter('Client Jobs',(Active="1")),StartsWith('Job Title',TextSearchBox1.Text)) ,"Title",If(SortDescending1,Descending,Ascending))

View solution in original post

7 REPLIES 7
PaulD1
Super User
Super User

You can try something like the following:

 

SortByColumns(Filter(Filter('Client Jobs', Not("Title" = "50 - Completed" Or "Title" = "99 - Cancelled"   )StartsWith(Title, TextSearchBox1.Text)),"Title" , If(SortDescending1, Descending, Ascending))

 

I.e. filter out code 50 and 99 first, then run your search filter against the result. This will likely give you a delegation warning, so only use if you expect to have fewer than 500 statuses.

Close...

 

The field with status is called Status

 

So I have changed it to be

 

SortByColumns(Filter(Filter('Client Jobs', Not("Status" = "50 - Completed" Or "Title" = "99 - Cancelled" )StartsWith(Title, TextSearchBox1.Text)),"Title" , If(SortDescending1, Descending, Ascending))))

 

how ever still no luck.

Sorry - remove the double quotes around Status and Title within the Not(...) part of the formula.

now is

 

SortByColumns(Filter(Filter('Client Jobs', Not(Status = "50 - Completed" Or "Title" = "99 - Cancelled")StartsWith(Title, TextSearchBox1.Text)),"Title" , If(SortDescending1, Descending, Ascending))))

 

came back woth lots of errors.

 

Result.jpg

Hi @Marktvc 

I think you're just missing the comma before the StartsWith. This might work a bit better.

 

SortByColumns(Filter(Filter('Client Jobs', Not(Status = "50 - Completed" Or Title = "99 - Cancelled")), 
StartsWith(Title, TextSearchBox1.Text)),
"Title" ,
If(SortDescending1, Descending, Ascending) )
Marktvc
Frequent Visitor

Improved

 

no have this error.  The field is a Drop down.  I noted this has some impact from other posts.

 

Result2.jpg

A work around I have found.

I created a caluclualted field in the sharepoint list called active. If status =

10 - New
20 - Approved
30 - Scheduled
40 - In Progress

Then I set the field to 1 else 0

Then using solution provided, tuned it to be filter where Active = 1

SortByColumns(Filter(Filter('Client Jobs',(Active="1")),StartsWith('Job Title',TextSearchBox1.Text)) ,"Title",If(SortDescending1,Descending,Ascending))

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.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

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