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

Remotix 6.1.4 - Access all your computer...
Remotix is a fast and powerful application to easily access multiple Macs (and PCs) from your own Mac. Features: Complete Apple Screen Sharing support - including Mac OS X login, clipboard... Read more
MacFamilyTree 9.0.12 - Create and explor...
MacFamilyTree gives genealogy a facelift: modern, interactive, convenient and fast. Explore your family tree and your family history in a way generations of chroniclers before you would have loved.... Read more
ffWorks 2.0.4 - Convert multimedia files...
ffWorks, focused on simplicity, brings a fresh approach to the use of FFmpeg, allowing you to create ultra-high-quality movies without the need to write a single line of code on the command-line.... Read more
Apple Pages 10.0 - Apple's word pro...
Apple Pages is a powerful word processor that gives you everything you need to create documents that look beautiful. And read beautifully. It lets you work seamlessly between Mac and iOS devices, and... Read more
Numbers 10.0 - Apple's spreadsheet...
With Apple Numbers, sophisticated spreadsheets are just the start. The whole sheet is your canvas. Just add dramatic interactive charts, tables, and images that paint a revealing picture of your data... Read more
Keynote 10.0 - Apple's presentation...
Easily create gorgeous presentations with the all-new Keynote, featuring powerful yet easy-to-use tools and dazzling effects that will make you a very hard act to follow. The Theme Chooser lets you... Read more
Carbon Copy Cloner 5.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
PDFpenPro 11.2.2 - Advanced PDF toolkit...
PDFpenPro allows users to edit PDF's easily. Add text, images and signatures. Fill out PDF forms. Merge or split PDF documents. Reorder and delete pages. Create fillable forms and tables of content... Read more
PDFpen 11.2.2 - Edit and annotate PDFs w...
PDFpen allows users to easily edit PDF's. Add text, images and signatures. Fill out PDF forms. Merge or split PDF documents. Reorder and delete pages. Even correct text and edit graphics! Features... Read more
ScreenFlow 9.0.3 - Create screen recordi...
ScreenFlow is powerful, easy-to-use screencasting software for the Mac. With ScreenFlow you can record the contents of your entire monitor while also capturing your video camera, microphone and your... Read more

Latest Forum Discussions

See All

