FileMaker Pro 9 contains a number of nice but non-dramatic improvements over previous versions of the product, and one major surprise: support for direct access to Structured Query Language (SQL) databases. Feel intimidated by the very sound of it? Don’t be. FileMaker still has the lowest geekiness-to-power ratio of any database management system on the market.
FileMaker Pro comes in two flavors: a standard and an advanced version. The latter includes some extra developer-oriented features, but otherwise it is much like the standard version, so most of what I say here applies equally to both. FileMaker also offers standard and advanced server versions. The advanced version is used only in certain Web publishing situations and is not part of this review.
SQL without tears
SQL is the standard used to communicate with the big databases found everywhere: in businesses, hospitals, universities, government, on the Web—almost anywhere that a lot of data must be accessed by a lot of people. In the past, FileMaker users were by and large shut out of these important data collections, because FileMaker uses its own proprietary (that is, non-standard) database engine. Technically, it has long been possible for a FileMaker database to query a SQL data source, then import a copy of the resulting data set to play with. But this feature was hard to use, didn’t work very well, and in any case was more like data exchange than data access. I know only a handful of advanced FileMaker developers that actually made use of it.
But that’s about to change: with FileMaker Pro 9, if you’ve got a SQL data source handy that was set up by somebody else, you can now connect to that data source and make full use of it without knowing a thing about SQL. In short, FileMaker Pro 9 offers SQL without tears.
The key to this miracle is the data source reference. One of the revolutionary features that appeared in FileMaker 7 was the ability to create in one FileMaker database file a reference to another file, and then to use that referenced data as if it were actually stored in the current file. File references opened up a number of opportunities for FileMaker developers. It was finally possible in FileMaker Pro to create applications in which all the data was in one file and all the elements of the application (such as layouts and scripts) were in another. So for example, by adding a simple file reference to my billing database, I was able to post payments in my checking database at the same time, without actually having to open and edit the checking database.
Well, with FileMaker 9, you can now reference and use an SQL data source in exactly the same way that you can reference and use another FileMaker file. If FileMaker Pro can see the SQL data source in the simple Find Data Source dialog, all you have to do is select it, authenticate, and you’re in. Although the data is actually stored in the SQL database, you can interact with it in your FileMaker database—seeing live data that updates automatically, editing that data, and writing it back to the SQL database so that other users accessing the same data will see the changes you’ve made. If you have the proper access privileges, you will be able to create or delete records. And developers can even use SQL data sources in field-level calculation formulas.
And what if you turned to FileMaker Pro in the first place because it wasn’t SQL? There’s good news for you, too. It still isn’t.
Server without tears, too
If you only expect half a dozen or so users to connect to your database, you will be happy to know that the number of peer-to-peer connections allowed by FileMaker Pro, which dropped from nine to four in a previous release, has risen back up to nine. But even for small numbers of users, FileMaker Server is the preferred way to go, and the installation and set-up is much easier to negotiate in version 9. A process that used to make even experienced network administrators break out in an anxious sweat has been improved so much that I was able to get the server up and running in under 20 minutes.
FileMaker Server and Server Advanced also provide better and easier support for PHP, an open-source programming language used to run many database-driven Web sites. This is a very welcome improvement over FileMaker’s earlier preference for using XML and XSLT technologies to program Web applications.
The new Pro’s pros (and cons)
FileMaker Pro 9 has a modest but solid set of feature enhancements that you can use right out of the box. My favorite improvement is the Append to PDF command, available both in the File menu and as a script step. FileMaker Pro 7 added the ability to save a report to a PDF, but the new Append command makes it possible to create a single PDF that uses several different layouts, or, for example, to append July results to a report that currently goes only through June. Conditional formatting can now be applied directly to a field object on a layout; for example, you can easily highlight invalid entries or negative amounts. It’s also now possible to configure layout objects so that they resize automatically on screen. This may be useful if you want to take the trouble to optimize your use of screen space for users who have displays of varying sizes.
The latest version of FileMaker Pro finally makes it possible to group related scripts together inside organizational folders, and serious scriptwriters will be grateful. There are many other smaller changes, including a new startup screen, better online help while you work, live updating, and more. FileMaker Pro 9 Advanced includes all of the enhancements just mentioned, plus some small improvements of its own. I’m particularly fond of the way the debugger in FileMaker Pro 9 Advanced automatically lets me track the current value of every field and variable used in a script.
I noticed one old bug that has not been fixed, but it’s a pet peeve among developers. In a Go to Related Record script step that uses a file reference (rather than a local table), when you specify the layout to go to in the current file, FileMaker still doesn’t display the name of the layout correctly. To be fair, this bug probably doesn’t affect most users, since most people still use the common-sense FileMaker approach of putting both data and layouts in the same file.
Macworld’s buying advice
For new buyers, FileMaker Pro 9 is the most attractive version of the product ever. But, should current users upgrade? For most, the answer is yes. If you are still using FileMaker 6 (or 7 or 8 or 8.5) and you tend just to tinker around—something FileMaker almost encourages—download the demo and decide for yourself if features like Append to PDF and conditional formatting are worth the cost of the upgrade. At least nothing will break while you try out the new version, since FileMaker 9 uses the same .fp7 file format as the last three releases. But for those users who work with FileMaker a lot, version 9 is the third home run FileMaker has hit in its last four times at bat.
[ William Porter is an independent database applications developer and writer in Dallas. ]
Getting FileMaker 9 to see and access an external SQL data source is a piece of cake. Just select the data source from a list of those available to your computer’s ODBC driver, authenticate, and you’re in. After that, you use the data as if it were stored locally in FileMaker. With FileMaker 9’s conditional formatting, you can change field formats—including the field background—in response to the data. Here, a required field is formatted so that it is highlighted in yellow until the user enters a name.