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
$112.65
Apple Inc.
+3.24
MSFT
$47.52
Microsoft Corpora
+1.78
GOOG
$511.10
Google Inc.
+6.21

MacTech Search:
Community Search:

Software Updates via MacUpdate

Mellel 3.3.7 - Powerful word processor w...
Mellel is the leading word processor for OS X and has been widely considered the industry standard since its inception. Mellel focuses on writers and scholars for technical writing and multilingual... Read more
ScreenFlow 5.0.1 - Create screen recordi...
Save 10% with the exclusive MacUpdate coupon code: AFMacUpdate10 Buy now! ScreenFlow is powerful, easy-to-use screencasting software for the Mac. With ScreenFlow you can record the contents of your... Read more
Simon 4.0 - Monitor changes and crashes...
Simon monitors websites and alerts you of crashes and changes. Select pages to monitor, choose your alert options, and customize your settings. Simon does the rest. Keep a watchful eye on your... Read more
BBEdit 11.0.2 - Powerful text and HTML e...
BBEdit is the leading professional HTML and text editor for the Mac. Specifically crafted in response to the needs of Web authors and software developers, this award-winning product provides a... Read more
ExpanDrive 4.2.1 - Access cloud storage...
ExpanDrive builds cloud storage in every application, acts just like a USB drive plugged into your Mac. With ExpanDrive, you can securely access any remote file server directly from the Finder or... Read more
Adobe After Effects CC 2014 13.2 - Creat...
After Effects CC 2014 is available as part of Adobe Creative Cloud for as little as $19.99/month (or $9.99/month if you're a previous After Effects customer). After Effects CS6 is still available... Read more
Command-C 1.1.7 - Clipboard sharing tool...
Command-C is a revolutionary app which makes easy to share your clipboard between iOS and OS X using your local WiFi network, even if the app is not currently opened. Copy anything (text, pictures,... Read more
Tidy Up 4.0.2 - Find duplicate files and...
Tidy Up is a complete duplicate finder and disk-tidiness utility. With Tidy Up you can search for duplicate files and packages by the owner application, content, type, creator, extension, time... Read more
Typinator 6.3 - Speedy and reliable text...
Typinator turbo-charges your typing productivity. Type a little. Typinator does the rest. We've all faced projects that require repetitive typing tasks. With Typinator, you can store commonly used... Read more
GraphicConverter 9.5 - Graphics editor w...
GraphicConverter is an all-purpose image-editing program that can import 200 different graphic-based formats, edit the image, and export it to any of 80 available file formats. The high-end editing... Read more

Latest Forum Discussions

See All

Shift - Photo Filters Designed By You (...
Shift - Photo Filters Designed By You 1.0 Device: iOS Universal Category: Photography Price: $.99, Version: 1.0 (iTunes) Description: | Read more »
Elastic Drums (Music)
Elastic Drums 1.0 Device: iOS iPhone Category: Music Price: $3.99, Version: 1.0 (iTunes) Description: *** Introduction price 3,99$ instead of 7,99$ *** Elastic Drums is a music app with 6 channels of synthesized drum sounds, a step... | Read more »
Fireworks Simulator (Games)
Fireworks Simulator 1.0.8 Device: iOS Universal Category: Games Price: $.99, Version: 1.0.8 (iTunes) Description: *** 50% discount – For a short time only *** You can play Fireworks Simulator on these devices: - iPhone 5, 5s, 5c, 6,... | Read more »
Nicky's Gift (Games)
Nicky's Gift 1.0 Device: iOS Universal Category: Games Price: $.99, Version: 1.0 (iTunes) Description: Everybody! Merry Christmas! There's 48 levels in the game. Let's go! Nicky's Gift | Read more »
The Hit List — Simply Powerful Tasks, To...
The Hit List — Simply Powerful Tasks, To-Dos, Projects, & Reminders 2.0 Device: iOS iPhone Category: Productivity Price: $9.99, Version: 2.0 (iTunes) Description: >> LAUNCH SPECIAL: The Hit List 2 for iPhone is ONLY $9.99... | Read more »
Mahjong Journey Review
Mahjong Journey Review By Jennifer Allen on December 18th, 2014 Our Rating: :: STEADY MATCHINGiPad Only App - Designed for the iPad Aimed at the more laid back gamer, Mahjong Journey isn’t for everyone, but those looking for some... | Read more »
Emoji Type - custom keyboard with predic...
Emoji Type - custom keyboard with predictive emojis 0.4.0 Device: iOS iPhone Category: Utilities Price: $.99, Version: 0.4.0 (iTunes) Description: Emoji Type is custom keyboard for iOS 8 that auto suggests emojis as you type. ABOUT... | Read more »
Game of the Year 2014 – 148Apps Staff Pi...
The end of 2014 is almost here, which can only mean one thing. Okay it can mean a lot of things, but in this specific context it means Game of the Year lists! Which is why the 148Apps staff have all picked their favorites from the past year. And why... | Read more »
UponPixels Review
UponPixels Review By Jennifer Allen on December 18th, 2014 Our Rating: :: CREATIVE TYPOGRAPHYUniversal App - Designed for iPhone and iPad Add cool typography and objects to your photos with the easy to use UponPixels.   | Read more »
The Vikings are Coming! CastleStorm’s Ne...
The Vikings are Coming! CastleStorm’s New Update Adds a Survival Mode Posted by Jessica Fisher on December 18th, 2014 [ permalink ] | Read more »

Price Scanner via MacPrices.net

Save up to $400 on MacBooks with Apple Certif...
The Apple Store has Apple Certified Refurbished 2014 MacBook Pros and MacBook Airs available for up to $400 off the cost of new models. An Apple one-year warranty is included with each model, and... Read more
Save up to $300 on Macs, $30 on iPads with Ap...
Purchase a new Mac or iPad at The Apple Store for Education and take up to $300 off MSRP. All teachers, students, and staff of any educational institution qualify for the discount. Shipping is free,... Read more
iOS and Android OS Targeted by Man-in-the-Mid...
Cloud services security provider Akamai Technologies, Inc. has released, through the company’s Prolexic Security Engineering & Research Team (PLXsert), a new cybersecurity threat advisory. The... Read more
KMI MIDI K-Board Great Gift for Amateur &...
The K-Board is a MIDI Nano keyboard for music creation for iPad, Android, And computers; the easiest way to make music with iPads & Android tablets, and Mac, Windows, or Linux computers. Ultra-... Read more
Amazon offers 15-inch 2.2GHz Retina MacBook P...
 Amazon.com has the 15″ 2.2GHz Retina MacBook Pro on sale for $1699 including free shipping. Their price is $300 off MSRP. Stock is limited, so act now if you’re interested. Read more
Holiday sales continue: MacBook Pros for up t...
 B&H Photo has new MacBook Pros on sale for up to $300 off MSRP as part of their Holiday pricing. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $1699... Read more
Holiday sale: Mac minis available for up to $...
 B&H Photo has new 2014 Mac minis on sale for up to $80 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 1.4GHz Mac mini: $459 $40 off MSRP - 2.6GHz Mac mini: $629 $70 off... Read more
Google Search App For iOS Gets A Major Makeov...
Google has given iOS users an early Christmas present with a substantial update of it’s not-very-often-upgraded Google Search app. Google Search has been my go-to tool for Web searches since it was... Read more
ShopKeep Apple Pay And Chip Card Reader Avail...
ShopKeep, a cloud-based technology provider to more than 10,000 small business owners to manage retail shops and restaurants with iPads, has released its new Apple Pay and chip card reader. This... Read more
Holiday sale! 27-inch 5K iMac for $2299, save...
 B&H Photo has the 27″ 3.5GHz 5K iMac in stock today and on sale for $2299 including free shipping plus NY sales tax only. Their price is $200 off MSRP, and it’s the lowest price available for... Read more

Jobs Board

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* 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
*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
*Apple* Retail - Multiple Positions (US) - A...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, 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
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.