Drop-Down Items not Showing with Filter

[Marktvc]

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

[PaulD1]

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.

[Marktvc]

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.

[PaulD1]

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

[Marktvc]

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.

[timl]

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]

Improved

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

[Marktvc · Accepted solution]

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