Working at Home
Serious FileMaker developers, database and network admins, and managers who worry about tying different piles of company data together are going to be the big beneficiaries of major improvements in the latest update to the FileMaker family of products. If you don’t fall into that category–and a lot of FileMaker users do not–don’t feel neglected. There are a number of nice things in FileMaker Pro 9 itself for solitary or small-workgroup users, too.
Se habla SQL
The big news with this release of the venerable database program is that FileMaker Pro now supports live two-way connections to SQL databases. For years, it has been possible to use FileMaker to query an SQL database and get a copy of the resulting data set (say, customers in a certain postal code). But this old support for access to SQL databases was hard to use, didn’t provide live access to data, and–as a practical matter–just didn’t work very well, especially on a Mac. That’s all changed with FileMaker 9.
There are many specific implementations such as Oracle and MySQL, but generically speaking, SQL is a standard for heavy-duty databases used in big corporations, universities, research, and on the Web–anywhere there is a lot of data to store and many people need to access it.
The medical records database in a hospital is almost certainly powered by SQL; so are your company’s human resources and payroll databases, the databases used by your bank and credit card companies, the inventory system used by the grocery store down the street, and your local library’s holdings catalogue, just to offer a few examples. Until now, your little FileMaker database was almost completely locked out of these data collections. To that end, using FileMaker was a bit like having cell phone service with a great company that made easy-to-use phones and offered low prices, but wouldn’t let you have a conversation with anybody whose service was provided by a different company.
Of course, you could have used SQL to build your database in the first place. But gosh, who wants to do that? Developing SQL databases usually involves dealing with a lot of nasty technicalities–or to put it plainly, SQL is pretty geeky. That makes it almost the antithesis of FileMaker Pro, whose geekiness-to-power ratio is the lowest in the database industry.
FileMaker 9 hasn’t made SQL itself any less geeky; it just makes it possible for FileMaker developers not to give a darn. You can now add a major dimension to your FileMaker projects by connecting to SQL databases that were developed and set up by somebody else, so you don’t need to know much about SQL at all.
The key to this trick 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 the referenced data as if it were actually stored in the current file. Every FileMaker solution I’ve built since FileMaker 7 appeared consists of two files: a data file that contains nothing but my client’s data and a user-interface file, which contains all of my layouts and scripts, the stuff that users actually see. The user-interface file doesn’t actually store any data at all.
Well, with FileMaker 9, you can now reference and use an SQL data source in the very same way that you might reference and use another FileMaker file. If you 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. After that, 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 data and having it written back to the SQL database so that other users accessing the same data with other interfaces will see the changes you’ve made. If you have the correct access privileges, you will even be able to create or delete records.
Until we get six to eight months of real-world experience with FileMaker 9, it may not be possible to assess this new feature. A FileMaker developer friend of mine who’s also experienced with SQL told me that FileMaker 9 isn’t quite ready to deliver the performance of a Porsche with the gas mileage of a Prius. But wouldn’t it be nice if it could? One thing I’m sure of: SQL-connectivity is going to sell a lot of copies of FileMaker 9 to network admins and managers who sometimes stayed away from FileMaker in the past because it couldn’t talk very well to the SQL database that was the heart of their company’s data management system. Now it can. In fact, FileMaker can now carry on a conversation with that database almost like a native speaker.
And what if you couldn’t care less about SQL? The do-it-yourself users and small workgroup users who are the majority of FileMaker’s market, will be relieved to know that you still do not have to use SQL. If you turned to FileMaker in the first place because it wasn’t SQL, well, it still isn’t.
More (and better) connectivity
There are a couple other deep changes in FileMaker 9 that will affect a lot of FileMaker’s existing users. There is a new version of FileMaker Server, the software that lets you share your FileMaker databases on a LAN or WAN. Particularly noteworthy is the fact that the installation and set-up process has been redesigned completely. A process that used to make even experienced network admins break out in an anxious sweat has been improved so much that you can pretty reliably hope to get the job done in under 20 minutes.
The Web-support provided by FileMaker Server Advanced now includes the ability to generate an entire PHP-driven Web site automatically from your FileMaker database. I haven’t tested this feature yet, but it looks very promising.
Finally, while FileMaker Server and Server Advanced have gotten much easier to install and administer, the price has not dropped, so if you only need half a dozen users to connect to your database, you may be happy to know that the number of peer-to-peer connections allowed by FileMaker Pro has risen from four to nine. FileMaker Server is the preferred way to go even for two or three users, but peer-to-peer sharing works, and if you’re on a very tight budget, it can be a life saver.
What else is new
Finally, let’s look at the surface, the improvements that you can start using the minute you install FileMaker Pro 9. My favourite enhancement is the new Append to PDF command, available both in the File menu and as a script step. Save as PDF has been around since FileMaker 7, but the new Append command makes it possible to create a single PDF that uses several different layouts, or to append July results to a report that currently goes only through June. Not sexy, but very useful.
FileMaker 9 also offers a number of nice enhancements for layout designers. Conditional formatting can now be applied directly to a field object on a layout. So for example you can easily highlight invalid entries or negative amounts.
It’s also possible now to configure layout objects so that they resize automatically on screen. Serious script writers will be grateful that, in FileMaker 9, it is finally possible to group related scripts together inside organizational folders. There are many other smaller changes, including a new startup screen, better online help while you work, live updating, and more.
Let’s close with a bit of trivia: FileMaker 9 is the first odd-numbered release in the product’s nearly 20-year history that does not introduce a new file structure. FileMaker’s press materials understandably don’t put this fact right at the top of the features to get excited about in FileMaker 9, but after the revolutionary upheaval that accompanied the release of FileMaker 7 a few years ago, it’s reassuring to know that upgrading to FileMaker 9 is not going to break anything.
In my testing, all of my old FileMaker 7, 8 and 8.5 databases opened up and ran in FileMaker 9 without a single hitch. Most of the FileMaker 9-specific features even break fairly gracefully when you open a database that uses them in an older copy of FileMaker. For example, layout objects set to resize in 9 simply don’t in FileMaker 8.5, and script groups created in FileMaker 9 simply show up as empty folders in the ScriptMaker in FileMaker 8.5. But everything still works perfectly.
Final impressions
At this preliminary stage, FileMaker 9 looks like an impressive upgrade. The product is moving in the direction that power users have been hoping for, and in the long run, this is going to benefit everybody who uses the application. I will have more details about the pros and a few notes about some minor cons in my upcoming review of FileMaker 9 at Macworld.com
FileMaker Pro 9 retails for $299; upgrades, $179. FileMaker Server 9 sells for $999; upgrades, $599. For additional pricing and more information about the FileMaker 9 family of products, visit FileMaker’s products page on the Web.
William Porter is an independent database applications developer and writer in Dallas.
Comment: [email protected]
