How to get automatically notified when a cell reaches a predetermined value.

[Paula_RN]

Hi. We have created an app which allows us to track medication expiration dates. We have a column which calculates the number of days between the "today" and "expiration date" columns and would like to get a reminder email when that number is <= to 120. Any ideas on how to do this?

[#2 · Accepted solution]

@Paula_RN the flow below does this for you. I've shown it in the classic designer as I still prefer that to the modern one.

In my SharePoint list the Days column is a number column and it will get updated at 2am each day with the new number of days to the the expiration date.

Flow

1. In Power Automate the trigger for the flow is a recurrence schedule set to run on working days (Monday to Friday) at 2am.

2. Next add a compose action to calculate 120 days ahead from today's date. This uses the expression

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

3. Next, add a SharePoint get items action and add a filter query of ExpirationDate le 'the outputs of the previous compose'. le stands for less than or equal to and the outputs must be wrapped in single quotes. This will just get those items with an expiration date in the next 120 days and will ignore the others.

4. Add a Select control. From the dynamic content box select value from the dynamic content box and then map the columns you want in your email table, adding in the column title then selecting the appropriate dynamic content.

Next, add a create html table action. Select the outputs from the select compose above and set the doors to automatic & cross-check, sorry the columns to automatic.

5. Add another compose action  which I've renamed to Compose Formatted Table. With the create html action there is no styling applied so it doesn't look too good. You can see the CSS I've used in the spoiler below for you to copy & paste.

Spoiler

<style>
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 80%;
text-align: left;
border-collapse: collapse;
}
table td, table th {
border: 1px solid #AAAAAA;
padding: 3px 2px;
}
table tbody td {
font-size: 13px;
}
table thead {
background: #1C6EA4;
border-bottom: 2px solid #444444;
}
table thead th {
font-size: 15px;
font-weight: bold;
color: #FFFFFF;
border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
border-left: none;
}
</style>
@{body('Create_HTML_table')}

At the bottom of the css select the outputs of the create html table action:

6. Next, add a send an email (v2) action and for the table select the outputs of the compose formatted table.

7. Next, add another compose to get the current date. This uses the expression

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

Then add another get items action but either don't put anything in the filter query field or something Title ne null. This will bring back all the items from the list (if you have more than 11 you'll need to go to settings, turn on pagination and set the threshold to a higher number).

8. And an apply to each control and select value from the get items 2 section of the dynamic content box.

Then add a compose to get the expiration date of each item.

And then add another compose action to calculate the days between today and the expiration date. This uses the expression

div(sub(ticks(outputs('ComposeExpiration')),ticks(formatDateTime(utcNow(), 'yyyy-MM-dd'))),864000000000)

9. Finally add an update item action to set the Days column to the outputs of the previous compose.

When the flow runs it will send the following email combining all the items expiring within 120 days in one html table.

The first screenshot of the SharePoint list was taken yesterday. The flow then ran overnight and this is the result this morning with the same email but the Days column reduced by 1.

I hope that helps.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

[#3]

@Rob_Elliott 

I'm helping Paula with this, using your steps, we are getting an issue with the first Get Items. Any ideas on how to solve this? 

[#4]

@Brandon_RN Seems like you just copy pasted the expression as "text" inside the compose action. You have to add it as an expression in the compose action (first compose action after recurrence trigger): 

Also, you can turn OFF the new designer and use old/classic designer as it will be easier to follow the steps given by @Rob_Elliott: 

Make sure to save your flow before turning OFF the new designer.

---

Please consider giving a Like if my post helped you in any way.

[#5]

@ganeshsanap 

Great! Thank you! This is what happens when you try to do multiple things at the same time. The error was on the Get Items portion, so I was focused on that :).