Same Room Games is a great collection of...
Staying at home under lockdown kinda sucks. Totally necessary, yes, but also more than a little anxiety-inducing and often boring. Thankfully, we've got lots of great multiplayer games to play with friends and keep us sane, the latest of which,... | Read more »
Bark Park is a joyfully silly multiplaye...
Bark Park is a charming, good-humoured game about competing against other players to claim as much territory as possible by peeing on nearby trees. Did I mention that everyone is playing as a dog? Makes things a little less weird. [Read more] | Read more »
The 5 Best Games Like Grindstone
Apple Arcade made a splash when it first launched, granting access to over 50 games as soon as it became available. Of those titles, the one that seemed to grab most people’s attention was Grindstone, a matching puzzle game from Capy Games. [Read... | Read more »
Awarding-winning puzzler Make One is now...
Make One is a game with a simple concept that it executes with flair and impressive levels of polish. It comes to us from developer Brazle and publisher PiG, and it's just landed this week for Android. At its core, Make One is a logic-based... | Read more »
Marvel Contest of Champions' latest...
Kabam's Marvel Contest of Champions has now teased some of the latest characters heading to the Battlerealm next month. Here's your chance to watch the new motion comic embedded below, Barons' War, before I spoil the surprise. [Read more] | Read more »
Why Calibria: Crystal Guardians is the p...
We could all use escapism right now, something to focus on and a project to feel part of during these times of social isolation. If you’re one of the many who’s turned to gaming to fill the lockdown void, Calibria: Crystal Guardians should be on... | Read more »
Mini Tank: Fire is a Worms-like, turn-ba...
Mini Tank: Fire is an online multiplayer game that takes a few cues from the likes of Worms and Shell Shock Live. It will feature 5v5 turn-based battles and is available now for both iOS and Android as a free-to-play game. [Read more] | Read more »
Poly is an endless runner for iOS where...
Poly is an endless runner from solo developer Denis Kakacka that's available now for iOS as a free-to-play game. It aims to bring a slight twist to the standard running formula by mixing up what you're actually controlling. [Read more] | Read more »
Reflex Unit 2 brings intense PvP battles...
Reflex Unit 2 will see players engaging in mech-based warfare, fending off waves and waves of autonomous robots that are threatening to destroy the planet. It's available now for iOS and Android. | Read more »
Snake Core is a modern take on a mobile...
Snake Core, from acclaimed indie developer Orangepixel, is a modern take a mobile classic: Snake. The idea is that you'll command a small army of troops as they go about attempting to take down a horde of invading aliens. [Read more] | Read more »

Price Scanner via MacPrices.net

Apple offers Certified Refurbished iPhone XS...
Apple has Certified Refurbished iPhone XS models available for up to $350 off MSRP, with prices starting at $699. Each iPhone is unlocked and comes with Apple’s standard one-year warranty and a new... Read more
Siri, Apple’s AI-Powered Virtual Assistant, N...
FEATURE: 04.01.20- Apple’s virtual assistant powered by AI (artificial intelligence), Siri, can answer almost any question you throw at it, from asking for today’s weather forecast to where the... Read more
Buy Apple’s 16″ 2.6GHz 6-Core MacBook Pro for...
Apple reseller DataVision has new 16″ 2.6GHz 6-Core MacBook Pros on sale for up to $170 off MSRP, each including free shipping. DataVision charges sales tax for NY, NJ, PA, and CA residents only. In... Read more
Boost Mobile 1-day Flash Sale: $100 off all A...
Boost Mobile is offering Apple’s 2019 iPhone 11 and 11 Pro models for $100 off MSRP. Boost is also offering the same $100 discount on new previous-generation iPhone XS, XR, X, 8, and 7 models. For... Read more
In stock! Apple’s new Mac Pro for $5995 with...
Apple reseller DataVision has the new 2019 Apple Mac Pro in stock today for $5995 ($5 off MSRP) including free shipping. DataVision charges sales tax for NY, NJ, PA, and CA residents only. If you don... Read more
Apple Sources Confirm Spring Media Event Canc...
NEWES: 03.31.20- A previously planned Apple media event scheduled to take place sometime this Spring that was rumored and widely expected to be held today, March 31, were confirmed by anonymous... Read more
Apple restocks 2017 13″ Dual-Core non-Touch B...
Apple has restocked a limited number of Certified Refurbished 2017 13″ 2.3GHz Dual-Core non-Touch Bar MacBook Pros starting at $1019. An standard Apple one-year warranty is included with each model,... Read more
Save $300 on the 13″ 2.4GHz MacBook Pro with...
Apple has a full line of Certified Refurbished 2019 13″ 2.4GHz 4-Core Touch Bar MacBook Pros available starting at $1529 and up to $300 off MSRP. Apple’s one-year warranty is included, shipping is... Read more
Just in! Apple’s new 2020 4-Core Mac mini $10...
B&H Photo has Apple’s new 2020 4-Core Mac mini in stock and on sale today for only $699 including free overnight delivery for many US addresses. Their price is $100 off MSRP, and it’s the lowest... Read more
New 2020 13″ Space Gray MacBook Airs on sale...
B&H Photo has Apple’s new 2020 13″ Space Gray MacBook Airs on sale today for $50 off Apple’s MSRP. While they don’t have stock today, preorders are accepted at this discount. Overnight shipping... Read more

Jobs Board

Sephora Merchandise/Operations Supervisor -...
Sephora Merchandise/Operations Supervisor - Apple Blossom Mall Location:Winchester, VA, United States- Apple Blossom Mall 1850 Apple Blossom Dr Job Read more
Sales Supervisor - *Apple* Blossom Mall - J...
Sales Supervisor - Apple Blossom Mall Location:Winchester, VA, United States- Apple Blossom Mall 1850 Apple Blossom Dr Job ID:1083621Store Hourly Positions Read more
*Apple* Mac Product Engineer - Barclays (Uni...
Apple Mac Engineer Whippany, NJ Support the development and delivery of solutions, products, and capabilities into the Barclays environment working across technical Read more
Essbase Developer - *Apple* - Theorem, LLC...
Job Summary Apple is seeking an experienced, detail-minded Essbase developer to join our worldwide business development and strategy team. If you are someone who Read more
Senior Software Engineer @ *Apple* - Theore...
Job Summary Apple is looking for a seasoned senior software engineer to join our worldwide business development and strategy team. This is an opportunity to lead a Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.