0 is dropping of when the total ends in 0

Question

We have a number field is set to 2 decimal places and works great. shows the total for the invoice and is great.&nbsp;To print it all nice we added a Rich text field with a formula that shows the total again"&lt;table bgcolor=\"white\" border='0' cellpadding='1' cellspacing='0'
style='width:100%;font-family:Arial;'&gt;" &amp;" &lt;tbody&gt;" &amp;" &lt;tr&gt;" &amp;"" &amp;" &lt;td style='text-align: right'" &amp;" &lt;p&gt;&lt;span style='font-size:20px;'&gt;&lt;strong&gt;Total: $
" &amp; Floor([Total Retail], 0.01) &amp; "&lt;/strong&gt;&lt;/span&gt;&lt;/p&gt;" &amp;" &lt;/tr&gt;" &amp;" &lt;td style='text-align: right'" &amp;" &lt;p&gt;&lt;span style ='font-size:14px;'&gt;" &amp; "&lt;/span&gt;&lt;/p&gt;&lt;/td&gt;" &amp;" &lt;tr&gt;" &amp;" &lt;/tr&gt;" &amp;" &lt;tr&gt;&lt;td style='text-align: center'" &amp;" &lt;p&gt;&lt;span style='font-size:16px;'&gt;Thank you! &lt;/span&gt;&lt;/p&gt;" &amp; " &lt;/tr&gt;" &amp;" &lt;/tbody&gt;" &amp;"&lt;/table&gt;"BUT its all perfect UNTIL the total ends in a 0. then it cuts it off...So $795.10 shows as $795.1Any thoughts what I am missing?
------------------------------Ali Mohsenian------------------------------

markshnier__you · Answer

yes, 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.&nbsp;&nbsp;Try thisvar number Value = Round([Total Retail],0.01);var text Decimals = "." &amp; Right(ToText(Int(Abs($value) * 100)),2);var text Thousands = If(Abs($Value)&gt;=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&lt;0, "&lt;font color=red&gt;- ")&amp;"$" &amp; List(",",$Thousands,$Hundreds) &amp; $Decimals);"&lt;table bgcolor=\"white\" border='0' cellpadding='1' cellspacing='0'
style='width:100%;font-family:Arial;'&gt;" &amp;" &lt;tbody&gt;" &amp;" &lt;tr&gt;" &amp;"" &amp;" &lt;td style='text-align: right'" &amp;" &lt;p&gt;&lt;span style='font-size:20px;'&gt;&lt;strong&gt;Total: $
" &amp; $TotalRetailTextFormat &amp; "&lt;/strong&gt;&lt;/span&gt;&lt;/p&gt;" &amp;" &lt;/tr&gt;" &amp;" &lt;td style='text-align: right'" &amp;" &lt;p&gt;&lt;span style ='font-size:14px;'&gt;" &amp; "&lt;/span&gt;&lt;/p&gt;&lt;/td&gt;" &amp;" &lt;tr&gt;" &amp;" &lt;/tr&gt;" &amp;" &lt;tr&gt;&lt;td style='text-align: center'" &amp;" &lt;p&gt;&lt;span style='font-size:16px;'&gt;Thank you! &lt;/span&gt;&lt;/p&gt;" &amp;" &lt;/tr&gt;" &amp;" &lt;/tbody&gt;" &amp;"&lt;/table&gt;"------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

doughenning1 · Answer

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)) &amp; "." &amp; $fraction;​------------------------------Doug Henning------------------------------

markshnier__you · Answer

Yes, Doug's is simpler, but mine should also do the commas, like$1,234.56------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

doughenning1 · Answer

Great point Mark!&nbsp; 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") &amp; "." &amp; $fraction;------------------------------Doug Henning------------------------------

markshnier__you · Answer

Thx Doug, I will add your formula to the Borg Collective knowledge.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

0 is dropping of when the total ends in 0