Skip to main content

Notifications

Power Automate - General Discussion
Unanswered

Calculating a score from a MS Form output stored in SharePoint

(0) ShareShare
ReportReport
Posted on by 25

Hi,

 

I've created a MS Form with 5 subforms branching within it. The output from this is successfully loaded into a line item in a SharePoint list (with 5 views created for each of the 5 subform types)

 

Upon GetItem in Power Automate the body of the item contains the following questions (shortened from the real case):-

{
"Health_x0020__x0026__x0020_Safet": "No problems observed",
"Safe_x0020_Access_x0020_and_x002": "Opp. for improv.",
"Site_x0020_Clean_x0020__x0026__x": "No problems observed",
"Ask_x0020_to_x0020_View_x0020_th": "Opp. for improv.",
"Ask_x0020_the_x0020_Operative_x0": "Non-conform. issued",
"Ask_x0020_the_x0020_Operative_x00": "Job stopped",
"Ask_x0020_to_x0020_View_x0020_Pe": "Non-conform. issued",
"Check_x0020_a_x0020_Sample_x0020": "Opp. for improv.",
"First_x0020_Aid_x0020_Kit_x0020_": "No problems observed",
"Fire_x0020_Extinguisher_x0020_Av": "Opp. for improv.",
SNIP
}

Each of the questions can be answered with 6 possible answers

"No problems observed"
"Opp. for improv."
"Non-conform. issued"
"Job stopped"

"N/A"
"Not Checked"

What I'd like to do is run through all the question answers and increment a variable for each of the different answer types each time it is encountered in the body

So e.g.
{
"Health_x0020__x0026__x0020_Safet": "No problems observed",
"Safe_x0020_Access_x0020_and_x002": "Opp. for improv.",
"Ask_x0020_the_x0020_Operative_x00": "Job stopped",
"Ask_x0020_to_x0020_View_x0020_Pe": "Non-conform. issued",
}

 

would result in 

No problems observed = 1
Opp. for improv. = 1
Non-conform. issued = 1
Job stopped = 1

N/A = 0
Not Checked = 0

Would score 311


Where = 

No problems observed = 1
Opp. for improv. = 10
Non-conform. issued = 100
Job stopped = 200


I've tried so many different ways of doing this I'm tearing my hair out (and I haven't got that much to spare).

What is the best (and 'how') to do this?

Thanks in advance 


 

 

 

  • DeckardREP Profile Picture
    DeckardREP 25 on at
    Re: Calculating a score from a MS Form output stored in SharePoint

    Thanks Chris, I thought that might be an option - thanks

    I managed to crack this by creating by loading an array with all the potential answers just after the GetItem, then loop through this to collect the total of each of the answer types for each form submitted (in the early hours last night). 

  • ChrisMendoza Profile Picture
    ChrisMendoza 425 on at
    Re: Calculating a score from a MS Form output stored in SharePoint

    Honestly, I think you should look into summarizing the results using Power BI & Power Query. The record for each form submission is not tabular so you need to unpivot the columns. You could even do this in Excel since Power Query is an option there. While Power Automate is an awesome product, you'll find that other Power Platform tools may be suited better even so much as using Power Apps instead of MS Forms.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,666

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,996

Leaderboard