cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkHohn1
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

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
Advocate II
Advocate II

Try != instead.

 

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

 

MarkHohn1
Frequent Visitor

That gives "Expected an operator" error

HolmesLL
Frequent Visitor

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

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.

Thanks but Job_Status.Selected.Value did not work.

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.

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

 

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (60,823)