Earlier today I needed 1 billion to render in the calc engine as 1000000000, but FM helpfully converts numbers to scientific notation if they A) are >= 1 billion, and B) contain nothing but zeros following the initial digits.
Example where “A” and “B” are both true:
Example where “A” is true but “B” is not:
At any rate, in the first example, I needed a result of 1000000000, not “1.0E+9”.
Before I get to the work around, I’ll just point out that SetPrecision is of no help here because it only operates on values to the right of a decimal point.
I’ll also point out that I was looking for a work around for any large number, not just 1 billion.
The work around:
And the nice thing about this work around is you can apply it to any number, not just numbers expressed in scientific notation — so you can apply it across the board, and no harm done if it’s not needed, but it will quietly fix any numbers that need fixing.
Bonus (in case you didn’t already know, or had forgotten) — NumToJText is a great way to insert thousands separators, when you want those separators to be part of the data itself.
Interesting! Thanks for the tip, Kevin. I’ve always ignored this function, because I figured that I’ll never have a need for “Japanese Text” output, unless I happen to be working on a solution for a Japanese client or market. This is my first time playing around with it. One of those things that I just wouldn’t assume, reading the help text, especially the third parameter:
Type:
0 – half-width (hankaku) number
1 – full-width (zenkaku) number
2 – kanji number Japanese kanji character number
3 – traditional kanji number Japanese traditional kanji number
We use a custom function:
FormatNumberAsText ( Number ; Currency ; Separator ; Decimal ; Precision )
It’s so simple and gives me exactly what I expect, that I never bothered looking at the code… well, today I learned that it uses NumToJText as one of its transformations along the way. Ha!
Thanks for taking the time to comment Shawn.
I suspect a fair amount of number formatting CFs use NumToJText as “secret sauce”.
Does this not cut it then?
let (
a = “1000000000”
;
a)
Hi Damian,
It would depend on what you were trying to accomplish.
Kevin
basically it’s the same; forcing a variable to a text rather than number value?
Again, it depends on what you’re trying to accomplish. A text string is not the same thing as a number, e.g., https://filemakerhacks.com/2021/03/14/when-2-is-greater-than-10/
When a number appears as scientific notation I just go to the Inspector and choose “As entered” or “as decimal”, if I want to have thousand separations; formatting the result as text may cause future combine calculations to fail.
In this case I am talking about how the number renders within the calculation engine.
The “commas” trick at the end was a bonus tip. I agree that you normally would not want embedded commas in your data, but sometimes you do.
Hi Kevin, This has given me an idea! I wrote to you recently with an issue I was having with your ‘Portal Sorting part 2’ file, where the number to be sorted was large enough for Filemaker to render it as scientific notation. I’ve now made a change, leveraging NumToJText, and it works great! Let me know if you’d like to see a demo file, and I’ll send on over.
Hello James, absolutely. Thanks.