Dynamic field names when using ExecuteSQL in FileMaker

One of the coolest new features in FileMaker 12 is the ExecuteSQL calculation function. There are several plug-in that makes FileMakers entire SQL-engine available to the developer but now there is also a native FileMaker function that makes Select-queries possible.
This post will not try to explain how to use ExecuteSQL but it will show one way to solve a common problem occurring when hardcoding names for tables or fields within a query.

The syntax for ExecuteSQL is as follows

ExecuteSQL ( query ; field separator ; row separator { ; arguments… } )

The Query itself is defined as a string passed to ExecuteSQL as the first parameter. A simple query can look like this

SELECT p.Givenname, p.Surname FROM Person p WHERE p.person_id = 1

Since the query is a string (pure text) it will not automatically update changes in fieldnames and tablenames and thus it will stop working if a name is changed. For example if someone changed the name of the field Givenname to Firstname in the Define Database dialog, the query above would instantly stop working. This can cause very serious issues that can take a long time to find and fix.

The solution is very simple but important to implement from the start. Create a new Custom Function and name it GetFieldNameSQL.

Give it one parameter that is named Field.

The calculation syntax for the function is

Let([

f = GetFieldName(Field);
l = Length(f);
p = Position( f; "::"; 0; 1) + 1

];

"\"" & Right(f; l-p) & "\""

)

What this does is to get the name of the field passed as parameter and stripping it of it´s tablename and then returning it with escaped quotes. This have two advantages.

Firstly it makes the fieldname in your query dynamic, meaning that it allows you to change fieldnames in your database without breaking the query.

Secondly it allows you to use reserved words as fieldname and even use fieldnames with spaces in it or special characters like the swedish characters å, ä and ö (I´m from sweden so that seems like a good example). You can do this without worrying about escaping characters or quotations.

When using the custom function in a query it looks like this.

"SELECT p."& GetFieldNameSQL( Person::Givenname ) &", p."& GetFieldNameSQL( Person::Surname ) &" FROM Person p WHERE p.person_id = 1"

The entire ExecuteSQL could look like this

ExecuteSQL ( "SELECT p."& GetFieldNameSQL( Person::Givenname ) &", p."& GetFieldNameSQL( Person::Surname ) &" FROM Person p WHERE p.person_id = ?"; "; "; "¶"; 1)

I strongly encourage you to use this or a simliar method to make fieldnames and even tablenames dynamic when working with ExecuteSQL in FileMaker. I am confident it will save you from tedious error searching later on.