So one would think this is a no brainer as you can now read and update multi-value fields in SharePoint Online. In August of 2018, Microsoft announced that multi-value choice fields can now be read from and updated (see this link). What wasn’t clear is that it doesn’t allow you to do this with SharePoint On-Prem environments via the data gateway. I have received confirmation from Microsoft that this is, in fact, a limitation for SharePoint 2013 and 2016 on-prem environments, but shouldn’t be for SharePoint 2019. I haven’t yet tested this claim but will update if the information is untrue. So in this post, I will be inserting values in multi-value SharePoint on-prem choice fields with Microsoft Flow. I would like to send a thank you to Rob Windsor who got me started on the path to determine the solution.
So I had a client request the other day to build a flow that would update an on-prem list with items from a SharePoint Online list. There were some other requirements not pertinent to this post, but basically a pretty straight-forward process. That was until I got to the multi-value choice field and realized I didn’t have the option to insert the value into my “Insert an item” action in Microsoft Flow.
Multi-Value Field On-Prem: Multi-Value Field SharePoint Online:
So after reaching out for confirmation that it didn’t work with the version of SharePoint my client was using (SP2013) I received a suggestion from Rob to utilize REST to do it. Important note: you can’t do this during the insert. It has to be done after the item has been created (unless you want to build the entire item with REST which I didn’t).
Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow
So let’s start with a list containing a multi-value field
Next, from Flow, we will insert a new item into the list, but don’t worry about the multi-value field (we’ll deal with that later). We first need to create the item. Then we build a string array. This is because the input for a multivalue field takes the form “[Value1,Value2,Value3…]” from the REST API. To accommodate this I initialize a string variable with a “[“
Then I loop through the source in order to build the string array. In my example below it is coming from a Multi-Value field in SharePoint:
So doing this adds a trailing comma to the array which we need to remove. We can do this with a substring in the flow. The substring has the form:
The final step is to update the item you just created. To do so you need to utilize the Patch Method. When you added the new item, SharePoint returns the ID of the item. You can then use this ID to update the item in question.
And that’s it. You can now update a multi-value choice from a Flow.
Thanks for reading!
Nice work and well documented for the rest of us.