Microsoft Flow – populate a multi choice field in SharePoint

7+

How to populate a multi choice field in SharePoint is a frequent question on Microsoft Flow forum. Officially, at the time of this post there is no confirmation that such functionality exists. So I started testing possible ways of doing it and eventually came up with a solution that works and kind of makes sense.

If the Trial and Error part is no interest for you, then feel free to scroll down to the Solution.


TRIAL AND ERROR

1) I created a Color Choices SharePoint list with Colors being a multi choice column. Choices are Red, Blue, Yellow, Pink, Green, and Orange.

Then from the list itself I submitted the 1st item with Red and Yellow values. The goal was to see how data would look like from the Flow perspective. To do that a simple Flow was created that works on selected item and gets data from it.

The result was:

"Colors": [
	{
		"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
		"Id": 0,
		"Value": "Red"
	},
	{
		"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
		"Id": 2,
		"Value": "Yellow"
	}
]

2) Then I created a Flow that would be triggered manually and would create an item with predefined values. I used the same IDs and Values from the step 1.

Please note that in every Create Item step a Switch to input entire array option must be selected for that very multi choice field.

It worked:

3) The next step would be to try IDs with “-1” value and let the Flow decide what to do with it. I saw that method somewhere once, not sure if it’s legit.

It worked as well:

4) The last step was to try to remove IDs at all. On purpose I also switched the sequence of populated colors.

It still worked:

From the previous attempts in the past I knew that just trying to add an array of values to a choice field would not work. It just doesn’t for some reason but clearly, having done these 4 steps above, adding an array with {“Value”: “…”} values works.


SOLUTION

The example that I have gets data from Color Choices (Microsoft Forms) form and saves it into Color Choices SharePoint list. The values in the form are multi choice type and are the same as in the SharePoint list.

Briefly, a Flow that works needs to have these core steps:

  • initialize an array variable
  • remove extra symbols from Microsoft Forms string output
  • split the previous line into an array (not the same as step 1)
  • append to array variable using a formula
  • create a SharePoint list item

Initialize an array variable:

Remove extra symbols from MS Forms string output:

replace(replace(replace(body('Get_response_details')?['r5beb9c2e9e4849129d94468e3db92b7a'],'","','|'), '["',''), '"]','')

Split the previous compose into an array:

split(outputs('Format_form_output'),'|')

Append to array variable using a formula:

Create an item:

Please note that in a Create Item step a Switch to input entire array option must be selected for that very multi choice field.

The result is what we need:

It gets much simpler if you don’t work with Microsoft Forms and already have an array of values. Then the steps are just initializing an array and appending to array variable using a formula. That’s it!

7+

