Skip links

SQLQueryExecute and field names with underscores

FileMaker training, courses, coaching

FileMaker Quick Tip: SQL queries and field namesFileMaker 12 includes "SQLQueryExecute", a function that is extremely helpful in daily FileMaker practice. This function can be used very well, for example, to create value lists from field values of other tables without the need for a relationship. In conjunction with the "MagicValueList" function presented by Arnold Kegebein in the FileMaker magazines 201301 and 201302, there are practically undreamt-of possibilities in terms of FileMaker and value lists.

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 :-)

Leave a comment

Share this page:

ERP software as flexible as your company.
We will be happy to advise you.

Customizable ERP software for Mac, Windows and iOS.

You are here: SQLQueryExecute in FileMaker with field names and underscores