Trying to keep the 0 after a decimal place in a numeric field eg. 1.10 1.20 etc

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
Numeric fields automatically remove the 0 at the end of a numeral with a decimal place, so 1.10 will become 1.1 but I want it to stay as 1.10.
Photo of Joshua Hamilton

Joshua Hamilton

  • 110 Points 100 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
np
Edit the field properties and set the # of decimals to 2
Photo of James Holton

James Holton

  • 114 Points 100 badge 2x thumb
Similar question. I have it set where it pulls correctly to 2 decimal places on any forms or tables. However, when I try to pull a field into a mailto: it loses the formatting. I saw a previous post of yours recommending using round to solve the problem for cutting it down to 2 points. That solved that problem. Now the only issue is when there is a 1.1 as in above it is losing the 0 and I need it added back.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Right,
so you will need to convert the number to text with a formula.  I have a small collection of them, but here is one that should work from my notes.

Substitute in your field name where I have the field [currency field]

var number Value = Round([currency field],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);

If($Value=0,"$0.00",
IF($Value<0, "-") & "$" & List(",",$Thousands,$Hundreds) & $Decimals)

Let me know how it goes and if I need to test the formula myself.
Photo of Ursula Ll

Ursula Ll

  • 1,450 Points 1k badge 2x thumb
what if you need to do this up to the millions, do you just keep adding variables for each digit?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
not tested but you woul;d need this extra line

var text Millions = If(Abs($Value)>=1000000,ToText(Int(Abs($Value)/1000000)));


and then 

.....List(",",$Millions, $Thousands,$Hundreds) & $Decimals)
Photo of Ursula Ll

Ursula Ll

  • 1,450 Points 1k badge 2x thumb
saving my life here! Thank you!
Photo of Robert

Robert

  • 214 Points 100 badge 2x thumb
Thanks for the tip. As I needed to display negative values in red, I went with the following approach. I just check how many characters are after the decimal after formatting, then I add back ad is needed.

var text txtRounded = ToFormattedText(Round([yourvalue]*100,0.01),"none_dot");
var number wheredot=If(Contains($txtRounded,"."),Length(Right($txtRounded,".")),0);
var text fmtnbr = If($wheredot = 0, $txtRounded & ".00", If($wheredot = 1, $txtRounded & "0", $txtRounded)) ; 

If([yourvalue] < 0,  "<span style='color: #EA3C53'>" & $fmtnbr & "</span>",$fmtnbr)