cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alanisp22
Helper II
Helper II

Split Form Multiple Choice Question Answers into Separate Columns in SharePoint List

Hello,

 

I am trying to set up a flow that will get the response details from a SharePoint Form and split the multiple-choice question answers into separate list columns in SharePoint. My main issue with how I have it currently set up is that my flow fails if a user submits less than 3 answer selections from the multiple-choice question. I believe the issue is that I am specifying to feed over exactly 3 answer selections into 3 separate columns by using variables('Choice')[0],  variables('Choice')[1],  and variables('Choice')[2], so my flow is failing if the answer selection is less than 3. Is there anything I can do to make sure my flow doesn't fail if it doesn't locate variables('Choice')[1] and variables('Choice')[2]?
In other words, how can I set my flow up to split multiple choice question answers from a form into separate columns in a SharePoint list and not fail if less than 3 choices were selected (I only have 3 possible answer selection options in the question and 3 separate columns they feed into in SharePoint)?

Here is the error details:

InvalidTemplate. Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2831': 'The template language expression 'variables('Choices')[2]' cannot be evaluated because array index '2' is outside bounds (0, 1) of array. Please see https://aka.ms/logicexpressions for usage details.'.

1 ACCEPTED SOLUTION

Accepted Solutions
Jronash
Impactful Individual
Impactful Individual

Coalesce is your friend. You can provide it with a list of variables or values, and it will return the first one that is not null.

 

For instance:

coalesce(variables('Choices')[2], 'Default value')

This will return the value of the third Choices item if it exists.  If it is null, it will return 'Default value'.

View solution in original post

5 REPLIES 5
Jronash
Impactful Individual
Impactful Individual

Coalesce is your friend. You can provide it with a list of variables or values, and it will return the first one that is not null.

 

For instance:

coalesce(variables('Choices')[2], 'Default value')

This will return the value of the third Choices item if it exists.  If it is null, it will return 'Default value'.

@Jronash  YAY!! That worked! Thank you so much!!!! 😁

swanders
Frequent Visitor

@Alanisp22 , realizing this is an old topic. Any chance you could share what you've created? I'm looking for a similar setup to get different Form responses in a separate SharePoint column, your topic has come closest to anything 😉

@swanders, here is a quick sample of what I did. 

I created a form with a multiple-choice question --> 

Alanisp22_0-1665625133618.png

And then set up my flow like this:

Alanisp22_1-1665625586288.png

 

Compose 2 is set up to remove additional characters I do not need.

 

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

 

 
Compose is set up to split the selected options by ',' so I can insert them into individual columns:

 

split(outputs('Compose_2'),',')

 

I then took the output from Compose and set it as my variable.

Alanisp22_2-1665626189070.png

 


From there, my flow is set up to create the item in a SharePoint list. I am able to insert each selected option from the multiple-choice question submitted on the form into a separate column by using coalesce. I have 'Not Selected' as a default if the user did not select an option for the placement I am looking for. For example, if the user only selected 1 option in the form, then my SharePoint list will post 'Not Selected' as the value for placement [1] & [2]. 

 

coalesce(variables('Options')?[0], 'Not Selected')

 

Note: [0] is placement 1 (1st value listed), [1] is for placement 2 (2nd valude listed), etc. 

Here is the outcome:

Alanisp22_3-1665626283015.png

 



I hope this helps. 

swanders
Frequent Visitor

@Alanisp22 , thank you for the time to reply. It's much appreciated; I think I've been looking way too far in this.

I think you forgot a step in the process, since my first compose hits me straight up with an error 😉
Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Object'. Please see https://aka.ms/logicexpressions#replace for usage details.'.

I've read about this, so shouldn't be a problem to resolve this part. I'll get back to this asap.

 

Edit: See, my bad.
I've used:

replace(replace(replace(outputs('Get_response_details')?['body/rdaa511f7076043bdbb48a8e096035d71'], '"'''), '['''), ']''')

Had to specify which part of the body I wanted to split, otherwise it does see it as an Object and tries to do so on all the responses / information.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (2,752)