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 http://www.filemaker.com.


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 marksm@mac.com.

 
AAPL
$101.32
Apple Inc.
+0.74
MSFT
$45.15
Microsoft Corpora
-0.07
GOOG
$582.56
Google Inc.
-0.81

MacTech Search:
Community Search:

Software Updates via MacUpdate

calibre 2.0.0 - Complete e-library manag...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital... Read more
Apple iMovie 10.0.5 - Edit personal vide...
With an all-new design, Apple iMovie lets you enjoy your videos like never before. Browse your clips more easily, instantly share your favorite moments, and create beautiful HD movies and Hollywood-... Read more
Apple Keynote 6.2.2 - Apple's prese...
Apple Keynote makes it simple to create and deliver beautiful presentations. Powerful tools and dazzling effects bring your ideas to life. You can work seamlessly between Mac and iOS devices. And... Read more
Apple Numbers 3.2.2 - Apple's sprea...
With Apple Numbers, sophisticated spreadsheets are just the start. The whole sheet is your canvas. Just add dramatic interactive charts, tables, and images that paint a revealing picture of your data... Read more
OpenOffice 4.1.1 - Free and open-source...
OpenOffice.org is both an Open Source product and a project. The product is a multi-platform office productivity suite. It includes the key desktop applications, such as a word processor,... Read more
Pages 5.2.2 - Apple's word processo...
Apple Pages is a powerful word processor that gives you everything you need to create documents that look beautiful. And read beautifully. It lets you work seamlessly between Mac and iOS devices. And... Read more
Quicken 2015 2.0.1 - Complete personal f...
The new Quicken 2015 helps you manage all your personal finances in one place, so you can see where you're spending and where you can save. Quicken automatically categorizes your financial... Read more
CleanMyMac 2.2.7 - Delete files that was...
CleanMyMac makes space for the things you love. Sporting a range of ingenious new features, CleanMyMac 2 lets you safely and intelligently scan and clean your entire system, delete large, unused... Read more
MacFamilyTree 7.2.4 - Create and explore...
MacFamilyTree gives genealogy a facelift: it's modern, interactive, incredibly fast, and easy to use. We're convinced that generations of chroniclers would have loved to trade in their genealogy... Read more
Videobox 4.1.1 - Download Flash video th...
Videobox allows you to quickly and easily download Flash video from most all of the popular video sites on the internet. Videobox will convert the video into a native Quicktime format so it's ready... Read more

Latest Forum Discussions

See All

Trolls vs Vikings Update Adds Over One H...
Trolls vs Vikings Update Adds Over One Hundred Levels, Reduces Item Cost, and More Posted by Ellis Spice on August 22nd, 2014 [ permalink ] | Read more »
SNK Celebrates the 20th Anniversary of T...
SNK Celebrates the 20th Anniversary of The King of Fighters With a Big Sale Posted by Ellis Spice on August 22nd, 2014 [ permalink ] | Read more »
It Came From Canada: Star Wars: Commande...
With a brand new Star Wars trilogy on the horizon, prepare yourselves for Disney and George Lucas’s space fantasy throwback to be more omnipresent than ever before. So it should come as no surprise that new adventures in that galaxy far, far away... | Read more »
Swing Copters Review
Swing Copters Review By Jordan Minor on August 22nd, 2014 Our Rating: :: DIE TRYINGUniversal App - Designed for iPhone and iPad The creator of Flappy Bird is back with a vengeance.   | Read more »
The Outcast Review
The Outcast Review By Nadia Oxford on August 22nd, 2014 Our Rating: :: HANDS OFF. WAY OFF.Universal App - Designed for iPhone and iPad It’s easy to see what The Outcast is trying for, but its execution needs a lot of work.   | Read more »
Brain+ Review
Brain+ Review By Nadia Oxford on August 22nd, 2014 Our Rating: :: DIM BULBUniversal App - Designed for iPhone and iPad Brain+ is just another entry in an over-saturated brain-training marketplace – and not a particularly fun entry... | Read more »
The Witcher Battle Arena – New Gameplay...
The Witcher Battle Arena – New Gameplay Trailer Revealed Posted by Jessica Fisher on August 22nd, 2014 [ permalink ] Based in the Witcher universe, | Read more »
Max Gentlemen Review
Max Gentlemen Review By Jennifer Allen on August 22nd, 2014 Our Rating: :: OUTSTAYING ITS WELCOMEiPhone App - Designed for the iPhone, compatible with the iPad Max Gentlemen seems pretty quirky initially but that appeal wears thin... | Read more »
Club Penguin Gets Frozen All Over in Lat...
Club Penguin Gets Frozen All Over in Latest Takeover Event Posted by Ellis Spice on August 22nd, 2014 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Doctor Who: Legacy Review
Doctor Who: Legacy Review By Jennifer Allen on August 22nd, 2014 Our Rating: :: RUN OF THE MILLUniversal App - Designed for iPhone and iPad Doctor Who: Legacy is an ok match-3 puzzle game, but it lacks some much needed personality... | Read more »

