Microsoft Flow – populate a multi choice field in SharePoint

  • Post Category:Flow
  • Post Comments:57 Comments
8+

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!

8+

This Post Has 57 Comments

  1. Ciaran Fletcher

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

      You are welcome!

      0
  2. Christina

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

      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
      1. Tiffany

        Hi, how do I obtain the unique id for the field that I am trying to use?

        0
        1. Pavel Bludov

          Hi Tiffany,
          Assuming you are talking about getting data from Microsoft Forms, when creating a Flow, as soon as you select your field from Dynamic Content (it should be in a Get Response Details category), you will be able to see that ID by hovering your mouse over it.
          If needed I can provide another screenshot. Let me know.
          Thanks!

          0
  3. Duane Alleman

    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. Pavel Bludov

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

    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. Pavel Bludov

      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. Amos Deering

    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. Pavel Bludov

      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. Jose Zaldivar

    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. Jose Zaldivar

    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. Pavel Bludov

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

    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. Pavel Bludov

      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. Rob Elliott

    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. Pavel Bludov

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

    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. Pavel Bludov

      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. Hassan Al-Rubaie

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

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

    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. Pavel Bludov

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

      0
      1. Billy

        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. Pavel Bludov

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

          0
  13. Matt

    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. Pavel Bludov

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

      0
  14. Steven Andrews

    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. Pavel Bludov

      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. Steven Andrews

        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. Hassan Al-Rubaie

    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. Pavel Bludov

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

        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. Pavel Bludov

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

          0
          1. Brian

            The output from the Append to array shows the following:

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

            0
  16. Sheryl

    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. Pavel Bludov

      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. Avinoam Faltin

    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. Pavel Bludov

      Hi Avinoam,
      Thank you for checking out my blog!

      0
  18. Hana

    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. Pavel Bludov

      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
  19. Heather Schacht

    Hi Pavel,

    I’m having issues with the flow in that it runs successfully but the value comes up as blank in the SharePoint List. Your note about switch to the entire array may be the issue because right now the icon next to the Create Item field input says ‘Switch to detail input for array item” but I am not sure how to change it.

    Thanks,
    Heather

    0
    1. Pavel Bludov

      Hi Heather,
      Yes, that option is important to select. The easiest way to redo it is to delete your “Create an item” step and re-create it from scratch. If no luck then feel free to send me an email with screenshots as a reply to my message that I’m about to send to you.
      Thanks!

      0
  20. Aylun

    Hi Pavel, I’ve followed your instructions, however I cant save the file due to an Error: Flow save failed with code ‘WorkflowRunActionInputsInvalidProperty’ and message ‘The inputs of workflow run action ‘Append_to_array_variable’ of type ‘AppendToArrayVariable’ are not valid. The provided value of ‘[
    {
    “Value”: “@items(‘Apply_to_each_2′)”
    }
    ]’ of type ‘Array’ must be a primitive type, or a valid template language expression.’.
    Can you possibly help me out with that?

    0
    1. Aylun

      Okay Nevermind found the issue๐Ÿ˜…

      1+
      1. Aylun

        Hey sorry for bothering. However there seems to be an error everytime in the apply to each 2 activity: ExpressionEvaluationFailed. The execution of template action ‘Apply_to_each_2’ failed: the result of the evaluation of ‘foreach’ expression ‘@outputs(‘Compose’)’ is of type ‘String’. The result must be a valid array.
        Any Idea how i might solve this?

        0
        1. Pavel Bludov

          Hi Aylun,
          Replying to your through an email as I might need some screenshots.
          Thanks!

          0
  21. Martin

    Hi Pavel do I have to use a different code [‘r5beb9c2e9e4849129d94468e3db92b7a’] like this one for each multi choices fields that I have? Is there any other way?

    0
    1. Pavel Bludov

      Hello Martin,
      It’s the ID of one the fields in my Microsoft Form (forms.office.com). So if you have several fields like this, then it would be different ID for each of them.
      Please note, if your data is not coming from a Microsoft Form, then you will not have such an ID at all.
      Hope it helps.

      0
  22. Martin

    Thanks! Yes, my data came from a Microsoft Form. This is what I understand. Feel free to inform me if you learn that a new solution allows to do it for several fields in one operation. At least, your solution is very useful.

    0
  23. Fernando

    Hi Pavel,

    Thanks a lot for this post it should be very useful (if I can make it work).

    It’s strange for me because my flow is doing OK in the difficult parts (formulas to replace characters, creating the array, etc.) but it gets stuck in the “Create item” action. It doesn’t return an error it just doesn’t end that part and I have to cancel the run.

    The only difference in my flow is that I don’t have the top “apply to each” because it does not allow me to select the List of responses of the form; so I just have the “Get response details” with the List of responses as output.

    Do you have any idea of what might be the problem with my flow?

    Thanks!

    0
    1. Pavel Bludov

      Hi Fernando,
      Looks like you are right, MS Team might changed something. I gotta double check with a new test flow.
      Thanks!

      0
    2. Pavel Bludov

      Ok, tested it, everything works just fine without the top “Apply to each”.
      The only thing I noticed Microsoft Team reverted the icons and now in the “Create Item” step the icon should look like a table when a correct setting is selected.

      0
  24. Adrian

    Hi! I cam across your solution! This is exactly what I’m looking for!

    I’m getting a strange issue though – for the append to array variable – this is what’s outputting in the value

    {
    “Value”: “{\”webhookId\”:\”9cf9e727-82ee-4973-bef2-29cf0ab26304\”,\”eventType\”:\”ResponseAdded\”,\”resourceData\”:{\”formId\”:\”q4U3vlLC1UGmIJdzuvAw7OqLNxS0LVhHslAu2pwq3ZRUMVhFTEFLWDZPUjExSEtPR09UUFBQN1JSMC4u\”,\”responseId\”:7},\”eventTime\”:\”2020-05-12T23:03:44.9588540Z\”}”
    }

    so them my array is just that..

    any ideas?

    0
    1. Pavel Bludov

      Hi Adrian,
      You must be appending a wrong value/something else. I will need to see more info so I’m about to send you an email.
      Thanks!

      0

Leave a Reply