Form to Sharepoint template - ["How do I get rid of the square bracket and quotation marks from my multiple choice options?"]

[TomHughes]

I'm totally new to flow so please bare with me... I've set up my first flow today. My objective was to get data from a MS form to a SharePoint list so I used the template an 90% of it was as expected. I found it straightforward to link fields from Forms to the columns in my SP list. So good so far, but then the data is coming from an multiple choice in Forms I'm getting ["Text"] in the SP List. I suspect it something really simple but it isn't obvious to me. Any ideas?

[v-yamao-msft · Accepted solution]

Hi @TomHughes ,

Do you want to save multiple selections from Forms to SharePoint list?

I have made the following flow for your scenario, please check it for a reference.

Add the trigger When a new response is submitted. In the form, I have a multiple selections enabled field.

Initialize variable, set its Name as Test, Type as Array.

Get response details. An Apply to each will be added automatically after you select the response Id.

Add a Compose action to remove all the special characters:

replace(replace(replace(body('Get_response_details')?['rb12b59186b54449d83e54595ebe4eb10'],'","','|'), '["',''), '"]','')

Add a Compose 2 action to split the previous output into an array:

split(outputs('Compose'),'|')

Add an Apply to each 2, select value from compose 2 output, add the action Append to array. Here, set its name as Test, Value as dynamic content Current item of Apply to each 2.

Under the Apply to each 2, add Compose 3 with the following code:

join(variables('Test'),',')

Add the action Create item to add the output of Compose 3 to corresponding column.

Images for your reference.

Best regards,

Mabel

[PMac]

@v-yamao-msft  I have the same issue and just trying your suggested workaround, however I am getting the this error in the first 'compose'.  

InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '1' and column '2578': 'The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#replace for usage details.'.

Any suggestions?  Thanks

[patspurrier]

While the accepted solution will work, I feel obligated to reply incase anyone comes here looking for help with the same issue. @v-yamao-msft solution is way over the top. All you have to do is insert the replace function where you set the value of the SharePoint column.

For a comma seperated list, use

replace(replace(replace(body('Get_response_details')?['r676aaefb8a4b4a6c81675db2517d6e1d'],'","',', '), '["',''), '"]','')

Just replace the "body('Get_response_details')?['r676aaefb8a4b4a6c81675db2517d6e1d']" with the dynamic content of the multiple choice response question.

Like so:

[patspurrier]

<deleted>

[Anonymous]

Thanks for sharing @patspurrier this worked perfectly for what I was needing - if only I had come across your post earlier. Will link this to the one I created 🙂 

[Anonymous]

Thanks for your example.  I'm getting close, but the multi choice field in my form is showing up as blank in my SharePoint list.  Could you show exactly what is in your replace function for the Fixed attribute.  I've not been able to interpret your instructions of 

"Just replace the "body('Get_response_details')?['r676aaefb8a4b4a6c81675db2517d6e1d']" with the dynamic content of the multiple choice response question"

TIA

[patspurrier]

@Anonymous,

This is exactly what is in my Fixed sharepoint field:

replace(replace(replace(body('Get_response_details')?['r676aaefb8a4b4a6c81675db2517d6e1d'],'","',', '), '["',''), '"]','')

After you paste this expression into the expression area, you'll have to modify it to point to your response. Just backspace out the "'Get_response_details')?['r676aaefb8a4b4a6c81675db2517d6e1d']", go to dynamic content and select your response, then click OK.

[Anonymous]

@patspurrier 

It appears I have multiple issues with my flow.  I was able to figure out what needed to be replaced in the formula, but I appreciate your response. 

It seems the only way I can get the multiple choice Form responses to display in my SharePoint list is to have the column configured as text.  I would have thought the proper format would be multiple choice, choices defined the same as in the Form, and checkboxes selected.  Any idea why it only works when the column is configured as text?

TIA, again.

[Anonymous]

Thank you so much! I was battling this for over an hour😁

[jasonmurphy]

Hi Folks,

I've been trying to use this solution to clean up the "\n" characters that get added to a MS Forms Choice response, which is getting added to a SharePoint List calculated column. I've inserted this expression:

replace(body('Get_response_details')?['re161b12e16c941ba887a822bf85f74d3'],'\n','')

Unfortunately, the form output, via the expression is continuing to include the "\n" characters.

Any suggestions would be greatly appreciated.

[notsmart]

wondering if you ever discovered a solution for this.  I'm having the same issue with pull-down responses.

[jasonmurphy]

Hi @notsmart, I had some success with this expression:

uriComponentToString(replace(uriComponent(body('Get_response_details')?['insertyourformresponseidhere']), '%0A', ''))

It seems that Forms generates a different output for some response fields, therefore I needed to use the uricomponenttostring expression to get the replace function to work.

[ddoxsey]

Hi I am very new to sharepoint and need help with this flow. Not sure how to make this work. Any help appreciated. 

[Anonymous]

@patspurrierthank you so much for this solution. Just applied your solution and it worked!

The only thing I had to do was replace the string in the formatting..

original output code

@{outputs('Get_response_details')?['body/r4c1fd8bcf3504d91bba373e268cd0985']}

and I used this to replace it

@{replace(replace(replace(body('Get_response_details')?['r4c1fd8bcf3504d91bba373e268cd0985'],'","',', '), '["',''), '"]','')}

[AnnetteM]

This is soooo easy. I used it when getting the multi-select (with Other) question in MS Forms. In included it in an HTML compose step! It worked PERFECT!!!