Updating and Retaining Existing Multi-Choice Column Values using Power Automate

[omontoya]

hi all, long-time listener, first-time caller 🙄

I'm new to power automate and working on a project for a not-for-profit organisation in my spare time. I have the majority of the solution components in place; lists, flows, form etc, but there is one flow that is doing my head in. Conceptually i believe I understand what needs to done, but i'm having problems with the execution.

The basic requirement is to be able to append to a multi-choice column with a a new value (type of code of conduct completed) which i declare as variable in the appropriate form, see Setup (3) below.

Setup:

1. I have a person list which has a colum for the volunteer's name and a column called "Code of Conduct Completed". There are 4 types of code of conducts that a volunteer can complete, for this example, let's say; "Code 1", "Code 2", "Code 3" and "Code 4". 

2. I have an MS Form for each Code of Conduct which the volunteer completes.

3. I have a flow for each Code of Conduct. In each flow, I declare a varible for the specific code of conduct, i,e "Code 1" or "Code 2", etc that will be used to append to the Multi-Choice column in the Person List (e.g varNewCode)

Scenario:

1. Person 1 has already completed Code 1 and Code 2 and these values are already stored in the multi-choice column. 

2. Person 1 completes a new code of conduct (e.g Code 3) and they complete an MS Form in which they add their fullname. 

3. The outcome sought, is that the "Code of Conduct Complete" column for Person 1, now shows Code 1, Code 2 AND Code 3.

What I understand needs to be done

1. When the MS Form is submitted, i retrieve the appropriate row from the Person List by filtering the "get Items" action using the fullname from the MS Form

2. I'm supposed to extract the current values of the "Code of Conduct" column for the ID that's been filtered in 1 above into a new array variable (eg. varCodes)

3. I'm supposed append the variable declared, varNewCode to varCodes

4. Using "Update Item" action, i'm suppose use the updated varCodes varible to my "Code of Conducts Completed" column.

I'm good with Step 1 above, but the wheels fall off from Step 2 to Step 4. I've reviewed lots of different tutorials on Youtube, that all get me part of the answers, but i can't seem to pull it all together.

You assistance would be much appreciated with included screen shots of how to achieve this outcome. Thanks in advance. 

[v-jefferni · Accepted solution]

Hi @omontoya ,

You need to initialize the variable varNewCode as an Objecte, and set it as below:

{"Value": "Code 3"}

Within the loop Apply to each, you need to append the object to the existing array. The array must have the appropriate format that has a single Value column. So, we need to use Select to format the Choices column value and set the array variable. With the array variable prepared, now we can use Append to array variable action to add the object to the array. 

I've create the flow for your reference. Here I used a hardcoded Choice 3 as input value as in my SP choices column they are default Choices.

Details in edit mode:

Hope this helps.

Best regards,

[omontoya]

amazing! thank you so much @v-jefferni for taking the time. Worked like a charm. The hours i spent trying to work it out! 😂