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

NeoFinder 7.4.1 - Catalog your external...
NeoFinder (formerly CDFinder) rapidly organizes your data, either on external or internal disks, or any other volumes. It catalogs and manages all your data, so you stay in control of your data... Read more
Tunnelblick 3.8.0 - GUI for OpenVPN.
Tunnelblick is a free, open source graphic user interface for OpenVPN on OS X. It provides easy control of OpenVPN client and/or server connections. It comes as a ready-to-use application with all... Read more
Tweetbot 3 for Twitter 3.3.1 - Popular T...
Tweetbot is a full-featured OS X Twitter client with a lot of personality. Whether it's the meticulously-crafted interface, sounds and animation, or features like multiple timelines and column views... Read more
Monosnap 3.5.9 - Versatile screenshot ut...
Monosnap lets you capture screenshots, share files, and record video and .gifs. Features Capture Capture full screen, just part of the screen, or a selected window Make your crop area pixel... Read more
Monosnap 3.5.9 - Versatile screenshot ut...
Monosnap lets you capture screenshots, share files, and record video and .gifs. Features Capture Capture full screen, just part of the screen, or a selected window Make your crop area pixel... Read more
Tweetbot 3 for Twitter 3.3.1 - Popular T...
Tweetbot is a full-featured OS X Twitter client with a lot of personality. Whether it's the meticulously-crafted interface, sounds and animation, or features like multiple timelines and column views... Read more
Tunnelblick 3.8.0 - GUI for OpenVPN.
Tunnelblick is a free, open source graphic user interface for OpenVPN on OS X. It provides easy control of OpenVPN client and/or server connections. It comes as a ready-to-use application with all... Read more
Bookends 13.2.5 - Reference management a...
Bookends is a full-featured bibliography/reference and information-management system for students and professionals. Bookends uses the cloud to sync reference libraries on all the Macs you use.... Read more
Quicken 2019 5.11.2 - Complete personal...
Quicken makes managing your money easier than ever. Whether paying bills, upgrading from Windows, enjoying more reliable downloads, or getting expert product help, Quicken's new and improved features... Read more
Bookends 13.2.5 - Reference management a...
Bookends is a full-featured bibliography/reference and information-management system for students and professionals. Bookends uses the cloud to sync reference libraries on all the Macs you use.... Read more

Latest Forum Discussions

See All

Void Tyrant guide - Tips and tricks for...
Void Tyrant continues to get a lot of play in these parts. Probably because the game is just so deep and varied. The next stop on our guide series for Void Tyrant is class-specific guides. First up is the Knight, as it’s the first class anyone has... | Read more »
Summon beasts and battle evil in epic re...
Imagine a tale of conlict between factions of good and evil, where rogueish heroes summon beasts to aid them in them in warfare and courageously battle dragons over fields of scorched earth and brimstone - that's exactly the essence of epic fantasy... | Read more »
Upcoming visual novel Arranged shines a...
If you’re in the market for a new type of visual novel designed to inform and make you think deeply about its subject matter, then Arranged by Kabuk Games could be exactly what you’re looking for. It’s a wholly unique take on marital traditions in... | Read more »
TEPPEN guide - The three best decks in T...
TEPPEN’s unique take on the collectible card game genre is exciting. It’s just over a week old, but that isn’t stopping lots of folks from speculating about the long-term viability of the game, as well as changes and additions that will happen over... | Read more »
Intergalactic puzzler Silly Memory serve...
Recently released matching puzzler Silly Memory is helping its fans with their intergalactic journeys this month with some very special offers on in-app purchases. In case you missed it, Silly Memory is the debut title of French based indie... | Read more »
TEPPEN guide - Tips and tricks for new p...
TEPPEN is a wild game that nobody asked for, but I’m sure glad it exists. Who would’ve thought that a CCG featuring Capcom characters could be so cool and weird? In case you’re not completely sure what TEPPEN is, make sure to check out our review... | Read more »
Dr. Mario World guide - Other games that...
We now live in a post-Dr. Mario World world, and I gotta say, things don’t feel too different. Nintendo continues to squirt out bad games on phones, causing all but the most stalwart fans of mobile games to question why they even bother... | Read more »
Strategy RPG Brown Dust introduces its b...
Epic turn-based RPG Brown Dust is set to turn 500 days old next week, and to celebrate, Neowiz has just unveiled its biggest and most exciting update yet, offering a host of new rewards, increased gacha rates, and a brand new feature that will... | Read more »
Dr. Mario World is yet another disappoin...
As soon as I booted up Dr. Mario World, I knew I wasn’t going to have fun with it. Nintendo’s record on phones thus far has been pretty spotty, with things trending downward as of late. [Read more] | Read more »
Retro Space Shooter P.3 is now available...
Shoot-em-ups tend to be a dime a dozen on the App Store, but every so often you come across one gem that aims to shake up the genre in a unique way. Developer Devjgame’s P.3 is the latest game seeking to do so this, working as a love letter to the... | Read more »

