Power Automate Exchange

Please login or click SIGN UP FOR FREE to create your FlowUG account to join this user group.
Expand all | Collapse all

Calculating a score from a MS Form output stored in SharePoint

  • 1.  Calculating a score from a MS Form output stored in SharePoint

    Posted Mar 24, 2021 12:28 PM
    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 5 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

    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?

    ------------------------------
    Geraint Vernon
    Business Systems Manager
    ------------------------------


  • 2.  RE: Calculating a score from a MS Form output stored in SharePoint

    Top Contributor
    Posted Mar 25, 2021 02:13 AM
    By what logic do you derive the number 311 from those 5 answers?


    ------------------------------
    Will Page
    Technical consultant
    Christchurch, NZ
    ------------------------------



  • 3.  RE: Calculating a score from a MS Form output stored in SharePoint

    Posted Mar 25, 2021 04:16 AM

    Ha! That info would help wouldn't it WIll!

    (sorry I was convinced I'd included that!)

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



    ------------------------------
    Geraint Vernon
    Business Systems Manager
    ------------------------------



  • 4.  RE: Calculating a score from a MS Form output stored in SharePoint

    Posted Mar 25, 2021 05:15 AM
    Each of the questions can be answered with 6 NOT 5 possible answers

    "No problems observed"
    "Opp. for improv."
    "Non-conform. issued"
    "Job stopped"
    "N/A"
    "Not Checked"

    ------------------------------
    Geraint Vernon
    Business Systems Manager
    ------------------------------



  • 5.  RE: Calculating a score from a MS Form output stored in SharePoint

    Top Contributor
    Posted Mar 26, 2021 12:53 AM
    There's a few ways you could do this. I think what I'd do is this:

    1. Early in the flow, initialise an integer variable, and also use a Compose to hard-code a table of the answers with their points:
    [{"Answer":"No problems observed","Points":1},
    {"Answer":"Opp. for improv","Points":10},
    {"Answer":"Non-conform. issued","Points":100},
    {"Answer":"Job stopped","Points":200}]​


    Then for each question, add a Filter array action and the input to that is this Compose (the table above) and the condition is that item()?['Answer'] is equal to the answer to the question. Then after the filter array, use an Increment variable action and increment your integer variable by first(outputs('Filter_array'))?['Points']

    By the end of your flow you should have a final tally.

    You could put these two actions in a scope and then copy-paste it lots of times, changing the condition in the Filter array.



    ------------------------------
    Will Page
    Technical consultant
    Christchurch, NZ
    ------------------------------



  • 6.  RE: Calculating a score from a MS Form output stored in SharePoint

    Posted Mar 26, 2021 04:20 AM

    Many thanks for getting back to me

    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 nigth). It's very re-assuring to know that I've approached it in the right way and that there wasn't a magic solution that I'd missed (or tried and not got to work!).

    Thanks again!




    ------------------------------
    Geraint Vernon
    Business Systems Manager
    ------------------------------