Send a mail every month when a field is still empty

Nick_DS Ok here's how you do it In my example SharePoint list I have only 3 columns compared to your 30. And there is a calculated column called Empty which has the following formula:

=IF(OR(ISBLANK(ColumnA),ISBLANK(ColumnB),ISBLANK(ColumnC)),"Yes","No")

(so yours will be somewhat longer!)

As you can see where any of the columns is empty the value is set to Yes.

Based on this list there will be an email sent to the owners of the 3 items that have an empty value, but only 1 email to each. The flow looks like this.

1. The trigger is a recurrence schedule set to 1 month. Next, add an initialize variable action which I've named varEmpty. The next action in my example is to get my company logo which will later be added to the email.

2. Next, add a get items action. But one thing you can't do is to include a calculated column into the filter query, so we just bring back all the items in the list.

3. To overcome the inability to add a calculated column in the filter query we now need to add a filter array action.

Next, add an apply to each and for the From field select Body from the filter array on empty equals yes section of the dynamic content box and then make the filter Empty is equal to Yes.

Then add an append array variable action, select your variable and for the value field add the expression items()?['Empty']

4. Next, outside the apply to each add a Select action. In the From field select varEmpty from the dynamic content box. Map the owner to the item()?['Owner'] expression.

Next, add a Compose action to get the unique email addresses. You do this with the expression 
union(body('Select'),body('Select'))

5. Next, add an apply to each and for the first field select the outputs of the previous Compose action. Then add a filter array action and select varEmpty and for the filter add the expression item()?['Owner'] is equal to the expression items('Apply_to_each')?['Owner']

6. Next, add a create an html table action. For the From field select Body from the Filter array by owner section of the dynamic content box. Add the columns you want using the expression item()?['Title'] etc

7. The formatting of an html table in power automate is non-existent, so add a compose action and paste in the css in the spoiler below followed by selecting the outputs of the create html table action.

<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')}

8. Finally, add your send an email (v2) action. I'm adding the logo I selected at the beginning of the flow so need to select the code view </> icon and add html tags and the expression:

When the flow runs it sends an email to the owner of the item with only the items that have an empty column,

Hope that helps. Come back with any questions.

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

Nick_DS I will post up screenshots and details shortly.

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

Vivek Jagga 

Wait WHAT ?? 🙂

Compute?