Forum Discussion

ChinmayDhandhan's avatar
ChinmayDhandhan
Qrew Trainee
7 years ago

Red Amber Green Status based on other fields - Please help

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

6 Replies

Sort By
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      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>"
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      That was a helpful tip.  I have never understood when to use singe quotes.
    • _anomDiebolt_'s avatar
      _anomDiebolt_
      Qrew Elite
      7 years ago
      You can also use no quotes if there are no spaces in the attribute value:

      "<div style=background-color:pink;>"& [Priority] & "</div>
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    7 years ago
    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"
    )