Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.
Sep 21, 2022 10:18 AM
I manually entered numbers in a Single Line field, then I changed the field type to Number, and the last two digits of the numbers changed before my eyes for no apparent reason. The change is in the last two digits (14 changed to 20, and 05 changed to 00). Note that I didn’t make a typo - I watched the digits change when I changed the field type to number.
I tried making a new number field and copy pasted the values in there, but the same thing happened there. I tried the same with a formula field with the same results.
It’s not a rounding error - the number field format is set to integer, and so is the formula field.
The screenshot SHOULD show the same number in every field - the “Group ID” field has the correct number.
Is there a ghost in the machine?
Solved! Go to Solution.
Sep 22, 2022 04:59 AM
Very interesting case. According to JS manual,
the “number” type cannot safely represent integer values larger than
(2^53-1)
(that’s9007199254740991
(15 digits)), or less than-(2^53-1)
for negatives.
that explains such ‘rounding’.
Sep 21, 2022 10:56 AM
Sigurður,
I get the same result — but only with very long numbers like the ones in your {Group ID} field. Your text string “number” is 17 characters long. If its length drops below 16 characters, this problem doesn’t occur.
With a 16-character number I had an interesting experience: I entered “9999999999999999” in the text column (your primary column). Copied, pasted into a second text column. Then I converted that column to a Number field (integer). When I did that, the value displayed changed from
9999999999999999
to
10000000000000000
!
I suspect it’s got something to do with floating-point numbers. I don’t understand floating-point math well at all. What I do know is that, you may not be rounding anything, but Airtable with those large numbers definitely is.
Worth noting that this problem occurs ONLY with numbers on this magnitude. It’s not the conversion from text to number that matters: it’s the length (size) of the number. If I type 9999999999999999 (sixteen nines in a row) into a number field, as soon as I exit the field, it gets converted to 10000000000000000. Does NOT happen with “99999999999999” (FIFTEEN nines).
William
Sep 21, 2022 11:15 AM
That’s fascinating. Thankfully I can accomplish what I need to do here with a Single Line field, so I don’t urgently need a solve, but this is very curious indeed.
Sep 21, 2022 11:29 AM
Actually it’s curious but not uncommon. More or less standard for very long numbers, I think, at least has been in the data systems I’ve worked with.
It’s never been a problem because I’ve only used these very long numbers as keys (primary/foreign) and I can store them as text. Never needed a number value that large. I don’t build databases that, say, track the US national debt, which gets up into numbers that make astronomers nervous.
number | US English | max #digits |
---|---|---|
999,999 | thousands | 6 |
999,999,999 | millions | 9 |
999,999,999,999 | billions | 12 |
999,999,999,999,999 | trillions | 15 |
Which is more than enough for the work I’ve done. I’ve built databases that handle hundreds of millions of dollars (although not yet in Airtable). If I am ever asked to build a database that handles something larger than trillions, I’m going to raise my rates.
Sep 22, 2022 04:59 AM
Very interesting case. According to JS manual,
the “number” type cannot safely represent integer values larger than
(2^53-1)
(that’s9007199254740991
(15 digits)), or less than-(2^53-1)
for negatives.
that explains such ‘rounding’.