Change the Text Color of Numeric Value

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a Numeric-Formula field, a simple subtraction formula. I want to make the number red if it's a negative number. I cant seem to find the specific way to do this... Is it possible?
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Neal

Neal

  • 70 Points
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
Doesn't help at all, looked at that and tried and tried, couldn't get it to work. If no one else has any comments, I'll just make a support ticket for it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Try this one.  There is not a way to simply format a cell like in Excel with Conditional formatting.



I think that this code works, I just tested it. (revised to correct a problem with an extra negative sign)

number Value = Round([currency field],0.01);


var text Decimals = "." & Right(ToText(Int(Abs($value) * 100)),2);
var text Thousands = If(Abs($Value)>=1000,ToText(Int(Abs($Value)/1000)));
var text Hundreds=Right(ToText(Int(Abs($Value))),3);

If($Value=0,"$0.00",
If($Value<0, "<font color=red>- ")
&
"$" & List(",",$Thousands,$Hundreds) & $Decimals)
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
I think the code works, no errors but wondering where I would put it since I cant put it into the formula field that is calculating the value.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
You need to make a 2nd field of type formula text.
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
Got it... Did that and now it displays this in the field when it's a negative number. <font color=red>-$-1,000.00
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
You need to check the checkbox to allow HTML
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
got it, works. I did need to remove a - sign from the end of the html. Appreciate the help.
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
ok so something weird... lol. if it's under 1000, with your formula as above the negative is inserted before the $ like this -$755.19. If it's over 1000 and it's a negative number it's like this, -$-2,602.44. I was looking at the code and see where it inserts the negative sign after the html code for RED, wondering why it's different.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
I will look at it later today when I get a chance.
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
ok thanks, appreciate the help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
OK, here is the revised formula to correct the extra negative sign problem

var number Value = Round([currency field],0.01);
var text Decimals = "." & Right(ToText(Int(Abs($value) * 100)),2);
var text Thousands = If(Abs($Value)>=1000,ToText(Int(Abs($Value)/1000)));
var text Hundreds=Right(ToText(Int(Abs($Value))),3);

If($Value=0,"$0.00",
If($Value<0, "<font color=red>- ")
&
"$" & List(",",$Thousands,$Hundreds) & $Decimals)
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
You sir, are a Quickbase genius. Appreciate the help.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Thx, Now I have a debugged formula too for when I need it.
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
So I dont know if it's the formula or something else but some of the values are off by a penny. I would say 90% of them are correct, the RED functionality is correct too. A few of them are off and any of them that were .01 are listed as .10 with the formula. Just thought you would want to know in case you wanted another crack at it.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Can you give me an example of a source number that is not calculating correctly?  can you also tell me if your number that you are formatting is a rounded number in a formula or just formatted to 2 decimal places but not rounded.  If it is a currency calculation you should be rounding your result to the nearest cent.

for example
Round (my formula calculation here), 0.01)
Photo of Adam

Adam

  • 140 Points 100 badge 2x thumb
This is the formula I am using, it's a Formula-Numeric field. [Schedule Value]-[Invoice Total]
2 decimal places
separator after 3 places
no rounding that I know of
The calculation field listed above for example says $39655.38 and the html field I created says $39655.37, off by just one penny.

The other issue is anytime is calculates to .01, it displays in the text field with the html as .10
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Try changing your formula to

Round([Schedule Value]-[Invoice Total],0.01)

I will look at the .01 issue when i get a chance.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
i tried the .01 issue and it worked for me.  Lets see what happens after you round to the nearest penny.  That will probably fix the problem.