cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Doddy83
Helper IV
Helper IV

Filter on calculated date column

Hello all,

 

I have a SP List that contains Corrective Actions, I've been asked to create reminder emails for 30, 15, 3 days and when it's overdue.

 

The problem I'm hitting is the Close_Out_Date column is calculated from the created date, based on the Risk Rating (High = 30 days, Medium = 60 days and Low = 30 days. So I'm getting this error when using it to filter query in Flow:

 

'Close_Out_Date’ of type ‘Calculated’ cannot be used in the query filter expression.” 

 

I've tried replicating the calculated column formula in flow: (example 3 days)

 

Created eq 'addToTime(utcNow(),-3,'days','dd-MM-yyyy')'

 

But this fails with the following:

 

The expression "Created eq 'addToTime(utcNow(),-3,'days','dd-MM-yyyy')'" is not valid. Creating query failed.

 

Is there a way of achieving this? I'm happy to have a separate flow for each reminder, it's just not being able to filter on the calculated column that's causing me issues with what I thought would be something simple to implement!

 

Any help would be much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
ekarim2020
Community Champion
Community Champion

Your query should look something like this:

(Created ge 'addDays(utcNow('yyyy-MM-dd'),-3)') and (Created lt 'addDays(utcNow('yyyy-MM-dd'),-2)')  and (Status eq 'Approved')

 

Does the error occur only when and (Status eq 'Approved') is added to the query?

 

See also advice on Odata filter queries (including using brackets/parenthesis in), see:

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Ellis

View solution in original post

9 REPLIES 9
happyume
Solution Sage
Solution Sage

Have you tried:
Created lt '@{formatDateTime(addDays(utcNow(),-30),'yyyy-MM-dd')}'

happyume_0-1634123885516.png


This will give you items that were created more than 30 days ago, you can combine this with another filter query on the status i.e. whether corrective action is closed or not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If this post or my previous reply was useful in other ways, please consider giving it Thumbs Up.

I've tried this and the filter doesn't error but it's bringing back all the corrective actions where the Created date is less than 3 days. What I'm looking for is for it to email only once on the day that matches the query, not any dates that are less than? Does this make sense? 

So if the created date is 10/10/2021, the filter will pick this up on 07/10/2021 and send the email, but wont send on out on the 8th and 9th.

ekarim2020
Community Champion
Community Champion

You could use a range for the filter query, something like:

addDays(utcNow('yyyy-MM-dd'),-3) and addDays(utcNow('yyyy-MM-dd'),-2)
 

ekarim2020_0-1634130927832.png

The query would thus filter for the following following date/times:

(Created ge '2021-10-10T00:00:00.0000000') and (Created lt '2021-10-11T00:00:00.0000000')

 

Note that the SharePoint Created field includes both date and time:

"Created": "2021-09-14T19:58:40Z"

 

Ellis

 

 

I've tried this, incorporating the status column too but this errors when the flow is ran.

Capture.JPG

addDays(utcNow('yyyy-MM-dd'),-3) and addDays(utcNow('yyyy-MM-dd'),-2)

 

Error:

The expression "(Created ge '2021-10-10T00:00:00.0000000') and (Created It '2021-10-11T00:00:00.0000000') and (Status eq 'Approved')" is not valid. Creating query failed.

 

Sorry, I don't fully understand these queries, this was meant to be something quite simple to implement if I could use my initial formula on the calculated column! 

ekarim2020
Community Champion
Community Champion

Your query should look something like this:

(Created ge 'addDays(utcNow('yyyy-MM-dd'),-3)') and (Created lt 'addDays(utcNow('yyyy-MM-dd'),-2)')  and (Status eq 'Approved')

 

Does the error occur only when and (Status eq 'Approved') is added to the query?

 

See also advice on Odata filter queries (including using brackets/parenthesis in), see:

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Ellis

View solution in original post

ekarim2020
Community Champion
Community Champion

Here are some other options you could use to filter your Get Items results:

2021-10-13_16-51-27.png

2021-10-13_16-55-28.png

 

Hope this helps.

 

Ellis

Really appreciate your efforts so far, I can only apologies for my incompetence here! I'm still getting the same error, it just doesn't include the Status now:

 

The expression "(Created ge '2021-10-10T00:00:00.0000000') and (Created It '2021-10-11T00:00:00.0000000')" is not valid. Creating query failed.

 

OData queries I find very confusing!

ekarim2020
Community Champion
Community Champion

Let's go back to basics and start with just one filter query and test:

ekarim2020_0-1634143328225.png

 

2021-10-13_17-43-29.png

 

Can you also post a screenshot of the Get Items action.

 

Ellis

Thank you for the time you have taken on this. After your last message I stripped it back to just one filter query as you suggested and still got the error, I triple checked that it has the ' in the right places, everything looked ok as it did in the previous attempts.

I decided to re-create the entire flow from scratch and used the below query you provided and this time it worked!

(Created ge 'addDays(utcNow('yyyy-MM-dd'),-3)') and (Created lt 'addDays(utcNow('yyyy-MM-dd'),-2)')  and (Status eq 'Approved')

Many thanks again for your time and patience, there was obviously something in the previous Flow that it didn't like but I'll be dammed if I can figure out what it is! They both look exactly the same to me!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,469)