I am trying to convert a number field to a text field, but keep the number formatted with commas. When formatted as a number, it reads: xx,xxx,xxx.xx but the commas don't stick when I use the 'totext' function. Any ideas?
 90 Points
Posted 11 months ago
QuickBaseCoach App Dev./Training, Champion
 53,702 Points
I have a few formuals, but try this one
var number Value = Round([Open Amount],0.01);
var text Decimals = "." & Right(ToText(Int($Value * 100)),2);
var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));
var text Hundreds=Right(ToText(Int($Value)),3);
var text Words =
List(",",$Thousands,$Hundreds) & $Decimals;
$Words
var number Value = Round([Open Amount],0.01);
var text Decimals = "." & Right(ToText(Int($Value * 100)),2);
var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));
var text Hundreds=Right(ToText(Int($Value)),3);
var text Words =
List(",",$Thousands,$Hundreds) & $Decimals;
$Words
 90 Points
thanks, but its not working. I assume I change out my numeric field for the 'value' in the formula?
QuickBaseCoach App Dev./Training, Champion
 53,612 Points
Sorry, I should have explanied.
Change where I have [Open Amount] to your numeric field
This formula makes use of formula variables.
https://help.quickbase.com/userassistance/formula_variables.html
Change where I have [Open Amount] to your numeric field
This formula makes use of formula variables.
https://help.quickbase.com/userassistance/formula_variables.html
 90 Points
wow, that's awesome. thank you! if I want to add millions in there, what do I do? and how do I round down to 1 decimal point?
QuickBaseCoach App Dev./Training, Champion
 53,612 Points
This is a another formula I have, but for some reason it drops the decimal after the number gets very large. It will also not deal with negative numbers.
var number MyNumber = Round([Big Number],0.1);
var number amt = ToNumber(Part(ToText($MyNumber), 1, "."));
var text dec = Part(ToText($MyNumber), 2, ".");
( If($amt < 1000,
ToText($amt),
$amt < 1000000,
NotRight(ToText($amt), 3) & "," &
Right(ToText($amt), 3),
$amt < 10000000,
NotRight(ToText($amt), 6) & "," &
Mid(ToText($amt), 2, 3) & "," &
Right(ToText($amt), 3),
$amt < 100000000,
NotRight(ToText($amt), 6) & "," & Mid(ToText($amt), 3, 3) & "," & Right(ToText($amt), 3), ""))&
If($dec <> "","." & If(Length($dec)<1,$dec & "0", $dec),"")
The alternative is to just use the build in function as Dan suggested
ToFormattedText([My number field], "comma_dot")
var number MyNumber = Round([Big Number],0.1);
var number amt = ToNumber(Part(ToText($MyNumber), 1, "."));
var text dec = Part(ToText($MyNumber), 2, ".");
( If($amt < 1000,
ToText($amt),
$amt < 1000000,
NotRight(ToText($amt), 3) & "," &
Right(ToText($amt), 3),
$amt < 10000000,
NotRight(ToText($amt), 6) & "," &
Mid(ToText($amt), 2, 3) & "," &
Right(ToText($amt), 3),
$amt < 100000000,
NotRight(ToText($amt), 6) & "," & Mid(ToText($amt), 3, 3) & "," & Right(ToText($amt), 3), ""))&
If($dec <> "","." & If(Length($dec)<1,$dec & "0", $dec),"")
The alternative is to just use the build in function as Dan suggested
ToFormattedText([My number field], "comma_dot")
 1,180 Points
There's also a builtin function: https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=188
Related Categories

Ask about Quick Base functionality
 902 Conversations
 63 Followers