Send email notification to user X days before a calculated date

[mansi27]

Hi,

We have a sharepoint list with a calculated date column "Next Inspection Date". This column values are derived by adding 6 months to another column "Last Inspection Date" which is filled by the user. We are trying to automate and add an email notification that is sent 14 days before the calculated column date in "Next Inspection Date".

I am new to power automate and would love any help in terms of how to design the flow. I have tried using the inbuilt flow from sharepoint list but since the column I am trying to use is a calculated column it gives me an error.

Thanks for your help in advance 🙂

Mansi

[RobElliott]

@mansi27 although you have a calculated column in your list this can't be used in the filter query so you need to do the calculation again in the flow.

So in this example there are 2 items where today (Sunday 2nd July) is 14 days before the next review date:

Create a new instant cloud flow from blank and the trigger should be a recurrence schedule set to 1 day and select your timezone and the time you want the flow to run each day. Next add a compose action, click in the field and from the expression tab of the dynamic content box enter

formatDateTime(utcNow(),'yyyy-MM-dd')

- when doing date comparisons it is best to use the yyyy-MM-dd format. I've renamed the compose to Today.

Next. add a SharePoint get items action. I've entered LastReview ne null so it only brings back items where there is a date in the LastReview column (ne = not equal to).

Next add an apply to each and select value from the dynamic content box. All the following actions will be inside the apply to each.

Next, add 3 compose actions

 - For the first one select your last review column. I've renamed the Compose to LastReview.

 - For the second one enter the following expression which adds 180 days to the previous LastReview compose and formats it in the yyyy-MM-dd format. I've renamed the compose to Add180.
formatDateTime(addDays(outputs('LastReview'),180), 'yyyy-MM-dd')

- For the third one enter the following expression to take 14 days from the Add180 compose. I've renamed the compose to 14DaysAhead.
formatDateTime(addDays(outputs('Add180'),-14),'yyyy-MM-dd')

Next, add a condition.  For the left field select the outputs of 14DaysAhead and for the right field select the outputs of Today.

Leave the red if no channel empty. For the green if yes channel I've added another compose action to change the format of the date in the email and used the expression 

formatDateTime(outputs('Add180'),'dddd dd MMMM yyyy')

Finally add the send an email (v2) action.

Rename, save and test your flow. As you can see below the correct 2 emails were sent:

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.