ExecuteSQL Function: Table & Field Name Formatting

With the newest file format of FileMaker 12, we also received the function ExecuteSQL to many developer’s delight. From then on, it has helped expand many FileMaker files in its capabilities, one being reporting. If SQL wasn’t something a developer already knew, they could try SQL Explorer made by SeedCode to easily implement this new function, and also, learn a little bit of how the query is formatted to put it in to FileMaker.

Formatting SQL for FileMaker may be different than regular SQL. One of the biggest things in formatting SQL for FileMaker is the use of quotes that may be needed around table and field names put into the query. This to escape any characters that SQL does not work well with. However, this presents its own problem. If field or table names change, and they are hardcoded in the SQL query, the query will not work.

Here are a couple custom functions I made to help with the formatting in the ExecuteSQL function query:

FieldName ( Field )

Let ( [

f = GetFieldName( Field ) ;

l = Length( f );

p = Position( f ; "::" ; 0 ; 1 ) + 1

] ;

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

)

TableName ( Field )

Let ( [

f = GetFieldName( Field ) ;

l = Length( f ) ;

p = Position( f ; "::" ; 0 ; 1 ) ;

r = Right ( f ; l-p ) ;

rl = Length ( r ) + 1

] ;

"\"" & Left( f ; l-rl ) & "\""

)

This is how I used the custom functions in a basic SQL query:

ExecuteSQL (

 "SELECT " & FieldName ( TableName::FieldName) & "

FROM " & TableName ( TableName::FieldName) ;

 " | " ; ¶ )

Which makes the query look like:

SELECT “FieldName”

FROM “TableName”

There still could be other things that could go wrong with the query, but that should cover any table or field name change, and any offending characters.