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.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

Boom 2 1.0.1 - System-wide pro audio app...
Boom 2 is a system-wide volume booster and equalizer app that is designed especially for OS X 10.10 Yosemite. It comes with a smart interface, self-calibrates itself according to your Mac, offers... Read more
Apple Security Update 2015-001 - For OS...
Apple Security Update 2015-001 is recommended for all users and improves the security of OS X. For detailed information about the security content of this update, please visit: http://support.apple.... Read more
Drive Genius 4.0.1 - Powerful system uti...
Drive Genius 4 gives you faster performance from your Mac while also protecting it. The award-winning and improved DrivePulse feature alerts you to hard drive issues before they become major problems... Read more
Yosemite Cache Cleaner 9.0.3 - Clear cac...
Yosemite Cache Cleaner is an award-winning general purpose tool for OS X. YCC makes system maintenance simple with an easy point-and-click interface to many OS X functions. Novice and expert users... Read more
xScope 4.1.1 - Onscreen graphic measurem...
xScope is powerful set of tools that are ideal for measuring, inspecting, and testing on-screen graphics and layouts. Its tools float above your desktop windows and can be accessed via a toolbar,... Read more
OS X Yosemite 10.10.2 - Apple's lat...
OS X Yosemite is Apple's newest operating system for Mac. An elegant design that feels entirely fresh, yet inherently familiar. The apps you use every day, enhanced with new features. And a... Read more
Apple iOS 8.1.3 - The latest version of...
The latest version of iOS can be downloaded through iTunes. Apple iOS 8 comes with big updates to apps you use every day, like Messages and Photos. A whole new way to share content with your family.... Read more
SpamSieve 2.9.19 - Robust spam filter fo...
SpamSieve is a robust spam filter for major email clients that uses powerful Bayesian spam filtering. SpamSieve understands what your spam looks like in order to block it all, but also learns what... Read more
RapidWeaver 6.0.5 - Create template-base...
RapidWeaver is a next-generation Web design application to help you easily create professional-looking Web sites in minutes. No knowledge of complex code is required, RapidWeaver will take care of... Read more
Duet 1.2.2 - Use your iPad as an externa...
Duet is the first app that allows you to use your iDevice as an extra display for your Mac using the Lightning or 30-pin cable. Note: This app requires a $14.99 iOS companion app. Version 1.2.2:... Read more

Storm & Skye – Magical Adventure Sto...
Storm & Skye – Magical Adventure Story For Kids Review By Amy Solomon on January 28th, 2015 Our Rating: iPhone App - Designed for the iPhone, compatible with the iPad The first book in the Storm & Skye series includes... | Read more »
New WOTA: U-Boat Compass ‘Wets’ Your App...
New WOTA: U-Boat Compass ‘Wets’ Your Appetite for WOTA: Wolves of the Atlantic. Posted by Jessica Fisher on January 27th, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Warship Solitaire Review
Warship Solitaire Review By Nadia Oxford on January 27th, 2015 Our Rating: :: A WAR OF NUMBERSUniversal App - Designed for iPhone and iPad Warship Solitaire might lack frills and looks, but it offers some thought provoking... | Read more »
Keep Track of Your Hectic Work Schedule...
Keep Track of Your Hectic Work Schedule With Shifts Posted by Jessica Fisher on January 27th, 2015 [ permalink ] iPhone App - Designed for the iPhone, compatible with the iPad | Read more »
All Star Quarterback Review
All Star Quarterback Review By Campbell Bird on January 27th, 2015 Our Rating: :: PLAY YOUR PARTUniversal App - Designed for iPhone and iPad Train your way to the Super Bowl in this free-to-play sports management game.   | Read more »
Sentinel 4: Dark Star Adds New Campaign...
Sentinel 4: Dark Star Adds New Campaign and More Posted by Ellis Spice on January 27th, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Wheel & Deal Review
Wheel & Deal Review By Tre Lawrence on January 27th, 2015 Our Rating: :: SIMPLE FUNUniversal App - Designed for iPhone and iPad A simple arcade shooter that has a retro feel.   | Read more »
Join the NAVY in a Big New Update for WW...
Join the NAVY in a Big New Update for WW2: Sandbox. Strategy & Tactics Posted by Jessica Fisher on January 27th, 2015 [ permalink ] Universal App - Designed for iPhone and iPad | Read more »
Earthworm Alchemy Review
Earthworm Alchemy Review By Campbell Bird on January 27th, 2015 Our Rating: :: FEED THE WORMUniversal App - Designed for iPhone and iPad Feed this funny looking worm and dodge bombs in this charming, free-to-play arcade game.   | Read more »
Graphic Novel App netwars – The Butterfl...
Graphic Novel App netwars – The Butterfly Attack has Received Some New Episodes Posted by Jessica Fisher on January 27th, 2015 [ permalink ] | Read more »

