Send email notification - summary of all items HTML

[cindelwood]

Hello,

I am trying to send an email notification that summarizes all open/past due tasks in the list Customer Relations Onboarding - IAM based on the column ASSIGNED FACILITATOR, where the STATUS column value is equal to "Pending Scheduling by Facilitator"

The email address for ASSIGNED FACILITATOR is being pulled from another list, User Directory

FLOW DETAILS

Send Notification – summary of open tasks

ERROR

Customer Relations Onboarding - IAM column details

Column Display Name	Type	Internal ID
ACTIVITY	Lookup (primary)	ACTIVITY
ACTIVITY SET	Lookup	ACTIVITY_x003a_Title_x0020__x002
DELIVERY MODE	Lookup	ACTIVITY_x003a_DELIVERY_x0020_MO
ESTIMATED DURATION (MINUTES)	Lookup	ACTIVITY_x003a_ESTIMATED_x0020_D
PERIOD	Lookup	ACTIVITY_x003a_PERIOD
FACILITATING DEPARTMENT	Lookup	ACTIVITY_x003a_FACILITATING_x002
FACILITATOR(S)  1= Primary 2 = Backup	Lookup	ACTIVITY_x003a_FACILITATOR_x0028
ASSIGNED FACILITATOR	Person	ASSIGNEDFACILITATOR
Date Created	Date & Time	_DCDateCreated
NEW HIRE NAME	Choice	NEWHIRENAME
STATUS	Choice	STATUS
TRAINING SCHEDULED FOR	Date & Time	TRAININGSCHEDULEDFOR
NEW HIRE START DATE	Date & Time	NEWHIRESTARTDATE

Any help would be much appreciated! 

Thanks,

candace 

@Expiscornovus 

Hi Dennis - I was hoping you might be able to help me with this one, given the fact you helped me out with and understand my initial flow we worked on a few weeks back. Thanks!!

[Expiscornovus]

Hi @cindelwood,

Just to double check. Do you want to send individual e-mails to each assigned facilitator (with their open/past due tasks)?

[cindelwood]

Hi @Expiscornovus 

Yes, the ultimate goal is to send a summary of past due items unique to each assigned facilitator. I actually got the flow to save without any errors, however, it is not sending the email notification. 

[okeks]

If you use a string as an array index, you need to put a "?" before the ['indexname'].

e.g. body('Get_items')?['value'].....

[Expiscornovus]

Hi @cindelwood,

I would retrieve the unique facilitators from the on boarding get items action and loop through that list of people. Per person you could filter the items from the onboarding list and also filter the user table to get the correct user e-mail. This way you would only have one apply to each loop.

Below is an example of that approach.

1. Add a Select action after the on boarding (with your filters). Select only the Assigned Facilitator field (switch to text mode for the map field with the button on the right side).

2. Add an apply to each loop. Use an expression with an intersection to get an unique list of facilitators

intersection(body('Select_-_Facilitators'), body('Select_-_Facilitators'))

3. Use a compose with the item(). You could also use a variable action for this. I was lazy 😁

item()

4. Use a Get Items with a Filter Query. Use the output of the compose for the filtering.

5. Add another Select action. This is to only select the UserEmail value from the user table (switch to text mode again).

6. Add a Filter Array action. Use this to filter your On Boarding items. Use the outputs of the compose action

7. Add another select action. Use this to select whatever field you want to use to show in the HTML table. I was again lazy and only selected the ID field

8. Use the output of the last Select in the Create HTML table

9. Send the e-mail. Use the following expression for the e-mail. Use the output of the Create HTML table for the body.

first(body('Select_-_UserEmail_Field'))

[cindelwood]

Hi @Expiscornovus 

Thank you so much for taking the time to provide such a detailed example. Unfortunately, the flow run timed out. Any suggestions? 

[Expiscornovus]

Hi @cindelwood,

Can you share a screenshot of your time-out flow run? In which action did it time-out?

Btw, some fields in my example setup might be different compared to your setup. Can you check if you have used the correct fields from your setup in different filters?

[cindelwood]

Hi @Expiscornovus 

The flow timed out at Apply to Each

Here are screenshots of my flow:

[Expiscornovus]

Hi @cindelwood,

As far as I can tell from your screenshots you have nested second apply to each in your flow? Which list of values is in the second loop? Is it the list of all on boarding items? I can imagine it takes it bit longer with that nesting and might even time-out.

I avoided the second apply to each loop by first selecting the Body from the Filter Array action and adding the different fields as expression instead of selecting them from the dynamic content list.

[cindelwood]

Hi @Expiscornovus 

Flow is forcing the second loop as soon as I enter value in map on the third select:

[Expiscornovus]

Hi @cindelwood,

I have created a small gif which hopefully makes it a bit clearer.

First I show how you will get the loop by selecting a dynamic content field like ID. In the second part I demonstrate how you can avoid the loop by using an expression.

[cindelwood]

Hi @Expiscornovus 

Thanks! That seems to have worked. One more question, please? What is the best way to format the HTML ouput? 

I would like to customize the link to the item url. I was able to achieve this in another flow, however, the expression doesn't seem to take when using it in Select vs HTML columns. 

I would also like to set the column width in the email (this may not even be necessary after fixing the url)

Current email output: 

Thanks!!

[Expiscornovus · Accepted solution]

Hi @cindelwood,

I always prefer the inject CSS styling approach by Ryan Maclean, https://ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/

[cindelwood]

Hi @Expiscornovus 

I don't know what happened to my flow but now I'm getting an error 😣 I rebuilt the flow and simplified after making some modifications to the last version that failed. Below is the new one I created with error messages.