Send reminder based on a CALCULATED date in Document Library

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

[tom_riha · Accepted solution]

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

[caseyg80]

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!

[caseyg80]

[tom_riha]

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.

[pakalolo13]

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

[en2365]

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?

[vdwallo]

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.