When 2 is greater than 10

Today we’re going to take a quick look at a potential “gotcha” that can occur when performing numeric comparisons. Jason DeLooze and I discussed the general behavior back in 2011 (Space Is The Place), but this time around I want to focus on one particular set of circumstances where the problem can arise.

At first glance this may appear to apply to beginner-level developers only, but the problem is nuanced, and I have occasionally seen it crop up in solutions written by experienced developers as well as those created by newbies.

Specifically, when comparing two variables, for example…

ExitLoopIf [ $counter >= $rows ]

…FileMaker must determine whether to do a text comparison or a number comparison, and under certain circumstances, contrary to developer expectations, may perform the comparison in “text space” rather than in “number space”.

Of course we can explicitly ensure that all comparisons take place in “number space” like so…

ExitLoopIf [ GetAsNumber ( $counter ) >= GetAsNumber ( $rows ) ]

…but let’s dig a little deeper to see the circumstances under which GetAsNumber is actually necessary.

First let’s look at the $counter variable… typically it is declared like so:

Set Variable [ $counter ; 1 ]

…and incremented like so…

Set Variable [ $counter ; $counter + 1 ]

Under these circumstances, there is no ambiguity — FileMaker understands that $counter is an integer, and there’s no reason to wrap it in GetAsNumber.

What about the $rows variable?

Here is where things get interesting… if you populate $rows via a standard numeric function such as Get ( FoundCount ) then FileMaker will consider $rows to be numeric, and assuming $counter = 2 and $rows = 10,

$counter >= $rows

…will behave as expected and evaluate as 0 (i.e., false), since 2 is less than 10.

But what happens if you have twelve Arizona customers in your CUSTOMERS table…

…and populate $counter and $rows like so?

In this case $counter > $rows will evaluate as 1 (i.e., true) because ExecuteSQL always returns results as text, and in text space 2 is greater than any “word” that starts with 1, just as “B” is greater than “A”, “AA”, “ABC”, “AZZZZZZ”, etc.

What if the $rows variable comes from JSON? That depends on how the JSON element in question has been defined. If the element has been defined as JSONNumber, FileMaker will interpret it as such and perform the comparison in number space.

But if the JSON element has been defined as a string (i.e., text), then…

…FileMaker performs the comparison in text space.

Basically, if either of the variables being compared are interpreted as text, the comparison will take place in text space rather than in number space.

Minor digression: given that FileMaker is interpreting $rows in the above example as text, what happens if we attempt to increment it by 1?

FileMaker recasts the variable as a number, performs the math as instructed…

…and the comparison now will take place in “number space”.

(End of minor digression.)

OK, we’ve seen some examples of how a numeric value in a variable can be interpreted as text. Are there other ways this could happen?

Yes, there are lots of other ways, for example, a numeric value sitting in a text field or a calculated field with a text result are two ways. A third would be where the value comes in via a script parameter that FileMaker interprets as text, and a fourth would be if the numeric value has intentionally been cast as text, e.g.,

Before we call it quits today, there’s one more aspect to this I’d like to mention, and I will venture to guess that this represents a “ticking time bomb” in many solutions out there… one that in some cases has never detonated, and in other cases that detonates once in a while, but then appears to be non-reproducible so has not been corrected.

I’m speaking of situations where, coincidentally, a text comparison behaves the same as a number comparison, so it appears that all is well. Here’s an example… you have a routine that iterates as many times as there are “pending” records in a “queue” table. You aren’t aware that SQL results are returned as text, but since there typically are only a few pending records in that table…

…you’ve dodged a bullet so far and your script works as intended…

…which is to say that, as long as the value in $pending is less than 10, the test at step 7 will function as desired, regardless of whether the comparison takes place in text space (as it does in this example) or in number space (as you, the developer, intended).

But then that fateful day arrives when there are ten or more pending queue records…

And, unexpectedly, the script bails out prematurely.

(A defining characteristic of this issue: when your iterator stops iterating at 2.)

So we close with a common-sense recommendation: if you aren’t 100% certain that FileMaker will interpret a value as a number, wrap it in GetAsNumber to be on the safe side…

…and all will be well.