Discussions

Expand all | Collapse all

Red Amber Green Status based on other fields - Please help

  • 1.  Red Amber Green Status based on other fields - Please help

    Posted 11-07-2017 16:47
    Hi,

    I have a table called Risk 

    I want to create 3 Fields - Risk Impact, Risk Probability, and RAG Status as shown below:




    What should be the field type for Impact and Probability. Ideally I would like it to be numeric. But if it is numeric it should also display the description for each number.

    I guess I can also put it as text and have options such as '1 - Extremely Remote'. In that case what will be the formula for RAG status?

    Thanks


  • 2.  RE: Red Amber Green Status based on other fields - Please help

    Posted 11-07-2017 18:12
    There is some help here on how to create new fields to display with background shading.  You need to make new fields, you cannot just do Conditional formatting like in Excel.  So typically you a a field for data entry and another field for use in display mode on a report or form.

    http://help.quickbase.com/user-assistance/Default.html?_ga=2.11633908.1761511295.1509972295-12185085...


  • 3.  RE: Red Amber Green Status based on other fields - Please help

    Top
    Contributor
    Posted 11-07-2017 18:28
    Don't follow the practice of backslash escaping double quotes shown in the help page:

    "<div style=\"background-color:pink;\">"& [Priority] & "</div>"

    Instead save your eyes and use single quotes:

    "<div style='background-color:pink;'>"& [Priority] & "</div>"


  • 4.  RE: Red Amber Green Status based on other fields - Please help

    Posted 11-07-2017 18:41
    That was a helpful tip.  I have never understood when to use singe quotes.


  • 5.  RE: Red Amber Green Status based on other fields - Please help

    Top
    Contributor
    Posted 11-07-2017 18:52
    You can also use no quotes if there are no spaces in the attribute value:

    "<div style=background-color:pink;>"& [Priority] & "</div>


  • 6.  RE: Red Amber Green Status based on other fields - Please help

    Top
    Contributor
    Posted 11-07-2017 18:19
    I would use text multiple choice fields for [Risk Impact] and [Risk Probability] both talking values "1", "2", "3" or "4". Then [RAG Status] field would be a text formula field taking values "G", "R" or "A" with the following formula:
    If([Risk Impact] = "1" and ([Risk Probability] = "1" or [Risk Probability] = "2" or [Risk Probability] = "3")
       or
       [Risk Probability] = "1" and ([Risk Impact] = "2" or [Risk Impact] = "3"),
       "G",
       If([Risk Impact] = "3" and [Risk Probability] = "4")
          or
          [Risk Impact] = "4" and ([Risk Probability] = "3" or [Risk Probability] = "4"),
          "R"
       ),
       "A"
    )

    To the extent you also need text labels for any of these fields you can use a Case() function to generate them. For example:
    Case([Risk Impact],
      "1", "Insignifigant"
      "2", "Signifigant",
      "3", "Critical",
      "4", "Catastrophic"
    )


  • 7.  RE: Red Amber Green Status based on other fields - Please help