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;fontsize:9pt\">$"&[SubTotal w/ Vacancy]&"</div>"
The [SubTotal] 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?
This is what I'm using in the Rich Text Formula field:"<div style=\"color:#ff0000;fontsize:9pt\">$"&[SubTotal w/ Vacancy]&"</div>"
The [SubTotal] 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?
 1,116 Points
Posted 9 months ago
Alex Molochko  now with 2019 Certification!
 3,370 Points
That's pretty interesting. What happens when you try wrapping the currency field with a ToText() ?
QuickBaseCoach App Dev./Training, Champion
 69,784 Points
I have a formula I will post when i get a chance....
QuickBaseCoach App Dev./Training, Champion
 69,784 Points
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)
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)
Keith, Champion
 1,148 Points
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))
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))
QuickBaseCoach App Dev./Training, Champion
 69,784 Points
Thx Keith. I’ve added it to my collection.
 1,116 Points
Mark, thanks for your help. This did the trick.
 70 Points
"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."
For the record I'm in an Advanced Formulas session at Empower right now and Eric Mohlman used ToFormattedText in an example and confirmed that the function drops trailing zeroes when converting decimals to text and that it's a known weakness. Unfortunately the documentation still doesn't speak to this issue!
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."
For the record I'm in an Advanced Formulas session at Empower right now and Eric Mohlman used ToFormattedText in an example and confirmed that the function drops trailing zeroes when converting decimals to text and that it's a known weakness. Unfortunately the documentation still doesn't speak to this issue!
 70 Points
This is Eric's version, to convert a currency field to text with everything intact:
"$" &
ToFormattedText([Price],"comma_dot",3) &
If(
not Contains(ToText([Price]),"."),
".00",
Length(Right(ToText([Price]),"."))=1,"0"
)
"$" &
ToFormattedText([Price],"comma_dot",3) &
If(
not Contains(ToText([Price]),"."),
".00",
Length(Right(ToText([Price]),"."))=1,"0"
)
Related Categories

Tables & fields
 7280 Conversations
 189 Followers
Heather Bryant