GetAsNumber Gotcha

Heads-Up!
, ,
1

I may be late to the game… but I just came across a gotcha I thought would be good to share.

GetAsNumber returns the numerical characters in a given string. In essence, this function is equivalent to Filter (textToFilter; "0123456789"), only the return value is a number and not text.

There is an exception. GetAsNumber ("?") returns the question mark character instead of an empty value. I speculate the reason is because FileMaker uses the question mark character as a return value for some functions to indicate an error condition and Claris wants to ensure the error condition propagates throughout this function.

Hope this helps.

4 Likes
2

The take-away is to do precautionary testing of content. Especially as the presence of a question mark in the data for maths wins.

Let ( price = "?" ; 10 * price ) // result is "?"

In simple tests, summary fields ignore a question mark or treat it as zero.

ExecuteSQL() statements use question marks in a completely different way. They are placeholders for parameters. It should be possible to contrive a way to create unexpected behaviour in an ExecuteSQL statement by passing in a question mark instead of a text or number value. That would "steal" one of the parameters, causing the following parameters to be mismatched.

3

One common test I've used is to compare the field with a GetAsNumber(field). If they're not the same, you know you may have a data issue.

4

Not necessarily. You may simply want to strip non-numeric characters from a string.

5

Happy Birthday @bdbd :champagne:

6

I struggled with, and eventually gave up on GetAsNumber( MyTable::MyField ) = MyTable::MyField as a test for detecting non-numeric data entry. (My use case was to allowing the user to do their data entry in a set of global fields, and then validate their input before saving it to a record.)

  1. If MyField is data-type number, and someone enters something like 123hello, then the above test returns a True result, but I want a test that flags the 123hello value as non-numeric.

  2. If MyField is data-type text, and someone enters something like 12.00 or 100000000000000000000000 then the above test returns a False result, but I want a test that does not flag these as non-numeric.

2 Likes
7

I made an error in the original post. The equivalent I gave does not take into account a thousand separator nor a decimal separator… and these are locale dependent. Filter (textToFilter; "0123456789 ,.") would be more accurate… though not perfect. Anyhow… my point was not on the Filter function but the GetAsNumber function. Hope my inaccuracy is OK in this context.

2 Likes