How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I am using row colorization formulas, mostly based on a date/time field, which works. However, I need to add a condition regarding a text field, which would need to override the colorization rule based on the date formulas. I have attempted to create a line item for each date formula combined with the text formula. It doesn't seem to be working. Here is what I have so far:

If(
not IsNull([Time Complete]), "",
([ETA:])>Now(),"#08ef08", 
([ETA:])<Now() and ToDays([ETA:]-Now())>-1, "#f9fc03",
ToDays([ETA:]-Now())<0,"#fd3110",
IsNull([ETA:]),"#00f6ff",
(Trim([Van Driver]))<>"" and ([ETA:])>Now(),"#00f6ff")

The last line is the only one not working.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Are your conditions listed in their priority?
The IF statement evaluates the tests in the order they are liste and stops at the first one which is true.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
I just moved it around, and it still isn't showing the blank value in blue over the ETA condition in green.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Can you post you current formula, and tell me which line of the IF is not triggering and what the values are in each of the fields in that line?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Also, if Van Driver is a text field,  why not just use

Trim([Van Driver])<>"","#00f6ff",
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Then I get this:


Not only is the field with no Van Driver not blue, but everything else that shouldn't be - is!
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
I figured it out!

If(
not IsNull([Time Complete]), "",
(Trim([Van Driver])="" and ([ETA:])>Now()), "#00f6ff",
(Trim([Van Driver])="" and ([ETA:])<Now() and ToDays([ETA:]-Now())>-1),"#00f6ff",
(Trim([Van Driver])="" and ToDays([ETA:]-Now())<0),"#00f6ff",
([ETA:])>Now(),"#08ef08", 
([ETA:])<Now() and ToDays([ETA:]-Now())>-1, "#f9fc03",
ToDays([ETA:]-Now())<0,"#fd3110",
IsNull([ETA:]),"#00f6ff")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Congratulations.
What I often do when using those hex color codes is to add a comment

(Trim([Van Driver])="" and ([ETA:])>Now()), "#00f6ff", // blue

You can add a comment by starting a line with // or even suffixing a line with //, like I did above.

That way when you go back to look at your formula you will remember what those color codes mean.
Photo of Kelly

Kelly

  • 1,762 Points 1k badge 2x thumb
Great idea! Thank You!