@Andy_Downs forgive me for saying this but they way you want to do it is not the best approach, not least because the calculated column wouldn't update automatically anyway every day because it's just a static value. You should do it all in the flow.
So in this example we want to send a single email when any of the items are 14 days ahead, i.e 29 April. So that will be just Guadeloupe and Curacao.
In your flow the trigger is a recurrence schedule. Set it to 1 day and in the advanced options set your time zone and the time you want the flow to run every day.
Next, add a Compose action, and click in the Inputs field. From the Expression tab of the dynamic content box enter the expression formatDateTime(addDays(utcNow(), 14),'yyyy-MM-dd') which calculate the date 14 days ahead of today:
Next, add a get items action and select your site and list. Open the advanced options and in the Filter Query field type the column name (in my case Arrival) eq (which stands for equal to) then add 2 single apostrophes '' and inside them select the outputs from the 14 days ahead compose action.
Next, add a Select action and select value from the get items section of the dynamic content box. For the Map fields you need to enter a text value in the left column, and in the right column select the information you want to display which in this case is the Title field and the outputs of the 14 days ahead compose action.
Follow that with a Create HTML action and set the columns field to Automatic.
The default HTML table doesn't have any styling attached to it, so to make the table look better add another Compose action, paste in the CSS from the spoiler below and then at the very bottom select the outputs of the create html action.
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 50%;
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>
Finally, add the send an email action, enter any text you want in the email and select the outputs of the formatting compose above.
This is the resulting email.
If you don't want the flow to send an empty table if there is nothing 14 days ahead there are ways to deal with that, but hopefully the above will get you going for now.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
Accepted Solutions
@Andy_Downs forgive me for saying this but they way you want to do it is not the best approach, not least because the calculated column wouldn't update automatically anyway every day because it's just a static value. You should do it all in the flow.
So in this example we want to send a single email when any of the items are 14 days ahead, i.e 29 April. So that will be just Guadeloupe and Curacao.
In your flow the trigger is a recurrence schedule. Set it to 1 day and in the advanced options set your time zone and the time you want the flow to run every day.
Next, add a Compose action, and click in the Inputs field. From the Expression tab of the dynamic content box enter the expression formatDateTime(addDays(utcNow(), 14),'yyyy-MM-dd') which calculate the date 14 days ahead of today:
Next, add a get items action and select your site and list. Open the advanced options and in the Filter Query field type the column name (in my case Arrival) eq (which stands for equal to) then add 2 single apostrophes '' and inside them select the outputs from the 14 days ahead compose action.
Next, add a Select action and select value from the get items section of the dynamic content box. For the Map fields you need to enter a text value in the left column, and in the right column select the information you want to display which in this case is the Title field and the outputs of the 14 days ahead compose action.
Follow that with a Create HTML action and set the columns field to Automatic.
The default HTML table doesn't have any styling attached to it, so to make the table look better add another Compose action, paste in the CSS from the spoiler below and then at the very bottom select the outputs of the create html action.
table {
border: 1px solid #1C6EA4;
background-color: #EEEEEE;
width: 50%;
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>
Finally, add the send an email action, enter any text you want in the email and select the outputs of the formatting compose above.
This is the resulting email.
If you don't want the flow to send an empty table if there is nothing 14 days ahead there are ways to deal with that, but hopefully the above will get you going for now.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)