locked
Calculated Columns for a Risk Matrix RRS feed

  • Question

  • Hello,

    I'm hoping someone can help me. I am trying to create a calculated column that uses 3 drop downs to calculate risk. We have a review process that we want to automate the risk evaluation during the addition of the item to the sharepoint list. see data below:

    our form would have 4 drop downs - Functional Area, Scope, Experience, Risk

    from those columns, we need to determine scope escalation - Options are "No Action, Inform, Escalate"

    Sample Data:

    Functional Area: Gateway Ops
    Scope: Regional
    Experience: First Time
    Risk: Medium

    This would calculate to "Inform" which would signal an escalation of scope from Regional to National

    OR

    Functional Area: Network
    Scope: Local
    Experience: Common Practice
    Risk: Low

    This would calculate to "No Action"

    At first I thought a Case, or Select statement would work - but I am apparently wrong. Then I tried an IF statement, but that didn't work either.

    Any suggestions?

    Tuesday, March 28, 2017 6:10 PM

Answers

  • Hi

    i agrree  with Grace WR

    and I would add that if you conditions to set excalation option and baed on multiple possibilities if all 4 choice columsn are involved, you will can't use IF calculated column ( limitated to 7 IF conditions in a line )

    I recommend you another approach, a mixed solution - calculated column with workflow

    1. First create a calculated column ( hidden if necessary ) in which you will concatenate values from all 4 choice columns

    from you explanation , values will be like:

    GatewayOpsRegionalFirstTimeMedium

    NetworkLocalCommonPracticeLow

    ( I recommend you to replace spaces in choice columns options and replace them with "_"

    2. create a secondary list , which will contain items for each posibility as described in step 1

    GatewayOpsRegionalFirstTimeMedium

    NetworkLocalCommonPracticeLow

    3. Add a new column with needed value for scope escalation   =  "No Action, Inform, Escalate" for each item as described in step 2

    4. Create a workflow and attach to the main list, set to start when an item is added or changed,

    This workflow will take the calculated column from step 1, will check in list 2 ( step 2) for that value, and will copy the value for Scope Escalation ( step 3 ) and will set the field in main list

    I hope these explanations are clear, if aren't  Fill free to ask me for more details.


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Wednesday, March 29, 2017 7:35 AM

All replies

  • Hi Ken,

    I’ not very clear about your request, please provide and verify more detailed information as following:

    1. Are there four choice columns( Functional Area, Scope, Experience, Risk) in your list?
    2. What’s the column you want to defined as calculated column? Is it “scope escalation”?
    3. What’s the rule that determines which option will be displayed in the scope escalation, such as which choice combination was selected in the four choices columns determines “No Action” or “Inform” or “Escalate”?

    Best regards,

    Grace Wang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, March 29, 2017 4:08 AM
  • Hi

    i agrree  with Grace WR

    and I would add that if you conditions to set excalation option and baed on multiple possibilities if all 4 choice columsn are involved, you will can't use IF calculated column ( limitated to 7 IF conditions in a line )

    I recommend you another approach, a mixed solution - calculated column with workflow

    1. First create a calculated column ( hidden if necessary ) in which you will concatenate values from all 4 choice columns

    from you explanation , values will be like:

    GatewayOpsRegionalFirstTimeMedium

    NetworkLocalCommonPracticeLow

    ( I recommend you to replace spaces in choice columns options and replace them with "_"

    2. create a secondary list , which will contain items for each posibility as described in step 1

    GatewayOpsRegionalFirstTimeMedium

    NetworkLocalCommonPracticeLow

    3. Add a new column with needed value for scope escalation   =  "No Action, Inform, Escalate" for each item as described in step 2

    4. Create a workflow and attach to the main list, set to start when an item is added or changed,

    This workflow will take the calculated column from step 1, will check in list 2 ( step 2) for that value, and will copy the value for Scope Escalation ( step 3 ) and will set the field in main list

    I hope these explanations are clear, if aren't  Fill free to ask me for more details.


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Wednesday, March 29, 2017 7:35 AM
  • I marked this as the answer because I have handed it off to our portal team with the instructions to put it into a workflow.

    Thank you for your help.

    Monday, April 3, 2017 5:18 PM