Working with multi-select person fields in Get items output
HTML-код
- Опубликовано: 26 июн 2024
- In a previous video ( • Working with multi-sel... ), I showed how to retrieve values or properties from multi-select choice or person fields in Power Automate. In that case, I used "Get item" to retrieve a single item from a SP list, but in this video, I'll show you how to do the same thing when using "Get items" to retrieve multiple list items.
Наука
Spent hours trying to understand the logic behind this until I found your tutorial. Thank you so much.
Thanks, Chad Kealey, I wasted two days to find out the solution, and your video solved my issue. Subscribed to your channel.
Thank you Chad! For the last 4 hours my Power Automate kept throwing a second Apply to Each, so when in your video at 11:05 Power Automate added another Apply to each 2 you showed me what to do!!!!! Thank you for leaving the "mistake" in the video. My flow now works! You saved my sanity.
That was totally intentional ;)
Thank you!
Finally... a routine that's explained clearly, and it works!
Thank you for this video - the simple act of dragging that second Apply to Each that popped up saved me from total frustration!!
Excellent. I've been looking for help with this exact scenario for some time. Worked like a charm
Thanks Chad! Straight to the point!
You made it really easy to understand. You just gained a subscriber, great video 🙏
Thank you so much for the video!! You made my life much easier!
This solution worked beautifully for me, thanks
Very useful, thanks Chad!
Thank you Chad!!! You saved my day.
Super helpful! Well explained! Thank you!!
I'm followed many videos ,your the best thanks
Wow!!! You could have saved me a few weeks ago with your drag and drop!!! Hahaha thank you 😊🙏
You saved my life!!!
This is great. Now I need to figure out how to join this with my flow that sends ONE email with HTML table to someone assigned to multiple lines on SharePoint!!!!
I don't have a video, but I've built a few flows like that. The general process is:
1. Get the relevant rows
2. From that data, get a unique list of recipients
3. In an apply to each loop, filter the original data for each unique recipient.
4. Build a table of the results and email them.
This video and your earlier one have been excellent in helping me progress my side project project (populating multiple department plans from an SP List into a Word template).
As a newbie "Citizen Developer", I'd just got my head around array basics to populate a table of repeating data, but was getting lost on the Multi-Select / Apply to Each conundrum.
Whilst I'm still lost in an Inception-like "array inside an array, inside another array..." dreamworld, your videos have helped me start to get some grasp on reality.
Your videos are methodical and beautifully explained. Please keep up the good work! It is truly appreciated!!!
Stay tuned... I'm working on a video series on data types (strings, arrays, objects, etc) in Power Automate. If you have any specific questions on that topic, let me know.
thank you helped a lot!
Thank you so much
Great tutorial!
My need is slightly different - I want to send one email to each person listing the plans they're members of. I have it working perfectly for single People fields, but I'm stuck when it's a multi-select People field.
I would recommend posting a message in the Power Automate Community (powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows) with a clear description of what you're trying to do and screenshots of what you have so far.
This was awesome! Thank you! I'm still trying to work out how to insert a unique HTML Table (Rows of information that only apply to the Folks in the "To:" section of the E-Mail) into the email body. If you have another video that explains that bit, I'd appreciate knowing about it. Thank you so very much!
It depends how those rows of data are related. In other words, you've got a person field with 5 individuals listed. Do you want to get items that they created? Modified? Something else? If it's items they created, you can iterate over the list of people with an apply to each and inside that have a get items action with an odata filter on the Author/Created by column.
@@cvkealey No, it is simply information that is directly related to them in the same row of information within the table. The scenario is: Using Sharepoint, I have a jobs table that has information such as: Title of Job, Job Type, Job Description, Job Address, Date Scheduled, Projected Completion Date, Status and Assigned Technicians. The email that I am attempting to send should have the relevant job information (in a HTML Table) for each associated assigned technician.
Again, thank you so very much!
@@cwjr1968 this is complicated, but possible. If you have a list of technicians (for example, another SharePoint list or an M365 or SharePoint group), that would simplify it. If you don't, then you need to first get all of the values from the technicians column to create a list of them. Once you have that, cycle through the list in an apply to each loop to filter the list of tickets to only those where the technicians column contains that particular person and format the output of that action as an HTML table.
@@cvkealey Thank you so much for your feedback! I truly appreciate it!
Hi Chad, excellent tutorial. One question if I may: how we should proceed if in addition to the multiselect person column we also have one or multiple multiselect choice columns ?
thank you
The same principles apply. See ruclips.net/video/T10-JRtgCaE/видео.html for more details.
Hi Chad, I'm following your steps however when reaching the 'select' action, it will not let me drag it out of the apply to each control. It's not applying to the 'current item' like yours does, it applys to the value of the column first. I'm a little confused! Is there a work around for this?
what field/value are you selecting in the select action? When you go to the dynamic content selector, what "action" is it under and what specific item are you choosing?
@@cvkealey I'm selecting the email field (in my case called 'Assigned To Email), it's under 'Get Items' as that's the only action it is under. Am I missing something?
@@georginahaylett1414 yeah...that's the right thing. Sometimes it's just wonky. Instead of selecting from the dynamic content, try entering this as an expression: item()['Email'] ,as shown here: www.screencast.com/t/pw98Y2wH3x
@@cvkealey Hi Chad, this worked a treat! Thank you so much!!
Thanks for the video, yes, interesting that the "apply to each" window is showing up multiple times on mine too
Question: what happen if you need to grab more than one multi-select fields? Do you then need to apply to each everytime? Thanks
It depends what that data is and what you want/need to do with it. In some cases, you can use a Select action to retrieve those arrays or specific elements from them and then use a Join action (or the join expression) to concatenate the values into a string. Generally speaking, I try to avoid using Apply to each loops unless they are the most practical or only option.
First off, thank you Shareing this, it was really helpful. I have a question - How could I go about Create_item for each teammemeber based on the outputs? Im trying to get the employee claims toek for each row created from this.
The Select action outputs an array of data, either values or objects (depending on the "map" you specify). Assuming the field you're selecting from is a Person column, one of the values you can extract is the Claims (although in most actions that require claims, the email will work as well). Basically, you would add an Apply to each loop and set the input to be the output of the Select action.
Hi, i think your videos are great. sorry for posting this here, but i want to do an initial load from a Sharepoint List to SQL Server. Do you have any strategies on how to best do that using Power Automate. THANKS! I was trying to use the manual trigger and get items, but was not having any luck.
If the issue is getting the items, I can probably help. If it's writing that to SQL, I'm useless (no experience with connecting to SQL from Power Automate).
@@cvkealey yeah, the sql server could be anything, writing to cvs etc. it’s looping through Items in the array that’s giving me trouble
@@bfantle got it. That can be a bit funky. Troubleshooting via RUclips comments is rarely effective, but if you can post a message in the Power Automate community (powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity) and tag me (ChadVKealey), that would be helpful. Screenshots are a big plus.
This is grand for sending emails, but can I use the JSON to send emails but I need to update another list using the names I have extracted from one to another - any ideas?
Probably? Definitely? It's hard to say without a clearer description of what you want to do. I'd recommend posting a message in the Power Automate community (powerusers.microsoft.com/t5/Get-Help-with-Power-Automate/ct-p/FL_GeneralDiscussions). Feel free to tag me (@ChadVKealey) and I'll take a look.
great! is there a way to send the email individually? My emails are in SQL as a single value, split by semicolon. When i am sending the emails, I want to look like they are being sent individually to each email. Is there a way to do that? Thank you.
So, you have a string of emails like this: bob@domain.com;joe@domain.com;sally@domain.com ? If so, you can use the Split expression to convert that string into an array and then use that array as the input to an Apply to each loop. Create the email action inside the loop.
The split expression would be split(,';')
NOTE: if there are leading or trailing spaces (e.g.: bob@domain.com; joe@domain.com), then you need to use a replace expression around to change the space to a null, like this:split(replace(,' ',''),';')
And when addressing the email inside the loop, the To field will just be the expression: item() - which is the current item/email in the array for that iteration of the loop.
Hi there! This tutorial was quite helpful. I'm trying to use it but I have to send an email reminder a few days out to each person... how can I apply the send email function properly to this?
This video (released a week or so ago) might be just what you're looking for: ruclips.net/video/4uO1wedT73I/видео.html
In terms of sending the email "to each person", how you do it depends on whether you want to send one email to multiple people (I suspect thats the case) or send each person an individual email. If it's the first case, then what's shown in this video will let you create a string of those users' emails. If it's the second, then you'd have to loop through the array of people to send each their own email.
Hi Chad, Thank you so much for this video.. I am trying to use these steps to extract the names instead of the email address to then use in an HTML table.. But for each record the output for name is showing me all the names for all the records that are being pulled.. Do you know what the issue might be please? Thank you.
The process would be the same, just replace "[person] email" with "[person] display name" in the select action.
@@cvkealey Thanks so much..
Hi Chard, i tried to mirror my flows using your video, my data source is a SP List. What if my scenario is not start from 'Recurrence >Get Items' but it is 'When and item is created or modified > Get changes for an item or a file'? and then i need to have a Condition to fulfill (column A 'Status' = Reviewed) before email triggered to multi-team members (column B 'Members' is a multi-person type). When i add 'Compose' after 'Get changes for an item or a file', i can't find 'Get Value', what should i do?
Get changes is only going to retrieve values that changed. You need to add a get item (or get file properties, if it's a file) to retrieve the Members field.
@@cvkealey i tried added Get Item after Condition, email can be triggered, but when user edit 2 items in SP List at the same time, notification triggered twice for each item
@@leeseankoay8691 you mean they got 4 notifications (twice for each item), or 2 (once for each item). If you mean 2 total notifications, that is to be expected since the flow is being triggered once for each item that's modified.
@@cvkealey yea, total 4 emails, 2 for each item. Is it due to Get item i didnt take ID as the data filter? currently i used the Title field..
@@leeseankoay8691 it also sounds like you're using get itemS (plural) rather than get item (singular). You want to use get item and use the ID from the trigger output to get the specific item that was modified. Also, your send email is probably inside an apply to each loop, which is not what you want.
When I try this with mentions it post two separate post for the emails in a multi select. How can you make it just one post?
I'm not sure I understand the question. Can you explain in more detail what you're trying to do?
Hi Chad,
That you for your great series on approvals.
Just wanted to ask for your advice on a flow which ive put together.
Basically an approval flow which is triggered off a document list. Requestor can enter an approval group which is based on an approval matrix in another list. Issue is that the user wants to be able to choose two or more groups to input. So the field has been set to a multi select option field. I am having trouble with getting my flow to pull through all the email/persons from both groups to spit into another sharepoint list where an automated 'when a new item is created' flow sends out approvals.
Any advice is much appreciated
That sounds a lot like a flow I built to "collect feedback" on documents. Basically, there's a list (SharePoint) of "reviewer groups," each of which has a multi select person column and they needed to be able to select multiple groups. I spent most of a week trying to achieve this without any loops, but ultimately gave up.
What I ended up doing was getting all the selected reviewer groups, looping through that result with an apply to each, and each iteration had another apply to each inside it. Within that loop, each reviewers email address is appended to an array variable. Finally, there's another apply to each that iterates over the reviewer array to create a "feedback placeholder" assigned to each of them. It's not super efficient, but it's reliable.
Great Video! I am trying to implement something similar where i have a multi select people column. Its a multi select assignee field. when a new user A is assigned, it sends email . next time when another user B is added for the same item, it shroud only send email to user B. My flow is sending it to both A and B). How can i extract only the last email added and send email? TIA!
The expression last(arrayOfUserEmails) should do what you want.
Is there a way to send an email to every member with a list of tasks assigned to them in one email to each?
Is there a way? Yes! Is it easy? Not exactly. Basically, it requires getting the list of tasks, then extracting a unique list of assignees, then filtering the list of tasks per assignee, adding their items to a table and sending an email. I don't have a video that walks through that, but it's on my to-do list. There are several ways to do all that data manipulation and I'm still working out the "cleanest" way to do it.
how would i put that apply for each in a select where you have your columns and the value of those. but one of those is the output of the apply to each. this is then all put into create csv table. It just isnt accepting the apply for each in the select part for the columns of the table.
I'm sorry, but I can't follow the logic you're describing there. I would suggest you post a message in the Power Automate Community (powerusers.microsoft.com/t5/Building-Flows/bd-p/BuildingFlows) with a clear description of what you have (what data) and what you need to do with it (what output you need). Including some screenshots of your flow as it is would also be very helpful. You can tag me there (@ChadVKealey) if you'd like.
In the same way, I wanted to place the each item of List into an Excel file. How can i do that if i have multi person column. Its taking into Apply to each and how can I get the Each item of Multi people values to place in excel column.
It depends on how/where you want them in that Excel file and whether you mean a new file or existing one. For example, do you want to record each person's email in a new row of the same column in a new Excel file? Or do you have an existing file with a table in it where you want to add a row for each person on that field? The output of the Select statement is an array, so if you search around for how to write an array of data to Excel, you will probably find another video to help. I honestly have not worked very much with Excel in Power Automate, so probably not the best person to advise you on this. Check out Damobird365's channel. He's got a few videos along those lines.
🎉
Hi kealy, I am very interested in this explanation, if I replace send email with start wait and approval...is it possible?how to change it..thanks a lot
You could use this method to start an approval. Just replace the send email action with either start and wait for approval or create approval. You'd be creating a single approval with multiple approvers, so need to think about whether you want to complete it on the first response or wait for all.
See this playlist for more details on approval actions, types and assignment scenarios. Power Automate Approvals: ruclips.net/p/PLMVyjgrWIGnwlh4YWxb4Aj_AK7n2hjq6p
How Can I Export to CSV file If I have multiselect person in multiple Columns (wants display name from each column and each person)
You can use the technique shown to get a comma separated string for the display names from each column, then concatenate the two strings with a comma in between.
I was wondering of this same logic can be used for claims
It'll work for any of the component properties (email, display name, job title, claims or department.)
2 weeks of trying to get something this kind of thing working:
Several rows with multi-select choice columns and needing that output eventfully to an HTML table..
That "feature" and new-to-all-this ignorance doesn't help.
For some strange reason I am getting 3 rows of output (2 duplicated rows) for each record instead any idea.
I was so happy I was getting just the values for the multi-select I didn't realize the data had tripled
It's kind of hard to say without seeing how you have things configured. If you can post a message (with screenshots) to the Power Automate community (powerusers.microsoft.com/t5/General-Power-Automate/bd-p/MPAForum) and tag me (Chad Kealey), I can take a look.
@@cvkealey Thanks - Done
I've been racking my brain on this one. I'm simply trying to move a multi-select person field from one list to another. I'm having some difficulty understanding how to get the data into a variable so I can add into my apply each for my Create item. I already have an apply to each for my Create item. Rather than have this apply to each separately should I move it into my apply to each for my Create item? I actually see the data. I see the data. Around the 9:03 mark in your video I'm able to replicate this step. I appended to Array Variable on that Select which I thought would do the trick in capturing what was in Outputs. I used that same variable in my Create item which is in the apply each. It just comes up blank.
So, you have List A with a multi-select person column and List B with another multi-select person column and your goal is to get the names/people from the column in List A to the column in List B? That can be done without any loops, but I want to be sure I understand that that's what you're trying to do.
See this video: ruclips.net/video/kVrXFu_3-3Y/видео.html
@@cvkealeycorrect. That's what I was trying to do but I was able to figure it out. I have a Create item in a Apply to each. Foolishly, I had my Select outside of the Apply to each. Once I put my Select for my multi-select person field inside the same Apply to each right before the Create item, it worked perfectly. Once I put an Output in the multi-select field for the Create item it was fine. However, this video was very helpful in helping me understand Selects as they relate to multi-select person and choice fields. Thanks for this.
kindly Chad , can you make a video for the following issue :
we have an approval process, the approvers are stored in the share point list, 2 columns one for the direct manager's names and the second for the department manager's names
the approval process 2 steps as the following :
1- first, the request go to the direct manager
2- secound after that the request goes to the department manager
that means I have 2 steps of approvals, the issue we looking to solve is if the direct manager person is the same department manager, how we can send only one approval request instead of sending 2 requests ?
I don't typically do requests ;) Also, this is an extremely busy week for me, so I probably won't be doing any recordings. However, there are a number of ways to accomplish this. Probably the simplest would be to use a condition to check if "Approver 1" and "Approver 2" are the same and if so, generate only one approval to that person. If NOT the same, then generate the two in sequence (based on whatever your requirements are, such as does Approver 1 need to approve BEFORE Approver 2?).
Also, you are unlikely to find a video or guide for doing exactly what you want to do. It's better to learn the basic patterns and then how to apply them to your scenario.
followed same method but not able to fetch the emails anything i am missing
I'd say you are missing something, but not sure what. Is the column an actual "Person" column that allows multiple selections? For the input of the Select, are you selecting the dynamic content that is just the column name (without "Item", "DisplayName", "Email", etc.)? If you're doing all that, I'd suggest posting a message in the Power Automate community (powerusers.microsoft.com/t5/Get-Help-with-Power-Automate/ct-p/FL_GeneralDiscussions) with some screenshots of your flow to see how it's configured.