Forum Discussion
Send email notification when calculated field value changes
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
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 futureconvertToUtc(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" toYourDateField 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
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 futureconvertToUtc(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" toYourDateField 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,
SvenHello, 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!
Hi SZLK85 ,
the Odata operator for "greater than" is "gt", not "<", so you should use the filterUIS_STATUSZ_HIDDEN gt 1Then I would use the "Create HTML Table" action
The Output will look like this
Best Regards,
Sven
