Red Amber Green Status based on other fields - Please help

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered
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
Photo of Chinmay Dhandhania

Chinmay Dhandhania

  • 224 Points 100 badge 2x thumb

Posted 1 year ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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...
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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>"
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
That was a helpful tip.  I have never understood when to use singe quotes.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
You can also use no quotes if there are no spaces in the attribute value:

"<div style=background-color:pink;>"& [Priority] & "</div>"
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
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"
)
Photo of Chinmay Dhandhania

Chinmay Dhandhania

  • 224 Points 100 badge 2x thumb
This is super helpful to me. This in combination with this post solves my problems - https://community.quickbase.com/quickbase/topics/calculation-for-formula-text