Send email notification when calculated field value changes

[SZLK85]

Hello,

I have a Sharepoint list with a Date field and a Calculated field.

The Calculated field calculates the difference between the Date field and NOW() (in days).

So when this calculation result will be less than 30, I should send an email to specified users.

Can you help me please how can I do this in Sharepoint (Office 365)?

(I am beginner with automation, this will be my first task)

Thank you very much for help!

BR, SZLK

[#2 · Accepted solution]

Hi @SZLK85 

a calculated column in SharePoint does only update if you update the item itself. If you want to dynamically display the remaining days then use this formatting sheet on your date column

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))+' day(s) remaining'"
}

You can create a new scheduled flow that runs every day at a certain time.
Then calculate in a variable "30DaysIntheFuture" the date 30 days in the future

convertToUtc(startOfDay(addDays(convertTimeZone(utcNow(),'UTC','W. Europe Standard Time'),30)),'W. Europe Standard Time')

Now use a "Get items" from SharePoint action for your list and set the "Filter Query" to

YourDateField eq datetime'@{variables('30DaysIntheFuture')}'

As a result you have all the items where the value of your date column is 30 days in the future.
Now send a Mail for each result.

This works if you just have a "Date" field. If you did include the time in your column, then we need to create two variables (One 30 in the future and one 31 days in the future) and check if the column value ist between these two dates.

Best Regards,
Sven

[#3]

@SvenSieverding 

Hello, Thank you very much for help! Based on your post I try simplify my life and I created a new field to list (UIS_STATUSZ_HIDDEN). When this field value > 1, then I should send email to users.

This email should contains a list of rows that is result of the filter criteria. 

If my steps seems good, can you help me please what should I write to the body part of the email.

So my email body should be:

Filtered items list (UIS_STATUSZ_HIDDEN > 1):

 - Item Name (Row 1 Name field value), Item Description (Row 1 Description field value)

 - Item Name (Row 2 Name field value), Item Description (Row 2 Description field value)

 - Item Name (Row 3 Name field value), Item Description (Row 3 Description field value)

etc...

Thank you for help!

[#4]

Hi @SZLK85 ,

the Odata operator for "greater than" is "gt", not "<", so you should use the filter

UIS_STATUSZ_HIDDEN gt 1

Then I would use the "Create HTML Table" action



The Output will look like this



Best Regards,
Sven

[#5]

@SvenSieverding 

Thank you, its working!

Have a nice day!