“Is there a way in Power Automate to split emails from a multiple people picker field to have a single email per user for all of the items in which they are included as an owner?”
Following a question from the original article to Send one email per user with multiple SharePoint items, what if the column is not a single people picker? What if the column allows selection of multiple users? If you have more than 1 user assigned per item? You probably still want to send only one email per user, even if they share the item with others.
Get user emails from the items
Once you switch the people picker column to multiple selection, its structure will change. It won’t be an object with the user anymore, now it’ll be an array of objects (=users). And as such you can’t get the email addresses as easily using a single ‘Select’ action. You’ll need more of them and one loop.
Start with the first ‘Select’ and select all the approvers from all the items.
The result will be an array of arrays – for each request you’ll get all the approvers. Here comes the need for a loop. Continue with ‘Apply to each’ where you loop through the output, processing the approvers for each item one by one.
That’s how you remove the first array, by looping through. Each item in this loop will now contain only the object with approvers, where you can ‘Select’ their emails as many times before. Don’t forget that you must reference the previous action, not the ‘Get items’!
From: item()
Map: item()?['Email']
This second ‘Select’ will give you an array of email addresses for users assigned to the task. Turn them into a string with the join(…) expression in a ‘Compose’ action.
join(body('Select_-_select_email_addresses'),';')
Use another ‘Compose’ after the loop to join all the email addresses for all the requests into a single string. Then split it to convert it into an array with each email being a single item.
split(join(outputs('Compose_-_string_with_emails'),';'),';')
At the end of the loop you’ll have a ‘Compose’ action that’ll contain all the email addresses as separate items. Process it with the union(…) expression to keep only the unique values = unique emails.
union(outputs('Compose_-_all_emails'),outputs('Compose_-_all_emails'))
Filter the items for each user
The other difference from the original flow is in the ‘Filter array’ action. As already said, the people picker column in each item will contain an array of users. Therefore, you can’t simply filter by the user email. You must check the whole array of objects if one of them contains the email address. The easiest way is to convert the array into a string with the string(…) expression, which will allow you to use the ‘contains’ operator.
string(item()?['SendTo'])
From that point on you can continue with the original flow – build an HTML table and send the email.
Summary
Once you switch from the single user to multiple people picker in SharePoint items, the Power Automate flows get more complicated. You don’t have a single object where you can easily access the email, you’ll get an array. And since it’s an array, you’ll have to loop through all the items, and for each item loop through the users. Once you’ve got them in a variable it’s easy to remove duplicates and filter the items with the little string(…) workaround.
great.. Thanks you for sharing this post.
I have a question though. In my SharePoint list, instead of a people or group field, i had to create a single line to text field that holds multiple email addresses with “;” delimiter as the values are coming from a different list via a lookup. What’s the best way to incorporate that in this flow. Essentially, i need to split the emails in that text field and then use those emails to send a list of items based on filter criteria. Thanks in advance for you help.
Hello Raj,, ‘;’). Loop through the result of the split(…), append all the values in the array variable, and from then on continue as in the flow.
if you already have the email addresses, you can split them using the split(…) expression: split(
In my scenario I have 7 branch office. and i want to automate Clearance request system. as well as i have 6 approves like( Line Manager,store,IT,Facility,Finance and HR).
when Item is created in share point the flow must send approval request based on requester Location means that if the requester Location is From “X” location all approvers who are in “X” Location should get the requester approval. if the requester Location is From “Y” location all approvers who are in “Y” Location should get the requester approval. How can I do this in Power Authomate?
Hello Berihun,
you just need a list with approvers, and every time you need an approver take him from the list based on the parameters, these articles should help:
https://tomriha.com/lookup-to-sharepoint-list-with-approvers-in-power-automate/
https://tomriha.com/how-to-combine-multiple-filters-in-get-items-filter-query-power-automate/
Hi, I get the following error: Flow save failed with code ‘InvalidTemplate’ and message ‘The template validation failed: ‘The inputs of template action ‘Filter_array’ at line ‘1 and column ‘2813’ is invalid. Action ‘Apply_to_each_2’ must be a parent ‘foreach’ scope of action ‘Filter_array’ to be referenced by ‘repeatItems’ or ‘items’ functions.’.’. Can you please help
Hello Newbie,
the error message tells you that the ‘Filter array’ action must be inside ‘Apply to each 2’, otherwise it can’t reference the dynamic content you use in the ‘Filter array’.
can you detail how to resolve? in the steps and diagram , the filter array is not under Apply to each 2
Hello Jam,
my guess would be that there’s a wrong dynamic content used in the ‘Filter array’ action.
Hi Tom, I get this error now
The ‘inputs.parameters’ of workflow operation ‘Send_an_email_(V2)’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ’emailMessage/To’ is required to be of type ‘String/email’. The source type/format ‘Array’ of the value ‘[“houahega@hoauehga.com”]’ is not convertible to type/format ‘String/email’.
Hello New,
this error is telling you that you can’t use an array – defined by the [ and ] as an email recipient, you must extract the value from the array. The flow is using ‘Apply to each’ to process all the arrays, you’re probably not using the currently processed item from the loop as the recipient but the whole array.
When we trying to do bulk update the appending array is not feasible as its appending all the Users from PeopleColumn and sending an email . Example: Each Item has 2 approvers. When doing bulk update like closing tickets, all the approvers are appended to array and in every email all the approvers are added in CC.
Can you have the steps to avoid that.
Hello Boobesh,
I’m sorry but I don’t understand what you’re trying to achieve. This blog post is about sending emails to users, it’s not about updating users.
If you have some array that contains more users then it should then you probably don’t empty it (set value to ‘null’ expression) at the beginning of each loop.
Hey Tom,
Would it be possible to use this approach with a few changes to identify if the email one of the Users within the field (column) is equal to the email of the Last Modified By? If so, do you think I could ask you for some direction on how to proceed?
(Also, I accidentally posted this same question in one of your other articles. Sorry. I meant to ask this question here.)
Thanks in advance.
Hello Alberto, contains . Maybe you’ll have to convert the field value to string(…) for the comparison to work.) contains
if your goal is just to check if a multiple people picker field contains the Modified By user then I’d use just a ‘Condition’: if
string(
Hi Tom,
I can’t seem to get the Filter Array to work correctly. It either doesn’t select anything, or it selects everything from the Get Items. I can see the array looks good, it was created as expected, but it doesn’t seem to grab the email address that’s in the array. I have the 3 Applys. 1st is the string array of email addresses. 2nd is the get items value. 3rd is the multi-person column emails. It’s the 3rd that seems to be problematic for me.
Can you help?
Thanks!
Joan
hi Tom, I was able to get the right information and got past the initial issue, but now I cannot use a HTML table with the custom option without it creating additional apply to actions, but this changes the email output. Do you have any suggestioned reading on how I can parse the information so that the users get only 1 email for past due items when they are part of a multi-person field (the source is a SharePoint List).
thank you in advance!
Joan
Hi Joan!
I’m facing the same issue in my flow. How did you solve it in your flow?
The issue with the 3rd Apply for each*
Hello Tainá,
if it’s adding an ‘Apply to each’ then one of the column is probably multiple selection columns, take a look on this article how to solve it using xpath(…) expression: https://tomriha.com/export-multiple-person-or-group-column-into-csv-table-in-power-automate/
Found it! I needed to format the item for the values – when I did that, the Apply to didn’t appear. Sometimes typing the issue out helps too.
item()?[‘Department’]?[‘Value’]
Thanks!
Hello Joan,
well done, I’m glad you solved it!
Can you share what you did? I’ve applied your suggestion with the custom option for the HTML table but it keeps failing when saved . Here’s what I’m using
Title – item()?[‘Title’]?[‘Value’]
Champions – item()?[‘Champions’]?[‘Value’]
Last Updated – item()?[‘Last_x0020_Updated’]?[‘Value’]
Next Update – item()?[‘Next_x0020_Update’]?[‘Value’]
Hey, I was able to get the custom option to work.
I used the same string(…) expression from the “Filter array” action for each required value. For the “Champions” field (in my case this is the column that allows selection of multiple users), I’m using the current item from the “Apply to each – loop through unique users” loop
Title – string(item()?[‘Title’])
Champions – items(‘Apply_to_each_-_Loop_through_unique_users’)
Last Updated – string(item()?[‘Last_x0020_Updated_x0020_’])
Next Update – string(item()?[‘Next_x0020_Update_x0020_Due_x002’])
Hello AJ,
I’m glad that you solved it, I wrote a separate article on this topic in case anybody has the same problem in the future: https://tomriha.com/access-sharepoint-columns-after-filter-array-in-power-automate/
Could you add more detail for how you were able to use HTML when a custom field that is added creates the Apply to action. I am using a people picker field. Do I need to add another variable inside the Apply to action?
Hello Norma,
if it’s a multiple people picker column then you might need the xpath(…) expression to get all the values without ‘Apply to each’: https://tomriha.com/export-multiple-person-or-group-column-into-csv-table-in-power-automate/
Hi Tom – Thank you! This has been super helpful.
I replicated this flow and it appears to work great, except that any person assigned to an item in my list will get a reminder for ALL ‘open’ items even if they are not assigned for that item/row — even when there is another owner on that item/row.
Any ideas on how to fix my issue?
Thanks!
Marissa
Hello Marissa,
I’d check the configuration of the ‘Filter array’ action, that’s where the items are filtered.
Hi,
It seems like I have the same issue and I’m sure that everything is ok with ‘Filter array’ action. Do you know what can be the problem here? Also, I’m wondering: string(item()?[‘SendTo’]) – what ‘SendTo’ stands for?
Hello Klaudia,
in my example ‘SendTo’ is the internal name of my SharePoint column that holds the people who should receive the email, in your case you should replace the SendTo part with your column name. It’s probably a different column and that might be the reason why it doesn’t work for you.
Thank you so much for this post. It works great, this made my day!
Tom, please help! Any chance you have a video of setting up this exact flow? I am getting the following error, “Flow save failed with code ‘InvalidTemplate’ and message ‘The template validation failed: ‘The inputs of template action ‘Filter_array’ at line ‘1 and column ‘2745’ is invalid. Action ‘Apply_to_each_2’ must be a parent ‘foreach’ scope of action ‘Filter_array’ to be referenced by ‘repeatItems’ or ‘items’ functions.’.’.” and I saw you responded that it needs to reference dynamic content from Apply to each 2 but I am stuck with how to actually achieve this. Any help would be GREATLY appreciated.
If you hover over “current item” in your flow diagram what does that show?
Hello Sawyer,
when you’re selecting a dynamic content it always shows in the header what action is the source of that dynamic content. Based on the error message you’re referencing dynamic content coming from a wrong action.
Hello,
I have column where users pick 1 or multiple people. I created flow following your steps and have error
Unable to process template language expressions in action ‘Compose’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘union’ expects either a comma separated list of arrays or a comma separated list of objects as its parameters. The function was invoked with parameters of type ‘String’.
Could you help?
Regards
Hello Margaret,
I’d double check that the variable is of type array and not a string.
Hi Tom,
Your post is very helpful as I have been struggling with group by for the past few weeks. No one wants to receive multiple emails.
On my list, I have Approvers, Purchasers and Users. Some items have multiple approvers and users.
Is there a way to have all the unique approvers, purchasers and users on one email instead of having an email being sent to each unique recipient? I tried to cc the purchasers and users using the dynamic field but Power Automate automatically added the Apply to All loop.
Regards,
Nate
Hello Nate,
that’s a tricky one, especially if the purchasers and users can vary between various requests, but I think it’s doable. I’d try to use the ‘Select’ action after the ‘Filter array’ to select the purchasers, then select the users, and join(…) their emails in the email CC.
Hi Tom, Thanks for taking the time to put this together. I spent several hours yesterday trying to figure out why I could not get to the email data I wanted to get to in my flow. Everyone and everything I looked at kept making it look so simple. It was the fact that I had a multi-select people column that made it not so simple. Your solution did the trick. Thanks.
Hi Tom,
Great stuff as always.
Is there a way to modify this so for any ‘send to’ fields with more than one person, it puts all of those people in the same email rather than sending them seperate emails.
That way they know they’ve all got it and won’t duplicate workload.
Cheers,
Garry
Hello Garry,
there might be some way to do it, but the amount of potential combinations can be theoretically huge and in the end might lead to people getting multiple emails as they’d be in different groups of approvers.
Hi Tom,
I could use some help. Scenario; 1 doc library, with on Multi person field. I have the same use case, get distinct emails from the mutli person field, filter it, build a HTML from that info and send thta info to the distinct emails in one email.
Using the actions listed about i get weird output below from union(outputs(‘Compose_all_ownersemail’),body(‘Select_OwnersEmail’))
Filter Array throwing:
@contains(string(item()?[‘OwnersEmail’]), items(‘Apply_to_each_3’))
The execution of template action ‘Filter_array’ failed: The evaluation of ‘query’ action ‘where’ expression ‘@contains(string(item()?[‘OwnersEmail’]), Item())’ failed: ‘The template language function ‘contains’ expects parameters of matching types: a dictionary and a key (string), an array and a value (object), or a string and a substring. The provided types ‘String’ and ‘Object’ are incompatible.
I hope you can help.
Thansk you for your time.
Hello Dave,
the union(…) in the solution has twice the same array as parameters to keep only the unique value, in your case you’re merging two different arrays leading to the mess above (which I had to shorten).
Hi Tom,
Curve ball for you. Is there any way to ONLY send the email/html table to the 1st person listed in the multi person field.