Answered

Update SQL table from Sharepoint list with a multi-select combobox

(2) Share

Report

Posted on by Brian_P

I have a flow that transfers a Sharepoint entry (from a powerapp form) to a SQL table. I need to use a multiselect box for "Region" and only the most recent selection is getting updated from the sharepoint list to the sql table. I googled the world about arrays but the approach below seems to be outdated?

https://wonderlaura.com/2020/09/01/flow-update-multi-select-column/

Categories:

Using flows

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Michael E. Gernaey 53,932 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @BP-02102142-0

This is because you are looping through the "records" in Region, updating the same row 1-M times, with the last one always being the last selected Region.

If you want to store them do this

1. leave the loop for Region but remove the actions you added to that loop

2. Concat them all together Comma separated in your flow

-Create a string Variable call it RunRegions

-add an Apply to each but only for Region don't do anything else

-In your Apply to each use Append to String action and do the below action

------concat(item(), ',')

3. lastly lets remove the final , that will be at the end.

--substring(variables('RunRegions'), 0, sub(length(variables('RunRegions')), 2))

4. Now after your Region Apply to each in the Region field, but within the Outter Apply to each, put the name of your string Variable

were the variable name is RunRegions

And you will have all 3 in there comma separated

If this resolves your issue please mark the answer as such and maybe a like :-)

Cheers

Was this reply helpful? Yes No
Brian_P 62 on at

Like (0)

Report
Copy link

Link copied!
Thanks for the swift reply @Michael E. Gernaey, I'm getting close! Here are my two results:

get an array error: The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@variables('RunRegions')' is of type 'String'. The result must be a valid array.

change the variable to a Type: Array, but then both the SP list and SQL table show [].

Any thoughts here? I'm sure I missed one simple step.

and then....

Was this reply helpful? Yes No
Suggested answer

Michael E. Gernaey 53,932 Moderator on at

Like (0)

Report
Copy link

Link copied!

HI @BP-02102142-0

No you accidentally did hehe something i didnt say.

So lets go through what I said, so that I make sure its what lol i want.

Let me state so that its clear. You have an Array, you need to turn it into a string to go into SQL. So you have to pre-loop the Region, create the string, THEN use that string in your Flow.

1. leave the loop for Region but remove the actions you added to that loop

You should have left Region there and remove Update row V2 as you haven't created the string yet.

2. Concat them all together Comma separated in your flow

-Create a string Variable call it RunRegions (perfect)

-add an Apply to each but only for Region don't do anything else <== here I said for Region (not RunRegion)

-In your Apply to each use Append to String action and do the below action

------concat(item(), ',')

3. lastly lets remove the final , that will be at the end.

--substring(variables('RunRegions'), 0, sub(length(variables('RunRegions')), 2))

4. Now after your Region Apply to each in the Region field, but within the Outter Apply to each, put the name of your string Variable

were the variable name is RunRegions

So it will look like this

So close hehe, let me know if you need more help

Was this reply helpful? Yes No
Brian_P 62 on at

Like (0)

Report
Copy link

Link copied!

10 weeks later I'm revisiting my failed attempt @Michael... and am failing at the Trim step. I tried putting the Region field in the Initialize Variable step but it still breaks. I've tried using Region Value and Region from the Get Item option but no dice.

Action 'Trim' failed: Unable to process template language expressions in action 'Trim' inputs at line '0' and column '0': 'The template language function 'substring' parameter is out of range: 'start index' must be non-negative integer and should be less than the length of the string. Please see https://aka.ms/logicexpressions#substring for usage details.'.

Like you said, I'm so close.

Was this reply helpful? Yes No
Verified answer

WillPage 2,307 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

This is all very complicated, unnecessarily so. All you need to do to turn a multi-selectable choice field into a string in Power Automate is two actions, Select and Join.

Firstly, add a Select action and the put the choice column as its input

In the Map action, press the icon on the right to switch the Select action from Key-Value mode to JSON input
Now, in the input box, add the following expression: item()?['Value']

Next action is a Join. The input is the output of your select and the thing to join on should be a comma-space or a carriage return or whatever you want to do to separate the values in the destination.

Then finally sink that into SQL. No loops, no string manipulation. Just two actions from Data operations.

Was this reply helpful? Yes No
Brian_P 62 on at

Like (1)

Report
Copy link

Link copied!

@WillPage going all caps here, THANK YOU SO MUCH. A colleague had a similar suggestion but it still involved the loop that would inevitably fail. For those curious, this is the set-up:

Was this reply helpful? Yes No