A guiding principle of good database design is that each piece of information should exist in only one place-duplicating data can lead to trouble. How often have you arduously searched through a database to correct all instances of a misspelled name, for example, only to find missed cases weeks later?
You can avoid such problems by creating relationships between records. Relationships can connect two or more databases, and they're a great way to link information in the same database; for instance, the personnel record for a department manager at a large company could link to and display information about the employees that manager supervises.
For our sample project, we used FileMaker Pro 5 to set up relationships in a database for a fictitious music retailer called One-Four-Five. The database would allow salespeople to see related products that the retailer had recommended be sold with an item-such as a small amplifier with a beginner's electric guitar-as soon as they opened that item's record. Music may not be your thing, but these techniques can apply to many business and personal databases-and they're handy for Web publishing and work in FileMaker Pro 3.X, 4.X, and 5.X.
GEOFF DUNCAN is the technical editor and chief database wrangler for TidBits ( www.tidbits.com ), a free weekly online Macintosh newsletter.
1: Create New Fields
Before you can create relationships among records, you must define a couple of new fields. You'll need an ID field (so each record will have a unique identifier, such as a serial number). You'll also need a field where you can enter a list of IDs for related records.
Create a text field with a descriptive name, such as our example's ProductID, in FileMaker Pro's Define Fields dialog box. Choosing a text field rather than a number field lets you use letters in your ID "number
Click on the Options button, select the Serial Number option, and add a serial number to the Next Value field (A). If this database already contained 620 products, we could start with M0000621-FileMaker Pro would increase the value of each new record's ID number, even with the letter M. You can fill in ID values for existing records using FileMaker Pro's Replace feature (press Command-= [equals sign] to access it).
Next, make a second text field to contain the ID numbers of the records you want to access from the current record. In our example, we wanted to see recommended products, so we created a field called RecommendedProductIDs.
TIP: Don't use a repeating field, since you'd have to know the maximum number of records you'd ever relate to another; an ordinary text field can link to thousands of records if necessary.
2: Make Your First Relationship
Now you can define a relationship between the two fields you created in step 1. FileMaker Pro uses this relationship to locate the related data.
Open FileMaker Pro's Define Relationships dialog box (from the File menu), and click on the New button. It feels a little counterintuitive, but use the resulting dialog box to select the database file in which you're currently working
Once you select the file, FileMaker Pro will open the Edit Relationship dialog box for you. Give the relationship a descriptive name, such as our example's RecommendedProducts. Then set up a relationship between the fields you created in step 1 (A). If you want to specify how FileMaker Pro will sort the related records' data, select the Sort Related Records option (B). You can sort by any fields in your database. If you're not sure which criteria to choose, don't worry-you can change the sort order later by editing the relationship.
3: Create a Portal with Related Fields
When you use your database, you'll want to see more than just the ID numbers of your related records. What you need is a portal-a layout object that displays rows of information from related records.
In FileMaker Pro's Layout mode, we created a layout containing our ProductID and RecommendedProductIDs fields, making the latter a scrolling field (A). We then used the Portal tool (B) to create a portal (B). In the Portal Setup dialog box, we chose the relationship we'd created in step 2 and formatted the portal to contain several rows and a vertical scroll bar. Data from related records should appear in this portal, so we added related fields-such as Product Name and Price-to its top row.
After creating your portal, switch to Browse mode to test your work. All that's left for us to do now is enter ProductID values into the RecommendedProductIDs field (D); in our example, these values represent products recommended with an electric guitar. (Each ID value must be by itself on a line ending in a return, or FileMaker Pro won't find the related records.) When we exit the ProductID field, the portal displays related information (E). By following this procedure, you'll be able to see related data without having to duplicate it. (And if you edit information in the portal, you're actually editing the related record.)
4: Make Another Relationship and Portal
We could stop here, but the relationships are lopsided: the guitar's record displays products recommended with it, but those products' records don't show that they're recommended with the guitar. To make a two-way relationship, you need to create a second relationship and portal.
Open FileMaker Pro's Define Relationships dialog box, and click on the New button. Create a new relationship to your current database file (just as you did in step 2), and give your second relationship a descriptive name, such as our example's RecommendingProducts. Then do the reverse of what you did in step 2. In our example, that meant matching the ProductID field in the current file to RecommendedProductIDs in the related file (A).
In Layout mode, use the Portal tool to open a second portal on your layout. We added related fields to the top row of this portal: these fields will display information from records recommending the current product.
You can see the results by switching to Browse mode. Going to a record for one of our recommended products-an instrument cable-we see that a guitar isn't recommended with the cable (that wouldn't make sense), but the second portal shows that the cable is recommended with the electric guitar (B).
5: Once You've Got It, Flaunt It
Now that your relationships are in place, take advantage of the information they provide. You can integrate similar portals into other layouts and create new fields that tally the records in your portals.
Our example is a product layout that a salesperson might use. The layout contains only one portal for recommended products-you don't need both portals to be visible on a layout to use the information they provide. We created two new calculation fields, NumRecommendingProducts and NumRecommendedProducts, which use FileMaker Pro's Count function (A) to determine how many records we link in each relationship.
To add the calculation fields to a layout, switch to Layout mode and place the cursor in a text object (such as a field label), press Command-M to enter the field name as a merge field, and select your calculation field (B).
When you switch to Browse mode, FileMaker Pro will automatically place the field's value in your text object (B).