Discussions

Expand all | Collapse all

toFormattedText rounding and changing hard coded number in formula field

  • 1.  toFormattedText rounding and changing hard coded number in formula field

     
    Posted 01-22-2020 11:51
    Hi All, 

    I am attempting to format a numeric field in a formula-text. Using the example for toFormattedText: ToFormattedText(1234567.89,"comma_dot"), my response should be "1,234,567.89". So why is it that when I display that field on the form, it is displaying: 1,234,567.9 !?!?!?!?!  

    I tried to use this formula to convert a currency field, and it is rounding it to the first decimal point. Any ideas what is going on?



    ------------------------------
    Ursula
    ------------------------------


  • 2.  RE: toFormattedText rounding and changing hard coded number in formula field

    Posted 01-22-2020 13:38
    Edited by Austin K 01-22-2020 14:20
    I see the exact same thing. If I save the formula as the example shows and go back into it the number is also different. "1234567.89" becomes "1234567.9" in my formula even.

    I was able to get it to finally display 2 decimals by using a Formula Numeric field(set to 2 decimal places in the properties) and this formula ToNumber(ToFormattedText(1234567.9,"comma_dot")) but it rounds 89 up to 90. Funnily enough if you do the same function but with dot_comma instead QuickBase interprets the first dot as the first decimal place instead of the first separator and only displays 1.23 instead of the full number. So this whole function may be a little screwy depending on your app settings as well, I assume that is what is causing that in mine.


  • 3.  RE: toFormattedText rounding and changing hard coded number in formula field

     
    Posted 01-23-2020 13:13
    That is really concerning to me. We use this often when converting numeric to text for Word document generation. If it is rounding the numbers or truncating them, then what is the point of having this as a formula?

    ------------------------------
    Ursula
    ------------------------------



  • 4.  RE: toFormattedText rounding and changing hard coded number in formula field

    Posted 01-23-2020 14:47
    I have never really got those to fully cooperate for me, so I do it the old way.  Here is one of my formulas.


    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, "<font color=red>- ")
    &
    "$" & List(",",$Thousands,$Hundreds) & $Decimals)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: toFormattedText rounding and changing hard coded number in formula field

     
    Posted 01-23-2020 15:21
    Thanks mark! 
    I actually ended up having to do something similar, but included more functionality for the decimal places, since that is where the issue is. The problem is, we shouldn't have to do any of this work, as the toFormattedText field is supposedly doing the formatting for us, not manipulating the data. Clearly, it is, which means that this formula should be removed until it is fixed. One rounded digit can skew data, and I would much rather not use it or even have it as an option if it isn't going to do what it is supposed to. To make sure that the decimals are not rounded, but are displayed exactly as they were input in the numeric field, I do the following: 

    -- Grab original value / Grab the decimal value
    var number OriginalValue = [Reimbursement Cap];
    var number theSplit = Frac([Reimbursement Cap]);

    --make sure the decimal value has at least two number, if it doesn't, add a 0. This will make sure that someone that just added a .5 displays as .50
    var number cents = ToNumber(Left(Right(ToText($theSplit)&"0","."),2));
    var text numt = ToText(Floor($OriginalValue));

    -- check to make sure that if the user did not add decimals, it still displays .00, adds a 0 before any single digits, and is formatted like so: ($xxx.xx) 
    var text centsFinal = "($" & ToFormattedText(ToNumber($numt),"comma_dot",3) & "." & If($cents>0 and $cents<10,"0"&ToText($cents),If($cents>=10,ToText($cents),"00")) & ")";

    The above function makes sure that no decimal is rounded up, but stays exactly as it was input in the field.

    ------------------------------
    Ursula
    ------------------------------