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

Filter for projects not closed

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Filter for projects not closed

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

View solution in original post

9 REPLIES 9
Highlighted
Advocate II
Advocate II

Re: Filter for projects not closed

Try != instead.

 

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

 

Highlighted
Frequent Visitor

Re: Filter for projects not closed

That gives "Expected an operator" error

Highlighted
Frequent Visitor

Re: Filter for projects not closed

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

Highlighted
Community Support
Community Support

Re: Filter for projects not closed

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

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Filter for projects not closed

Thanks but Job_Status.Selected.Value did not work.

Highlighted
Frequent Visitor

Re: Filter for projects not closed

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.

Highlighted
Frequent Visitor

Re: Filter for projects not closed

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

 

Highlighted
Frequent Visitor

Re: Filter for projects not closed

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

Highlighted
Frequent Visitor

Re: Filter for projects not closed

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

View solution in original post

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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