Filemaker pro 16 sql query update#
Filemaker pro 16 sql query full#

I recommend finding the ideas you like and voting them up. There are many good ideas that have been logged to make ExecuteSQL() better in the FileMaker Product Ideas space. The Good is really good the Bad and the Ugly can mostly be avoided. It is not a very wide envelope, but do not let that discourage you or shunt it. It does some very unique things extremely well provided you stay within its performance envelope. In Encouragement of Using ExecuteSQL()ĮxecuteSQL() is an incredibly useful tool in our tool belt, and I would strongly encourage FileMaker developers to use it where it fits. You can also find an approach in that 2014 DevCon demo linked earlier in this blog post. A good approach to that is available here. One potential roadblock in using the IN clause to speed things up is that it does not allow dynamic parameters like elsewhere in the ExecuteSQL() calls. Here’s another good discussion about avoiding slow JOINs with sub-queries, by Taylor Sharpe. Explicitly repeating the JOIN predicates in the WHERE clause (by FileKraft).Using sub-selects with the IN clause (Greg Lane of Skeleton Key).It also provides pointers to possible performance optimizations: “ Looping script that went down from 3h 15m to 1m 45sec“.“ From 6 hours to 6 minutes with ExecuteSQL()”.This thread includes links to two success stories: Very often you can achieve faster results by breaking up a semi-complex query into different parts, sometimes even by mixing and matching ExecuteSQL() with other FileMaker features. You can often mitigate the first behavior with (semi) complex queries by not cramming too much into one request.

Filemaker pro 16 sql query full#

However, you may also need to carefully consider using the function in areas where you do not always full control over the state of the record - places such as calculation fields, hide conditions, conditional formats and tooltips. The fix can be as simple as making sure that all records are committed before running the ExecuteSQL() query. That second behavior where ExecuteSQL() will slow down when the user has an open record in the target table is discussed and demoed in my 2014 Devcon presentation.

Running queries (even very simple ones) against a table where the user has an open record in.

Complex queries that include JOINS, GROUP BY or SQL functions.

In our experience, there are two broad areas where it will slow down: Using ExecuteSQL() effectively all comes down to understanding the circumstances around what makes it fast and what makes it slow.

The Ugly: When it is slow it can be extremely slow.

The Good: ExecuteSQL() is blazingly fast.

So here is the good, the bad and the ugly: I certainly wouldn’t want to see people avoid it. While participating in the online FileMaker forum, it struck me that ExecuteSQL() seems to be getting a bad reputation, and I do not think it deserves it.

There are reserved SQL keywords that can clash with your field and table names.įor now, I want to focus on performance.

ExecuteSQL() supports a particular subset of the SQL syntax it does not always support SQL syntax that you may be used to from other environments.

Looking for “wim” will not return records where the value is “Wim”.

The fact that SQL queries are case sensitive.

Then there are subtle differences that can catch people out:

If you need the ability to manipulate data, there are many FileMaker plugins that provide that capability.

Filemaker pro 16 sql query update#

The big obvious restriction is that ExecuteSQL() does SELECTS only, in other words: you use it to collect data, you cannot use it to create or update existing records.

Don’t let the age of the blog post give you pause all of it is still very relevant if you are not so familiar with all the ins and outs of the function (and even serves as a refresher if you are familiar with the function). What Does ExecuteSQL() Do?įirst off, if you need to get up to speed on what ExecuteSQL() does and does not do, the best reference is this blog post by Kevin Frank and Beverly Voth. Ever since the ExecuteSQL() function was introduced in FileMaker Pro 12, there’s been much debate over its use and usefulness and its speed.