VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources] نسخه متنی

Field Design Tactics

The final step before moving to FileMaker and creating your database files is determining the fine points of your fields and relationships. A good strategy can be undermined by errors in tactical detailsthose important decisions that lead to a FileMaker implementation that's clear, flexible, and scalable.

Identifying key fields

Not all data is created equal. Certain types of information will be needed almost everywhere, whereas other types will rarely appear. Column headings that appear in multiple spreadsheet tables are good candidates for database key fields.

For example, in our imagined university database, a student's name or ID number will appear in many places: on the course roster, on the student's grade listing at the end of the term, and on the bursar's office listing of course payments. On the other hand, the student's phone number, although important in some situations, will hardly ever be needed outside the Student file listing itself. The name or ID number are good candidates for use as key fields; the phone number, which could easily change and is less useful, would not make a good key field. In your own database, you should always look for the most stable data to use as an identifier.

Choosing unique key fields

In our example, notice that both the students' names and their ID numbers appear on multiple forms. They aren't, however, equally good candidates for use as a key field. For one thing, which part of the student name would you use? You certainly couldn't use the last name alone. Even first and last names are likely to be repeated.

Adding a middle initial can help, but any large college or university will have some duplications even using all three pieces. So, despite the constant recurrence of this information, it's not unique. The ID number, on the other hand, although not the first column in any spreadsheet list, is the only portion of the student data that can't be duplicated by any other student record. We use the same reasoning to choose our unique key fields for the other files in our database (Figure 2.5).

Storing global information

Most database fields have different information for every record. On the other hand, fields that are set with a global option that is always enabled contain the same information no matter which record or layout you are viewing within a table. As you will see in Chapter 3, "Layout Enhancements," storing data globally is the best way to insert universal container data, like form letter text and graphic elements (logos, signatures, or special symbols). Global storage is also one way to hold information needed for data variables in calculations and scripts. As you examine your flowcharts, look for unchanging data elements that you can store as global data.

In our college example, the school logo or boilerplate affirmative action disclaimer text would do well stored with a global option. If you plan to create scripts that will require a loop counter (see Chapter 8, "Working with Conditional Script Steps," for more on using a loop counter), a global treatment would serve this purpose nicely.

Tip

Even if you don't have an immediate need to reuse the contents of a field, you can create one (or more) empty fields of different typestext, number, or containerand hold them in reserve. Empty fields with global settings may save both time and inconvenience later, since FileMaker doesn't allow you to easily create new fields when multiple users are actively accessing a file.

Using repeating fields

Generally speaking, you shouldn't repeat fields. Whenever possible, build your database by creating relationships between files, and list your multiple entries in a portal. Repeating fields in FileMaker is an old practice used when the program was a flat file database, not a relational one. Because repeating fields can contain multiple entries in the same field, they are fairly inflexible, shouldn't be used in a relationship, and don't work well in calculations.

In Chapter 6, we examine some of the few instances when a repeating field can be useful.

Tip

One of the first details you should notice in your field list is that some fields may appear in more than one place. If you are using separate files (rather than tables in a single database), once you've built the skeleton for one database (the simpler one, like the Faculty table in our example), you can duplicate it, rename it, and update the field names for other similar tables (like the Student table in our example). Then you can continue by adding the extra fields and relationships the additional table requires.

Tracking fields

Depending on how active your database is and how many people use it, you might want to create fields that track the date and time changes were made to it. Creation and modification fields (created with Auto-Enter options), although not part of the data structure per se, can be invaluable tools for tracking records, as can a field that automatically enters the name of the database user who input or modified the record. In your courses and classes database, for example, you might have a kiosk-type setup with your database set to Entry Only so students could register themselves for classes. By time-and date-stamping these entries, it would be easy to determine which students should be given precedence for a seat in a class with limited enrollment.