Answered

Send reminder based on a CALCULATED date in Document Library

(0) Share

Report

Posted on by Timbo

I need to send a reminder based on a date from a CALCULATED column in a Document Library. The flow works if I use a Date formatted column. But we need a reminder based on a calculated date from another date column. This is the flow error when I use the needed calculated column.

"status": 400,

"message": "The field 'Expiration_x0020_Calculated' of type 'Calculated' cannot be used in the query filter expression.

I can overcome this issue if there is a way to convert the calculated data (Expiration Calculated) to text values into date format into another column (Expiration Value). Then I can just hide the extra column from the view.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report
Hello @Timbo ,

you can't use Filter Query on a calculated column, but you can move the whole calculation into Power Automate. The calculated column is the Agreement Date + Duration Value, the same calculation can be done with the addDays(...) expression.

addDays([AgreementDate],[DurationValue]) Note: replace both the values with the dynamic content representing the columns

similarly, if you want to check if today is the expiration date based on the AgreementDate:

AgreementDate eq 'addDays(utcNow(),-[DurationValue])' Note: replace [DurationValue] with the dynamic content representing the column

Was this reply helpful? Yes No
caseyg80 4 on at

Like (0)

Report

Tom,
I have a similar issue and I cannot work around with your posted topic here - mainly because I am not given dynamic content options (I think). Can you help me as well? Thanks!

Was this reply helpful? Yes No
caseyg80 4 on at

Like (0)

Report

Was this reply helpful? Yes No
tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report

Hello @caseyg80 ,

the easiest solution would be to store the dynamic content in a variable or in a 'Compose' action before the 'Get items', those will be available all the time.

Was this reply helpful? Yes No
pakalolo13 66 on at

Like (0)

Report

@tom_riha or @Timbo , could you share a screen shot of the flow. This seems extremely simple to me and i created the flow but i get an error msg every time i run it.

The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/DueDate' is required to be of type 'String/date'. The runtime value '"adddays(2020-01-10,365)"' to be converted doesn't have the expected format 'String/date'.

Was this reply helpful? Yes No
en2365 2 on at

Like (0)

Report

Hello @tom_riha,
You can't put the compose before because the Dynamic content won't include the calculated field as an option, instead will only the variables for the reminder flow. In the @caseyg80 example: varreminder and varnumday). I have the same issue and I can't understand where to enter your solution. Also tried formatdate and it is impossible to understand the syntax.
You proposed:

My flow:
My calculated column field is TicketDueDate (Date of Request + 5 ) - I need to send reminders if due date is past 1 day. How and where can I insert the proposed work around?

Was this reply helpful? Yes No
vdwallo 6 on at

Like (0)

Report

Hi there,

I am trying to do a 7 day and 1 day reminder. From the below I understand that I can't do this off a calculated column, however I don't understand your example of were to put the query?

Situation - The calculated column referenced is 6months from the start date (date column), I need to send reminders to managers 7 days and 1 day prior that the employees 6 month anniversary is coming up.

Can you please assist or advise where I need to add this. Below is my scheduled workflow so far - hopefully I am not to far off track.

Was this reply helpful? Yes No