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

Dropbox 193.4.5594 - Cloud backup and sy...
Dropbox is a file hosting service that provides cloud storage, file synchronization, personal cloud, and client software. It is a modern workspace that allows you to get to all of your files, manage... Read more
Google Chrome 122.0.6261.57 - Modern and...
Google Chrome is a Web browser by Google, created to be a modern platform for Web pages and applications. It utilizes very fast loading of Web pages and has a V8 engine, which is a custom built... Read more
Skype 8.113.0.210 - Voice-over-internet...
Skype is a telecommunications app that provides HD video calls, instant messaging, calling to any phone number or landline, and Skype for Business for productive cooperation on the projects. This... Read more
Tor Browser 13.0.10 - Anonymize Web brow...
Using Tor Browser you can protect yourself against tracking, surveillance, and censorship. Tor was originally designed, implemented, and deployed as a third-generation onion-routing project of the U.... Read more
Deeper 3.0.4 - Enable hidden features in...
Deeper is a personalization utility for macOS which allows you to enable and disable the hidden functions of the Finder, Dock, QuickTime, Safari, iTunes, login window, Spotlight, and many of Apple's... Read more
OnyX 4.5.5 - Maintenance and optimizatio...
OnyX is a multifunction utility that you can use to verify the startup disk and the structure of its system files, to run miscellaneous maintenance and cleaning tasks, to configure parameters in the... Read more
Hopper Disassembler 5.14.1 - Binary disa...
Hopper Disassembler is a binary disassembler, decompiler, and debugger for 32- and 64-bit executables. It will let you disassemble any binary you want, and provide you all the information about its... Read more
WhatsApp 24.3.78 - Desktop client for Wh...
WhatsApp is the desktop client for WhatsApp Messenger, a cross-platform mobile messaging app which allows you to exchange messages without having to pay for SMS. WhatsApp Messenger is available for... Read more
War Thunder 2.33.0.135 - Multiplayer war...
In War Thunder, aircraft, attack helicopters, ground forces and naval ships collaborate in realistic competitive battles. You can choose from over 1,500 vehicles and an extensive variety of combat... Read more
Iridient Developer 4.2 - Powerful image-...
Iridient Developer (was RAW Developer) is a powerful image-conversion application designed specifically for OS X. Iridient Developer gives advanced photographers total control over every aspect of... Read more

Latest Forum Discussions

See All

Gorgeous Tactical Puzzle Game ‘Howl’ is...
Following its release on PC and Nintendo Switch this past November, and it’s arrival on Xbox and PlayStation back in January, publisher Astragon Entertainment and developer Mi’pu’mi Games are now bringing their super stylish tactical puzzler Howl to... | Read more »
Best iPhone Game Updates: ‘Shoot the Moo...
Hello everyone, and welcome to the week! It’s time once again for our look back at the noteworthy updates of the last seven days. It feels like a bit of a dry spell this week, at least in terms of really interesting updates. I mean, I found some... | Read more »
Celebrate Phobies spooky second annivers...
Get ready to have that classic song stuck in your head, as Phobies celebrates its second anniversary with the release of its latest update; Birthday Bash, Monster Mash. Starting March 5th and lasting for four weeks, it will be a month of... | Read more »
‘Dissidia Final Fantasy Opera Omnia’ Sto...
Square Enix finally shut down Dissidia Final Fantasy Opera Omnia (Free) on iOS and Android last week following the end of service announcement back in November last year. Following the game shutting down, Square Enix | Read more »
‘Monster Hunter Now’ Is Celebrating the...
Niantic and Capcom have begun celebrating the 20th anniversary of Capcom’s best franchise from today inside Monster Hunter Now (Free) on iOS and Android for a limited time. | Read more »
New ‘Warframe Mobile’ Update Adds 60fps...
Warframe Mobile (Free) launched worldwide on iOS just under two weeks ago. I’ve been playing it for review across multiple iOS devices, but have also been picking it up on Steam Deck and Switch to compare. Right from launch, I was impressed with... | Read more »
Passionate About Fidget Toys – The Touch...
In this week’s episode of The TouchArcade Show we kick things off with some passionate discussion about… fidget toys? For some reason? We quickly change gears to talk about the card-based rogulike Balatro, which we’ve both been playing and enjoying... | Read more »
TouchArcade Game of the Week: ‘Flying Ta...
For me Hexage is one of those developers that harkens back to the early days of the App Store and really the beginnings of iPhone gaming. I have spent many collective hours playing the likes of Totemo, Radiant, Radiant Defense, EVAC, Reaper… the... | Read more »
SwitchArcade Round-Up: ‘Ufouria 2: The S...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for March 1st, 2024. In today’s article, we’re looking at the remaining releases of the week. There are a few really good ones today, but the bin bunch certainly isn’t going home hungry... | Read more »
Steam Deck Weekly: Reviews of PowerWash...
Welcome to the first Steam Deck Weekly of March and this week’s edition is bigger than usual. I was a bit unwell last week and had to push some reviews to this week. Alongside that, there have been many notable announcements, releases, and new Steam... | Read more »

