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

Amazon Chime 4.3.5721 - Amazon-based com...
Amazon Chime is a communications service that transforms online meetings with a secure, easy-to-use application that you can trust. Amazon Chime works seamlessly across your devices so that you can... Read more
Posterino 3.3.7 - Create posters, collag...
Posterino offers enhanced customization and flexibility including a variety of new, stylish templates featuring grids of identical or odd-sized image boxes. You can customize the size and shape of... Read more
Airmail 3.2.9 - Powerful, minimal email...
Airmail is an mail client with fast performance and intuitive interaction. Support for iCloud, MS Exchange, Gmail, Google Apps, IMAP, POP3, Yahoo!, AOL, Outlook.com, Live.com. Airmail was designed... Read more
Arq 5.8.4 - Online backup to Google Driv...
Arq is super-easy online backup for Mac and Windows computers. Back up to your own cloud account (Amazon Cloud Drive, Google Drive, Dropbox, OneDrive, Google Cloud Storage, any S3-compatible server... Read more
Microsoft Remote Desktop 8.0.39 - Connec...
With Microsoft Remote Desktop, you can connect to a remote PC and your work resources from almost anywhere. Experience the power of Windows with RemoteFX in a Remote Desktop client designed to help... Read more
Arq 5.8.4 - Online backup to Google Driv...
Arq is super-easy online backup for Mac and Windows computers. Back up to your own cloud account (Amazon Cloud Drive, Google Drive, Dropbox, OneDrive, Google Cloud Storage, any S3-compatible server... Read more
Airmail 3.2.9 - Powerful, minimal email...
Airmail is an mail client with fast performance and intuitive interaction. Support for iCloud, MS Exchange, Gmail, Google Apps, IMAP, POP3, Yahoo!, AOL, Outlook.com, Live.com. Airmail was designed... Read more
Microsoft Remote Desktop 8.0.39 - Connec...
With Microsoft Remote Desktop, you can connect to a remote PC and your work resources from almost anywhere. Experience the power of Windows with RemoteFX in a Remote Desktop client designed to help... Read more
Posterino 3.3.7 - Create posters, collag...
Posterino offers enhanced customization and flexibility including a variety of new, stylish templates featuring grids of identical or odd-sized image boxes. You can customize the size and shape of... Read more
Airmail 3.2.8 - Powerful, minimal email...
Airmail is an mail client with fast performance and intuitive interaction. Support for iCloud, MS Exchange, Gmail, Google Apps, IMAP, POP3, Yahoo!, AOL, Outlook.com, Live.com. Airmail was designed... Read more

Latest Forum Discussions

See All

Pokémon GO might be getting legendaries...
The long-awaited legendary Pokémon may soon be coming to Pokémon GO at long last. Data miners have already discovered that the legendary birds, Articuno, Moltres, and Zapdos are already in the game, it’s just a matter of time. [Read more] | Read more »
The best deals on the App Store this wee...
If you’ve got the Monday blues we have just the thing to cheer you up. The week is shaping up to be a spectacular one for sales. We’ve got a bunch of well-loved indie games at discounted prices this week along with a few that are a little more... | Read more »
Honor 8 Pro, a great choice for gamers
Honor is making strides to bring its brand to the forefront of mobile gaming with its latest phone, the Honor 8 Pro. The Pro sets itself apart from its predecessor, the Honor 8, with a host of premium updates that boost the device’s graphical and... | Read more »
The 4 best outdoor adventure apps
Now that we're well into the pleasant, warmer months, it's time to start making the most of the great outdoors. Spring and summer are ideal times for a bit of trekking or exploration. You don't have to go it alone, though. There are plenty of... | Read more »
Things 3 (Productivity)
Things 3 3.0.1 Device: iOS iPhone Category: Productivity Price: $7.99, Version: 3.0.1 (iTunes) Description: Meet the all-new Things! A complete rethinking of the original, award-winning task manager – with a perfect balance between... | Read more »
Oddball mash-up Arkanoid vs Space Invade...
In a move no one was really expecting, Square Enix has put forth an Arkanoid/Space Invaders mash-up aptly titled Arkanoid vs Space Invaders. The game launched today on both iOS and Android and the reviews are actually quite good. [Read more] | Read more »
Arkanoid vs Space Invaders (Games)
Arkanoid vs Space Invaders 1.0 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0 (iTunes) Description: LAUNCH SALE: GET THE GAME AT 20% OFF! Two of the most iconic classic games ever made meet in Arkanoid vs Space... | Read more »
The best new games we played this week
Things got off to a bit of a slow start this week, but as we steadily creep towards Friday a bunch of great games have started cropping up. If you're looking for a quality new release to play this weekend, we've got you covered. Here's a handy... | Read more »
No Stick Shooter (Games)
No Stick Shooter 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Happy Robot presents a fresh take on the classic retro-arcade shooters we all know and love. Featuring 30 levels of non-stop... | Read more »
Chroma Squad (Games)
Chroma Squad 1.0.85 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0.85 (iTunes) Description: | Read more »

Price Scanner via MacPrices.net

Five To Six Million 10.5-inch iPad Pro Tablet...
Digitimes’ Siu Han and Joseph Tsai report that upstream supply chain shipments for Apple’s new 10.5-inch iPad Pro have been increasing, with monthly shipment volume expected to hit 600,000 units by... Read more
Georgia Tech Students Win Toyota and Net Impa...
Earlier this year, a team of students at Georgia Tech realized that there was a critical gap in transportation services for people who use wheelchairs, and wondered if the solution could be in the... Read more
13-inch 2.0GHz Space Gray MacBook Pro on sale...
Amazon has the 13″ 2.0GHz Space Gray non-Touch Bar MacBook Pro (MLL42LL/A) on sale for $1299.99 including free shipping. Their price is $200 off MSRP, and it’s currently the lowest price available... Read more
Roundup of 15-inch MacBook Pro sale prices, m...
B&H Photo has the new 2016 15″ Apple Touch Bar MacBook Pros in stock today and on sale for up to $200 off MSRP. Shipping is free, and B&H charges NY & NJ sales tax only: - 15″ 2.7GHz... Read more
15-inch 2.2GHz Retina MacBook Pro on sale for...
B&H Photo has the 2015 15″ 2.2GHz Retina MacBook Pro (MJLQ2LL/A) on sale for $1849 including free shipping plus NY & NJ sales tax only. Their price is $150 off MSRP. Read more
21-inch iMacs on sale for $100 off MSRP, save...
B&H Photo has 21″ iMacs on sale for $100 off MSRP, each including free shipping plus NY & NJ sales tax only: - 21″ 3.1GHz iMac 4K: $1399.99 $100 off MSRP - 21″ 2.8GHz iMac: $1199.99 $100 off... Read more
Groupon Survey Finds Families Will Spend One-...
With warmer weather finally arriving in most parts of the country and the school year winding down to a close, Groupon (http://www.groupon.com) asked 1,000 U.S. parents how much time their families... Read more
Panasonic Anounces Toughbook 33 2-in-1 Rugged...
Panasonic has announced the U.S. availability of the Panasonic Toughbook 33, claimed to be the first fully-rugged 2-in-1 detachable laptop that features a 3:2 display. Inspired by the hard-working... Read more
9-inch 32GB iPad Pros available for up to $10...
Amazon resellers are offering new 9″ 32GB iPad Pros for up to $101 off MSRP, starting at $498, including free shipping. Colors may be Space Gray, Silver, Gold, or Rose Gold, depending on the reseller... Read more
Back in stock: Apple refurbished Mac minis st...
Apple has Certified Refurbished Mac minis available starting at $419. Apple’s one-year warranty is included with each mini, and shipping is free: - 1.4GHz Mac mini: $419 $80 off MSRP - 2.6GHz Mac... Read more

Jobs Board

Director, *Apple* Program - ERP & Cloud...
…make a real difference. Come, shine with us! Astellas is announcing a Director, Apple Program - ERP & Cloud Platform Architecture Lead opportunity in Northbrook, IL. Read more
*Apple* Mobile Master - Best Buy (United Sta...
**508102BR** **Job Title:** Apple Mobile Master **Location Number:** 000511-Athens-Store **Job Description:** **What does a Best Buy Apple Mobile Master do?** At Read more
*Apple* Media Products - Commerce Engineerin...
Apple Media Products - Commerce Engineering Manager Job Number: 57037480 Santa Clara Valley, California, United States Posted: Apr. 18, 2017 Weekly Hours: 40.00 Job Read more
Geek Squad *Apple* Master Consultation Agen...
**500444BR** **Job Title:** Geek Squad Apple Master Consultation Agent **Location Number:** 000103-City Of Industry-Store **Job Description:** **What does a Geek Read more
Security Engineer, *Apple* Retail - Apple I...
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 Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.