This Post Has 41 Comments

  1. Thank you so much. I’ve been struggling with updating a Multi-choice select field from MS Forms into a file din SharePoint with values that are slightly different in the Form and the SharePoint list, and have parentheses and commas in the values. I was able to use this method to construct the right format for input to my list field

    0
    1. You are welcome!

      0
  2. In the replace(replace(replace(body(‘Get_response_details’)?[‘r5beb9c2e9e4849129d94468e3db92b7a’],'”,”‘,’|’), ‘[“‘,”), ‘”]’,”)
    What is r5beb9c2e9e4849129d94468e3db92b7a?
    Is this the form ID?
    Where do you get this from?

    0
    1. Hi,
      It is the name of a field within Microsoft Forms. Or at least it is how Microsoft Flow sees it.
      A few examples:
      This is “Comments” field – body(‘Get_response_details’)?[‘rf2a4008595404fd095fe300aa70ebd5d’]
      This is a “Contact Information” field – body(‘Get_response_details’)?[‘r478a024b3bbc46e08074f2b70dab7b30’]
      This is “Responder’s Email” field – body(‘Get_response_details’)?[‘responder’]
      This is “Submission time” field – body(‘Get_response_details’)?[‘submitDate’]
      So system fields have friendly names. All custom fields in your Microsoft Form have these unique ids.

      0
  3. Thanks for sharing. I am trying to replicate this for a solution I am working on, and It is erroring on the format output step.

    My Syntax is:
    replace(replace(replace(body(‘Get_response_details’)?[‘r5beb9c2e9e4849129d94468e3db92b7a’],'”,”‘,’|’), ‘[“‘,”), ‘”]’,”)

    any thoughts or suggestions to what I am doing wrong?

    0
    1. Hi Duane,
      It looks like you are using the same ID (‘r5beb9c2e9e4849129d94468e3db92b7a’) as I had in my example. That ID is for my “Select your color” field in my Microsoft Forms form and is unique for everyone. To get a list of all field names (including your custom ones) take a look at the Get Response Details flow action – it outputs them all. If any other questions, feel free to ask.
      Thanks!

      0
  4. Hi, I have been struggling with Form multi choices flowing into a SP list as well. When I try and follow the instructions above I can’t even set up the array variable, it won’t come up with any selections for the ‘Name’, the drop down is blank? Any quick way to fix this?

    0
    1. Hi Trevor,
      Sorry for the late reply. We all know MS Flows can be touchy, e.g. you set up a variable action but it doesn’t appear anywhere in the suggestions. I’ve seen that myself. You can try and recreate your flow from scratch. Other way to select that variable is manually type a formula in the Expression window. Without any screenshots it’s hard to say where you are stuck. If you are still struggling with it, please reply to the email I’m about to send to you. Thanks!

      0
  5. Please notice as of this date, your code at “Append to array variable using a formula:” required the ” to be removed from around the Current Item object. like this

    {
    “Value”: Current item
    }

    0
    1. Hi Amos,
      I’ve just checked it: adjusted the flow forth and back, and it’s still working just fine with the quotes.
      Also tried without the quotes, it’s working too.

      0
  6. Hi, I followed you example. The flow runs perfect. My array is created:
    [
    {
    “value”: “Trip and on-site logistics”
    },
    {
    “value”: “English/Spanish translation”
    },
    {
    “value”: “Patient relations”
    }
    ]

    Then output seems correct:
    [
    {
    “@odata.type”: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
    “Id”: 1,
    “Value”: “Trip and on-site logistics”
    },
    {
    “@odata.type”: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
    “Id”: 3,
    “Value”: “English/Spanish translation”
    },
    {
    “@odata.type”: “#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference”,
    “Id”: 5,
    “Value”: “Patient relations”
    }
    ]

    HOWEVER, when I take a look in SharePoint entry form, the string shows like this:

    https://www.screencast.com/t/oR6f2Lg9ODSH

    What did i miss?

    0
  7. I have double checked everything. But the weirdest thing just happened.

    I removed the formula, and selected 2 values in MS flow. Just see what would happen.

    After I run the flow I get the same result. BUT three values are selected. It appears that is CACHING the results from previous runs.

    [“Trip and on-site logistics\”}”,”{\”Value\”:\”English/Spanish translation\”}”,”{\”Value\”:\”Patient relations”]

    0
    1. Hi Jose,
      So, is it still not working the way it should?
      I do recall such a behavior (having a previous run) in some cases.
      I believe it was happening when I had several runs in my first Apply_To_Each (https://365basics.com/wp-content/uploads/2018/12/20181202_9.png). If that’s the case, if you are not using Microsoft Forms in your example, if you do have several runs within the first Apply_To_Each, then you can try to nullify your variable at the end of each(!) Apply_To_Each. It has to be right after Create Item but within the Apply_To_Each.

      0
  8. Thanks for this writeup, it got me on the path to success.
    I think I found a slight improvement: rather than use the horrendous “replace(replace(replace” string function, I used JSON parsing which should cope better if there were any odd characters in the values.

    My approach was:

    * create an intermediate string variable which looks like JSON and plug in the arrays as the value of the properties. So it looks something like this:
    {
    myPropertyName: [Choose field from the Forms form here]
    }
    … so when run, it will get populated like this:
    {
    myPropertyName: [“FormOption1”, “FormOption2”]
    }

    * Use the “Parse JSON” action, with a schema like this:
    {
    “type”: “object”,
    “properties”: {
    “myPropertyName”: {
    “type”: “array”,
    “items”: {
    “type”: “string”
    }
    }
    }
    }
    … so that the Flow designer now has a valid array of strings to work with. (That’s the improvement, the rest below here is the same as what you had, but included for completeness)

    * then use “Append to array variable” to an array variable as per your example to turn that simple array into an array of entries that each look like:
    {
    “value”: “FormOption1”
    }

    * then use that array variable in the sharepoint list field.

    0
    1. Hi Darren,
      Thanks for your input and I’m glad my post helped a bit.
      I’ll surely take a look at your JSON approach as I haven’t really had a chance to use “Parse JSON” much before – just didn’t have any need yet.

      0
  9. This is really brilliant. Complex but brilliant. I got it work after taking out the ” around the current item in the Append to array variable. It has solved a business problem so thanks very much.

    0
    1. Thanks Rob!
      Yeah, I don’t know why using a “” didn’t work. The other guy reported the same thing; however, when I checked it myself again with quotes, it worked just fine. Weird…
      I’m glad it worked for you nonetheless although with some adjustments.

      0
  10. Hi Pavel and thanks for this great post. I tried to implement this in my “Form->Flow->SP list” but I have this error :
    OpenApiOperationParameterValidationFailed. The ‘inputs.parameters’ of workflow operation ‘Create_item_test2’ of type ‘OpenApiConnection’ is not valid. Error details: The API operation does not contain a definition for parameter ‘item/TEST’

    I created an array with a default set of simple values (with a matching Sharepoint list, of course) to remove the form from the equation, but the same error appears.
    The Sharepoint list’s multivalued field is set with default parameters, and adding multiple values for the field actually works using “detail inputs for array item” (like in your “trial and error” part), but just not from an array using “input entire array”. Any idea ?

    Any help would be highly apreciated !

    0
    1. Hi Phil,
      I’ve just made a test from scratch and it worked without issues. I tried to break it in some places but never got the same error as you did. I’ve also sent you screenshots of a test flow and test list, please give it a try. Thanks!

      0
    2. Phil,

      I’m running into the same error message as you. Were you able to get past it and if so how?

      Thanks,
      Hassan

      0
  11. Forgive me I am a newbie and dumb! My output is not doing what it is supposed to. It is taking every multiple choice option I choose, and appending it to a new line in the sharepoint. So in this example I just did, I chose 4 options (425, 2603, 7157, and 7090/7091) This is the output in sharepoint.

    test 425
    test 425, 2603
    test 425,2603,7157
    test 425, 2603, 7157, 7090/7091

    0
  12. This has been really helpful; one thing that has caused me trouble however is that my multi-choice field allows ‘Fill-in’ responses and when I tried to create a list item, I got the below error:

    “message”: “Error converting value \”Other Text\” to type ‘Microsoft.SharePoint.Connector.SPListExpandedReference’. Path ‘[0]’, line 2, position 15….

    Any ideas how I can get round this? Google doesn’t appear to be my friend on this one!

    0
    1. Hi Billy,
      Good question, let me try it on my side and I will let you know.

      0
      1. Turns out I wasn’t following your guidance closely enough… In the append to array part I just had to explicitly say { “Value” : “[Current Item]”} and it resolved itself… I was trying to do it without that and naturally it fell apart!

        Thanks for the prompt response and really useful article!

        0
        1. Good to hear and thank you for checking my site out!

          0
  13. Thanks for this post. I’ll openly admit I’m pretty bitter about SharePoint/Flow stuff and lack of what I would consider to be basic features. This wasn’t too complicated and lowered my stress level quite a bit today.

    0
    1. Thank you for visiting my site, Matt!
      If any questions, don’t hesitate to contact me, I’m always willing to help.

      0
  14. Hi there,

    What action are you using for the Format Form Output step. I’m expecting to see it in the Control Actions but nothing is there. I’m guessing that its the Compose Step?

    0
    1. Hi Steven,
      Yes, it is a Compose step. Sometimes I use them for debugging, other times to “simplify” an action down the road (rather than combining two pieces of codes together).

      0
      1. Hi Pavel,

        Took me a while but I got there. This is a great solution to have stumbled across, so thanks for taking the time to post it for us.

        Cheers
        Steven

        1+
  15. Thanks for creating this tutorial as it certainly will fill a need for us. I am having some trouble getting it to work though.

    I tried initially with my own list and form, but after failing, I tried just recreating yours from the example. On both I am getting the same error message, similar to what another user saw.

    OpenApiOperationParameterValidationFailed. The ‘inputs.parameters’ of workflow operation ‘Create_item’ of type ‘OpenApiConnection’ is not valid. Error details: The API operation does not contain a definition for parameter ‘item/Select_x0020_Your_x0020_Colors’.

    Any insight you can provide would be appreciated, thanks in advance!

    Hassan

    0
    1. Hi Hassan,
      That other person was able to fix that issue by recreating a flow from scratch and carefully selecting all values. I know it doesn’t sound helpful.
      I’m about to send you a very simple flow that doesn’t include Forms at all; however, it has a mechanism that populates multi-choice fields. Let’s start with that one. Please reply to my email if needed.

      0
      1. I am getting the same error “OpenApiOperationParameterValidationFailed. The ‘inputs.parameters’ of workflow operation ‘Create_item’ of type ‘OpenApiConnection’ is not valid. Error details: The API operation does not contain a definition for parameter ‘item/color'”

        I have tried recreating my flows. I created a new flow that is just a button push with an array variable that I initialize with my options, and that fails too. If I hardcode the options in the Create Item with the same content from the array it works with no problem. If I compare the Inputs on the Create Item when hardcoding or using the array they look *exactly* the same. Very frustrating

        0
        1. Hey Brian,
          What is your output of the Append to array variable?

          0
          1. The output from the Append to array shows the following:

            [
            {
            “Value”: “Yellow”
            },
            {
            “Value”: “Red”
            }
            ]

            0
  16. Hi Pavel,

    Very thankful for your post. I actually used this to get an initial version of the flow working.

    Weird thing, after rerunning the code after getting it to work, I get the same error as Brian and Hassan. I’ve also tried recreating the flow from scratch and I still get the same error:

    The ‘inputs.parameters’ of workflow operation ‘Create_item’ of type ‘OpenApiConnection’ is not valid. Error details: The API operation does not contain a definition for parameter ‘item/Color’.

    Really don’t know what happened there 🙁

    0
    1. Hi Sheryl,
      The good news, both Brian and Hassan have their flows working now.
      The bad news, the flows fixed themselves and started working on their own.
      What we noticed – when we started comparing the code (Peek the Code) of the freshly created “Create Item” actions, those were different in our tenants. So it is very possible Microsoft has been updating forth and back some code.

      0
  17. Hi Pavel,
    Thank you for your post. It was a breakthrough for me.
    In my case, I have to build a “Contact” object in salesforce from every form received in Sharepoint.
    It worked well except the multichoice fields that had to be transformed from the form to the new object.
    Only after I saw your post I succeeded to complete the job.
    Thanks

    0
    1. Hi Avinoam,
      Thank you for checking out my blog!

      0
  18. Hi Pavel,

    Thanks for this post! However, I am having trouble when I am putting this into a SharePoint list. It is only showing my first response and not the other ones selected in Microsoft Form. Any idea on what I am doing wrong or missing?

    0
    1. Hi Hana,
      I would think you’ve done something different in the Apply_to_each_2, but it’s hard to guess without seeing it. So I’m sending you an email, feel free to reply to it with more details.
      Thanks!

      0

Leave a Reply

Close Menu