Cascading Value Lists: Learning ExecuteSQL
It's finally here, and it's a power tool you simply can't ignore. Yes, you can still build a FileMaker solution the same way you always have. With lots and lots of table occurrences to make FileMaker do exactly what you want it to do.
The problem, however, has always been information overload. As once the solution gets to a certain point, it becomes increasingly harder to decipher. This is due to the fact that FileMaker has always been a tightly bound environment between the data and the presentation of that data (the UI).
The new ExecuteSQL function presents a wide number of opportunities to SERIOUSLY (note the all caps!) reduce the complexity of both your relationship graph and the content of scripts.
The best way to visualize this is thinking of an apple tree. You can always start grabbing apples from the bottom. As you need apples from the top, you've got to come up with something to get you higher. Maybe you climb the tree. Maybe you see a board lying around and can lean it against the tree. Maybe there's enough boards to build a ladder. This is like adding table occurrences to achieve the end result.
Then again, wouldn't it be nice if you simply already had a ladder? This is the situation with ExecuteSQL. FileMaker now provides a tool which allows you to pull out any data (i.e. grab any apples) you wish at any time. While this has been possible for many years with plugins, some developers just never felt the need to venture into the land of SQL. Things can break and you have to learn a new syntax.
With excuses about SQL now gone, because it's baked in, - at least for the SELECT statement - you simply must start taking advantage of it.
This video is a great example of just how much this new function can reduce the complexity of your relationship graph - and it's only one example of many. Need to get your head around this new functionality? Take a ride on the SQL train!
Comments
Dedicated table occurrences for running SQL queries
Hi Matt,
Great video! I have a quick question about the dedicated TOs you advised creating solely for running SQL against (mentioned at 17:20 in the video). Did you mean that it's best practice to create the SQL TOs in addition to the standard developer tables (prefixed with the '@' symbol as specified at filemakerstandards.org) or that running SQL queries against the developer tables is enough? That is to say, do you recommend having a full set of developer tables prefixed with the '@' symbol AND a full set of tables prefixed with the 'SQL' prefix in a solution?
thanks so much
-P
Personal preference
Because the cost of a TO -performance wise -isn't that much by itself (other than an organizational cost), I opt to have both @ Developer tables and 'sql' prefixed tables.
This is purely for code clarity where it's a bit easier to read "SELECT id from sqlTable WHERE..." than "SELECT id from \"@ Developer\" WHERE..." (which I don't even know if it will work given the @ and space).
When you have to start escaping reserved words for tables and fields, your SQL code gets a bit more difficult to read. Using a dedicated prefix of 'sql' with a no spaces tablename just makes it a bit easier. You simply have to operate with rules such as "NEVER EVER rename any TO prefixed with 'sql' unless you are going to refactor (search and replace literal references)"
You can use the ObjectID custom function originally by Fabrice Nordman and being promoted by Kevin Frank (something I've been using since Fabrice came up with) but deciding on a fixed naming convention such as sqlTable name can be just as strong as abstracting tables and field names using ObjectID functions.
Having worked within the world of SQL with the Drupal content system for many years now, I can say that once you establish tables in other systems you RARELY if EVER rename them. Because refactoring code would just be too painful. FileMaker is unique in that developers assume that it's just no big deal to rename a field or table. :)
-- Matt Petrowsky - ISO FileMaker Magazine Editor
thanks so much, that's very
thanks so much, that's very helpful!
Like the Cascading Valuelist alla SQL
This is very helpful Matt. It is very much appreciated. :)
Warmest regards,
Ben
Mistake in the video
Thanks to Eden Morris for pointing out a mistake I made in the video. It's towards the end where I say that your SQL SELECT will return a corresponding 1:1 of keys to value when trying to get at the human-readable name for a key value.
What I was "trying" to imply was that the human-readable names would directly correspond to the keys returned BECAUSE the relationship was unsorted.
SQL will always return values in their creation order UNLESS you use an ORDER BY clause.
In the case of this example technique, if you were to provide item re-ordering, say using a 'sequence' field, then you would need to add an ORDER BY 'sequence' on the sql and you would need to make sure the relationship was also sorted by the sequence field in order to get the 1:1 corresponding match per your expectations.
I hope this clears things up for anyone who may have issues with what I said in the video.
Matt
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Reserved words
This was a great video, I wandered what to use the {; arguments.. }-part of the formula for, thanks to you, now i know ;-). There is only one remark I would like to add and that is that the issue with reserved words can also be bypassed by quoting a reserved word:
ExecuteSQL ( "SELECT Code FROM Export WHERE \"Order\"=10" ; "" ; ¶ )
The same thing works when your fieldname starts with an underscore or has spaces:
ExecuteSQL ( "SELECT \"Article Code\" FROM Export WHERE \"Order\"=10" ; "" ; ¶ )
ExecuteSQL ( "SELECT \"_Code\" FROM Export WHERE \"Order\"=10" ; "" ; ¶ )
This way, there's no need to rename fieldnames ;-)
(this also works with several (maybe all) plugins that are used)
Value list GLOBAL field?
This is really solving a lot of problems for me!
I'm implementing a version of this into my solution now. I want to include it on a few different layouts that each have different TO contexts.
Any reason why ValueListKeys couldn't be a global field? SQL is doing the heavy lifting
Then only one relationship to the utility table is needed to generate value lists on many different, unrelated layouts.