Forum Discussion
Send a mail every month when a field is still empty
You can use capability of Microsoft Power Automate or Azure Logic App. You can decide the option based on the volume of items in list.
Vivek Jagga I tried, but it makes an APPLY TO EACH every time :
But then it sends me a mail per item, and I want 1 mail with all the items ?
- Nick_DSOct 03, 2023Copper Contributor
- Rob_ElliottOct 03, 2023Bronze Contributor
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.
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')}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:
dataUri(body('Get_file_content_using_path'))Then select the outputs of the format hrml compose.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_DSOct 27, 2023Copper Contributor
Hi sorry but at steps:
I don't see the items()[Empty] thingy ?
And in 4. where you put OWNER with the blue icon, I don't see any blue icon items ? 😞
- Rob_ElliottOct 03, 2023Bronze Contributor
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 JaggaAug 01, 2023Copper Contributor
Nick_DS you need to compute it first with foreach and store related info in a variable. Once all computation is completed then use Send Email action.
Screenshot that you had shared is not computing, its 1:1 so its sending 1 email per item.
- Nick_DSSep 11, 2023Copper Contributor