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.

Problem

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:

Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow - OnPrem MultiValue Field                   Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow - SPO MultiValue In Flow

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

Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow - MultiValue 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 “[“

Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow - Initialise String Array

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:

Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow - Build String Array

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:

“substring(variables(‘multiChoiceFieldValue’),0,sub(length(variables(‘multiChoiceFieldValue’)),1))”  This formula is creating a substring of the array we built and removing the final character (the comma in this case).
The final thing we need to do is close up the variable with a closing bracket ‘]’.
Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow - Add Trailing Bracket

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.

Inserting Values into Multi-Value SharePoint On-Prem Choice Fields with Microsoft Flow - Update MultiValue List Item

And that’s it.  You can now update a multi-value choice from a Flow.

 

Thanks for reading!