Calculation for Formula Text

• 0
• Question
• Updated 6 years ago
I need to produce a traffic light icon based off the total of two numeric fields - Probability and Impact. Each field can be labeled from 1-5 and depending on the scenario a Formula - Text field will show a red, yellow or green traffic light. The attached screenshot shows the criteria for which color should appear. I'm having trouble with the calculation.

Thanks!

• 30 Points

Posted 6 years ago

• 0
• 200 Points
Here's a formula you can use in a formula text field, with "Allow HTML tags" enabled. Since the product of the 2 numbers has some overlap for the desired result, a few IF statements are used within the Case statement to resolve. And remove spaces from "h t t p s" or sub your own graphic file names.

Ken

--------
var Number probtimesimpact = [Probability] * [Impact];
var Text green = "<img src=\"h t t p s://images.quickbase.com/si/16/228-rect_green.png\">";
var Text yellow = "<img src=\"h t t p s://images.quickbase.com/si/16/229-rect_yellow.png\">";
var Text red = "<img src=\"h t t p s://images.quickbase.com/si/16/227-rect_red.png\">";
var Text gray = "<img src=\"h t t p s://images.quickbase.com/si/16/226-rect_gray.png\">";

If(\$probtimesimpact=0, \$gray,
Case(\$probtimesimpact,
5, \$yellow,
6, If([Impact]=3,
\$green,
\$yellow
),
10, If([Impact]=5,
\$red,
\$yellow
),
12, If([Impact]=4,
\$red,
\$yellow
),
If(\$probtimesimpact <= 6,
\$green,
If(\$probtimesimpact <= 12,
\$yellow,
\$red
)
)
)
)
• 30 Points
you're awesome! worked perfectly. thanks.