Maintain Numeric Settings w/ HTML

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
Hello, I'm trying to show a Numeric Formula field in red by way of a Rich Text Formula field.

This is what I'm using in the Rich Text Formula field:"<div style=\"color:#ff0000;font-size:9pt\">$"&[Sub-Total w/ Vacancy]&"</div>"

The [Sub-Total] field is a Formula - Numeric field, with the currency option chosen. 

The rich text field removes the dollar sign and decimal point from the numeric field. On one value it also added more decimal points than the numeric field allows for.

Any ideas how to maintain the numeric settings in the rich text field?
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
That's pretty interesting. What happens when you try wrapping the currency field with a ToText() ?
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb
No change! 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
I have a formula I will post when i get a chance....
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb
Thanks Mark!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Try this one.  Just change the first line to use your currency field.


var number Value = Round([My 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)
So, it's necessary to recapitulate the baked-in currency settings for a numeric field in code in a formula field that wants to use the settings selected for that field, do I have that right?

Do you think this represents a bug in the ToText function, or at least an error in its documentation, which reads 'Returns a Text value containing the print representation of the argument', which appears based on this conversation not to be true?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
I don’t know if they will accept it as a bug, but I seem to recall in my testing that it did not retain the decimals when the cents end in 00 or 0 like 123.10 comes out as 123.1. Is that what you have found.

I am just on my iPhone now but I think the actual function is ToFormattedText but I don’t think I was able to get it to retain 2 decimals.
Photo of Keith

Keith, Champion

  • 1,030 Points 1k badge 2x thumb
Mark, i've used this formula and it retains the decimals and it also extends to millions

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

If($value>=1000000,"$" & ToFormattedText($value, "comma_dot") & $decimals,
If($Value=0,"$0.00",
If($Value<0, "- ")
&
"$" & List(",",$Millions,$Thousands,$Hundreds) & $Decimals))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Thx Keith. I’ve added it to my collection.
Photo of Heather Bryant

Heather Bryant

  • 994 Points 500 badge 2x thumb
Mark, thanks for your help. This did the trick.