Filter for projects not closed

[MarkHohn1]

I am trying to use PA to display and edit items from a sharepoint list. 

This sort works partially and returns some closed projects but not all.

SortByColumns(Filter(Project_Number_Log, StartsWith(Title, TextSearchBox1.Text)&&(Job_Status.Value="Closed")), "Title", If(SortDescending1,Ascending, Descending))

I am unable to get results for projects that ar not closed. 

<> brings back some not closed projects

! does not work

"not" does not work

How do I bring back all projects with a Job_Status of anything but closed. 

Job_Status in list is choice field

Mark

[TML]

Try != instead.

EDIT: Oh sorry, I didn't see that you already posted the ! didn't work.  I have used != before with success.

[MarkHohn1]

That gives "Expected an operator" error

[HolmesLL]

"Job_Status in list is choice field"   Is it a dropdown in Powerapps, if yes, it could be Job_Status.Selected.Value. 

[v-micsh-msft]

Hi @MarkHohn1,

Could you please share more details for the Job_Status field, specially for the value list?

Currently only '=' is supported for delegation when working with SharePoint list in PowerApps, see the article below for more details:

Delegable data sources

To workaround your scenario, please add a dropdown control (here we call it Dropdown1), then specify the value list of the Job_Status under the items property:

["Closed","Open","Available",.....]

Then specify the Gallery formula as below:

SortByColumns(Filter(Project_Number_Log, 
                     StartsWith(Title, TextSearchBox1.Text),
                     (Job_Status.Value=Dropdown1.Selected.value)), 
              "Title", 
              If(SortDescending1,Ascending, Descending)
            )

Doing it in this way, you should be able to filter the wanted value.

Regards,

Michael

[MarkHohn1]

Thanks but Job_Status.Selected.Value did not work.

[MarkHohn1]

List item in Sharepoint is a dropdown with 13 options. 

I am interested in displaying every item in the list that does not have a status of closed. 

I do not want to filter on each status just filter out any item thas has the "Closed" Status.

This is a pretty simple filter that should just work. 

Is this on a roadmap somewhere? 

Basic Filtering should be a priority.

[HolmesLL]

I would suggest to troubleshoot it step by step,

1. Remove other function from the formula, but keep the filter only, ie. Filter(Project_Number_Log, Job_Status.Value="Closed")

2. Then add the NOT condition, Filter(Project_Number_Log, Not(Job_Status.Value="Closed"))

3. Then add the first "StartsWith" Condition, and so on....

[MarkHohn1]

SInce the PA is saved back to Sharepoint as a view, is it possible to use an existing view to query the data in PA. 

I have existing view that does the filter. If I could use the filter it would only bring the data I want to use into PA.

Mark

[MarkHohn1 · Accepted solution]

I got this to work. Several issue I had to work thru.

List was over 500 items.  Cleaned up list and created flow to move archived items to new list.

Job_Status column was a dropdown list in Sharepoint list. Job_Status.Value would not work with sort as Not (!) does not work.

Created new calculated column in Sharepoint JobStatus1. Calculated if Job was closed mark as closed and any other value marked job as open then used this column in the filter.

Basic query that works is:

SortByColumns(Filter(Project_Number_Log, JobStatus1="Open"), "Title", Descending)

Thanks for the help

Mark