TweetFollow Us on Twitter

Nov 01 Databases2

Volume Number: 17 (2001)
Issue Number: 11
Column Tag: Databases

by Mark Shuchat-Marx

All in the Family: Relational Databases in FileMaker Pro 5

Apple's FileMaker Pro database has long been the standard to beat for the Mac world, much as Microsoft Access is the database standard for Windows. While many users keep records in FileMaker of video collections or recipes, it does have some considerably more advanced features, such as ODBC (stands for Open Database Connectivity, a standard cross-platform protocol for accessing data from SQL servers) and use of relationships. It is this second feature that we will examine here.

Relationships in databases have nothing to do with giving flowers or making a commitment. It has everything to do with how different database files share data with each other. Suppose that you are constructing an expense report database and you don't want to type in the employee's name, department number, and so on every time you create a new record. If you already have an existing employee database, you can set up a relationship between the two files that will keep the data organized for you. (For this example, we took the sample files "Personnel Records" and "Expense Report" included as part of the FileMaker installation.)

In the expense report file, go to the "Define Relationships" item in the File menu and create a relationship between the expense report and the personnel files, linking the related fields in the two databases. In this example, we linked the Employee ID fields from the two database files so that data in the expense report will be automatically filled in based on data in the personnel file. Why did we choose the Employee ID field? Because in this example that is the one field guaranteed to be unique for each and every person.

The second step is to set up the fields you want to be automatically filled in. In our example, we want FileMaker to automatically fill in the employee's first and last names whenever we enter in the Employee ID number. Go to "Define Fields" in the File menu and scroll down to the First Name and Last Name fields, then select First Name and press the Options button. Check off "Lookup" under the "Auto-Enter" tab, then press the Specify button.

Now select the relationship you created earlier, and you will see the fields from the personnel file. Select the "First Name" field, make sure the "Don't copy contents if empty" box is checked off, then press OK. Do the same for the Last Name field.

You are now ready to create records. Put a few sample records into the Personnel Records file, making sure each person has a unique Employee ID number. Now switch to the Expense Report file and create a new record, Put in an Employee ID number of the person for whom you want to fill out an expense report. And voilà, you will see that the person's first and last names are automatically entered into the form.

Another way to do this is simply to put a field from the related form onto the original. In our example, we put the First Name, Last Name and Title fields from Personnel Records (using the relationship created earlier) onto the Expense Report form directly. To do this, put the form into Layout mode, and then drag the "Field" item from the toolbar on the left side of the window to the desired location on the form. Select the desired field from the list that appears.

Once you select the field and it's placed on the form, you can manipulate it like any other field, changing the size, font, color, and so on. On your form, it looks like any other field from the original file, except that in layout mode, the field name begins with two colons (::)

Now go back into Browse mode and create a new record. Put in an existing Employee ID number from Personnel Records and you will see the fields you just placed populated with data from Personnel Records.

A third method of using a database relationship is to use a portal. This is a way of placing several instances of a field on a form. Suppose you want to create a travel record form to track travel records that groups take. Create a relationship between the travel file and the aforementioned Personnel Records file, then place a portal on the layout form. Once you are back in Browse mode, you can then use the portal to display multiple records from Personnel Records in a single travel record.

In a way, you can use the portal even in database files that are not related. In such files, this is called a repeating field, and is a way of containing a relational database within a single file. Suppose you're creating a database to keep track of your video collection and you want to put in a field to hold cast members. You can create a single text field and use commas to separate the stars' names (e.g., "Humphrey Bogart, Ingrid Bergman, Claude Rains" and so on) or you can designate the field as a repeating field.

To do this, go into the "Define Fields" item in the File menu, select the field you want to repeat and press the Options button. Under the "Storage" tab, you'll see an option to turn on repeating fields, as well as the number of times you want the field repeated. Once you press OK, you'll see "Repeating" listed next to the field name under Options. [display "repeating field list" picture here]

Now, when you place the field on your form, it will look like any other field. But if you select the field and select "Field Format" from the Format menu (or control-click on the field and select "Field Format" from the contextual menu), you'll see an option to display anywhere from none to all of the field's repeats. You can also place the repetitions horizontally or vertically. This will let you enter data on multiple cast members within one field.

The ability to use relationships in linking databases together is one of FileMaker Pro's strongest features. With several ways to do it, you are sure to find a method that fits your requirements.

As of this writing, FileMaker Pro 5.5 has been introduced, and has replaced version 5 on the store shelf. The biggest change in 5.5 is that the program now comes in two versions: a Classic version for OS 8.1 or higher, and a Cocoa version for OS X. The Classic version requires a Power Mac with 32MB of RAM and running Mac OS 8.1-9.1. The Cocoa version requires an OS X-capable Power Mac G3 or G4 (upgrade cards are not supported) with 128MB of RAM and running Mac OS X.

The list price for a new copy of 5.5 is $249, with a price of $149 for registered owners of FileMaker 2.1 or later. More information, including downloadable demo copies of both Classic and Cocoa versions, is available at

Mark Shuchat-Marx is a longtime Mac technician, consultant, and writer. He lives in the foothills of Albuquerque with his wife Jacqueline, their cats Gingie and Pyewacket, and a Power Mac G4 named Godzilla. You can reach him at


Community Search:
MacTech Search:

Software Updates via MacUpdate

