Forum Discussion
QuickBaseCoachD
7 years agoQrew Captain
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)
- KeithJusas7 years agoQrew CaptainMark, 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)) - AlexCertificati7 years agoQrew CadetSo, 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? - QuickBaseCoachD7 years agoQrew CaptainI 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. - QuickBaseCoachD7 years agoQrew CaptainThx Keith. I�ve added it to my collection.
- HeatherBryant27 years agoQrew Assistant CaptainMark, thanks for your help. This did the trick.
- AlexMolochko16 years agoQrew MemberI 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! - AlexMolochko16 years agoQrew MemberThis 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"
)