Price Scanner via MacPrices.net

New Good Management Suite Simplifies Enterpri...
Good Technology has announced the availability of the Good Management Suite, a comprehensive cross-platform solution for organizations getting started with mobile business initiatives. Built on the... Read more
15-inch 2.0GHz Retina MacBook Pro (refurbishe...
The Apple Store has Apple Certified Refurbished previous-generation 15″ 2.0GHz Retina MacBook Pros available for $1489 including free shipping plus Apple’s standard one-year warranty. Their price is... Read more
15-inch 2.5GHz Retina MacBook Pro on sale for...
Amazon has the 15″ 2.5GHz Retina MacBook Pro on sale for $2319.99 including free shipping. Their price is $180 off MSRP, and it’s the lowest price available for this model from any reseller. Read more
iPad Turns Five – The ”Book Mystique
Five years ago this week, the late Steve Jobs took the stage at an Apple special press event to unveil the first Apple iPad — a slab-shaped, hand-holdable device that was to shake the personal... Read more
Sale! 15-inch Retina MacBook Pros for up to $...
B&H Photo has the new 2014 15″ Retina MacBook Pros on sale for $150 off MSRP for a limited time. Shipping is free, and B&H charges NY sales tax only: - 15″ 2.2GHz Retina MacBook Pro: $1889.99... Read more
Apple refurbished Mac minis for up to $150 of...
The Apple Store has restocked Apple Certified Refurbished 2014 Mac minis, with models available starting at $419. Apple’s one-year warranty is included with each mini, and shipping is free: - 1.4GHz... Read more
Apple Releases New Tranche Of OS Updates For...
Apple has released incremental bugfix and security updates for its most recent Mac and iOS versions. OS X Yosemite 10.10.2 Update Apple says this update includes the following improvements: Resolves... Read more
Samsung To Be Main Supplier Of A9 Chips For i...
Korea’s Maeil Business Newspaper reports that Samsung has beat out its chipmaking rivals, Taiwan’s Qualcomm and TSMC, as supplier of chipsets for the next generations of both its own Galaxy S6 and... Read more
Save up to $340 on iMacs with Apple Certified...
The Apple Store has Apple Certified Refurbished iMacs available for up to $340 off the cost of new models. Apple’s one-year warranty is standard, and shipping is free. These are the best prices on... Read more
AppleCare Protection Plans on sale for up to...
B&H Photo has 3-Year AppleCare Warranties on sale for up to $105 off MSRP including free shipping plus NY sales tax only: - Mac Laptops 15″ and Above: $244 $105 off MSRP - Mac Laptops 13″ and... Read more

Jobs Board

*Apple* Systems Engineer - Pre Sales, Educat...
…is responsible for proactively providing technical expertise to drive sales of Apple solutions into assigned accounts. The SE architects, validates, and assists in Read more
*Apple* Solutions Consultant - Retail Sales...
As an ambassador of the Apple brand, the ASC is accountable for driving sales performance by: Connecting with customers. Discovering customers' needs and values. Showing Read more
*Apple* Solutions Consultant - Retail Sales...
As an ambassador of the Apple brand, the ASC is accountable for driving sales performance by: Connecting with customers. Discovering customers' needs and values. Showing Read more
*Apple* Tree Children's Center is hirin...
Apple Tree Learning Center is Hiring Preschool Teachers! Phoenix Childrens Academy is a national leader in the early childhood education industry and we are currently Read more
*Apple* Solutions Consultant - Retail Sales...
**Job Summary** As an Apple Solutions Consultant (ASC) you are the link between our customers and our products. Your role is to drive the Apple business in a retail Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.