cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkHohn1
Level: Powered On

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
MarkHohn1
Level: Powered On

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
TML
Level 8

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
MarkHohn1
Level: Powered On

Re: Filter for projects not closed

That gives "Expected an operator" error

HolmesLL
Level: Powered On

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. 

Community Support Team
Community Support Team

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.
MarkHohn1
Level: Powered On

Re: Filter for projects not closed

Thanks but Job_Status.Selected.Value did not work.

MarkHohn1
Level: Powered On

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.

HolmesLL
Level: Powered On

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

 

MarkHohn1
Level: Powered On

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

MarkHohn1
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,918)