Search code examples
power-automatesharepoint-list

How do we insert an array with multiple values into a sharepoint choice column that allows multiple selections?

We have sharepoint list with a column having a choice type. It also allows multiple selections.

How do we insert from Power Automate an array, with multiple values, into this sharepoint column?

Solution

  • Summary

    There are two ways to insert multiple values from a single element into a sharepoint list column:

    1. Sharepoint list column has “Choice” type. Then in Power Automate use a Select action where the value side of the mapping is the expression item().
    2. Sharepoint list column has “Single line of text”. Then in Power Automate insert the JSON parsed field directly, rather than have to go via a select.

    Sharepoint Choice column with Power Automate Select

    Sharepoint List

    • Create a target column:

      • Type: Choice

      • Allow multiple selections: Yes

      • Can add values manually: Ticked {Currently irrelevant. Can be ticked or unticked and values from the flow not found in the sharepoint choice list will be added. However, recommended "ticked" in case this changes.}

        enter image description here

    Power Automate Flow

    • Create an array. E.g. as an array type in a Parse JSON action.

      enter image description here

    • Create a Select action.

      • From: Choose the array (e.g. if coming from a Parse Json directly choose the element that is multiselect).

      • Map:

        • "value": item() {Expression, not Dynamic Values}

          enter image description here

        Be warned when returning to the flow the UI might deceptively render the item() as a Parse JSON field. Behold the Select-Legislation item() entry below ...

        enter image description here

    • In your Flow's sharepoint target field, insert "Output" from the select. Power Automate Flow > Sharepoint Create Item > Sharepoint Choice Type Column ...

      • "Switch to input entire array"

        enter image description here

      • Dynamic Content > Output from the prior Select.

        enter image description here

    • Save your flow and test.

    Sharepoint “Single line of text” with JSON parse field directly inserted

    If, in the Sharepoint List, one chooses a column type of "Single line of text", rather than "Choice", then you can insert multiple selects straight into the column as an array of strings.(LocalLandServicesAffected is a "Single line of text"). ...

    enter image description here

     ... and this simplifies the flow in that you can just insert the JSON parsed field directly, rather than have to go via a select ...

    enter image description here

    However, there might be consequences, between the two design patterns (List "Single line of text" V "Choice"), in terms of reporting (e.g. Using PowerBI from the List). I suspect going with Choice is the more robust path. But that might create, in principle, a much higher maintenance burden (one could otherwise just change input element choices with with nothing else to do).