General, Level: Intermediate, SQL, Version: FM 8 or later

FileMaker’s Internal SQL Engine, part 2

When my kids were little, I sometimes found myself echoing that familiar parental refrain: “Just because you can do something, doesn’t mean you should.” And I think a related question can be asked with regard to FileMaker’s internal SQL engine: Given FileMaker’s almost infinite flexibility, why bother with SQL?

Three reasons come immediately to mind: 1) efficiency; 2) power; 3) SQL is the most widely used database language, and worth becoming familiar with. Number 3 is self-explanatory, and I’ll address #2 below, but what do I mean by #1? How is SQL more efficient? In a nutshell, it allows you manipulate data via text commands without having to “establish context” (by going to a particular layout or record), and also without having to add table occurrences or relationships to your Relationships Graph.

Let’s say I have a scheduling solution that ships with demo data. I want to ensure that all dates in the system are current, so that when the user looks at the calendar, they will see appointments for the current month, and dates for invoices, payments, purchases, etc., are contemporary as well. Now there’s no reason this can’t be handled the “traditional way”, but what a load of drudgery.

Go to layout
Show all records
Replace field contents of date field
(repeat as necessary for multiple date fields in a given table)
Go to another layout
Repeat ad nauseam…

You could easily end up producing a mountain of script steps to accomplish what could have instead been done with a single Set Variable script step:

…which contains multiple SQL calls.

Note: I am using the doSQL plug-in in these examples, but the code inside the parentheses would be the same, regardless of which SQL plug-in I chose to use.

A similar situation arises if we want to delete all test data prior to shipping a new version of our product. Sure, we could navigate to umpteen layouts, repeatedly issuing Delete All Records commands, but doesn’t this seem more elegant?

Incidentally, for a couple of the tables, rather than deleting all the records, I delete only the ones that meet the condition imposed by the WHERE clause. And of course the WHERE clause could be much more complex than the one I’m using here.

The two examples we’ve looked at demonstrate the economy of using internal SQL to accomplish tasks that would not pose particular challenges for the average FileMaker developer. But so far we haven’t waved a magic SQL wand to solve a difficult problem. I said in the second paragraph that “power” is one of the reasons to use internal SQL, so let’s look at a problem that is difficult to solve using traditional FileMaker methods. Not impossible, just difficult.

Consider a database with two tables, Donors and Donations, related in the usual manner. We would like to see who is donating during a given date range, how many times and how much.

Rather than using a standard summary report, we want to show the equivalent information inside a portal. So, we need an interface that will allow the user to enter a date range, and then see each unique donor, and the count and total dollar amout of donations made by the donor during the date range, like so:

As I say, it’s possible to do this using “pure” FileMaker, in fact I built this demo when FileMaker 7 was first released in 2004, but it took a fair amount of trial and error to get it working properly.

With internal SQL, on the other hand, this can be knocked out in a few easy steps.

1. Build a multiline key of donor ids for the specified date range.

2. Relate this key to the primary key in the Donors table, and base the portal on that relationship.

3. Define a calculated field in Donors to count the related donations for the specified date range.

4. Define another calculated field in Donors, to sum the related donation amounts for the specified date range.

That’s all there is to it, and if you don’t want to take the time to build it from scratch, you can download this demo: sql-summary-report-in-portal (requires either doSQL, or the SQL plug-in of your choice, but if you don’t use doSQL then obviously you’ll need to modify the plug-in calls accordingly).

Finally, the DateToSQL custom function in the above code samples, will be discussed in my next posting (and the Q custom function was explained here).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.