Nov 05 2020 09:30 AM
Nov 05 2020 09:30 AM
We have various surveys that we send to client using an external survey provider. I am investigating the ability to satisfy our needs by using MS Forms. One requirement we have is the ability to have a weighting tied to our questions as I have specified below. We would like to capture the scoring value in the Excel document behind the Form for easy tabulation. Can someone guide me as the whether this is possible? Thanks.
Question: How was the quality of service you received?
1. Superior (worth 100)
2. Good (worth 80)
3. Average (worth 60)
4. Poor (worth 40)
5. Terrible (worth 0)
Nov 18 2020 05:08 AM - edited Nov 18 2020 05:09 AM
Hi Chris (@Chris_Mancini) it's not possible with Forms on its own, but you could have a simple flow in Power Automate to save the weighting number (and any of the other answers in your form) to a different spreadsheet. The result is shown on the attached video.
The flow is shown below and triggers whenever a new form response is submitted. The first action is to get the response details, then we initialize a variable to hold the weighting score. Next, a type of condition called a Switch is added that looks at what the answer was in the question and then for each case applies the appropriate weighting to it and set the variable value. Finally it adds a row into a table in Excel using that variable value:
Come back with any questions about this.
Microsoft Power Automate Community Super User
Nov 18 2020 06:30 AM
@RobElliott Thanks for your response. That is pretty much what I thought would need to be done. I was thinking a list in SP that contained each question with the appropriate weight. When the response was received, the Flow would check the list, get the weight, and update the response record which would have a hidden question that related to each real answer. Thanks again
Jun 16 2021 11:28 AM
@RobElliott I see you responded to this a while back. I am having some trouble with the weighted scores. I followed your instructions and did a weighted score for answers, for example, High Risk should equal 5, Medium Risk should equal 3 and so on. The information transfers to Excel from forms but it does not show the values, only the words like High Risk etc.
Not sure what to do at this point. I may be doing something wrong in the Switch section. I am using a Likert Scale question in Microsoft Forms.
Thanks in advance for your help!