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

Latest Forum Discussions

See All

How to evolve Eevee in Pokemon GO
By now, almost everyone should be hip to how to evolve Pokemon in Pokemon GO (and if not, there's a guide for that). Just gather enough candy of the appropriate type, feed them all to the Pokemon, and evolution happens. It's a miracle that would... | Read more »
CSR Racing 2: Guide to all game modes
It might not seem like there are all that many ways to go fast in a straight line, but CSR Racing 2 begs to differ. [Read more] | Read more »
Bulb Boy (Games)
Bulb Boy 1.0 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0 (iTunes) Description: Multi-award winning 2D point & click horror adventure about a boy with a glowing head. | Read more »
5 top free emoji keyboard apps
If we're not at peak emoji yet as a society, it feels like we definitely should be. The emoji concept has gone far beyond what anyone in Japan could have envisioned when the people there unleashed it on an unsuspecting world, but the West has... | Read more »
How to unlock more characters in Disney...
One of the big charms of Disney Emoji Blitz is seeing a wide variety of beloved Disney and Pixar characters transformed into smiling emojis. Even someone like the sneaky Randall from Monsters Inc., who probably never cracked a smile on film, is... | Read more »
Cubway (Games)
Cubway 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Cubway is a journey with an abstract story of lifecycle of rebirth, called Samsara. Guide the cube through the long way full of dangers... | Read more »
Colorcube (Games)
Colorcube 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Turn pieces and blend colours in this minimal yet visually stunning puzzler.Over 200 handcrafted and challenging levels. Features... | Read more »
Doodle God Griddlers (Games)
Doodle God Griddlers 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: | Read more »
Crusader Kings: Chronicles (Games)
Crusader Kings: Chronicles 1.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0 (iTunes) Description: Crusader Kings: Chronicles is an interactive text based game that puts you in the shoes of Guy de Rose as you make... | Read more »
Roads of Rome: New Generation (Games)
Roads of Rome: New Generation 1.0 Device: iOS Universal Category: Games Price: $5.99, Version: 1.0 (iTunes) Description: | Read more »

Price Scanner via MacPrices.net

Charitybuzz Set to Auction Unique Apple-1 Com...
Offering an opportunity to own the computer that sparked a revolution, on Monday, July 25, leading online charity auction platform Charitybuzz will auction what is claimed to be the world’s most... Read more
MacBook Airs on sale for up to $150 off MSRP
Amazon has 11″ and 13″ MacBook Airs on sale for up to $150 off MSRP for a limited time. Shipping is free: - 13″ 1.6GHz/128GB MacBook Air (sku MMGF2LL/A): $899.99 $100 off MSRP - 13″ 1.6GHz/256GB... Read more
Apple refurbished 13-inch Retina MacBook Pros...
Apple has Certified Refurbished 13″ Retina MacBook Pros available for up to $270 off the cost of new models. An Apple one-year warranty is included with each model, and shipping is free: - 13″ 2.7GHz... Read more
Apple refurbished 11-inch MacBook Airs availa...
Apple has Certified Refurbished 11″ MacBook Airs (the latest models), available for up to $170 off the cost of new models. An Apple one-year warranty is included with each MacBook, and shipping is... Read more
Apple iPad Pro Sales Far Outpacing Microsoft...
A report on Appleinsider notes that despite Microsoft Surface tablet PC sales growing by 9 percent year over year, revenues remained below $1 billion, and are down sequentially from the $1.1 billion... Read more
DEVONthink 2.9 Features Ultra-fast, Robust, A...
DEVONthink 2.9 allows users to keep databases synchronized using many means of transport. It transmits them between Macs on the local network or stores them in a syncable form on removable hard... Read more
12-inch WiFi Apple iPad Pros on sale for up t...
B&H Photo has 12″ WiFi iPad Pros on sale for up to $100 off MSRP, each including free shipping. B&H charges sales tax in NY only: - 12″ Space Gray 32GB WiFi iPad Pro: $749 $50 off MSRP - 12″... Read more
Apple refurbished 15-inch Retina MacBook Pros...
Apple has Certified Refurbished 2015 15″ Retina MacBook Pros available for up to $380 off the cost of new models. An Apple one-year warranty is included with each model, and shipping is free: - 15″ 2... Read more
Apple refurbished Mac minis available for up...
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
Apple Mac Sales Finally Colliding With ‘Innov...
After successfully swimming against a tide of diminishing PC sales through 2014 and 2015, it appears that Apple has finally stopped defying gravity and is running up against the unwritten “innovate... Read more

Jobs Board

*Apple* Solutions Consultant - APPLE (United...
Job Summary As an Apple Solutions Consultant, you'll be the link between our future customers and our products. You'll showcase your entrepreneurial spirit as you Read more
*Apple* Professional Learning Specialist - A...
Job Summary The Apple Professional Learning Specialist is a full-time position for one year with Apple in the Phoenix, AZ area. This position requires a high Read more
*Apple* Picker - Apple Hill Orchard (United...
Apple Hill Orchard, Co. Rte. 21,Whitehall, NY 9/7/16-10/228/16. Pick fresh market or processing apples Productivity of 60 boxes and 80 boxes processing fruit per Read more
*Apple* Solutions Consultant - APPLE (United...
Job Summary As an Apple Solutions Consultant, you'll be the link between our future customers and our products. You'll showcase your entrepreneurial spirit as you Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description:SalesSpecialist - Retail Customer Service and SalesTransform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.