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
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).
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.