Parsing CSV, Filtering JSON by a Dynamic Field, and Sending 1 Email with Filtered HTML Table to each of the dynamic field values

[LoufromNH]

Hello,

Running into a bit of a head scratcher. I had a report with line items for overdue courses which I was trying to manipulate in Power Automate to accomplish the result. Frustrated with my lack of progress, I have changed the incoming report to more closely resemble my desired output.

The report comes in like the following:

Manager E-mail	Person Full Name	Total Number of Courses
Manager E-Mail 1	Person Name 1	40
Manager E-Mail 1	Person Name 2	40
Manager E-Mail 1	Person Name 3	38
Manager E-Mail 1	Person Name 4	30
Manager Email 2	Person Name 5	41
Manager Email 2	Person Name 6	40
Manager Email 2	Person Name 7	40
Manager Email 2	Person Name 8	37
Manager Email 2	Person Name 9	26
Manager Email 2	Person Name 10	20
Manager Email 2	Person Name 11	1
Manager Email 2	Person Name 12	1
Manager Email 2	Person Name 13	1
Manager Email 3	Person Name 14	58
Manager Email 3	Person Name 15	42
Manager Email 3	Person Name 16	20
Manager Email 3	Person Name 17	7
Manager Email 3	Person Name 18	5

The goal for me here:

• Parse CSV - done
• Filter by Manger Email - this is dynamic and giving me trouble, seems to only work when I hard code the values and run in parallel, but I don't know the values ahead of time
• Send 1 email to each of the Manager Email values with a summary of their employees with overdue courses - having trouble with this as well as it seems to loop for each of the line items even when trying to get the values from my filtered JSON

For the email filtering, I've tried creating an array variable and appending it:

That didn't work, so now I've tried just filtering by the same field (doesn't seem to work either:

For getting the email values, I've tried to use a union of the same variables, which seems to work, but it still runs for each of the above 19 line items:

The complete JSON is functioning as desired prior to my desire to filter by Manger Email.

I'm pretty new at this but have had some success. Looking for any ideas as to how to make this work. Really just want to automate notifications on a periodicity to drive compliance.

Thank you,

Lou

[MarconettiMarco · Accepted solution]

Hello @LoufromNH ,

please follow these steps:

1. List the rows, in my case I'm using the Excel "List rows present in a table" action:

2. Add a "Select" action where to grab only the Manager email:

3. Add a "Compose" action in which put the following expression:

union(body('Select'),body('Select'))

4. Add an "Apply to each" control: put the previous Compose outputs in the "Select an output from previous steps" field

5. In the Apply to each loop, add a "Filter array" action where filter the list based on the Manager email:

6. Always in the Apply to each loop, add a "Create HTML table":

- From: body grabbed from the "Filter array" action;

- in the Values please use the following expressions:

item()?['Person Full Name']

item()?['Total Number of Courses']

7. Always within the Apply to each loop, I suggest to add another Compose action and put the following html to format the table:

<style>
table {
  border: 1px solid #1C6EA4;
  background-color: #EEEEEE;
  width: 100%;
  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>

At the end of the HTML, add the HTML table outputs:

8. Always within the Apply to each loop, add the Outlook "Send an email (V2)" action and add the previous Compose outputs in the email body:

Here the full flow:

Here the output:

So, each Manager will receive his own email with the employees list.

If I have answered your question, please mark my post as Solved.
If you like my response, please give it a Thumbs Up.

My blog site about Power Automate: https://powerautomatejoy.com/

BR,

Marco

[LoufromNH]

Thank you, Marco.

I'm now getting an appropriate number of emails going out, but the html table is blank. Additionally, the To in the email would need to be dynamic based on the table.

I'm not using an excel sheet, so that might be the problem. The report comes in as csv and I convert it into JSON. Excel data is not an option in the system I'm generating the report from. Maybe I can convert it, but it still seems like the filtering should work with the JSON array.

I'll keep playing with it.

[LoufromNH]

Well,

A little messing around and I'm getting the appropriate number of emails, each with unique data that is populated.

Now the only issue I'm having is making that "To" field in the email action dynamic based on the email for the action. When I select the "Manager E-Mail" field from the filterByManager step, it creates an apply to each loop which appears to send an email for each line item.

The playing will continue until the problem is solved.

[MarconettiMarco]

Hello @LoufromNH ,

in regards of the "To" field in the Email action, you simply should add the "Current item" value, cause the email sending action is already contained in the Apply to each loop where the "from..." is the email manager.

In regards of the blank table, you should use the expressions that I've mentioned in my previous post, eventually by replacing "Person Full Name" and "Total Number Of Courses" with your excel table column names.

Please remind to add the "Create HTML table" outputs at the end of the HTML contained in the "Compose" action: 

If are there any other issues, please share the detailed flow in Edit Mode.

If I have answered your question, please mark my post as Solved.
If you like my response, please give it a Thumbs Up.

My blog site about Power Automate: https://powerautomatejoy.com/

BR,

Marco

[LoufromNH]

Thank you, Marco. All working now. 

Slowly learning. I appreciate the help.