Cascading Value Lists: Learning ExecuteSQL

by Matt Petrowsky

Article options

• Watch Preview Video
• Watch Full Version
• Download Technique File
• Download Movie File
• SQL Builder
• SQL Explorer
• FileMakerHacks SQL Series
• Skeleton Key SQL Examples

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!