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

TunnelBear 3.0.15 - Subscription-based p...
TunnelBear is a subscription-based virtual private network (VPN) service and companion app, enabling you to browse the internet privately and securely. Features Browse privately - Secure your data... Read more
Carbon Copy Cloner 4.1.16 - Easy-to-use...
Carbon Copy Cloner backups are better than ordinary backups. Suppose the unthinkable happens while you're under deadline to finish a project: your Mac is unresponsive and all you hear is an ominous,... Read more
Day One 2.3 - Maintain a daily journal.
Day One is the easiest and best-looking way to use a journal / diary / text-logging application for the Mac. Day One is well designed and extremely focused to encourage you to write more through... Read more
Sketch 45 - Design app for UX/UI for iOS...
Sketch is an innovative and fresh look at vector drawing. Its intentionally minimalist design is based upon a drawing space of unlimited size and layers, free of palettes, panels, menus, windows, and... Read more
NeoFinder 7.1 - Catalog your external me...
NeoFinder (formerly CDFinder) rapidly organizes your data, either on external or internal disks, or any other volumes. It catalogs all your data, so you stay in control of your data archive or disk... Read more
Day One 2.3 - Maintain a daily journal.
Day One is the easiest and best-looking way to use a journal / diary / text-logging application for the Mac. Day One is well designed and extremely focused to encourage you to write more through... Read more
Carbon Copy Cloner 4.1.16 - Easy-to-use...
Carbon Copy Cloner backups are better than ordinary backups. Suppose the unthinkable happens while you're under deadline to finish a project: your Mac is unresponsive and all you hear is an ominous,... Read more
Sketch 45 - Design app for UX/UI for iOS...
Sketch is an innovative and fresh look at vector drawing. Its intentionally minimalist design is based upon a drawing space of unlimited size and layers, free of palettes, panels, menus, windows, and... Read more
NeoFinder 7.1 - Catalog your external me...
NeoFinder (formerly CDFinder) rapidly organizes your data, either on external or internal disks, or any other volumes. It catalogs all your data, so you stay in control of your data archive or disk... Read more
TunnelBear 3.0.15 - Subscription-based p...
TunnelBear is a subscription-based virtual private network (VPN) service and companion app, enabling you to browse the internet privately and securely. Features Browse privately - Secure your data... Read more

Latest Forum Discussions

See All

Kalimba™ (Games)
Kalimba™ 1.0 Device: iOS Universal Category: Games Price: $3.99, Version: 1.0 (iTunes) Description: ** BAFTA-nominated ‘Best New Property’ 2015 ** Jump, slide and fly your way through a beautiful, mind-bending world in this award-... | Read more »
All That Remains: Part 1 (Games)
All That Remains: Part 1 1.0 Device: iOS Universal Category: Games Price: $.99, Version: 1.0 (iTunes) Description: “Duncan Price is paranoid” they used to say. He’s just a “local nut” they said. They used to say a lot of things about... | Read more »
Super Samurai Rampage (Games)
Super Samurai Rampage 1.0 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0 (iTunes) Description: Super Samurai Rampage is a bloody, retro pixel style high score chaser. You play as a legendary Samurai warrior provoked... | Read more »
You can now travel to Skyrim in The Elde...
The Elder Scrolls: Legends' new expansion has opened up Skyrim's craggy mountains and snowy plains for exploration today. Heroes of Skyrim is out now, adding a bunch of new Skyrim content to Bethesda's recent CCG. [Read more] | Read more »
High-stakes solitaire game 'Missile...
Missile Command and Solitaire might seem like an odd couple, but indie developer Nathan Meunier has brought them together to create his first game, Missile Cards, which launched on the App Store today. [Read more] | Read more »
Eos 2 (Music)
Eos 2 2.0.2 Device: iOS Universal Category: Music Price: $5.99, Version: 2.0.2 (iTunes) Description: | Read more »
Supercell celebrates Hay Day's comm...
Before there was Clash Royale or Clash of Clans, there was Hay Day. Now, Supercell's first game is celebrating its fifth anniversary, and the developer is commemorating the event with this touching new video. Supercell picked one long-running Hay... | Read more »
Dive into epic summer adventure with Oce...
Summer may be the best time to enjoy ocean adventures, and now you don’t even have to leave the comfort of your own home, thanks to the folks at Joycity, creators of Oceans & Empires. The old-timey naval MMO is getting a sizable new June Grand... | Read more »
Missile Cards (Games)
Missile Cards 1.0.9 Device: iOS Universal Category: Games Price: $2.99, Version: 1.0.9 (iTunes) Description: "Missile Command meets Solitaire...only with more doomlasers, death, and explosions." | Read more »
Collect mini assassins in 'Assassin...
Assassin's Creed is traveling back in time to the Spanish Inquisition for its latest mobile entry, Assassin's Creed Rebellion. The game is giving the series a look that's a huge departure from its past design, recreating classic characters in a... | Read more »

