Power Automate Workflow - SharePoint list concatenate multi select column values into a single line text column

[axray]

I found a community answer that supposedly worked for someone... but it isn't working for me... 

I have a few SharePoint list columns that I am listing in a new single line text column. I can't include the multi select column values, so I am trying to run a flow to concatenate the multi select values into a single line text column, then use that column in the single line text column that includes the various fields (that was a mouthful, I hope I'm making sense - I explain more below).

End game: I want the values selected for the following fields to show up in a new field ("RootCause1") in a concatenated string:

1_Root Cause Department; 1_Root Cause Category; 1_Failure Mode Category; 1_Countermeasure; 1_Benefit

I already have it set up to concatenate 4/5 fields above. The 5th field isn't an option because it's a multi select choice column. So, I tried creating a flow to concatenate the selected values from the multi select choice column "1_Root Cause Department" into a new, single line text field ("RCDept1"). I found a flow setup on this forum that was marked as a solution... so I set mine up that way, but it isn't working right. Can anyone tell me if I did something wrong... or is there a better way to accomplish this?

When this flow runs, this is what shows up:

Selected values in the multi select choice column:

  

The flow writes this to the new, single line text field:

Thank you!

[creativeopinion]

@axray 

The multi-choice column requires an array with a key, not a string. 

You will need to convert your string of values into an array. I've used a Compose action and the split() function.

split([yourvariable],'; ')

Then use a Select action to create your array with a key. The Outputs of the select action will look like this:

You can use the select action in your Update Item or Create Item action. 

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

[axray]

Sorry, I'm lost. I tried adding the coding you showed above, but when I type in split([varRCDept1],'; ') and hit OK, I get an error message: "The expression is invalid." I also don't know what this means "Then use a Select action to create your array with a key." Or this "You can use the select action in your Update Item or Create Item action." The image I shared is the flow in its entirety. There is no "Update Item" or "Create Item."

[creativeopinion]

No worries. I'll outline it step by step.

1. In the Compose action, you need to insert an expression. To do this you need to click on the Expression tab.
2. .Start off by typing in the split() function. The split() function takes two strings. The first one is the string of text, in your case it would be your variable. 
   
3. Put your cursor in between the parentheses of the function, click on the Dynamic content tab and insert the variable. 
   

   Your expression should look like this:

   
4. Next you need to add a comma and single quotes with your separator. In your case it's a semi-colon and a space.
   
   To do this, put your cursor right before the closing parentheses. Then enter a comma and your separator between single quotes. So now your expression should look like this:
   
5. Then you need to add a Select action. Insert the Outputs from the Compose action above into the From field of the Select action. 
   
6. As mentioned in my previous post, SharePoint requires the values of a multi-choice column to be an array with a key. The Select action will help you do this. 
   
   In the Map fields you need to insert Value (just plain text into the key column). In the Value column you need an expression. In order to access the content from your array, you need to use the item() function. 
   
7. I'm not sure if you are using an Update Item or Create Item action, it doesn't matter which one you use.. but in that action you are going to use the Select action outputs. 
   
   In your multi-choice column, select this icon to switch to input entire array.
   
8. Insert the outputs from the Select action.
   

I have a TikTok tutorial of how to do this here: https://www.tiktok.com/@acreativeopinion/video/7256125830869290245

Hope this helps!

[axray]

OK, so that was super helpful... thank you. I am with you until I get to step 7... there are multiple fields for my one multi select choice field listed... do I put the Select output into each one? What do I put in the single line text column I created to put the concatenated list of selections (so I can use that column to add to my other concatenated list)?

[creativeopinion]

I misunderstood your original ask. I thought you were trying to take your multi-select choice form field and put it into a SharePoint multi-choice column field. 

I'm a bit confused. How are you mapping a single multi-select choice form field to multiple multi-choice column fields in SharePoint.

Usually if you have a multi-choice form field in MS Forms, you want to map those values to a single multi-choice column field. I might be missing something... 

[axray]

Hmmm - so I want to concatenate the selected values from a multi select choice SharePoint column, then write that string of concatenated text into a different, single line text SharePoint column.

The goal is to then be able to select that single line text SharePoint column in my calculated (concatenated) SharePoint column (right now I can't select the column I want because it is multi select).

[creativeopinion]

Can you upload a screenshot of the form field as well as the SharePoint Columns with what it should look like. 

I'm finding it a bit hard to understand and picture right now.

[axray]

This is the multi select choice column on my form:

I want whatever choices are selected in that column to be concatenated, then that concatenated string of text to be written to a single line of text column (not on my form - only using it for SharePoint view purposes).

Once that concatenated string of text is in that text column, I will be able to select that text column in a different, calculated column that has a concatenate formula in it (field called "RootCause1").  

End game: I want the values selected for each of the following fields on my form to show up in a new, calculated field ("RootCause1") in a concatenated string:

1_Root Cause Department; 1_Root Cause Category; 1_Failure Mode Category; 1_Countermeasure; 1_Benefit

Right now, the 1_Root Cause Department is the multi select choice column that I can't select (not shown in Insert Column) in my calculated column because it is multi select. I need the values selected in that column to show up in the calculated concatenated string of text (along with the other 4 field values above).

[creativeopinion]

I'm struggling to understand the reason that you need a calculated column. From your screenshot of the form, it looks like you are using a PowerApp is that correct? I thought you were using a MS Form. 

Can you also explain why you aren't able to take the multi-choice selections from your form and patch those selections directly into a SharePoint column. 

[axray]

The manager wanted a "table" view - so I had to create a ton of combo box people and date pickers to make it look like a table... there is currently no Patch coding within my Power Apps form and I'd like to keep it that way. We just need a concatenated view of each row from the table in a SharePoint view - so I want to concatenate those 5 field values for the view (so it isn't each field separate, they want to see the row as a whole).

[creativeopinion]

Can you upload a screenshot of the SP List? I'm still trying to picture what this looks like and it's really hard. You can blur out the info.. I just want to see the list. 

[axray]

The list is huge... it's a big form with two main sections.

Here is a snippet (is this what you were looking to see?):

[axray]

This is the "table" I created - the Root Cause Department fields are the multi select choice combo boxes I need to concatenate and add to the calculated field with the values from each of the fields in each row...

[creativeopinion]

This helps a bit. You can't access complex columns (such as person or group and multi-choice columns) with a Calculated Column.

In this case you don't need a Calculated Column. You would need a Single Line of text column. 

With your trigger (When an Item is created or modified), you will need to compose the text that needs to go into this column in Power Automate. 

Will that work for you?

[axray]

Right.

Correct - I have a single line of text column for the concatenated 1_Root Cause Department values.

Then I have a calculated column to hold the entire list of values (the 5 fields I mentioned), concatenated, for the entire row in the table (I just need to get the first text column to work before I can add that column to the calculated column).

I found a thread that said to set it up this way - but this does not appear to be working. Do you happen to know why?

[creativeopinion]

Can you upload the outputs from this part of your flow from a recent run?

[axray]

It also has this warning:

Yikes, I just modified an item and checked the run history (this is just the last 10 they show you) - why is it triggering like this?

[axray]

Outputs from which part of the flow? What I sent is the entire flow.

[creativeopinion]

This part:

Take a screenshot of the outputs... the screenshot above is of the flow in edit mode. Click on one of your recent runs, and click on it. 

Expand the actions and screenshot the outputs.