Calculation for Formula Text

  • 0
  • 2
  • Question
  • Updated 6 years ago
  • Answered
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!



Photo of Heather Bryant

Heather Bryant

  • 30 Points

Posted 6 years ago

  • 0
  • 2
Photo of Ken

Ken

  • 200 Points 100 badge 2x thumb
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
)
)
)
)
Photo of Heather Bryant

Heather Bryant

  • 30 Points
you're awesome! worked perfectly. thanks.