Help with creating a flow that sends multiple emails to different people with specific items in a Sharepoint list.

[RossKnorr]

Hi!

I'm working on a Flow that will create an email report from items in a Sharepoint list on a weekly basis. Users use a PowerApp to record new items (Principals observations of teachers) to the Sharepoint list. Because they get these observations daily - sometimes multiple times -  it would be too much to send an email for each observation, so I'd like for the teachers to receive one report in their email each week with all of the items that were created that week, relevant to them. Here's what I've done:

I tried using this post, but it only gets me halfway: https://powerusers.microsoft.com/t5/Building-Flows/flow-to-send-single-email-for-set-of-records-in-S...

- Trigger: I have the trigger set to recurrence, and it triggers each Friday.

- Action: I get items from the Sharepoint list.

- Action: I filter the array to select all items with a "Created" value greater than or equal to five days past.

- Action: I create an HTML table with the items I want from the array

- Action: I initialize a variable called "EmailAddress"

- Action: I append the email address from the array to the "EmailAddress" variable for each item in the array.

- Action: I do a union and a join (my SQL is not very good - this is me copying and pasting from that post)

- Action: I send an email with the HTML table output, sent to the email address output.

The problem is that this sends ONE email with ALL the data to ALL the email addresses. I want instead to sort the array for individuals, and then email each individual their specific rows for the week. Can this be done in flow?

[MarkStokes · Accepted solution]

Hi Ross,

This can be done but the hard part is going to be getting the “distinct” list of teachers.

Essentially, you want a list of the teachers, then “for each” teacher get the SharePoint Items for that teacher for that week, compose the email and send it.

Personally, in this case I would have used two SharePoint lists. One for teachers and one for Observations that has a lookup for the relevant teacher. The PowerApp could easily work in this setup.

Then your Flow would be easy. You get every item in the teachers list and for each one get the latest observations and compose the email.

Without changing the data structure you need to get all items created in the last 5 days, go through every item and see if an array variable contains that persons email. If not add it. Then, loop over the array of unique emails and get the observation items. If you do this then be sure to look at OData filters to get the items you want in the Get Items action.

- Mark