SOLVED

Forms to Sharepoint

Copper Contributor

I have a Microsoft Form with 20 questions. 

I know how to export the answers to a Sharepoint-list but I have a challenge I've been struggling with for a few days. 

 

A simple transfer will give me a list with answer 1-20 in different columns but I want it to based on the information in question 11-20 be different rows in the list.

 

Row 1: Columns with questions 1-10 and 11 (if not empty)

Row 2: Columns with questions 1-10 and 12 (if not empty)

Row 3: Columns with questions 1-10 and 13 (if not empty)

Row 4: Columns with questions 1-10 and 14 (if not empty)

etc.

 

Can someone help me with if this is possible?

 

Thank you!

7 Replies
best response confirmed by Christopher Hoard (MVP)
Solution

Hi @nima_einarmattsson, a way to do this is with Power Automate (Flow). 

 

First of all create your list with the Title column for the responder's email address and then a single line of text column for each question Q1 - Q10 and then another single of text column for called Q11-20 which will hold the value of whichever of the answers is completed.

 

0-SP-list.png

 

1. I've made the first 10 questions to have required answers:

1-Mandatory-Questions.png

 

2. The second set of 10 questions are optional:

 

2-Optional-Questions.png

 

3. Then in the flow the trigger is a Forms When a new response is submitted. The first action is then to Initialize a string variable. This will later on hold the answer from questions 11 -20. The next item is to get the response that was submitted:

 

3-Trigger-var-Get.png

 

4. The next action is to append to the string variable the answers from the Get response details section in the dynamic content box. What I've done here is also to add the question number so that later you know which question the user has answered.

 

4-Append.png

 

5. The next action is a SharePoint Create item. Select the site and list and add in the responder's email and the answers to the first 10 questions in the relevant field from the dynamic content. Finally you add the variable into the Q11-20 field.

 

5-Create-item.png

 

When the user submits the form with the required answers 1-10 and the answer they've chosen from 11-20 the result will be as follows. It shows which question number has an answer and what that answer is.

 

6-SP-list-result.png

 

Hope that does what you want and that it helps.

 

Rob
Los Gallardos

Microsoft Power Automate Community Super User

 

@RobElliott 

Thank you very much for your answer. In my case some people will just answer question 11, some 11 and 12, some 11 12 13, etc. 

Is there a similar code for that? So that if someone answers 11, 12, 13 and 14 I'll get:

Row 1: Q1, Q2, ...., Q10, Q11

Row 2: Q1, Q2, ...., Q10, Q12

Row 3: Q1, Q2, ...., Q10, Q,13

Row 4: Q1, Q2, ...., Q10, Q14

and similar..

 

But if only 11 and 12:

Row 1: Q1, Q2, ...., Q10, Q11

Row 2: Q1, Q2, ...., Q10, Q12

 

Thank you!

@nima_einarmattsson my solutions copes with that with no problem. We're not restricting the user to answering just 1 question from the optional ones: they might answer answer 1, they might answer 10. The image below shows what you see in the list if they answer questions 11 and 12:

 

7-Multiple-answers.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@nima_einarmattsson 

 

But if only 11 and 12:

Row 1: Q1, Q2, ...., Q10, Q11

Row 2: Q1, Q2, ...., Q10, Q12

 

I'm not quite sure that could be achieved without making the flow incredibly complex with multiple nested conditions. The easiest thing is just to have a column for every question, if the answer is blank then it will be blank in the SharePoint list.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Thank you!

 

What I am trying to do is to create a form to make orders. 

 

The first questions are about basic information like: your name, address, preferd delivery, etc. 

And the last questions are for your orders. If you just want one product you fill that out in Q11, if you want two different products you fill out Q11=bike and Q12=car, Q13=shoes, etc. 

 

In my Sharepoint list I want one row for every product in a order:

Nima - Sweden - December 1st - bike

Nima - Sweden - December 1st - car

Nima - Sweden - December 1st - shoes

 

And if someone else make an order Q11=pillow, Q12=shirt, Q13=hat, Q14=telephone

 

The list will look like this:

Nima - Sweden - December 1st - bike

Nima - Sweden - December 1st - car

Nima - Sweden - December 1st - shoes

Andreas - Finland - December 1st - pillow

Andreas - Finland - December 1st - shirt

Andreas - Finland - December 1st - hat

Andreas - Finland - December 1st - telephone

 

Maybe there is an easier way to do this when you know what I wish the outcome to be and the rest is free of choice for you. The only thing is that I don't want just one question/product per Form. The users must have the chance to make multiple orders in every Form :)

@nima_einarmattsson ok in which case you need to use parallel branching in your flow and add a condition into each branch to check if , initially question 11 has an expression of null. If it does then do nothing. If it doesn't then create an item in the list using the question 11 answer.

 

8-Condition.png

 

9-Create-item.png

 

In the next branch you'd need to do the same for question 12, checking oif the answer was null and if not creating an item in the list with the answer to question 12.

 

10-Condition-Q12.png

 

11-Create-item-Q12.png

 

You'd have to add a branch for the rest of the answers from 13 to 20, each with a condition and a create item action. The result will look like this and creates a new line for each of the answers selected.

 

12-SP-list-result.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Thank you @RobElliott!

 

Parallel branching and conditions did the work and will be useful to me in many more cases.  

 

Thank you for your help!

 

/Nima

1 best response

Accepted Solutions
best response confirmed by Christopher Hoard (MVP)
Solution

Hi @nima_einarmattsson, a way to do this is with Power Automate (Flow). 

 

First of all create your list with the Title column for the responder's email address and then a single line of text column for each question Q1 - Q10 and then another single of text column for called Q11-20 which will hold the value of whichever of the answers is completed.

 

0-SP-list.png

 

1. I've made the first 10 questions to have required answers:

1-Mandatory-Questions.png

 

2. The second set of 10 questions are optional:

 

2-Optional-Questions.png

 

3. Then in the flow the trigger is a Forms When a new response is submitted. The first action is then to Initialize a string variable. This will later on hold the answer from questions 11 -20. The next item is to get the response that was submitted:

 

3-Trigger-var-Get.png

 

4. The next action is to append to the string variable the answers from the Get response details section in the dynamic content box. What I've done here is also to add the question number so that later you know which question the user has answered.

 

4-Append.png

 

5. The next action is a SharePoint Create item. Select the site and list and add in the responder's email and the answers to the first 10 questions in the relevant field from the dynamic content. Finally you add the variable into the Q11-20 field.

 

5-Create-item.png

 

When the user submits the form with the required answers 1-10 and the answer they've chosen from 11-20 the result will be as follows. It shows which question number has an answer and what that answer is.

 

6-SP-list-result.png

 

Hope that does what you want and that it helps.

 

Rob
Los Gallardos

Microsoft Power Automate Community Super User

 

View solution in original post