Further Explorations in SQL for FileMaker

Once you start down the path exploring new features or technologies, often the deeper you dig the more cool stuff you expose. In my first post on SQL and FileMaker I focused simply on pulling and comparing data between tables. There are several advantages to employing SQL in FileMaker: speed and context perhaps the primary ones. While SQL the graph remains clean, as the queries remain inside the script, no additional Table Occurences are required.

One area in FileMaker, where we tend to rely on jumping through hoops in the graph, deals with report creation. This is especially true when pulling together reports from multiple sources. Solutions require extra TOs to build queries through relationships, and significant scripting to hop between contexts and gather data.

A recent game changer is Bruce Robertson’s Virtual List (see Lisette Wilson’s recent article and her follow-up for an overview), which gathers data into a utility table for the report output. Combining SQL and the Virtual List seems like a perfect marriage. Martha Zink, Advocate of Awesomeness at MightyData, recently showed me an example of SQL combined with the Virtual List. This post borrows heavily from concepts in her solution.

SELECT From ( SELECT )

Again, for the SQL queries I used a plugin. Several plugins expose FileMaker’s internal SQL. I selected MMQuery from CNS Plug-ins. One nice feature of its Execute SQL function is the ability to set your own delimiters between fields returned in the result. The default delimiter is the comma, but when returning number you may end up with extra commas and I set my delimiter to the pipe symbol. Other plugins may not have this feature, and require you to substitute the comma for the pipe symbol before sending the data through the Virtual List process.

(I recently discovered another plugin, from the makers of Base Elements, that is not only free but lets you execute Shell and SQL, plus a host of other features.)

Although I’m not going to show long query examples in detail, I want to highlight one SQL idea used to build the queries that get sent to the Virtual List “engine”, and how to parse the output from SQL in order for the Virtual List engine to parse the data. The beauty of the SQL in Martha’s solution turns on an elegant and powerful technique in SQL: subselects.

In essence, this is a query nested within another query. The advantage lies in removing joins, which makes the query more readable. Subselects also allow you to build result sets against which you then can run other queries. For example, imagine if you want to pull a list of fields using an ID value or set of IDs. You first want to get that ID set, and then you can select information based on this list.

select <> from Table as T where T.ID in
( select O.ID from OtherTable as O where age < 45 )

To illustrate this process I added some formatting. The key lies in bracketing the select that creates the initial result, and then selecting from this above the result. This “inner” query inside the parentheses is processed first. With “in” the outer query looks at a list of records.

The inner select in the above example is a very simple statement. As with any other select in SQL you can change this to look across multiple tables, using multiple criteria, AND/OR, and other options. Using subselect is almost like creating virtual tables that exist solely within a query. Inside the <> would be all the fields required in the final result.

Dynamic Criteria by the User

In the solution that called for SQL, each report called for user-selected criteria, and searching across multiple tables. Relying solely on the graph or scripting to build the data would have required complex TOs and scripts jumping from layout to layout to build data, possibly even hard-coding criteria. By making the reporting dynamic, users could select from drop downs for different criteria, such as “single” and/or “double” graft. The query would build a pair of select statements, with the sub-select handling building a second list.

SELECT
DISTINCT P.id
FROM (( Patient as P
JOIN Admission as A ON P.id = A.idPatient )
JOIN Intervention as I ON A.id = I.idAdmission )
JOIN allvalue as AV on I.id = AV.idIntervention
WHERE lower( AV.Type ) = 'proximal'
AND lower( AV.Field1 ) = 'aortic arch'
AND lower( AV.Field2 ) = 'graft replacement'
AND lower( AV.Field4 ) LIKE '%single y-graft%'
AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'

OR P.id IN (
	SELECT DISTINCT P.id
	FROM (( Patient as P
	JOIN Admission as A on P.id = A.idPatient )
	JOIN Intervention as I ON A.id = I.idAdmission )
	JOIN allvalue as AV on I.id = AV.idIntervention
	WHERE lower( AV.Type ) = 'proximal'
	AND lower( AV.Field1 ) = 'aortic arch'
	AND lower( AV.Field2 ) = 'graft replacement'
	AND lower( AV.Field4 ) LIKE '%double y-graft%'
	AND I.Proximal_Date BETWEEN DATE '2002-01-01' AND DATE '2011-08-12'
	)

While both queries end up fairly complex, imagine the complexity if she had to rely on joins to perform both queries. All of this takes place inside FileMaker, using (in her case) the SyncDek plugin for the SQL. Combine the power of SQL with the Rapid Application Development (cool interfaces) of FileMaker, and you get true development synergy.