Level: Intermediate

# 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.

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.

## 8 thoughts on “When 2 is greater than 10”

1. As always, very insightful Kevin. The ESQL text result thing was something I was aware of, but it’s great to see things clarified.

2. Caleb Ruth says:

Nice analysis of the issues Kevin. Thanks

3. Shawn A. Krueger says:

Great stuff. Definitely one of those “gotchas” that comes up.

4. Allan McDonald says:

I’ve seen this in loops now and again, and have learned (via the hard way) to ensure variables are set as numbers at the beginning of scripts etc.

Very useful to see the logic behind it, rather than just as a learned behaviour. Thanks Kevin.

5. Paul Jansen says:

Kevin,

Thanks ever so much for this article. It is a timely reminder to even the most experienced of us to take care – I know I have been guilty of using JSON strings with numbers just because it’s easier to treat every object the same. In fact because the JSON format is text based I had kind of assumed that FileMaker treated everything extracted from JSON as text. It’s good to be reminded that FileMaker is pretty good at working out data types that we would need to be explicit about in other development environments.

1. Kevin Frank says:

I appreciate you saying so.

I know that you know this, but for those who may not, another benefit of formatting numbers in JSON as text (JSONString) vs. number (JSONNumber), is that JSONNumber renders empty values as 0, whereas JSONString returns “”.

Depending on circumstances either behavior might be preferable, but typically if I pass in an empty value, I want it to remain empty, and not be translated to 0.

6. Another way I like to ensure that a value is cast as a number is to add +0 to it. Compared to GetAsNumber(), this has the added advantage (or disadvantage, in some circumstances perhaps), of replacing empty values with 0. Plus, it takes up less space.

Here’s another gotcha where this makes a big difference.

Min ( “” ; 5 )
or
Min ( GetAsNumber ( “” ) ; 5 )

You might think the answer is 0, but in both cases, it’s 5!

But…

Min ( “” + 0 ; 5 )

This returns 0. Which is usually the desired behaviour.

7. Kevin Frank says:

That’s a great explanation of a subtle nuance I was not aware of. Thanks Jason.

This site uses Akismet to reduce spam. Learn how your comment data is processed.