How do I calculate a new field based on a comparison of a text field and a duration field?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I have a multiple choice field called TIER with the options of Gold, Silver, and Bronze. I have a formula duration field called "RECOVERY." I want to use "RECOVERY" as minutes. I want to push yes or no to another field called "MET SLA" if certain conditions are met. If the none of the conditions are met, then I want to leave MET SLA blank. For example:

If TIER='Gold' and RECOVERY<=60 then MET SLA='Yes'

If TIER='Gold' and RECOVERY>60 then MET SLA='No'

if TIER='Silver' and RECOVERY<=120 then MET SLA='Yes'

if TIER='Silver' and RECOVERY>120 then MET SLA='No'

Else MET SLA=null

Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb

Posted 5 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
This would be the formula for the field [Met SLA]

Its a bit unusual (for me anyways) to use a duration field to hold a duration - I usually would just have it be a numeric field in minutes, but assuming that the [Recovery] field is type duration,


IF(
[TIER] = "Gold" and [RECOVERY]<= minutes(60), "Yes",
[TIER] = "Gold" and [RECOVERY]> minutes(60), "No",
[TIER] = "Silver" and [RECOVERY]<= minutes(120), "Yes",
[TIER] = "Silver" and [RECOVERY]> minutes(120), "No")
Photo of Anne

Anne

  • 100 Points 100 badge 2x thumb
Geez - I had "minutes" before, after, in between... parenthesis all over the place - thought I tired EVERY combination... and now I see it was that simple.   Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
:)