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

How to send money to a friend with the F...
Facebook is already a lot of things to a lot of people. It's a social network, sure, but also a tool for catologing memories, chatting with friends, and much more. [Read more] | Read more »
Pocket Arcade Story (Games)
Pocket Arcade Story 1.00 Device: iOS Universal Category: Games Price: $4.99, Version: 1.00 (iTunes) Description: Here comes a new challenger: it's the arcade simulation game you've been waiting for! Build your very own gaming... | Read more »
How to get coins faster in Rodeo Stamped...
There comes a time in a cowboy or cowgirl's life when all the riding and lassoing skills in the world aren't enough. You're going to need some cold, hard cash to keep your sky zoo expanding in Rodeo Stampede. [Read more] | Read more »
How to out-do Cam Newton in Can You Dab?
The thing about dance crazes is that you're never really sure when they've run their course. Take the Dab, for instance. Propelled by its adoption as the touchdown celebration of choice for Carolina Panthers quarterback Cam Newton, the Dab seemed... | Read more »
Artik Games releases Splashy Cats for An...
Splashy Cats had us hooked from the title alone, and when we found out the game was literally just zig-zagging one of our favourite pop-culture references, guised as a playable cat character, down a river – our appetites were whetted to say the... | Read more »
Battle Cars (Games)
Battle Cars 1.1 Device: iOS Universal Category: Games Price: $1.99, Version: 1.1 (iTunes) Description: Welcome to the world of Battle Cars. Battle Cars is a classic arcade top-down racing game with fast mini cars and funny weapons to... | Read more »
How to get started with live.ly
One could be forgiven for thinking that there are already plenty of streaming video apps out there. It's just that the App Store charts would insist that you're mistaken. [Read more] | Read more »
Rodeo Stampede: Guide to all Savannah an...
A "gotta catch 'em all" joke seems appropriate here, even though we're talking animals in Rodeo Stampede and not pocket monsters. By now you've probably had plenty of rides, tamed some animals and built yourself a pretty nice zoo | Read more »
Is there cross-platform play in slither....
So you've sunken plenty of hours into crawling around in slither.io on your iPhone or iPad. You've got your stories of tragedy and triumph, the times you coiled four snakes at one time balanced out by the others when you had a length of more than... | Read more »
Rodeo Stampede guide to running a better...
In Rodeo Stampede, honing your skills so you can jump from animal to animal and outrun the herd as long as possible is only half the fun. Once you've tamed a few animals, you can bring them home with you. [Read more] | Read more »

Price Scanner via MacPrices.net

Why Use Indie Opera And Vivaldi Instead Of Sa...
For many years my web browser workhorses were various permutations and spinoffs of the Netscape/Mozilla/Firefox Open Source platform, and the Norwegian indie browser Opera, which I took a shine to... Read more
Western Digital Launches Worlds Fastest 256GB...
At the Mobile World Congress in Shanghai Western Digital Corporation this week introduced a new suite of 256 gigabyte (GB) microSD cards, which includes the new 256GB SanDisk Extreme microSDXC UHS-I... Read more
KeyCue 8.1 Integrates With Typinator To Displ...
Ergonis Software has released KeyCue 8.1, a new version of the company’s keyboard shortcut cheat sheet. KeyCue 8 introduced a new way to define a wide variety of triggers, which can be used to... Read more
Save up to $600 with Apple refurbished Mac Pr...
Apple has Certified Refurbished Mac Pros available for up to $600 off the cost of new models. An Apple one-year warranty is included with each Mac Pro, and shipping is free. The following... Read more
21-inch 2.8GHz iMac on sale for $1199, save $...
Amazon has the 21″ 2.8GHz iMac (model #MK442LL/A) on sale for $1199.99 including free shipping. Their price is $100 off MSRP, and it’s the lowest price available for this model. Read more
13-inch 2.5GHz MacBook Pro (Apple refurbished...
Apple has Certified Refurbished 13″ 2.5GHz MacBook Pros available for $829, or $270 off the cost of new models. Apple’s one-year warranty is standard, and shipping is free: - 13″ 2.5GHz MacBook Pros... Read more
MacBook Airs on sale for up to $50-$100 off M...
B&H Photo has 13″ and 11″ MacBook Airs on sale for up to $100 off MSRP. Shipping is free, and B&H charges NY sales tax only: - 11″ 1.6GHz/128GB MacBook Air: $849 $50 off list price - 11″ 1.... Read more
Brexit Vote Result Forecast To Slash UK 2016...
Uncertainty and economic volatility can be expected to increase over the next nine months, as the Brexit and concerns over the future of the EU hit IT investment, say Canalys market analysts, with... Read more
13-inch 256GB MacBook Air on sale for $1149,...
Amazon has the 2016 13″ 1.6GHz/256GB MacBook Air (model MMGG2LL/A) on sale for $1149.99 including free shipping. Their price is also $50 off MSRP. Read more
Haven App Launches New Age Of Wirless 911 Eme...
Haven from RapidSOS represents a transformation in access to emergency services from a phone call solely dependent on voice to a robust data connection for voice, text, medical/demographic data.... Read more

Jobs Board

*Apple* iPhone 6s and New Products Tester Ne...
…we therefore look forward to put out products to quality test for durability. Apple leads the digital music revolution with its iPods and iTunes online store, Read more
*Apple* iPhone 6s and New Products Tester Ne...
…we therefore look forward to put out products to quality test for durability. Apple leads the digital music revolution with its iPods and iTunes online store, Read more
*Apple* iPhone 6s and New Products Tester Ne...
…we therefore look forward to put out products to quality test for durability. Apple leads the digital music revolution with its iPods and iTunes online store, Read more
*Apple* Retail - Multiple Positions, Willow...
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* iPhone 6s and New Products Tester Ne...
…we therefore look forward to put out products to quality test for durability. Apple leads the digital music revolution with its iPods and iTunes online store, Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.