Conditionally change color of formula numeric field, either text or background, without using a a Formula Text field.

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
I want to change either the background or text of a formula numeric field based on conditions (e.g. over budget). I have made it work using a formula text field that mimics the number, and even got a "$" or "%" to work.

It seems hit or miss, and sometimes I get weird numbers. Is there a way to directly change the color of a numeric field? Here is an example of a formula:



If([Remaining Project Budget Funds Original]<0,"<div style=\"color:red;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>",

[Remaining Project Budget Funds Original]=0,"<div style=\"color:black;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>",

[Remaining Project Budget Funds Original]>0,"<div style=\"color:#3EC41C;\">"&"$"&ToFormattedText(Round([Remaining Project Budget Funds Original],0.01),"comma_dot")&"</div>")
Photo of Ian

Ian

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
The short answer is no. A formula numeric has no option for inserting HTML. The only way to do that would be to use the Text Formula with HTML enabled as you mentioned.
Photo of Ian

Ian

  • 0 Points
Thanks Blake. What would the formula be to conditionally change a related formula field? For example, if the project is over budget, have a [Budget Status] field change red.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Is the problem that the pennies get truncated if they are zero?
Photo of Ian

Ian

  • 0 Points
I am trying to do this with percents, remaining hours, and budget. Yes, sometimes there is a truncation issue. Sometimes I get a "1.03" instead of a "103" in the formula text field. If I add 100 to the source field, it messes up the original calculation.
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
It would be done in the same way that you outlined in your example, except that the value presented would be a Text value. To change the background, this is the example (albiet with numeric values) that I've used:

If(Round([numeric])<[numeric goal],"<div style=\"background-color:pink;width:50\">"&ToText(Round([numeric]))&"</div>",ToText(Round([numeric]))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
For Percent fields, you need to multiply by 100.
Photo of Ian

Ian

  • 0 Points
If I'm converting numeric to text such as 6.0, what do I add to the formula to keep that decimal place? Right now it converts on to 6.
Photo of Ian

Ian

  • 0 Points
Right, but can I multiply by 100 in the formula text field? If I multiply by 100 in the formula numeric field it obviously screws that up.
Photo of Ian

Ian

  • 0 Points
OK, I got this to work from an earlier post from Mark.

If([Remaining Hours Original] >0,
ToText(Int([Remaining Hours Original]))
& "."
& If(Int([Remaining Hours Original])=[Remaining Hours Original],"0",
     Length(Right(ToText([Remaining Hours Original]),"."))=1,Right(ToText(Frac([Remaining Hours Original])),1) & "0",
     Right(ToText(Frac([Remaining Hours Original])),".")))

I also want to conditionally change the color. How do I add that? thanks
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
You'll actually have to break apart the value into the Integer and the Remainder and do something along these lines:

Right(ToText(Frac[Numeric]*100+100),2)
Photo of Ian

Ian

  • 0 Points
When I add this: &"<div style=\"background-color:red;\">"& [Remaining Hours Original] I get this:

6.
6
0

Vertical in the field, like that.
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
You need to close your DIV element after the field
Photo of Ian

Ian

  • 0 Points
I still get something like this:

34
34
.0
Photo of Ian

Ian

  • 0 Points
I need = to be black, over to be red, and under to be green. I don't understand why it's returning multiple values....
Photo of Ian

Ian

  • 0 Points
And returning 34.0, not this mess:

34
34
.
340
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Yes,  multiply by 100 in the formula text field.
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
Ok, what's the full formula at this point?
Photo of Curtiss

Curtiss

  • 0 Points
If([Labor Budget Balance Original]<0,"<div style=\"color:red;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>",

[Labor Budget Balance Original]=0,"<div style=\"color:black;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>",

[Labor Budget Balance Original]>0,"<div style=\"color:#3EC41C;\">"&"$"&ToFormattedText(Round([Labor Budget Balance Original],0.01),"comma_dot")&"</div>")
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
Using that exact formula, but for substituting my own numeric field instead of the [Labor Budget Balance Original] that you have, I received the expected result.
Photo of Curtiss

Curtiss

  • 0 Points
Yes, sorry I went a different direction. I was trying to get the cell to change colors conditionally, while keeping the correct decimal spaces and $ sign. I still would like to figure that out.
Photo of Blake Harrison - DataBlender

Blake Harrison - DataBlender, Champion

  • 100 Points 100 badge 2x thumb
Ok, here are the two options I came up with.

For coloring the Font Only:

var text tnum = ToText(Int(Abs([Numeric]))) & "." &Right(ToText(Frac(Abs([Numeric]))*100+100),2);

If([Numeric]<0,"<div style=\"color:red;\">"&"$ "& $tnum&"</div>",

[Numeric]=0,"<div style=\"color:black;\">"&"$ "&$tnum&"</div>",

[Numeric]>0,"<div style=\"color:#3EC41C;\">"&"$ "&$tnum&"</div>")

For coloring the Background & making the Font white:

var text tnum = ToText(Int(Abs([Numeric]))) & "." &Right(ToText(Frac(Abs([Numeric]))*100+100),2);

If([Numeric]<0,"<div style=\"background-color:red;color:white;\">"&"$ "& $tnum&"</div>",

[Numeric]=0,"<div style=\"background-color:black;color:white;\">"&"$ "&$tnum&"</div>",

[Numeric]>0,"<div style=\"background-color:#3EC41C;color:white;\">"&"$ "&$tnum&"</div>")
Photo of Curtiss

Curtiss

  • 0 Points
Thank you Blake!
Photo of Jim

Jim

  • 20 Points
How to right align the text?