Forum Discussion

AliMohsenian's avatar
AliMohsenian
Qrew Trainee
2 years ago

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

6 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago
    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
    ------------------------------
    • DougHenning1's avatar
      DougHenning1
      Community Manager
      2 years ago
      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__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        2 years ago
        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
        ------------------------------
    • AliMohsenian's avatar
      AliMohsenian
      Qrew Trainee
      2 years ago
      as always Mark... PERFECT. 

      thank you!

      ------------------------------
      Ali Mohsenian
      ------------------------------