Flow Update Multi-Select Column
When building flows in Power Automate, there’s a trick when it comes to updating a field where you can select multiple items.
This applies to:
- People-pickers
- Lookups
- Choice fields
- Managed metadata
Especially if the field is required in SharePoint, flow will insist that you populate it with a value, even if that record already has one. In this post, I’ll show you how to get around this issue, no matter the column type.
In this very simple example, I have a document library full of company policies. When a file is modified, it goes through an approval process and then the file’s metadata (columns) need to be changed. The focus in this post isn’t what is happening in the approval process.
The focus is on the update action…
which applies to the Update Item action (for a SharePoint list item) or the Update file properties action (for a file in a library).
My person column is called “Policy Owners”, and I can have multiple owners per policy. In a flow, the action looks like this:
The tendency, especially when it’s a required field with a red asterisk next to it, is to just add the dynamic content of itself, just put the same value back in this field. Not so easy with a multi-select.
This is wrong:
The problem with the above screenshot, is that it will create a loop. For each policy owner, it will update the file properties and put that policy owner’s name in the field. The problem is that, for example if I have 2 policy owners, it edits the item twice, and each person’s name is inserted as the policy owner, two separate times, so that the end result is only one person’s name will still be in the field. EVERYONE ELSE IS REMOVED EXCEPT FOR ONE PERSON
Again, this problem is the same for all of the different types of multi-select fields. The solution is to create an array variable.
1. At the beginning of the flow, right after the trigger, add an action: Initialize variable
2. After initializing the variable, add an action to append to an array variable.
3. Now the syntax is needed, for what the array needs to consist of. This will be different for different kinds of fields. Go ahead and add your action to update item or update file properties, and type some random word in the field, and click the button Switch to input entire array.
4. Voila, here is the syntax you’ll need. THE SQUARE BRACKETS ARE NOT NEEDED. Copy this content to your clipboard. You do need the curly brackets, but not the square ones on the outside.
5. Scroll back up to your action called Append to array variable. Paste this in the Value box.
6. Now the “xyz” needs to be replaced with your dynamic content. Delete it, and select your field from the dynamic content panel. A loop will be created, which is fine. Notice that the field is called “claims” so the policy owner claims is what I select. Other types of fields may have something like Id or Value, but a person field has “claims”. Now, for each policy owner, it will add that person to the array, so then you’ll have one big array with everyone in it.
7. In the Update item or Update file properties action, go back to your field, in my case it’s “Policy owners”, and insert the array variable. Be sure that the toggle is still set to “switch to input entire array” (from step 3).
The end result is that the value is correctly placed back into the multi-select field. And you can use this append to an array concept to add more people to the list of policy owners if you needed to do something fancy like that.
Check out my advanced Power Automate (flow) class, self-paced online!
BONUS:
If you need to send an email to, say, all of the policy owners, you would append to a string variable, not an array, and you would use their email address and a semicolon. Then that variable could be used in the “To” box of the email.
If you change to input entire array then insert the dynamic value. This does the same thing doesn’t it? No need to create a separate array to hold the dynamic array unless you need to manipulate the array in some way.
No, it doesn’t do the same thing.
Great content!! This is exactly the info i needed to fix my issue.
Thanks for the post.this take care of long running issue I needed to fix with multi-choice columns.
Thank you so much for the steps to update a multi select lookup field.
My lookup field is actually a SharePoint online library lookup onto itself, so that i can create a relationship between documents in the same library.
The issue is that no matter what ID I used to build the array… the lookup always resolves to null.
Any assistance and advice would be greatly appreciated.
I append the array with:
{
“Id”: @{body(‘Update_file_properties_-_xxxxx’)?[‘ID’]}
}
Input
I set the lookup with:
[
{
“Id”: 2600
},
{
“Id”: 2599
},
{
“Id”: 2598
},
{
“Id”: 2601
},
{
“Id”: 2602
}
]
Output
The lookup is not part of the body
The lookups value is []
Even I am getting the same issue. Multi-select look up column is getting Null value. Did anyone got an answer for the above ?
Great info!! Thanks!
Laura,
Since 2010 when I’ve found myself hitting my head against a wall I’ve learned that there’s a small group of content producers who are actually helpful. With this post, once again, you’ve once again shown yourself to be super helpful. Thank you!!
Laura, I echo @Danny Pantoja. This solved a sticky problem for me, too!
Thank you SOOOOO MUCH! This saved me on a project I was working on. 👍
Hi,
for me is writing back not only the content XYZ but [{@odata.type”:”#Microsoft.Azur.Connection.Sharepoint.SPListExpandeRference”,”ID”:1,”value”: “XYZ”]…
What should I correct?
Thank you
Hi, I get this same issue, instead of selecting the multiple choice column options, the flow just forces the same coded text into the list item.
I’m having this same issue, did you got to a solution?
Thank you, but this is happening for me too. SP is displaying the value as
[{“@odata.type”:”#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,”Id”:4,”Value”:”General Observation”}]
Anyone have any ideas?
We tried your suggestion but we keep getting a list instead of 3 separate Claim items(our test array has 3 items), what could be going wrong.
we have an apply to each with the array as output, and append to array with a pre defined variable and the text setup with { “Claims”: Array() }
the output becomes 3x a list with this :
{
“Claims”: [
“user@user.com”,
“user2@user.com”,
“user3@user.com”
]
}
Thank you very much for this!!!
Thank you so much, Laura – now I do understand the “Wonder”-prefix! Tried it the hard way (without success) and it’s actually this easy…..
All the best and stay safe!
Laura,
Twice during my professional career you have helped me move forward with Sharepoint in a big way (besides many others were you were also very helpful). I can’t thank you enough for all what you do. My deepest admiration and best wishes to you.
Warm regards,
Pablo
Awww thanks so much!!
I’ll echo what others have said. You’ve been saving my butt since InfoPath and I love how clearly you explain things. I’m wondering about combining this with getting changes for an item. I’ve got multiple choice fields and would like the changes made post automatically to a Notes field. Right now, we manually note what was selected/de-selected. Any thoughts? This was super helpful: https://wonderlaura.com/2020/08/25/flow-when-a-sharepoint-column-is-updated/
Hi Laura,
Great article, it helps a lot!
I have been working on a flow and facing this kind of issue:
– I have a sharepoint list in which colleagues can create new items or request
– My flow allows to have each new request approved by my teammates
– Once approved, I want to move this item from the initial list to an “Approved” list. So I need to create the item on “Approved” list and then delete it from initial list.
The problem is that I have a multiple choice field but cannot keep the multiple choices selected when creating the item, the Create Item box doesn’t offer the button Switch to input entire array. Do you know if there is a reason for that ?
I have used your solution on an Update item box and it worked well… I don’t understand the difference here…
Hi Aurelie, maybe the column in the second list, in the SharePoint list settings, doesn’t have the option to “allow multiple”. I recommend double-checking that.
How to make it even better:
To save some steps and avoid using variables use a Select. In your example you could put “Policy Owners” in a Select action’s From parameter. In the Select’s Map parameters type Claims and insert “Policy Owners Claims” on the other side. Then you can use the output from the select action in the Update. This removes a variable and an apply to each.
There is a gif I want to use to express my sheer amazement at how simple and effective this solution is and how its solved an issue that has been plaguing me for too long. Thank you so much!
I’ve a multi select fields wherein I’ve values like CEO;#CFO;#CIO. I need to update these to Chief Executive Officer;#Chief Finance Officer;#Chief Information Officer
How to do it – it’s a massive list!!
Just a simple thank you for solving my problem! Very clearly explained!
Thank You! You are a GOD send!
I have a flow where I want to update multiple items with a multi-select field. I can’t seem to stop it applying to each twice when using the append to array variable action. It updates my multiple choice field using data from all items pulled from the condition. Do you have any suggestions please?
Don’t put the update item action in the loop. Only put the append to an array variable in a loop.
Could you help me with a flow please? I have a flow with a recurrence as the trigger and ‘Get items’ as the action. I have a list of approved suppliers and I have the flow emailing me a table containing a list of the suppliers whose insurance has expired that day and it also emails each supplier requesting the relevant insurance. I then want the flow to update the item with the request date, however one of the fields is a multi choice column and I cannot stop the flow applying to each value and then applying to each insurance value in the multi-select column. it’s driving me mad! have you got any suggestions at all please? I did already leave a question but for some reason it disappeared?
thank you!!
Hi there, I see you posted this a wee while ago but hope you don’t mind me asking a question?
I created a test list with a lookup, and got this to work just fine. But then in my actual list where I want to use it, I have two different lookup columns (Root Cause, Future Outlook Reason), both allowing multiple selections.
I’ve tried to do the same, created the two array variables and appended to each, but I get a “BadGateway” error message when it tries to do the Update Item action. When I look at the detail on the flow run, it is showing separate “Root Cause Id” and “Root Cause” fields and separate “Future Outlook Reason Id” and “Future Outlook Reason” fields.
In these two Id fields, it has entered the item ID, which of course doesn’t make any sense. I wonder if this is the cause of the error and how I can resolve it? Here’s a screenshot: https://imgur.com/zayx82E
Lookup fields will always have an “Id” and “Value”. Updating a lookup field requires the ID of that item.
Thanks for your prompt response, Laura!
So I am getting the IDs in the two Append to array actions.
Here’s a picture showing the two Append to array actions: https://imgur.com/E1pCu1C
Here’s a picture from the test run showing that the Append to array actions are picking up the Ids: https://imgur.com/3hIay6Z
In the Update item action, when the flow is in edit mode, I have ensured the lookup field is set to “input entire array” mode and I have added the variables there. Here’s a picture showing the Update item action: https://imgur.com/y3CO6S5
When I’m looking at the flow in edit mode, those are the only fields I see. But when a flow has run and I look at the run details (that’s the screenshot I provided in my previous comment), that’s when the Update item action shows the extra Id field (where it is entering the Id of the item and I get a ‘bad gateway’ error message).
In the root cause field, try resetting it back to empty, back to before you clicked the toggle, then type just a number in it, and then click the little toggle again, and it should show you the syntax of what the field needs to have in it. Maybe ID is in all caps or something like that?
Just a wee update on my issue – it turned out the problem was with some other lookup fields – I had saved a live list as a template then created it in a new site but hadn’t removed those lookup fields. 🙂
How to retain the current selections in the Lookup field and update (add new) selections. For example, my Lookup field currently has A, B, C selected. Based on another field, I need A, B, C ++ D, E, F selected. Your scenario is great, but it does not keep A, B, C. It only replaces with D, E, F.
You could append an array variable with all the values that are currently in the field, and then append it with the new values.
Thank you @wonderLaura. That helped a lot. Actually Microsoft send me link to your solution. Also it helped me with all other multiselect dropdowns.
great into thanks a lot ! 😀
As usual, my friend, you are awesome and this was very helpful.
Hi, this is great when running this for a single item at a time. But how do you do it when you use a get items and update item within and apply to each? I found when I did it, the array just got bigger and bigger as the workflow ran. Instead of using the array for that single item, it added to the array for each item it ran against, even though the append to array was inside of the apply to each item. I hope this makes sense.
You’d have to set the variable back to blank, inside of the loop.
OMG you are a life saver! Worked perfectly for my multi-person and multi-managed metadata fields. I thought I was going to have to figure out how to use send HTTP request to SharePoint. Thank you! Thank you!
Thank you for this Flow! I think that it is exactly what I need. I want to update a multi value lookup column. However when I want to choose the array variable in the “Update Item” action in Power Automate, I get the error that only integer variables are valid… This results in a failed flow when I run the flow.
Is this maybe due to an update of SP? Can I use an integer variable in a similar manner? Thank you very much!!
Bless your cotton socks – this saved my booty! I went around in circles for days and days and only had one more day left before I had to demo a working flow!
Thank you so much for this solution. Does it also work for multiple choice inputs as I am getting an error in step 2:
Action ‘Initialize_variable’ failed
Error Details: The variable ‘multibusiness’ of type ‘Array’ cannot be initialized or updated with value of type ‘Object’. The variable ‘multibusiness’ only supports values of types ‘Array’.
Automation is:
1. The Trigger is: For a selected file
Which includes…
Add an input: Business Unit Input (List of multi-select options)
2. Next step is: Initialise Variable
Name: multibusiness
Type: Array*
Value: using your technique:
{
“Value”: @{triggerBody()?[‘text_1’]}
}
(Where @{triggerBody()?[‘text_1’]} is the Business Unit Input from the trigger)
3. Get file properties
4. Update file properties
Which includes…
Business Unit(s): multibusiness (selected from the Variable dynamic content)
(Where Business Unit(s) is a multiple choice list column)
* Notes re Variable type “Array”: I also tried “Object” but it just loops indefinitely and I tried “String” and I got no error but the column value did not change in Sharepoint but it placed this value in the Update File Properties field:
{
“Value”: [“Board & Senior Management”]
}
Screenshot of “Array” flow: https://tinyurl.com/24ec6npu
Screenshot of “Array” error: https://tinyurl.com/2ap4bfxh
Thanks so much for making this a lot easier however I have the same problem as others already mentioned.
For a multiple choice field the output is the following:
“Id”: [
{
“@odata.type”: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
“Id”: 5,
“Value”: “My Value”
},
{
…
}
]
How can I parse just the Id Value ?