Price Scanner via MacPrices.net

Apple discontinues 15-inch M2 MacBook Airs, d...
With today’s introduction of new M3-powered 15″ MacBook Airs, Apple has dropped prices on clearance, Certified Refurbished, 15″ M2 MacBook Airs to a new low of $1019. These are the cheapest 15″... Read more
Price Drop! 13-inch M2 MacBook Airs at Apple...
Apple has dropped prices on Certified Refurbished 13″ M2 MacBook Airs to a new low of $849. These are the cheapest M2-powered MacBooks for sale at Apple. Apple’s one-year warranty is included,... Read more
Apple finally discontinues the 13-inch M1 Mac...
With the introduction of M3-powered 13″ MacBook Airs today, Apple has dropped prices on clearance 13″ M1 MacBook Airs, Certified Refurbished, to $759 for 8-Core CPU/7-Core GPU/256GB models and $929... Read more
Updated Apple iPad Price Trackers
Our Apple award-winning iPad Price Trackers are the best place to find the latest information on iPad sales and deals. We track prices from 20+ Apple retailers, including Apple, Amazon, Best Buy,... Read more
Updated Apple MacBook Price Trackers
Our Apple award-winning MacBook Price Trackers are continually updated with the latest information on prices, bundles, and availability for 16″, 14″, and (recently-discontinued) 13″ MacBook Pros... Read more
Mac Studios with Apple M2 Max and M2 Ultra CP...
B&H Photo has the standard-configuration Mac Studio model with Apple’s M2 Ultra CPU in stock today and on sale for $300 off MSRP, now $3699 (24-Core CPU and 64GB RAM/1TB SSD). B&H Photo has... Read more
Extended: Switch to Verizon and get the Apple...
Verizon has the iPhone 15 on sale for $0 per month when you add a new line if service. Discount is applied to your account monthly over a 36 month term and is valid for the 128GB model. For the first... Read more
Select 16-inch M3 Pro and M3 Max MacBook Pros...
B&H Photo has select 16-inch M3 Pro and M3 Max MacBook Pros on sale for $250 off MSRP. Their prices are the lowest currently available for these configurations. Free 1-2 day shipping is available... Read more
27-inch Apple Studio Displays on sale for $10...
B&H Photo has 27″ Apple Studio Displays on sale for $100 off MSRP today. Free 1-2 day shipping is available to most US addresses: – Studio Display (Standard glass, Tilt stand): $1499 $100 off... Read more
iPad Airs return to their all-time lowest pri...
Best Buy has 10.9″ M1 WiFi iPad Airs on record-low sale prices again for $150 off Apple’s MSRP, starting at $449. Sale prices for online orders only, in-store price may vary. Order online, and choose... Read more

Jobs Board

Teller Part Time *Apple* Valley MN *Apple*...
…is not eligible for Visa sponsorship **Posting Location:** + 15574 Pilot Knod Road Apple Valley, MN 55124 @RWF22 **Posting End Date:** Job posting may come down Read more
*Apple* End User Support Specialist - North...
…that they are performed. + Responsible for support of all College owned Apple computers, mobile ios devices, and peripherals, and for diagnosing and resolving Read more
Omnichannel Associate - *Apple* Blossom Mal...
Omnichannel Associate - Apple Blossom Mall Location:Winchester, VA, United States (https://jobs.jcp.com/jobs/location/191170/winchester-va-united-states) - Apple Read more
Teller Part Time *Apple* Valley MN *Apple*...
…is not eligible for Visa sponsorship **Posting Location:** + 15574 Pilot Knod Road Apple Valley, MN 55124 @RWF22 **Posting End Date:** Job posting may come down Read more
Nurse Anesthetist - *Apple* Hill Surgery Ce...
Nurse Anesthetist - Apple Hill Surgery Center WellSpan Medical Group, York, PA | Advanced Practice Providers | Certified Registered Nurse Anesthetists | FTE: 1 | Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.