One of FileMaker Pro’s strengths is that it allows for organic design: It’s easy to modify FileMaker Pro databases, and they tend to grow and change over time as their users’ needs change. On the flip side, this flexibility can be frustrating: a simple database will often have to be reworked to allow for additions or expansions.
Returning to a database after a long hiatus often starts the maddening process of having to become familiar with the database all over again. You’ll forget which scripts do what, why you created a field named “rt077,” and whether or not your boss should have access to your Admin layout. After a few cycles of design over time, some databases become inscrutable patchwork quilts of scripts, fields, relationships, and layouts.
These challenges will confront everyone but those using the simplest of databases. By practicing good database design habits, you’ll be able to work with more advanced designers, keep track of your work, and save large chunks of time. Follow these ten tips, and you’ll be better able to keep up with your database’s needs.
Tip 1: Use ID Numbers
The first thing you should do when creating any new database — even one you’re dead certain will only have two fields and no scripts — is create an identification field. To do this, define a number field, and in the Options, set an auto-entry serial number.
This ID number field will allow you to keep track of individual records. If later you export data, set up relationships between databases, or simply try to find a specific record, this ID number will be invaluable. You may never use it, but on the day that you need it, it may save your bacon.
Some FileMaker Pro designers like to keep ID numbers at a fixed number of digits. Most designers and users are comfortable working with ID numbers of about six digits.
Another important thing to remember about ID numbers is that you should always,
use numbers, not text, to establish relationships between databases. Spelling errors and typos are all too common in regular text.
Tip 2: Include User and Date Info
By keeping track of who enters data and when, you’ll be better able to correct mistakes and — if need be — isolate problems. Also, if you ever need to import data into another system or migrate data into a new database, this information will prove invaluable.
To track this information, define two text fields and auto-enter Creator Name and Modifier Name. Do the same with Create and Modify dates. If you really want to cover your bases, add another set of fields for Create and Modify times.
These fields (especially the two time fields) may initially seem like overkill, but if you ever have to restore a database from a backup, or migrate data into or from a system with time-stamping, these fields will save your life. These are also fields that cannot be applied retroactively: from the first day of your database’s life, it’ll be able to tell you when and by whom a record was created and/or modified. These fields will add to your file size, but are a bit of necessary insurance for the future.
Tip 3: Build a Template Database
Creating new databases can be tedious: building ID fields, setting up user interfaces, and defining standard tracking fields over and over again gets old.
To save time, make your life simple: Build a single database to use as a template. Add an ID field, define the Create and Modify tracking fields, and build your favorite interface with Detail and List View layouts. Make sure everything is perfect, complete with layout colors and security options. Then save the file somewhere on your hard drive and set it as stationery. (To do this, highlight the file icon, choose Get Info from the Finder’s File menu, and check the Stationery Pad box.) Every time you double-click on your file — voila! — you’ll be hours into the project.
Tip 4: Think Ahead about Security
Security in FileMaker Pro gets complicated, especially as you start working with multiple, relational databases. You’ll need to modify user and group settings for each database in your system. Some complex FileMaker Pro systems can include well over a dozen databases.
Save yourself a great deal of time and mousing: anticipate your user’s security needs and set up passwords and groups in your template file. For example, you may wish to simply have one password for using the database and another for editing its design. You’ll find these options listed under File: Access Privileges. Once established in your template file, these passwords will propagate across your system as you create duplicates of the file.
Tip 5: Use Naming Conventions
In six months, you’ll have no clue that StAds stands for
. Field, script and other names can quickly become confusing. If you try to pass your database on to a new designer, he or she will really have no clue what some names mean if you don’t label them clearly. To prevent this kind of confusion, adopt and stick to naming conventions.
First off, don’t abbreviate. FileMaker works perfectly well with long names. Err on the side of clarity with field names such as Invoice Number and Customer Name, and script names such as Go to Month Report Layout and Sort by Date.
Second, keep your fields in order by thinking alphabetically. FileMaker allows you to set a custom order of fields in the Define Fields dialog box, but it’s a sure bet that you’ll forget to manually position fields, or that someone else may not follow the same organization scheme. Use a low-tech solution and rely on the alphabet. Instead of First Name and Last Name as field labels, try Contact Name First and Contact Name Last. These will sort next to each other alphabetically and will allow anyone who can read to divine your organizational scheme. It will also allow for sorting if at some point your data is exported with its field labels.
Tip 6: Nest Your Scripts
One of the most difficult things to do when managing a FileMaker Pro database — or suite of related databases — is keeping track of which script does what. First, name your scripts using language that will be clear to you and your users. Beyond that, try to break complex, multi-step scripts into multiple smaller scripts. This will help on two fronts: you’ll be more able to follow the logic of a multi-step script, and you’ll also be able to reuse individual steps if need be.
Notice here the master script followed by smaller steps. Within the Compile Reports script, use the Perform Script command to trigger other, nested scripts.
This simple technique will allow you to keep one function to a script. Rather than building a script that runs a calculation, sorts some records, changes layouts, and updates a report, you can write four clear scripts that, by name, will be immediately comprehensible.
Tip 7: Use Dividing Lines
Ever notice the dividing lines in your application menus? (View your FileMaker File menu for examples of these lines.) This simple visual cue has helped keep users organized since the first Mac OS. Using a hyphen as a script name in the script menu will establish a menu divider. This will make building and using databases easier for you and your users.
Notice the difference between these two menus.
The one with the dividers is much easier to follow, and you can include more scripts without the menu looking too long or being too confusing for your users.
is a simple script command that will save a great deal of your brainpower for other things. You’ll find it near the bottom of your ScriptMaker commands. Use it as a script’s first command to describe what a given script does. This can be as easy as “This script sorts by user first name, last name, and middle initial” to something as detailed as explaining the logic of an if-then loop. I’ve found that it’s best to start a script with a single comment, rather than clutter the script itself.
Tip 9: Create a Documentation Layout
Documenting your work is important. Consider taking the time to create a documentation layout that explains every field in your database.
Next to each field, simply write an explanation of what that field does and why it’s there. As you get into the swing of designing, it’ll be easy to skip this step, but sticking with this habit will do wonders in keeping you organized. It’s also very considerate to include a documentation layout when passing your database on to someone else.
Tip 10: Think Ahead with File Names
FileMaker allows you to link more than one database together in a related system. Unfortunately, once you’ve related more than one file, it can easily get confused if you rename any of your files. You run the risk of breaking relationships if you rename files.
The easy answer is to not rename files. Think ahead with your file names. It’ll be helpful if you begin each related (child) database with a prefix to show that it belongs with another database.
This will also make it easier for people to recognize which database they need to launch across a network if you’re working in an office.
Most of these tips are geared toward saving time and keeping your database design process running smoothly. Establish these habits with every database you design, and you’ll find that even your Mom’s Recipes database can grow to become a related, fifty-file database used to support legions of chefs worldwide.
FileMaker Pro consultant SCOTT LOVE is still trying to live down the goofy interface he built for his first professional database more than a decade ago.