Price Scanner via MacPrices.net

Will iPad Running iOS 11 Be The ‘Ute’ Of The...
Steve Jobs’ analogy comparing iPads and PCs to cars and trucks respectively is seven years old but still stimulates discussion and debate. Appearing on an All Things D panel in 2010 shortly after the... Read more
Free CarePassport App gives Patients control...
Boston based CarePassport is on a mission to enable patients to take control of their medical records by allowing patients to aggregate, store, share and manage all their medical data including... Read more
Western Digital Launches New My Passport Ultr...
Western Digital Corporation has expanded its WD brand My Passport portable drive line with the redesigned My Passport Ultra drive. In addition to a new metallic look, the drive offers intuitive WD... Read more
Clearance 2016 13-inch MacBook Pros available...
B&H Photo has clearance 2016 13″ MacBook Pros in stock today for up to $210 off original MSRP. Shipping is free, and B&H charges NY & NJ sales tax only: - 13″ 2.9GHz/512GB Touch Bar... Read more
Apple Releases iOS 11 Public Beta; How To Get...
The official release of Apple’s latest mobile operating system iOS 11 is vaguely slated for the fall, but as of June 26, ordinary users can download an iOS 11 public beta. To download the iOS 11... Read more
Extend Life of MacBook Pro Retina 2.0TB With...
MacSales.com/Other World Computing has announced availability of the new OWC 2.0TB Aura Pro Solid State Drive for mid-2012 to early 2013 Apple MacBook Pro with Retina display. One of the fastest... Read more
BBEdit SummerFest 2017 Discount Ends Friday,...
You can get 20% off BBEdit for a limited time in Bare Bones Software’s http://www.eastgate.com/Tinderbox/Specials/SummerFest.html?mc_cid=f2101ca260&mc_eid=[UNIQID]SummerFest 2017 sale and... Read more
Use Apple’s Education discount to save up to...
Purchase a new Mac using Apple’s Education discount, and take up to $300 off MSRP. All teachers, students, and staff of any educational institution qualify for the discount. Shipping is free: - 15″ 2... Read more
Clearance 27-inch 3.3GHz 5K iMac available fo...
Amazon clearance 27″ 3.3GHz 5K iMacs (MK482LL/A) available for $1799.90 including free shipping. Their price is $500 off original MSRP, and it’s the lowest price available for this model from any... Read more
13-inch 1.8GHz/256GB MacBook Air on sale for...
B&H Photo has the updated 2017 13″ 1.8GHz/256GB MacBook Air (MQD42LL/A) in stock and on sale for $1129 including free shipping plus NY & NJ tax only. Their price is $70 off MSRP. Read more

Jobs Board

*Apple* Online Store WW Customer Insights -...
…with data mining tools: R, SAS, etc.Experience with common shell scripting tools: unix, python, apple script, Swift etc. Apple Online is one of the largest and Read more
Engineering Project Manager, *Apple* Online...
…the electronic commerce (eCommerce) systems and solutions that enable and support the Apple Online Store (AOS) - one of world's largest online retail businesses, Read more
*Apple* News Product Marketing Mgr., Publish...
…organizational consensus on strategy and vision for publisher tools, authoring, and Apple News Format.Carries this strategy and vision across the organization to Read more
*Apple* Retail - Multiple Positions - Apple,...
Job Description: Sales Specialist - Retail Customer Service and Sales Transform Apple Store visitors into loyal Apple customers. When customers enter the store, Read more
Security Data Analyst - *Apple* Information...
…data sources need to be collected to allow Information Security to better protect Apple employees and customers from a wide range of threats.Act as the subject matter Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.