Snowboard Party 2 (Games)
Snowboard Party 2 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Crowned the best snowboarding game available on the market, Snowboard Party is back to fulfill all your adrenaline needs in... | Read more »
The best games like Animal Crossing on m...
Animal Crossing amiibo Festival is out right now for the Wii U, reminding us of just how much fun that world can be. Or at least to go back and check in on our villages once in a while. [Read more] | Read more »
Between 2 Taps - Tap for Tap interview M...
Hello, and welcome back to Between 2 Taps, Tap for Tap’s Indie Dev interview series. [Read more] | Read more »
Facility 47 (Games)
Facility 47 1.0.1 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0.1 (iTunes) Description: You wake up alone and freezing in an icy cell. You try the cell door but it’s locked, it seems that you are stuck with no... | Read more »
The best Photoshop alternative on iPad
Instagram and Lightroom are great and all, but sometimes people need to get extra creative with their image editing.Like, Photoshop creative. If you're one of these people, take a look at our pick for the best mobile Photoshop experience on iPad... | Read more »
The Walking Dead: No Man’s Land guide -...
A new update for The Walking Dead: No Man’s Land was released last week, making it the perfect time for you to head back to your base and take out some walkers. Here’s the lowdown on what’s new to the game, and how to take advantage. [Read more] | Read more »
Goat Rider guide - Tips and tricks to st...
We've all been there. One second, we're riding high on a crazed goat, and the next, we've been tossed off it like someone who's no good at goat ridin'. [Read more] | Read more »
Real Boxing 2 CREED: How to become a gre...
Just in time for Rocky fans who can’t wait to see CREED, the latest movie, we have the official tie-in game,Real Boxing 2 CREED. It builds on the success of its predecessor and there’s lots to take in so we at 148apps thought we’d run you through... | Read more »
CoinOp Heroes 2 guide - How to build an...
CoinOp Heroes 2 justlaunched and, like all clickers, it's dangerously addictive stuff. You have to furiously tap your screen to defeat wave after wave of foes and earn an insane amount of cash to spend on character upgrades and an army of minions... | Read more »
Dr. Panda Firefighters (Education)
Dr. Panda Firefighters 1.0.1 Device: iOS Universal Category: Education Price: $2.99, Version: 1.0.1 (iTunes) Description: FIGHT FIRES AND SAVE THE DAY!Work together with Dr. Panda and his firefighting team to rescue his trapped... | Read more »

Price Scanner via

Save $30-$40 on new Apple TVs after rebate
Adorama has new Apple TVs on sale for up to $40 off MSRP after mail-in rebate, good through December 15th. Shipping is free, and Adorama charges NY & NJ sales tax only: - 32GB Apple TV: $119.99... Read more
13-Inch Haswell MacBook Air At Two Years – Th...
The 13-inch mid-2013 “Haswell” MacBook Air I ordered in Apple’s November 2013 Black Friday sale was my first new Mac in four and a half years — the longest interval I’ve gone between system upgrades... Read more
Target Black Friday Early Access deals: $100...
Target is offering early access to their Black Friday deals on Apple products on their online store for today, the 25th, only. Choose free shipping or free local store pickup (if available): - Apple... Read more
BlackBerry Q3 Mobility Index Report Finds iOS...
BlackBerry has announced results of its thirteenth Good Mobility Index Report, showing that organizations are increasingly building custom secure apps. Among Good Powered by BlackBerry (formerly Good... Read more
Wednesday roundup of early Black Friday Mac s...
Save up to $500 on a new Mac with these early Black Friday deals from Apple resellers, currently the lowest prices available for these models: (1) B&H Photo has all new Macs on sale for up to $... Read more
iPod nano on sale for $119, $30 off MSRP
Walmart has the 16GB iPod nano (various colors) on sale for $119.20 on their online store for a limited time. That’s $30 off MSRP. Choose free shipping or free local store pickup (if available). Sale... Read more
Adorama Black Friday deals: Up to $400 off Ma...
Adorama has released their Black Friday deals for 2015. Save up to $400 on MacBook Pros, $200 on MacBooks and MacBook Airs, and $270 on iMacs. Use code RYBFDEAL during checkout to see these prices.... Read more
B&H Photo Deals: $200 off 12-inch 1.2GHz...
In addition to the B&H Photo Black Friday week sales we posted yesterday, B&H has lowered their price on two products to $200 off MSRP: - 12″ 1.2GHz Gray Retina MacBook: $1399 save $200 - 13... Read more
Best Buy Early Access: Today only, Up to $125...
Best Buy has iPad Air 2s on sale for up to $125 off MSRP and Apple Watch models on sale on their online store for up to $100 off MSRP with special codes through midnight CT tonight. Choose free... Read more
UPPERCASE DESIGNS Premium Ultra Thin Keyboard...
UPPERCASE Designs today announced its new Premium Ultra Thin Keyboard Protector and its Palm Rest Protector Set for the 12-inch MacBook. The accessories provide durable protection for the 12-inch... Read more

Jobs Board

Hardware Systems Integration Engineer - *App...
# Hardware Systems Integration Engineer - Apple Watch Job Number: 39380139 Santa Clara Valley, Califo ia, United States Posted: Apr. 23, 2015 Weekly Hours: **Job Read more
Sr. Technical/Project Manager, *Apple* Educ...
# Sr. Technical/Project Manager, Apple Education Job Number: 36588557 New York City, New York, United States Posted: Jul. 30, 2015 Weekly Hours: 40.00 **Job Summary** Read more
*Apple* Retail - Multiple Positions (US) - A...
Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, you're also the Read more
Merchant Operations Manager: *Apple* Pay -...
Changing the world is all in a day's work at Apple . If you love innovation, here's your chance to make a career of it. You'll work hard. But the job comes with more than Read more
*Apple* Pay QA Manager - Apple Inc. (United...
Changing the world is all in a day's work at Apple . If you love innovation, here's your chance to make a career of it. You'll work hard. But the job comes with more than Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.