Decimal Formatting in Calculation Field

Tap Forms – Organizer Database App for Mac, iPhone, and iPad › Forums › Using Tap Forms › Decimal Formatting in Calculation Field

Tagged: 

Viewing 7 reply threads
  • Author
    Posts
  • July 31, 2021 at 4:16 PM #44878

    Chris Medeiros
    Participant

    Hello Tap Forms Community,

    Can someone give me a tip on how to format a calculated field result in the attached pic?

    Instead of “…(0.5600000000000001)…”, I’d prefer it to be (0.56).

    Attachments:
    You must be logged in to view attached files.
    August 1, 2021 at 12:56 AM #44882

    Sam Moffatt
    Participant

    What is the type of current year rate? I was trying to figure out how to repro this to figure out a viable solution but I didn’t see one. I create a number field and populated it with 0.56 and the text calculation field printed .56 as the rate as I’d expect. I tried doing it as a calculation field with 56/100 but that didn’t seem to trigger the weird behaviour either.

    What you’re seeing is floating point rounding errors. The 0.56 ends up being stored as 0.560000000000000053290705182007513940334320068359375 which is why you see 0.5600000000000001 as the 5 rounds up:

    0.5600000000000001
0.560000000000000053290705182007513940334320068359375

    I suspect this is a reflection of the underlying JSON data model (Javascript floating point numbers are IEEE-754 64-bit double precision numbers) and unfortunately leaking out. For some more gory details I found a YouTube video on floating point representation and rounding error and an online converter that lets you put in 0.56 and see what the computer is doing.

    I have an sprintf implementation that I ported for printing formatted strings in Javascript that should handle it if you want, there was a not dissimilar thread on if to use a calc or script field that shows formatting floating point numbers. That may yet be the solution for you as well but it’s a bit heavier than a simple calc field.

    August 1, 2021 at 11:16 AM #44883

    Daniel Leu
    Participant

    Assuming that the result of your calculation is a number: in the field properties, you can select the number of decimal places to something like 2.

    August 2, 2021 at 5:24 PM #44896

    Chris Medeiros
    Participant

    Sam,

    To answer your question, the Current Year Rate field type is “Number”, set to 3 decimal places. The odd part…If I change the Current Year Rate value to 0.55 or .57, the report is formatted fine. It’s just 0.56 that seems to have the rounding issue. You’re probably right on the money with your explanation. The only person that will see this report is my Tax Person, so it’s not critical. Thank you for your time and thoughts on this Sam!

    Daniel,

    Thanks for your suggestion. The calculation field result is “Text”. However, I could try sandwiching a calculation field set to Number, 2 Decimals in between the text and see if it handles the rounding issue better.

    I’ll let you know how that works.

    Attachments:
    You must be logged in to view attached files.
    August 2, 2021 at 9:38 PM #44902

    Sam Moffatt
    Participant

    The other two numbers round correctly to the right value:

    0.5600000000000001
0.560000000000000053290705182007513940334320068359375
0.5500000000000000444089209850062616169452667236328125
0.56999999999999995115018691649311222136020660400390625

    The script field is probably the answer if you really want to fix it, I don’t think there is a way within a calc field you can make it work when embedded in a string like that.

    August 4, 2021 at 9:29 AM #44907

    Brendan
    Keymaster

    For a Calculation field, use the FORMAT() function to format your results.

    FORMAT(Deduction Amount; "0.00")

    That’ll ensure your values are always prefixed with a 0 if less than 1 and will always have 2 decimal places.

    August 4, 2021 at 5:14 PM #44919

    Sam Moffatt
    Participant

    oooh it’s in the text section, would it be possible to copy the documentation for that in the math section as well? I was looking in the math section for something to format numbers and TRUNC was the only thing I saw. Also looking in the text section, should STR2NUM be in the math area too?

    August 4, 2021 at 7:26 PM #44920

    Chris Medeiros
    Participant

    Thank you Brendan. That did the trick! I had used the format function built into the field Properties column for Date & Time formats, but I didn’t realize there was a separate FORMAT function to use in this case. Thanks for the example also!

    Thanks for your help also Sam!

    –Chris

  • Author
    Posts
Viewing 7 reply threads

You must be logged in to reply to this topic.