Price Scanner via MacPrices.net

Apple’s $1489 clearance price on refurbished...
Apple has Certified Refurbished 2018 13″ 2.3GHz 4-Core Touch Bar MacBook Pros available starting at $1489. Apple’s one-year warranty is included, shipping is free, and each MacBook has a new outer... Read more
New 2019 13″ 2.4GHz 4-Core MacBook Pros on sa...
Apple resellers B&H Photo and Amazon are offering the new 2019 13″ 2.4GHz 4-Core Touch Bar MacBook Pros for $150 off Apple’s MSRP. These are the same MacBook Pros sold by Apple in its retail and... Read more
B&H drops prices another $50 on clearance...
B&H Photo has dropped prices on clearance 2018 13″ MacBook Airs by a further $50 with models now available for $250 off Apple’s original MSRP. Overnight shipping, or expedited shipping, is free... Read more
Find the best sales & lowest prices on Ap...
Our Apple award-winning price trackers are the best place to look for the best sales and lowest prices on Apple gear. Scan our price trackers for the latest information on sales, bundles, and... Read more
Apple has clearance 2018 13″ MacBook Airs now...
Apple has Certified Refurbished 2018 13″ MacBook Airs available starting at only $849. Each MacBook features a new outer case, comes with a standard Apple one-year warranty, and is shipped free. The... Read more
Save $400 on the 8-Core iMac Pro today at Ama...
Amazon has the base 8-core iMac Pro on sale today for $4599 including free shipping. Their price is $400 off Apple’s MSRP, and it’s the currently lowest price available for an iMac Pro. For the... Read more
Flash sale! New 11″ 1TB WiFi iPad Pros for th...
Amazon has the 11″ 1TB WiFi iPad Pro on sale today for only $1199.99 including free shipping. Their price is $350 off Apple’s MSRP for this model, and it’s the lowest price ever for a 1TB 11″ iPad... Read more
Weekend Deal: 2018 13″ MacBook Airs starting...
B&H Photo has clearance 2018 13″ MacBook Airs available starting at only $999 with all models now available for $200 off Apple’s original MSRP. Overnight shipping, or expedited shipping, is free... Read more
Apple has clearance 10.5″ iPad Pros available...
Apple has Certified Refurbished 2017 10.5″ iPad Pros available starting at $469. An Apple one-year warranty is included with each iPad, outer shells are new, and shipping is free: – 64GB 10″ iPad Pro... Read more
Apple restocks refurbished iPad mini 4 models...
Apple has restocked Certified Refurbished 32GB iPad mini 4 WiFi models for $229 shipped. That’s $70 off original MSRP for the iPad mini 4. Space Gray, Silver, and Gold colors are available. Read more

Jobs Board

Best Buy *Apple* Computing Master - Best Bu...
**711495BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Store Associates **Location Number:** 000882-Waterfront-Store **Job Description:** The Read more
Best Buy *Apple* Computing Master - Best Bu...
**711597BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Sales **Location Number:** 000873-Colma CA-Store **Job Description:** **What does a Read more
Best Buy *Apple* Computing Master - Best Bu...
**711346BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Store Associates **Location Number:** 001095-Chesterfield-Store **Job Description:** Read more
Best Buy *Apple* Computing Master - Best Bu...
**704899BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Sales **Location Number:** 000135-Pleasant Hill-Store **Job Description:** **What does Read more
Best Buy *Apple* Computing Master - Best Bu...
**707083BR** **Job Title:** Best Buy Apple Computing Master **Job Category:** Sales **Location Number:** 000045-Rockford-Store **Job Description:** **What does a Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.