Skip to main content
Question

0 is dropping of when the total ends in 0

  • December 13, 2022
  • 0 replies
  • 118 views

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 total again

"<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" & Floor([Total Retail], 0.01) & "</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>"

BUT its all perfect UNTIL the total ends in a 0. then it cuts it off...
So $795.10 shows as $795.1

Any thoughts what I am missing?



------------------------------
Ali Mohsenian
------------------------------
    This topic has been closed for replies.

    MarkShnierYou
    Forum|alt.badge.img+22
    • MarkShnierYou
    • Quickbase Alumni
    • December 13, 2022
    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.  

    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
    ------------------------------
    Mark Shnier. Your Quickbase Coach
      Terms & ConditionsAccessibility statement