For those that have field names that start with and underscore such as some do when identifying key fields, the new ExecuteSQL function does not play nice with them directly. Normal escapes with a prime or quotes do not work.

You must use the Quote function.

ExecuteSQL ( "SELECT " & Quote ( "_kp_ID" ) & " FROM table"; "," ; ¶ )

Thanks for pointing this out.

Also applies if the table name starts with an underscore.

And there's likely a bunch of other exceptions too.

You also have to be careful with field names which have periods (".") in the name. This disrupts conversion from FMKR 7-11 files to the new FMKR12 format. But this may also affect other functions within Filemaker such as the new ExecuteSQL function.

Looks like the days of free naming of field names and variables are over. I wish Filemaker could clarify what the naming rules are so we don't have to pull out our hair when bugs occur.

I wouldnt recommend naming fields with periods to being with. I wouldnt mind if FileMaker would go to stricter data types though. It would make SQL integration a whole lot easier as well as the ODBC driver not having to deal with how loose it is.

Many thanks Mr Vodka. I was starting to get a bit worried about this behaviour. Having discovered ExecuteSQL I was all up for chucking out 80% of my TOs, then hit this snag as all my key fields (local and foreign) have underscores. Problem solved albeit making the expressions a bit more complex than ideal.

Anyway, thanks for the very useful post.

MIchael

Looks like the days of free naming of field names and variables are over. I wish Filemaker could clarify what the naming rules are so we don't have to pull out our hair when bugs occur.

Free-naming never really was an option if you had to do any kind of integration with other systems.

The rules are simple and they cover pretty much everything out there:

only use a-z, A-Z and the underscore in your table, TO, field, layout and script names.

Don't start your object names with an underscore or a number.

You mention A-Z a-z and do not START names with numbers or underscores. But numbers within names are OK?

For the most part... I have a vague memory of running into problems with numbers but I can't recall the exact circumstances.

Another issue to watch for is field names that are SQL functions. These too must be wrapped in quotes. I set up a test file to work through the syntax for querying field names with spaces and I kept getting a question mark. After much hair-pulling I wrapped my field "Year" in escaped quotes, "Year", and all worked.