Price Scanner via MacPrices.net

Leftover 15-inch 2.0GHz Retina MacBook Pros a...
B&H Photo has leftover previous-generation 15″ 2.0GHz Retina MacBook Pros now available for $1599 including free shipping plus NY sales tax only. Their price is $400 off original MSRP. B&H... Read more
Pro.Calendar – New Productivity App for iPad...
Austin, Texas based mobile business and productivity app developer LightArrow, Inc. has announced Pro.Calendar, a powerful and intuitive calendar app with eight versatile calendar options including... Read more
SanDisk Ultra II SSD — Supercharge Your Syste...
SanDisk Corporation has announced the new SanDisk Ultra II SSD with enhanced SSD Dashboard. The new drive is designed to deliver a cost-effective and easy upgrade solution for PC owners looking to... Read more
Samsung and Barnes & Noble Introduce New...
Samsung Electronics America and NOOK Media, a subsidiary of Barnes & Noble, Inc. have announced the introduction of the new Samsung Galaxy Tab 4 NOOK, a 7-inch tablet combining Samsung’s leading... Read more
21-inch iMacs on sale for up to $150 off MSRP
B&H Photo has 21″ iMacs on sale for up to $150 off MSRP including free shipping plus NY sales tax only. B&H will also include a free copy of Parallels Desktop software: - 21″ 2.7GHz iMac: $... Read more
27-inch 3.2GHz iMac on sale for $1698, save $...
Abt has the 27″ 3.2GHz iMac on sale for $1698 including free shipping. Their price is $101 off MSRP. Read more
Mac Backup Guru 2.0 Drive Backup/Cloneing Uti...
Mac Backup Guru developer MacDaddy has released Mac Backup Guru 2.0, offering new and enhanced advanced features, such as bootable backups, synchronised volumes and folders, and a Snapshot mode that... Read more
Operate GE’s New Free-Standing KItchen Range...
Think you accidentally left the oven on? Switch it off while on the go. The new free-standing Profile™ Series gas and electric ranges are GE’s second cooking appliances, following their wall oven, to... Read more
Parallels Announces Parallels Desktop 10 for...
The no. 1-selling software for running Windows applications on a Mac becomes an even easier choice for millions of consumers and IT professionals worldwide with the launch of the most powerful... Read more
Apple now offering certified refurbished 2014...
 The Apple Store is now offering Apple Certified Refurbished 2014 MacBook Airs for up to $180 off the cost of new models. An Apple one-year warranty is included with each MacBook, and shipping is... Read more

Jobs Board

*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
Position Opening at *Apple* - Apple (United...
**Job Summary** As more and more people discover Apple , they visit our stores seeking ways to incorporate our products into their lives. It's your job, as a Store Read more
Position Opening at *Apple* - Apple (United...
…Summary** As a Specialist, you help create the energy and excitement around Apple products, providing the right solutions and getting products into customers' hands. You Read more
Project Manager, *Apple* Financial Services...
**Job Summary** Apple Financial Services (AFS) offers consumers, businesses and educational institutions ways to finance Apple purchases. We work with national and Read more
*Apple* Solutions Consultant (ASC)- Retail S...
**Job Summary** The ASC is an Apple employee who serves as an Apple brand ambassador and influencer in a Reseller's store. The ASC's role is to grow Apple Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.