Inconsistent Text Display (Formula driven)

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
I am seeing inconsistent display of text color based on a field text selection in any report I use the fields in. For example: Budget Status field (text-mult choice) is "At Risk". A separate field Budget Status2 is a formula-Rich TExt field that changes the color based on selection - in this cse it should change to yellow. The formula correctly applies to many but not all entries in my report. Is this a known issue or have I taken a misstep in how I have compiled my reports or even in how I make changes in fields?
Photo of Kirby Wilkerson

Kirby Wilkerson

  • 70 Points

Posted 7 months ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 12,658 Points 10k badge 2x thumb
Hi Kirby,

One thing that can regularly throw off colorization formulas that are using matching text can be when a formula calls the value and is missing or has extra spaces. What is open helpful to do is to check the multiple choice field to see if any of the entries have a leading or trailing space and if it does make sure that is also captured in the formula. So for example if it isn't colorizing "At Risk" it could be because the entry is actually "_At Risk" or "At Risk_" with that dash representing a space somewhere in there and since you are using an equal to Quick Base isn't considering them exactly the same even though just looking at it casually they appear to be equal. When working up a formula I have had that catch me a few times.

The other trick is to wrap the field tag in a Trim function that would cut off any extra spaces Trim([Budget Status])="At Risk"
.. or if you suspect data is contaminated or inconsistent, you can also use

IF(Trim([My field]) = "At Risk", "yellow")

The Trim will trim away and leading or training invisible characters like spaces.