Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

How to use a calculated column in Filter Query in Power Automate

(2) ShareShare
ReportReport
Posted on by 33
Hi Experts,
 
I'm struggling to build a filter to recognise the values in the calculated column in SharePoint.
 
That is the error message.
"Action 'Get_files_(properties_only)' failed: The field 'OverDueReminder' of type 'Calculated' cannot be used in the query filter expression"
 
It is a simple flow: it reads the ReviewStatus column and OverDueReminder Column's values, then updates ReviewStatus  to a set status when the condition is meant, then fires off an email to remind the process owner to check the docs.
 
OverDueReminder Column is a calculated column the depends on the Next Review Due Date column's values then add 1 day (formular for that is =[Next Review Due Date]+1).
 
The Next Review Due Date triggers another automated email when the Next Review Due Date entry is due in the next 30 days. The logic behind the second reminder  for the admins to monitor any docs whose status is not changed after 31 days.
 
 
My filter is below.
 
ReviewStatus eq 'Upcoming' and OverDueReminder le '@{addDays(utcNow(),31,'yyyy-MM-dd')}T23:59:59' and OverDueReminder ge '@{addDays(utcNow(),31,'yyyy-MM-dd')}T00:00:00'.
 
Those are my columns.
 
Any suggestions are welcome.
 
Thank you.
 
 
 
  • Suggested answer
    abc 123 Profile Picture
    abc 123 724 on at
    How to use a calculated column in Filter Query in Power Automate
    Store the calculated dates into a variable(s), and then use the variable(s) in the Filter Query. 
     
    There's an Action called Add Days that will do the date math. 
  • Galazus Profile Picture
    Galazus 33 on at
    How to use a calculated column in Filter Query in Power Automate
    @FLMike,
    Power automate is newish to me, so I'm trying to get more advanced in building flows.
     
    Do you have an example or a similar post here I can follow? Especially that line you stated " add a Filter Array action and using your output from the previous (get items) value/body".
     
    I can use get items with a filter for that column(Next Review Due Date), that returns any files due for review 30 days from today.
     
    However, how can I use the Filter array action to pick up the files that will be overdue for review if their ReviewStatus is and stuck on "Upcoming" and OverDueReminder (calculated column) value is 31 days from today.
     
    Note:
    I have another flow that is working ok for this process, but that is only firing an email when the Review status is equal to Live and (Next Review Due Date) column's entry is equal to 30 days from today, then changes the Review status to Upcoming.
     
    Later the document owner has to manually change the Review Status to "InProgress" when they start working on the file.
     
    If the doc owner/process owner has not actioned the file by changing the review status to "InProgress", we want to send a reminder and change the ReviewStatus to OverDue 1 days after the (Next Review Due Date) has passed, hence the idea of using a calculated column which turns out the filters don't like.
     
    The rest of the flow is straight forward, but I just can't get that logic to work via a filter conditions that uses a calculated column.
     
    Thank you.
     
     
  • FLMike Profile Picture
    FLMike 30,829 on at
    How to use a calculated column in Filter Query in Power Automate
    Hi,
     
    You cannot use Calculated Columns in a Filter Query, it just isn't supported.
     
    You would have to Filter based on what you CAN. Then you would add a Filter Array action and using your output from the previous (get items) value/body
    as the input
     
    Then in the filter click the little mapper and write the filter query you want against the returned rows.
    Then you will have what you want.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard