Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4624 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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"; "," ; ¶ )

  • Like 3
Posted

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.

Posted

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.

Posted

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

Posted

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.

Posted

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

Posted

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

  • Newbies
Posted

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.

This topic is 4624 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.