Context & Table Occurrences
This is one of those gotchas that I have run into more times than I care to admit. Essentially, this one has to do with me thinking that I am referring to a table in a context other than the one that I am really referring to it in. By "context," of course I mean "table occurrence."
For example, suppose that you have multiple occurrences of a table on a graph. One sits by itself with no relationships based on it, and another has many relationships. When referencing the table and related, you need to be careful that you are referring to the correct occurrence. Otherwise, any attempt to reference related data will fail. And unless you are doing some serious error trapping, the failure might be apparent to you.
Booleans vs Strings: "False" is not False!
If you've used boolean values in FileMaker, you may have run into this one yourself. On many occasions, I have, for whatever reason, found myself trying to compare a boolean value to a string, and the results have not been what I've expected them to be. For example, suppose that you set a variable to true, like this...
Set Variable[$something; Value:True]
... and you then try to evaluate the variable, like this...
If[$something = True]
Show Custom Dialog["$something"; "$something is true."]
Else
Show Custom Dialog["$something"; "$something is false."]
End If
... you will get "$something is true." That makes sense. However, if you change the conditional slightly, so that you are comparing $something to the string "True," like this...
If[$something = True]
Show Custom Dialog["$something"; "$something is true."]
Else
Show Custom Dialog["$something"; "$something is false."]
End If
.. you will get "$something is false." At first, this looks like a bug. But it isn't. The reason is that FileMaker differentiates between the string value of "True" and the boolean value of True. According to FileMaker's documentation:
A Boolean value is either True or False. A field containing any number except zero evaluates as True. A field containing zero, no data, or content that does not resolve into a number evaluates as False. For example, a field containing "ABC," "ABC0," or an empty field is False. A field containing "1" or "ABC2" is True.
So be careful if you use booleans!
Execute SQL is not ESS
One of the very cool things about FileMaker Pro 9's External SQL Source (ESS) function is that, when a database that uses ESS is hosted on FileMaker Server, all of the interaction with the external database funnels through the server itself. What this means is that the clients that access that data through the server do no need to have their own ODBC datasources configured locally. How cool is that?!?
However, keep in mind that other FileMaker functions that require ODBC do not work the same way as ESS. For example, the infamous Execute SQL script step, which uses an ODBC data source to execute a SQL command against a remote database, still requires that the ODBC datasource that is being referred to be setup as a datasource on the client!
Which brings me to a FileMaker 9+ wish list item: Enhance the "Execute SQL" script step! Please! I'd like to see it do two things: [1] Provide an option for running that SQL through the server and [2] provide an option for getting access to any results returned by the SQL call (for example, stored procedure output parameters).
So there you go. I hope that in sharing these "gotchas" it will help other FileMaker users avoid them.
-- Tim
