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:

sharepoint-update-file-properties-action

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:

sharepoint-dont-update-file-properties-like-this

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
flow-initialize-array-variable-people

2.  After initializing the variable, add an action to append to an array variable.
append-to-an-array-variable-people

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.
flow-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.
INPUT-ARRAY-CURLY-BRACKETS-FLOW

5.  Scroll back up to your action called Append to array variable.  Paste this in the Value box.
append-claims-array-variable-flow

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.
flow-append-policy-owner-claims-array

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).
image

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.

35 comments

  • 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.

    Like

  • Thanks for the post.this take care of long running issue I needed to fix with multi-choice columns.

    Like

  • 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 []

    Like

  • Great info!! Thanks!

    Like

  • 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!!

    Like

  • Laura, I echo @Danny Pantoja. This solved a sticky problem for me, too!

    Like

  • Thank you SOOOOO MUCH! This saved me on a project I was working on. 👍

    Like

  • 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

    Like

  • 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”
    ]
    }

    Like

  • Thank you very much for this!!!

    Like

  • 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!

    Like

  • 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

    Like

  • Honey Shor-Posner

    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/

    Like

  • 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…

    Like

    • 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.

      Like

  • 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.

    Like

  • 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!

    Like

  • 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!!

    Like

  • Just a simple thank you for solving my problem! Very clearly explained!

    Like

  • Thank You! You are a GOD send!

    Like

  • 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?

    Like

  • Georgina Haylett

    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!!

    Like

  • Sandra McGechan

    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

    Like

    • Lookup fields will always have an “Id” and “Value”. Updating a lookup field requires the ID of that item.

      Like

    • 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).

      Like

    • 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?

      Like

  • sandramcgechan

    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. 🙂

    Like

  • Thank you @wonderLaura. That helped a lot. Actually Microsoft send me link to your solution. Also it helped me with all other multiselect dropdowns.

    Like

  • great into thanks a lot ! 😀

    Like

Leave a Reply to Siddhartha Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.