One of my favorite new features of FileMaker Pro 12 is the ExecuteSQL function, which allows you to perform a SELECT SQL query against your FileMaker tables. ExecuteSQL allows you to use SQL to create calculations that otherwise might be very difficult or cumbersome. For example, in the past, if you wanted to get the list of values that had been used anywhere in the field areaCode in your contact table, you had to first create a value list using that field, then create the calculation: ValueListItems ( Get ( FileName ) ; “AreaCodes” ). It worked, but it was a bit of a hack to have to create that extra value list.
Now, all you need is:
ExecuteSQL ( "SELECT DISTINCT areaCode FROM contact" ; "" ; "" ) /* The last two empty parameters tell FileMaker to use the default comma and carriage return as column and row separators */
Okay, so it does require learning a bit of SQL (check out the W3Schools’ SQL Tutorial). But many queries are straightforward, and using SQL this way can simplify the relationship graph.
Say I wanted to get all the area codes used in Massachusetts. Using the old value list method, I would need to create a field somewhere to store the abbreviation “MA”, create a relationship from that field to the contact::state field, and define my value list to use only related values starting from the table with the new field. With ExecuteSQL, all I need is to update the formula to:
ExecuteSQL ( "SELECT DISTINCT areaCode FROM contact WHERE UPPER(state)=?" ; "" ; "" ; "MA" ) /* The fourth parameter is used to fill in the ? in the query. It could be a field instead of a text constant */
Here’s one of my favorites: often it’s useful to know the name of the base table (the ones define on the Tables tab of the Manage Database dialog) that is the source for a table occurrence (the tables that appear in the graph). You may have lots of table occurrences, named leads, students, personsOfInterest, relatives, and so one, but each is really an instance of the contact table. Outside the Manage Database dialog, any reference to a table is really a reference to a table occurrence (so Get ( LayoutTableName ) returns leads, for example, not contact).
In the past, it would be necessary to name each consistently to include the underlying base table (so contact_leads, contact_students, contact_personsOfInterest, etc.), and then parse the table occurrence name. Be careful changing table names! But with SQL, you can use the following to get the name of the base table of the current layout:
ExecuteSQL ( "SELECT BaseTableName FROM FileMaker_Tables WHERE TableName=?" ; "" ; "" ; Get ( LayoutTableName ) )
FileMaker_Tables is a special table you can query via SQL to get a list of all table occurrences, and the BaseTableName column tells you the name of the source table.
You can replace Get ( LayoutTableName ) with any table occurrence name to get it’s base table. I like the following calculation field that stores the name of the table as data, which can be extremely useful when exporting and importing data:
Let ( [ FQFN = GetFieldName ( Self ) ; tableOccurrence = GetValue ( Substitute ( FQFN ; "::" ; ¶ ) ; 1 ) ] ; ExecuteSQL ( "SELECT BaseTableName FROM FileMaker_Tables WHERE TableName=?" ; "" ; "" ; tableOccurrence ) )
The two variables in the Let function first get the fully qualified field name (including table occurrence name) of the calculation, and then parses out just the table occurrence name for use in the query. Since the calculation never makes reference to anything but Self, you can copy and paste this calculation field to any table without needing any editing.
What’s your favorite use of ExecuteSQL?
12ti Studios was founded to bring exceptional software design and experience together with a venture capital-like approach to helping small businesses, and businesses-to-be, create innovative software products.
It all started when people came to founder Chad Novotny with ideas. During his career as Vice President of Technology for a custom software development company, he had been approached by many people, each with an idea for a product or service. In return for equity, they would ask, could he build their ideas for no charge? Unfortunately, he had to say no; that wasn’t the business his firm was in. But Chad always wondered what could have happened had he been able to say yes.
When Chad left his previous position and joined The Venture Capital Fund of New England as Entrepreneur-in-Residence, (after having personally been involved in several VC investment opportunities and “gotten the bug”), he decided to also launch 12ti Studios. The mission is to provide not just software development, but to partner with and assist in the development of businesses that traditionally would be unable to take advantage of venture capital.
12ti continues the tradition of Chad’s contributing to the FileMaker community — experienced developers and beginners alike — by sharing techniques, tips, and thoughts through its blog. We welcome your feedback and ideas.
To find many more in depth articles from Chad and some must have tools and services from 12ti Studios visit their site at the link below:Website: 12ti.com