I wouldn’t describe the last several versions of FileMaker Pro as ho-hum, but I wouldn’t exactly call them exciting. The addition of XML support in FileMaker Pro 6 was so revolutionary an enhancement that most developers still don’t know what to make of it two years later. Otherwise, version 6 felt like a maintenance release, with a few new status functions, the capability to import photos directly from a digital camera, a Find and Replace command, etc.
As a result, many people who aren’t already in the know will be surprised – no, scratch that – shocked to discover that the just-released FileMaker Pro 7 is dramatically, profoundly and comprehensively different from its predecessors. Different and, I hasten to say, better.
Now, the difference is not primarily a matter of new or changed features, although there are more of those than I can mention here. It’s more a matter of a new way of thinking. Experienced FileMaker developers learning to work in FileMaker Pro 7 may feel like Texans accustomed to attacking their food with steak knives, now forced to eat noodles with chopsticks. Chopsticks are not just a different tool for picking up food: they’re designed for a different cuisine, behind which there is a different conception of what constitutes a meal. The same applies to FileMaker Pro 7. We’re not just going to be building databases differently, we’re going to be building fundamentally different kinds of databases.
To Upgrade or Not to Upgrade? FileMaker Pro 7 and FileMaker Developer 7 are available immediately. New licenses for FileMaker Pro 7 cost $300, and a special upgrade offer available through 17-Sep-04 makes upgrades available for $150 for previous versions of FileMaker all the way back to FileMaker Pro 2.1. After 17-Sep-04, only FileMaker Pro 6 owners will be eligible for the upgrade pricing. FileMaker Developer 7 costs $500, with a $100 rebate available to previous owners. Special deals are available for some buyers, so be sure to check FileMaker, Inc.’s Web site.
If you are already using FileMaker Pro, this upgrade is something you will want at some point. But you should proceed deliberately, particularly if you’re an end-user or are involved on the IT side of hosting and maintaining database solutions.
The main reason not to install FileMaker Pro 7 on all your computers tomorrow is that it’s hard to predict whether any given solution will come into FileMaker Pro 7 singing "Hallelujah!" or kicking and screaming. Some (perhaps many) old solutions will convert with relatively little effort. But many will not. In my limited experience, any moderately complex solution is likely to require some effort before conversion, and perhaps afterwards, too. FileMaker, Inc., has a wealth of information on the subject of conversion on its Web site. Read it, or weep. And in the meantime, you can eat your cake and have it, too, because FileMaker, Inc., has adjusted its license to let upgraders get started with version 7 while continuing to run old solutions under version 6; both versions can run simultaneously without trouble.
If your solution is shared on a network, you need to consider something else: you can’t share FileMaker Pro 7 databases under the current FileMaker Server 5.5, and FileMaker Server 7 won’t be out for a couple of months.
FileMaker Pro 7 requires Mac OS X 10.2.8 and later (or Windows 2000/XP). If you are using an older operating system, you may need to upgrade your operating system, or even your Mac, before installing FileMaker Pro 7.
What’s in It for End Users? You museum curators and small-business managers, secretaries and paralegals, salesmen, doctors, engineers, teachers, accountants, and publishers – you folks are FileMaker Pro’s principal customers. You may not get your hands on FileMaker Pro 7 for weeks or months, but there are improvements to look forward to.
In FileMaker Pro 7, you can view the same data in multiple windows. This means you will be able to view the same found set of records in both form and list view, or view lists of two entirely different found sets.
When you finish editing a record in FileMaker Pro 7, you may now be asked if you want to save your changes. Have you ever made a bunch of changes in a record, then changed your mind? Or, worse, realized you had edited the wrong record? In FileMaker 7, click Don’t Save to restore the record to its former state.
What if you need to edit a record urgently, but Jenkins in marketing got there first? With FileMaker Pro 7, you can send Jenkins an instant message asking him to put down the record and back away from the database. Now it’s more fun to be the boss.
Is your budget tight? FileMaker Pro’s built-in Instant Web Publishing feature is now actually useful. Turn it on and share the database over your workgroup’s intranet. What your colleagues will see in their browsers looks very close to what they’d see sitting in front of a client copy of FileMaker Pro. Instant Web Publishing is good for up to five concurrent users; FileMaker finally eliminated that old 10-different-IP-addresses-in-a-rolling-12-hour-period silliness.
I’m not sure whether this is something that end users have been clamoring for, but container fields can now store almost anything except lunch leftovers. We are not just talking photos and QuickTime movies here, but Word and Excel documents, PDFs, MP3s, even other FileMaker Pro databases. Click on a container field and use the appropriate command in the Insert menu. This gives FileMaker Pro the capability to become a real document management system. Files stored in a FileMaker Pro 7 database do not have to be accessible to users on a shared network drive. And you can use FileMaker’s security to control the access users have to stored files.
Speaking of security, the security model in FileMaker Pro 7 is entirely new. End users who must login to shared databases are now asked to provide an account name as well as a password. There is really important news here but it’s technical and a bit premature, since the new security features won’t come into their own until FileMaker Server 7 is released. Suffice it to say that managers, IT/IS staff, and developers working together will be able to make access to your valuable data simpler and safer than ever before.
I could go on, but in all honesty, the most radical changes in FileMaker Pro 7 are not obvious on the surface. A few cosmetic changes in the status area and dialogs have been implemented, and Mac OS X users get the toolbar back. But otherwise, if you have never defined a relationship or written a script, FileMaker Pro 7 will look very familiar. End users are the ultimate beneficiaries of everything good in FileMaker Pro 7, but those benefits will be indirect.
So What’s In It for Developers? If you are a developer, you may find FileMaker Pro 7 a mixed blessing. On one hand, you have been given a new tool that’s an order of magnitude more powerful than what it replaces. On the other hand, almost everything you knew a week ago is obsolete.
There are scores of new functions, including an Evaluate function that can perform a calculation based on the contents of a field, and a Let function that lets you define and use variables on the fly inside a calculation formula. Buttons can pass parameters to scripts; for example, the same button, placed on different layouts, can pass the current layout name to the script it’s calling and produce a different result in each case. If you work inside FileMaker Developer, you can now define custom functions and store them in the file so they can be used when the solution is opened in FileMaker Pro. Text in the middle of a field can be formatted via calculation, for example, to highlight a search string. You can add comments to calculation formulas using either C or C++ formats. And there are even more improvements along the same lines.
But, important as these enhancements are, they aren’t the big news. The big news in FileMaker Pro 7 has to do with file structures and relationships.
One File, Many Tables — FileMaker Pro 7 now distinguishes between database files and tables, and more important, a single database file can now store multiple tables. How many tables? A million, which is more than you will ever need. And now that FileMaker has something that can properly be called "tables," we’re finally free to use the word "database" more or less the way the rest of the world uses it.
Multiple tables in the same file, combined with new features like script parameters and some of the new functions, mean that it’s possible to write more generic, more modular scripts than ever before. In one 14-file solution that I wrote, a whole slew of scripts in each of the 14 files do essentially the same things: go to list view, perform a scripted search with input from the user, etc. In the FileMaker Pro 6 version of my solution, each of these scripts must exist in each file separately. If I decide to improve the find-records script in one file, I have to edit it in the other 13 files as well. In FileMaker Pro 7, much of this duplication of effort is unnecessary.
You do not have to store everything in one file. Files can refer to one another and access one another’s tables. FileMaker Pro 7 takes advantage of this fact when it converts your existing multi-file solutions: each individual file in the old solution becomes an individual, one-table file in the new solution. These files are related in a group of small, file-to-file relationships, pretty much the way they were related before. In many cases, this is a benign but undesirable consequence of the solution’s origin in the earlier version of FileMaker Pro. If you want to consolidate these individual files, New Millennium’s FM Robot utility can do the job automatically. But in many cases, if you or your client can afford it, rebuilding old solutions from scratch will be desirable.
The Separation Model — When one file references another file, it references everything in that file. In other words, if one file, called
My_Data.fp7 has 22 tables in it, another file called
My_App.fp7 can access every one of those tables as if they were stored internally rather than externally. What does "reference" mean here? It means that
My_App.fp7 can contain layouts that provide form and list views of each of the tables in
My_Data.fp7. Not list as in "portal" – a limited interface element familiar to current FileMaker users – but list as in a real list, a list that can print nicely on page after page, a list that can be displayed with subsummary totals.
My_App.fp7 can do this even if it does not contain a single table itself. It might contain nothing but layouts and scripts, all taking advantage of the tables in
Can you see where this is going? In FileMaker Pro 7, it’s possible to put the data in one file, and all the programming resources – layouts, scripts, value lists, summaries – into a separate file. Separating data (and the data structure) from the stuff you do to analyze and display the data brings an enhanced clarity to the development process. It’s also a huge boon for any client whose developer is working remotely. When it’s time to update that solution with 6 million records, the IT guy takes the solution off line, throws out the old front-end file, puts the new front-end file in its place, and places the solution back online. Downtime: five minutes, tops.
FileMaker developers have been talking about "The Separation Model" for years, but it was more of a dream than a reality, due to the limitations on the ways in which one file could access, manipulate, and display another file’s data. Line-item reports can’t be printed effectively from portals, so you usually had to jump to a data file to print line items (like invoices or class rosters). And that usually meant you had to include scripts in the data file to sort the records and display them on a report layout.
But in FileMaker Pro 7, since one file can logically incorporate all the tables in another file and make use of them as if they were stored internally, these impediments to true separation no longer exist. The main remaining obstacles to The Separation Model are the need to deal with unanticipated fields and user modifications to accounts and passwords, but a lot of smart developers are working on these problems. I have a solution on my PowerBook right now that implements The Separation Model completely. It’s somewhat modest, but it works.
The Separation Model won’t be adopted by every developer. Many developers will be so thrilled to be able to put everything in a single file that they won’t want to think about the alternative, at least not for a while. Even those who embrace it may not use it in every solution, in part because it may unnecessarily increase the amount of data that needs to be backed up regularly. But the sky’s the limit now as far as file size goes. FileMaker Pro database files can balloon to 8 terabytes (if you have an Xserve RAID to store it on). As FileMaker Pro databases grow ever larger, the advantages of The Separation Model may become more obvious and more compelling.
Can You Relate? I think the capability of one file to incorporate another file’s tables is the most significant single change in FileMaker Pro 7. But the change that many developers so far seem to be most excited (or confused) about is the change in the way that relationships are defined and managed.
FileMaker Pro 1 was a flat-file database. Everything went into one file containing only one type of record. In FileMaker Pro 2, it became possible for one file to lookup data in another file and copy it. This wasn’t real relationality, but we didn’t care. We hugged lookups in our arms tightly and ran toward the goal post with them, screaming all the way. Ah, 1993!
In version 3, FileMaker Pro became relational, sort of. It was certainly more relational than version 2. The sharing of data from one file to another was automatic (whereas lookups had to be triggered), related data wasn’t stored twice (as it had been with lookups), and you could edit one file’s data directly in another, related file. Developers took this new capability and did some truly amazing things with it.
But this was an idiosyncratic kind of relationality. For one thing, relationships were unidirectional. You could relate INVOICES to LINE ITEMS, but that just meant you could see line items from the invoices file. If you wanted to see invoice data (such as the invoice date) from the line items file, you had to go to the LINE ITEMS file and define the relationship a second time. It was a bit like needing to conduct a wedding twice: once for the groom and once for the bride. Worse, if you wanted to display a value from PRODUCTS in the portal in INVOICES that displayed line item records, you had to create a calculation field in the line items file to capture that value and pass it up the relational line. It was like being unable to borrow sugar from anybody but your next door neighbor. The old relational model was a cool kludge, but a kludge nonetheless. And it remained virtually unchanged for almost a decade.
Until last week. Relationships in FileMaker Pro 7 are always bidirectional and they reach as far as you want them to. If invoices are related to line items, line items to products, and products to companies, a portal in the INVOICES file can display the company name for each product in each line item, and you won’t have to create a single calculation field. And in FileMaker Pro 7, you can, in one file, define relationships between tables in another file, even if the tables are not related in the file in which they are stored.
Moreover, tables can now be related in scores of new ways. In FileMaker Pro 6 and earlier, relationships always involved a simple match between data in a field in one file and corresponding data in a field in another file. In FileMaker Pro 7, however, you can define relationships with a variety of operators and multiple match fields. Before, we had to create complex calculation fields to achieve results that can now be achieved entirely by means of relationships.
One of the quirkiest things about FileMaker Pro versions 3 through 6 was that a program with a solid graphical interface used a text-based dialog to define relationships. For crying out loud, even Microsoft Access defined relationships graphically! In FileMaker Pro 7, relationships are defined in a special dialog called the relationship graph. You place instances or "occurrences" of the various tables on the graph, and relate them by drawing lines between them.
In some technical ways, FileMaker Pro’s implementation of relationality is still idiosyncratic. We used to have to create redundant relationships to sort data in portals in different ways. We don’t have to do that any more, but in FileMaker Pro 7, for various reasons, you may still need or want to relate the same tables more than once. You won’t read about this sort of thing in the standard guides to relational design. Even so, those guides are far more relevant to FileMaker Pro 7 than they were to earlier versions. All in all, the new relational model is powerful, flexible and extremely well implemented.
What’s Not to Like? Given the ambitious nature of this release, it shouldn’t be too surprising that FileMaker Pro 7 has a few blemishes.
The Sort Records and Export Records dialogs, which weren’t resizable at all, are now resizable, but only on the right side of the dialog. The field list on the left side of each dialog is still too small to display long field names fully, although, after you move a field from the list on the left side to the list on the right side, you can confirm on the right side that you moved the correct field.
There seems to be a significant problem with fuzzy (badly anti-aliased) text in FileMaker Pro 7 for Windows. This matters even to Mac OS developers, because Windows users make up the lion’s share of FileMaker’s market.
It looks like the powerful new Let function mentioned above creates variables limited in scope to the calculation in which they are defined. You may still need to use global fields as pseudo-variables, for example, to pass values from one script to another.
Alas, in FileMaker Pro 7, it is still not possible to trigger a script when the user exits a field. This is my one major disappointment with the new release.
Conclusion — FileMaker Pro has been widely touted as easy to use. This claim has always been a half-truth at best. FileMaker Pro version 6 was and still is a great product. But like playing the guitar, FileMaker Pro is easy only for beginners. Advanced FileMaker developers in the past have spent a lot of their time compensating for FileMaker’s limitations. In other words, in the past, if you stuck with FileMaker Pro and extended your skills and knowledge, you reached a point of diminishing returns, where it actually became harder to do things in FileMaker that could be done more easily in development systems that were ostensibly more difficult. Frankly, it was frustrating at times. Most of us stuck with FileMaker Pro because the results were worth the effort. But it wasn’t "easy."
For end users and beginning developers, FileMaker Pro 7 is neither much easier nor much harder than it was before, while for developers, it’s a bit of both. Many tasks have gotten harder for developers in the short run, because the program is an order of magnitude more complex than it was before. But I am confident that development will become easier in the long run because we won’t hit that point of diminishing returns that dogged previous versions. Instead, at that point, we will be able to create more interesting solutions in more efficient, more intelligent, and more satisfying ways. And that’s an exciting prospect, both for us and for our clients.
[William Porter is a former classics professor who, in 1998, gave up academic tenure to pursue "other interests," including developing database applications. An Associate Member of the FileMaker Solutions Alliance, Will is currently working on a book about FileMaker Pro 7 for No Starch Press.]
PayBITS: Did Will’s review of FileMaker Pro 7 give you the data
you were looking for? Consider sending him a few bucks via PayBITS!
Read more about PayBITS: <http://www.tidbits.com/paybits/>