Oct 25 2021
- last edited on
Aug 03 2023
I'm trying to make an assessment/quiz/form/surway...
There will be multiple questions
On each question the responder will choose a point from 0 - 4
At the end - when all questions are answered they will get the total sum of points. And an explanation like
You got 15 points that indicates that you should....
Since I'm not fluent in technical English - especially Survey words - I get lost in founding out which type of form question to use and even more if they can be calculated.
So please help anyone?
Simply links to videos, articles where it is described will be wonderful!
Oct 27 2021 04:03 AM
@AnnA_Webbinstitutet one way that you can achieve this is shown below, but you can't do it with Forms on its own, you need a Sharepoint list and a flow in Power Automate.
First we have the form. Each question is a text question but you set a restriction that it must be a number:
Next, create the SharePoint list. The responder's email will go into the Title column (it's just something I always do). Then we have 1 number column for each of the questions in the form:
We also need a column for the total, so create a calcuated column and in the formula set it to add up all the number columns you've just created:
Next, in Power Automate do not use a template to create your flow but add a new cloud instant flow:
For your trigger you should select the Forms "when a new response is submitted" and select your form from the dropdown. Then the first action with a Forms trigger is always "get response details": again you select the form and when you click in the Response ID field the dynamic content box will appear and you select Response Id (it's the only thing you can select).
Next, add a Compose action for each of the questions in the form:
Next, we want to add the answers to the item in the SharePoint list. So add a SharePoint "create item" action, click in each field and select the appropriate item from the dynamic content box. You'll see that there is an "output" for each of the compose actions you created earlier.
Next, add another Compose action and click in the inputs field. Over in the dynamic content box select the Expression tab and enter
Next, we add a numbe rof nested Conditions. For the first one select the output of the ComposeTotal from the dynamic content box is greater than or equal to 15 - there are 5 questions each with up to 4 points so a maximum total of 20.
In the green if yes channel add a "send an email (v2)" action and enter the text you want and select the relevant outputs from the dynamic content box.
In the red if no channel add another condition with the output of the ComposeTotal is greater than or equal to 10 and then add another row with the ComposeTotal is less than 15. In the green if yes channel add another "send an email (v2)" with the text you want (you can copy actions to your clipboard to save time entering them manually).
In the red if no channel add another condition with ComposeTotal is greater than or equasl to 5 and less than 9. And then add a "send an email (V2) into the green if yes channel (for a score from 5 to 9) and in the red if no channel (for any score less than 5)
As yoiu'll see there are different emails with different text depending on the score.
So when a form is submitted and the flow runs it will add the scores to the SharePoint list which will calculate the total:
and the relevant email will be sent to the responder:
As all this will probably be new to you, come back with any questions.
Microsoft Power Automate Community Super User
Apr 25 2022 06:13 AM
@RobElliott hi Rob, could you send a picture of what your Excel sheet would look like? And could this be done using just Excel with incremental values and applying an initialize variable and Switch?