
For this purpose, usually only simple SQL-queries are required, such as SELECT field name FROM Table WHERE criterion=xyz. Such queries work fine in FileMaker 12 - at least if the field names involved do not contain an underscore at the beginning. However, many FileMaker developers mark key fields with an underscore as a prefix so that they appear at the top of the field definitions and in the relationships graph. Unfortunately, there is a bug in all previous FileMaker versions which means that SQL queries with such field names do not work.
Result = "?"
If there is an underscore at the beginning of a field name, the "SQLQueryExecute" function only returns a question mark as the result. But don't worry: there are ways to work around this error.
Practical example: We have a customerDatabaseto which addresses in another table are linked with a 1:n relationship. The respective default address of the customer is marked with the value "1" in the field <>, all other addresses do not contain this marking. If we now want to find out the ID of the corresponding address data record from the customer database using an SQL query, the query would be as follows
SQLQueryExecute("SELECT _pk_address_ID FROM addresses WHERE sta.default=1"; ""; "")
This query would not work and would only return a question mark as a result. However, this is not because there is an error in the SQL query, but FileMaker simply does not seem to like the underscore of the field name <> at this point.
This can be remedied by quoting the field name - many thanks at this point for the corresponding tip from the Forum of the FileMaker Magazine by Otmar Kramis. If the query is modified as follows:
SQLQueryExecute("SELECT \"_pk_address_ID\" FROM addresses WHERE sta.default=1"; ""; "")
...then it also works without problems in FileMaker :-)
Addendum: Tips for the robust development of SQL queries
The FileMaker Developer Robert Kaiser recommends in another forum post a Articles from filemakerhacks.comwhich describes how SQL queries in FileMaker can be developed in such a way that they function correctly even after renaming or changing relationships or fields, for example. This solution also eliminates the problems described with underscores at the beginning of field names. The article describes the use of custom functions, with which SQL queries in FileMaker can be developed extremely robustly. It is therefore worth delving further into this topic :-)

Markus Schall has been developing individual databases, interfaces and business applications based on Claris FileMaker since 1994. He is a Claris partner, FMM Award winner 2011 and developer of the ERP software gFM-Business. He is also a book author and founder of the M. Schall Publishers.
