“The Power Automate flow works if I use a date formatted SharePoint column, but I need a reminder based on a calculated date from another date column.”
“The field ‘CalculatedDate’ of type ‘Calculated’ cannot be used in the query filter expression.”
When you calculate a date or a number of days in SharePoint, you might not want to end there. Sure, it’s nice to display the date/number of days in the list view, but you can take it one step further. You can build a Power Automate flow that would send a reminder on that date or after that number of days.
But once you try to use that calculated column in a Filter Query, Power Automate won’t like it. “The field ‘CalculatedDate’ of type ‘Calculated’ cannot be used in the query filter expression.” You can’t use calculated columns in the OData filter query. But you can rebuild the whole calculation into an expression, and use that expression in the Filter Query.
Building the Filter Query
You should take one step back and look on the formula in the calculated column. What do you calculate? Is it a number of days from a date until today? Or do you add time period to a date? Which existing date column you use?
Especially the last question is important. When building a Filter Query in Power Automate, you need some ‘fixed’ points. One of them is the ‘base’ date column as that’s a column you can use. The second is today’s date provided by the utcNow() expression (as you’ll check if today is the ‘reminder’ day). Every Filter Query must be build around them.
Number of days from a date
Let’s take the first situation, you want to send a reminder after a number of days since SharePoint item was created, e.g. 7 days. You might use the JSON column formatting to calculate the number of days in the list view with the following formula:
=floor((Number(@now)-Number([$Created]))/(1000*60*60*24))The full formula is explained in the post linked above, for this post it can be a bit simplified. @now is JSON placeholder representing today’s date:
= today - CreatedSince you want to send the reminder on the 7th day, you can add 7 to the equation:
7 = today - CreatedAs already said, the filter must use the ‘base’ date, an existing date column. In this situation it’s the Created date that must be moved to the left side of the equation. That will give us the Filter Query to use:
Created = today - 7
Created eq 'addToTime(utcNow(),-7,'day','yyyy-MM-dd')'
Note: you must always use the column internal name on the left side of the filter.
Adding a time period to a date
The other situation is when you calculate a date in the calculated column by adding a time period. For example, you add 1 month to a Date date column and you want to send a reminder on that date. Let’s start here from the simplified formula:
= Date + 1 monthYou want to send the reminder only if today is the date:
today = Date + 1 monthAgain, the Filter Query must be based on a date column. Here we’ve got the column Date and it must be moved to the left side of the equation:
Date = today - 1 month
Date eq 'addToTime(utcNow(),-1,'month','yyyy-MM-dd')'
Summary
Power Automate doesn’t allow you to use SharePoint calculated columns in the Filter Query, but that doesn’t mean that you can’t “use them” for a reminder. Calculated column is just a formula using other columns, and as such you can replicate it from scratch in Power Automate.
This post is about replacing such calculated date column in the Filter Query with a ‘base’ date column and an expression. It is the main part of Power Automate reminder process, once you can filter the relevant items, you can send the reminder.
If you’d like to see the full reminder flow, there’re already two posts on this topic. The first one is sending reminders for Date only columns and it uses similar Filter Query as described above: how to build basic reminder flow. You can also build a bit more complex flow to send multiple reminders for various time periods.
The second reminder flow is designed for Date and Time columns. With Date and Time columns you can’t use the ‘equals date’ operator as the time will be rarely equal. You must work with a range ‘less than date’ and ‘greater than date’ as you can see in the event reminder flow.
And there’s also a post where you can learn more about the addToTime(…) expression I used.
Hi Tom,
Thank you for your interesting and useful article.
I have a question for you: is it possible to create a reminder flow which should be sent only to the approvers who did not take any action?
Thank you
Claudia
Hello Claudia,
if it’s an approval task with multiple ‘Assigned to’ users then unfortunately Power Automate won’t give you the interim results, it’ll give you all the information only after all users approve. It’s not possible to send a reminder only to the users who didn’t complete their task.
Thank you for your useful article.
I follow the steps descripted in the article to build the flow, the flow runs successfully, but I can’t receive the reminder email. Do you have any ideas for that?
Hello Anne,
it’s hard to analyse without more information, try to check this older article if you’ve got the right date format, if your SP columns doesn’t include time, etc.
How to filter “get items” that is on the same month/year as current date.
Example the Created date column value is 09/15/2021 or 09/01/2021 and current date is 09/29/2021.
If the Created date column value is 08/25/2021 then if won’t be included because it is not the same month/year.
Hello RF,
take a look on the new post: https://tomriha.com/filter-sharepoint-items-with-date-in-the-same-month-in-power-automate/
I am a little dense when it comes to building queries. I have a scenario in which I want an email reminder sent to the “person assigned” 90 days prior to the due date. There’s no templates for due date reminders, and I don’t understand how to code. I wasn’t even sure I was using the right logic. I created a flow, figured out how to select my sharepoint site and list, but then it get’s pretty confusing for me. I’m good at creating sharepoint lists and sites, but I don’t understand how to integrate any of the power apps to really make it efficient. If anyone could help, I would greatly appreciate it.
Hello Paula,
if it’s 90 days before some Due date then you must base the Filter Query on the due date as described in the post:
DueDateInternalName eq ‘addDays(utcNow(),90,’yyyy-MM-dd’)’
Hi Tom,
I have followed the flow to the T and taken on board your comment to Paula above in relation to the Filter Query which is as follows:
Next_x0020_Review_x0020_Date eq ‘addDays(utcNow(),7,’dd-MM-yyyy’)’
However I am still getting the following error:
The expression “Next_x0020_Review_x0020_Date eq ’23-09-2022” is not valid. Creating query failed.
clientRequestId: f3305f1a-cdd6-4c37-a3f2-3b02b0562f65
serviceRequestId: f3305f1a-cdd6-4c37-a3f2-3b02b0562f65
I have taken the name of the calculated column from the field name in the list settings. Do you have any idea as to where I may be going wrong… Many thanks Robin
Hello Robin,
based on the error message there’s a missing single quote after the date, it has a quote only at the beginning.
But I think there’re two more problems:
– you can’t use calculated column in the Filter Query, you must move the calculation into the flow as explained in the article, the Filter query must be always based on a column that contains hard data
– you should also use the ISO formatted date in the Filter Query: yyyy-MM-dd, otherwise it won’t be evaluated correctly as SharePoint stores all dates in ISO format
Hi Tom, hope all is well… I fully appreciate your comment about not being able to use a Calculated Column in a query and that the Filter query must be always based on a column that contains hard data, however the issue I am having is that the query I am trying to create requires the following conditions:
IF Review Date is older than 90 Days and the residual Risk Score 16 or over, then send email reminder
or
IF Review Date is older than 180 Days and the residual Risk Score is between 8-15, then send email reminder
or
IF Review Date is older than 360 Days and the residual Risk Score is 7 or less, then send email reminder
Is this at all possible or am I trying to bite of more than I can chew being that I am relatively new to Power Automate.. Any advice/guidance would be greatly appreciated.
Kind regards
Robin
Hello Robin,
it’s doable using a Filter Query, even though it’ll be a more complicated one. Just build it step by step, condition by condition, and keep testing them. Then connect each part with the AND or OR operator, and put them into brackets in the parts that are related to each other.