0 is dropping of when the total ends in 0
We have a number field is set to 2 decimal places and works great. shows the total for the invoice and is great. To print it all nice we added a Rich text field with a formula that shows the tota...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew Legendyes, many others have had the same problem. The issue is when you convert a number into text if it ends in a 00 is lost and if it ends on an even dollar then you lose both decimals.
Try this
var number Value = Round([Total Retail],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);
var text TotalRetailTextFormat=
If($Value=0,"$0.00",
If($Value<0, "<font color=red>- ")
&
"$" & List(",",$Thousands,$Hundreds) & $Decimals);
"<table bgcolor=\"white\" border='0' cellpadding='1' cellspacing='0'\nstyle='width:100%;font-family:Arial;'>" &
" <tbody>" &
" <tr>" &
"" &
" <td style='text-align: right'" &
" <p><span style='font-size:20px;'><strong>Total: $\n" & $TotalRetailTextFormat & "</strong></span></p>" &
" </tr>" &
" <td style='text-align: right'" &
" <p><span style ='font-size:14px;'>" & "</span></p></td>" &
" <tr>" &
" </tr>" &
" <tr><td style='text-align: center'" &
" <p><span style='font-size:16px;'>Thank you! </span></p>" &
" </tr>" &
" </tbody>" &
"</table>"
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Try this
var number Value = Round([Total Retail],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);
var text TotalRetailTextFormat=
If($Value=0,"$0.00",
If($Value<0, "<font color=red>- ")
&
"$" & List(",",$Thousands,$Hundreds) & $Decimals);
"<table bgcolor=\"white\" border='0' cellpadding='1' cellspacing='0'\nstyle='width:100%;font-family:Arial;'>" &
" <tbody>" &
" <tr>" &
"" &
" <td style='text-align: right'" &
" <p><span style='font-size:20px;'><strong>Total: $\n" & $TotalRetailTextFormat & "</strong></span></p>" &
" </tr>" &
" <td style='text-align: right'" &
" <p><span style ='font-size:14px;'>" & "</span></p></td>" &
" <tr>" &
" </tr>" &
" <tr><td style='text-align: center'" &
" <p><span style='font-size:16px;'>Thank you! </span></p>" &
" </tr>" &
" </tbody>" &
"</table>"
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
- You could also use PadRight to fill in the missing zero(s):
var Number total = Round([Total Retail], 0.01); var Text fraction = PadRight(ToText(($total - Int($total)) * 100), 2, "0"); var Text TotalRetailTextFormat = ToText(Int($total)) & "." & $fraction;
------------------------------
Doug Henning
------------------------------- MarkShnier__YouYes, Doug's is simpler, but mine should also do the commas, like
$1,234.56
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------- Great point Mark! You can also add number comma formatting by using the ToFormattedText function:
var Number total = Round([Total Retail], 0.01);
var Text fraction = PadRight(ToText(($total - Int($total)) * 100), 2, "0");
var Text TotalRetailTextFormat = ToFormattedText(Int($total), "comma_dot") & "." & $fraction;
------------------------------
Doug Henning
------------------------------
- as always Mark... PERFECT.
thank you!
------------------------------
Ali Mohsenian
------------------------------
