[Note: the following review was written for translation into Japanese, so style is sometimes sacrificed for clarity.]
FileMaker Pro, while long the most popular database on the Macintosh, has never achieved similar status among Windows users, despite its being significantly easier to learn and use than competing products like Microsoft Access and Lotus Approach. That's partly due to the program's late arrival on the PC platform (September 1992). Claris's lack of name recognition in the Windows market has also been a factor. However, I think the program's biggest problem has been the numerous negative reviews that complained about its lack of relational capabilities.
Claris addresses those reviewers' complaints with FileMaker Pro 3.0, which adds relational database features while leaving the basic design and operation of the application unchanged. This means that advanced users can now use FileMaker for more complex applications than previously, while new and unsophisticated users will find the program easier to learn and use than ever.
For inexperienced users, the biggest difficulty in creating their first relational database application is not mastering the program's commands and dialog boxes, but mastering the art of database design. Like every other attempt at "easy" relational database programming I've seen (including Microsoft Access 95) FileMaker Pro 3.0 does not provide the training and reference materials necessary for a beginner to get past this initial obstacle.
The manual contains a good introduction of the subject, but it doesn't explain all the important concepts in the depth required to do any real work. For example, there's no discussion of when each of the three basic relationship types should be used: the seldom-used one-to-one type is presented as if it were as important as the essential one-to-many type, and the important many-to-many type isn't mentioned at all. The manual contains instructions for creating one sample relational database, but since some steps are described very vaguely and there's no explanation for any of them, this is little help in understanding the process. (Frankly, the manual seems to have been written by someone who did not understand relational databases in general or FileMaker in particular.) The many sample databases on the CD are not much help for learning either, as none of them include explanations of why they were designed the way they were. The online overview of relational databases is likewise no help: it just repeats some of the simple introductory material from the manual.
As an experienced FileMaker user with a fair understanding of database design, I thought perhaps I could figure out how to use these new features by trial and error, but the attempt was pretty frustrating. My usual test of a relational data manager's ease of use is to create a data entry form with a pop-up list across a many-to-many relation, specifically an invoice with lines for several products. (See illustration.) I was able to create the relation by copying a similar example in the manual, but then when I modified it to match the model I had in mind it stopped working. Even with a working relation I couldn't find a way to get the pop-up list. The similar database I found among the sample files didn't have a list. Instead, it required users to type in product ID numbers--not very intuitive. (Microsoft Access 95 wasn't much better. Its Table Design wizard made it very easy for me to create my test database, but there were no instructions for creating the kind of pop-up list I wanted, and while one of the sample databases used the right design, I couldn't figure out how to imitate it.)
Given these obstacles, I don't think FileMaker's new relational capabilities will make 3.0 any more useful than 3.1 except for the most sophisticated users. However, other new features will appeal to current and new users alike.
Many small additions increase FileMaker's capability and efficiency. Database files can now be larger than 32MB, and you can have 50 databases open simultaneously. You can reduce file sizes by storing constant values in new "global" fields that appear in every record, defining calculations as "unstored," and having a single graphic file appear in various places in a database. (Most files I converted from 2.1 to 3.0 decreased in size by 25-35%.) You can speed up find and sort operations by switching off indexing on fields that aren't often searched or used as sort keys. There are now 8 zoom levels instead of only 4 (though that's still a poor substitute for the arbitrary zoom Windows users are used to).
Text fields have been greatly improved. They now support rich text, so you can use different fonts and text attributes in a single field. You can format paragraphs with different margins, indents, line spacing, and alignment. You can even define a tab grid for text charts. This should make FileMaker much more useful for text-management tasks, like form letters.
During data entry and editing, if the database design allows, you can add new items to a field's pop-up list by picking the "Other" item from the end of the list. When you create a new record, data can automatically be entered in selected fields based on calculations or values in the previous record. The "Replace" command will let you replace the contents of a particular field in selected records with the result of a calculation (so for example you could increase the value of the Unit Cost field in every record in an inventory table by 2%) or with a serial number, so you can easily add them to existing databases. If you make a mistake while entering data, the new "Revert" command will undo all changes (provided you haven't saved the record by clicking outside of a field).
File conversion and import-export show some major improvements as well. While in earlier releases you had to create a database with the appropriate fields before you could import data from other programs, in 3.0 you can simply open the file (ClarisWorks, DBF, DIF, SLK, WKS, WK1, Word mail-merge, or delimited ASCII). FileMaker will automatically create a database, picking up field names from the original file when available and creating both a single-record form for data entry and scrolling columns for browsing. When importing records into an existing database, if you wish FileMaker can now perform the same automatic data entry it would for new records, e.g. adding serial numbers, default values, and so on. When you open a FileMaker 2.1 database, 3.0 automatically saves a backup copy and converts it to 3.0 format. Unfortunately you can't save a 3.0 database in 2.1 format.
Like its competitors, FileMaker now includes an assortment of ready-made databases. There are about 45 templates covering a variety of business, home, and school tasks like address books, purchase orders, and video collections. Some seem like they might be useful, like the "Integrated Solution" business system that includes customer and employee information, expense report and inventory tracking, and invoices.
Database designers can now validate data entry (that is, prevent input of unacceptable data) with a calculation or by checking against a list of acceptable values, and can write a custom message for FileMaker to display when validation fails. (There's still no mask feature like every other database has, though.) They can hide selected layouts (forms) and any or all menus. New tools make creating buttons faster and let you change buttons' text without unlocking or ungrouping them. The scripting language has been greatly enhanced, most notably by the addition a "Dial Phone" command to send phone numbers to your modem and logical commands like "if," "else," "end if," "loop" and "end loop if" (equivalent to the more common "while") for subroutines. There are also many new functions that can be used in both scripts and calculations, including a new class of Status functions that return values like current field or file names, view mode, and the number of users currently accessing the file.
3.0's built-in networking adds support for TCP/IP and more flexible configuration options for MacIPX (for Macs connected to NetWare LANs). The capacity of the server version (which will still run only on a Mac, but works with both PC and Mac clients) has been increased from 16 to 100 databases and it can simultaneously run all supported protocols (AppleTalk, NetWare IPX/SPX, and TCP/IP).
The Windows 95 version adds long file names and support for OLE linking, embedding, and drag-and-drop. At least on my system, it also had an odd bug: fonts and bitmaps appeared about 40% smaller than in other applications--including FileMaker 2.1, which meant that the text in old databases I imported was usually too small to read. 3.0 was also noticeably slower than other applications in drawing graphic objects like buttons and boxes. Neither of these was a problem in the Mac version.
For average users looking for a database they can learn quickly and use without frustration, FileMaker remains the best tool available. For those who have the time and talent to master the art of designing relational databases, it may be worth the extra effort to learn Microsoft Access--but I suspect a lot of people who are struggling with that program would be more productive and happier with FileMaker Pro 3.0.
Copyright © 1996 Robert Lauriston. All Rights Reserved.
To illustrate how FileMaker handles the three basic types of relations,
let's look at the structure of a simple three-table relational
database for tracking inventory and creating invoices. This is
the Product Inventory table, which lists the various items the
company sells, their wholesale and retail prices, and the amount
currently in stock.
This dialog shows the relation between the Product Inventory table
and the Product Line Items table. Each record in the Product Line
Items table refers to one of the products in the Product Inventory
table. Since each product appears only once in the Product Inventory
table, but can appear many times in the Product Line Items table,
this is called a "one-to-many" relation.
This is the Product Line Items table. The user never edits this
table directly; the records it contains are created in the Product
Sales (invoice) table.
This dialog shows the relations between the Product Line Items
table and the other two tables. The first is the same one that
we saw above in the Product Inventory "Define Relationship"
dialog above. The second joins the records to the portal (subform)
in the Product Sales table. Since each line item in the Product
Line Items appears only once in the Product Sales table, this
is called a "one-to-one" relation.
Here you can see how two of the records from the Product Line
Items table appear in an invoice form in the Product Sales table.
Since each product in the Product Inventory table can appear in
many invoices in the Product Sales table, and each invoice can
list many products, the two tables are said to have a "many-to-many"
relation. (The Product Line Items table and the one-to-many and
one-to-one relations defined in it are simply an indirect way
of implementing what is fundamentally a many-to-many relation
between the other two tables.)
This is how the Product Sales form looks in layout (form design)
view. Notice how the portal (subform) lists the fields from the
Product Line Items table only once, in the top row. The other
blank rows repeat the same pattern of fields during data entry
as the user adds additional products to the order. Creating a
portal is not difficult but it is much more tedious than it should
be, as you have to place each field individually and drag each
new field's label from its default position to the left of the
filed to its proper position above the portal.
This dialog shows the other side of the one-to-one relation between
the Product Sales table and the Product Line Items table. The
"Allow creation of related records" option means that
each new product is added to a Product Sales invoice form, a corresponding
new record is created in the Product Line Items table. The "When
deleting a record in this file, also delete related records"
option removes the corresponding records from the Product Line
Items table when a product is deleted from an invoice or an invoice
is deleted from the database. (Without these items, garbage records
would accumulate in the Product Line Items table and eventually
degrade performance.)
With basic one-to-many relations, defining a pop-up menu is simple.
Here I will go through the steps required to access names from
this student records file in an award certificate file. (These
are both among the database templates that come with FileMaker
Pro 3.0.)
The first step was to open the certificates file, switch to "Layout"
(define form) mode, select the existing student name field, and
choose Format Field command. That brought up this dialog box,
where I checked the "Pop-up list" option and chose "Define
Value Lists" to create a new list for the field. (The two
existing lists are part of the database template.)
In the Define Value Lists dialog, I created a new "Student
Name" list, then clicked the "Use values from field"
button.
After selecting the "Student Records" table with a standard
file dialog, I selected the "Student" field (which contains
the students names).
The name field in the certificate table, which originally required
manual input, now displays a pop-up list. The ability to easily
access information from existing FileMaker databases in new ones
will probably be the most useful aspect of 3.0's new relational
features for average users. (Strictly speaking this isn't really
a relational database at all, simply a lookup from one table to
another.)
FileMaker does not provide a graphical display of relational database
structures like the Relationships tool in Microsoft Access (shown
above). That's not as big a difference as it might seem, since
you can only edit or delete existing relations in this window,
not define new ones or modify tables. However, this tool does
make it much easier to understand the database structure and correct
occasional minor problems with relations between tables.