FileMaker Pro 5 Advanced for Windows and Macintosh: Visual QuickPro Guide

Creating a Data Structure Chart

In our college database example, we've progressed through information gathering and categorizing data into fields, and have assigned the fields to categories that will be separate files in the database. Now we need to figure out what relationships each file should have to each of the other files in the database. The best way to visualize the relationships is to create a series of charts that represent the individual categories of information, then determine the relationship these groups should have to each other.

We have several files that will have a variety of relationships. The main ones are Students, Faculty, Courses, and Classes:

• Students: Contains the data related to each individual student. Except for graderelated information, this data should remain relatively static once it is input.
• Courses: Contains the data related to each course offered. Course information changes infrequently, so this is also a fairly stable file.
• Faculty: Contains the data related to each member of the faculty. Faculty can be added or deleted but, like the student and course files, will not change on a regular basis.
• Classes: Contains a list of running courses, along with the list of students taking them and the instructor who teaches each course. This list changes every semester and requires a separate record for each class taken by every student.

Note that we have begun to divide our list into fields that will contain records in the individual databases and fields that will depend on lookups or relationships to other databases. All of our duplications are here, as well as those fields that are unique but depend on data from other files-like the students' grade point averages and their accumulated credits, which will be calculated from their class data (Figure 2.4). We can use these organized field charts as blueprints for creating our database fields.

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 details-those 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, while 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, while 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, we've noticed 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).

Tip

• If a persons Social Security number is available, it is an ideal key field since only one person can have that number. To minimize input errors, always make sure that you specify the Social Security field as number, rather than text Most database fields have differentInformation for every record. Global fields on the other hand, always contain the same information no matter which record or layout you are viewing. As you will see in Chapter 3, global fields are the best way to insert universal container data, like form letter text and graphic elements (logos, signitures, or special symbols). They're also the way to hold the information needed for data variables in calculations and scripts. As you examine your flowcharts, look for unchanging data elements that you can place into global fields.

In our college example the school logo or a boilerplate affirmative action text disclaimer would do well as global fields. If you plan to create scripts that will require a loop counter, a global field would serve this purpose nicely.

Tip

• Even if you don't have an immediate need for a global field, you can create one (or more) and hold it in reserve. This may save both time and inconvenience later, since FileMaker doesn't allow you to create new fields when multiple users are actively accessing a file. The same logic can apply to any types of fields, whether they be text, number ofrcontainer.

Using repeating fields

Generally speaking, you shouldn't. Whenever possible, build your database by creating relationships between files, and list your multiple entries in a portal. Repeating fields in FileMaker are leftovers from when it was a flat file database, not a relational one. Because repeating fields can contain multiple entries in the same field, they are fairly inflexible, car ft be individually searched or used in a relationship, and don't work well calculations. In Chapter 6, we examine some of the few instances when a repeating field can be useful.

Tip

• One of the first things that you should notice in your field list is that some categories are duplicated, sometimes in more than one place. Once you've built the skeleton for one database (the simpler one, like the Faculty database in our example), you can copy the file, rename it. and update the field names for other similar files (like the Student file in our example). Then you can continue by adding the extra fields and relationships the second file 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 to it were made. 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.

Implementing Relationships

When you created your flowchart of relationships, you outlined the most obvious places where the data in individual files would intersect. When it comes to implementing these relationships, you need to consider not just what the relationships will be, but how you want to express them in layouts and where you want them to join.

Thinking ahead about sorting and searching

When planning your database, you should be thinking about which pieces of information will be used to find certain records and which may be used to sort data. Then you can make sure that those pieces of data are in separate fields.

For example, you will probably want to sort and search using such criteria as Last Name (for alphabetical listings) or Zip (for mailings). That is why it is critical that these sets of data be in separate fields, and not combined with the first name or the city and state.

Another useful field is one listing the high school that a student graduated from, because you can generate reports that let you track them as alumni later These fields should be included from the outset, so that you don't have to go back and laboriously enter the data later.

Tip

• If you don't want your finds and sorts to take forever, look at your data relationships for likely search combinations before you break data into different files. Indexing is the key to a speedy search. Fields that are in portals can't be indexed, so FileMaker will take a lot longer to locate this related information.

Using key fields and relationships

Once the database are created. you need to establish the relatioships among them. In this cexample we noticed early on that the Class data contained very little unique information. Therefore It will have two main relationships: to the Student database (using the Student ID as the key field), and to the Course 1D. The bulk of the data found in the Class database will be a result of lookups from the other related files. (The link to the Faculty database is indirect. since the Instructor ID field in Courses can be used to connect faculty information to the Class database.)

The Student ID will look up the rest of the student information and copy it into the class roster. At the end of the class, grades and credits earned will flow back to the Student database so that you can list which courses a student has taken, and summarize how many credits have accumulated. When you enter the Course ID in the Class database, the Course ID relationship will copy the course information (name, description and credits) into the lookup fields (Figure 2.6).

Using lookups vs. relating data

By definition, building a relational database is based on the idea of using related data. Ordinarily, relating data is a much better strategy than relying on lookups, even though lookups are initially easier to create on the fly. One major problem with lookups is that they defeat one of the main purposes of a relational database, which is to avoid duplication of effort. Because you copy information from one file to another in a lookup, you freeze that data in the second file. Unless you update your lookup each time you change the original file (and we can hardly think of a worse waste of time), it's not long before the information is incomplete, flawed, and ultimately useless.

Despite these negatives, there are a few situations where you should plan to relate data using lookups. When a database entry needs to display related information that shouldn't be constantly updated, lookups are perfect. In our college example, once a course roster has been completed and the time period during which a student can drop or add a course elapses, data from the roster should become part of the student's historical information. That's why the Classes database is important: It combines selected student, course, and instructor information. When you create a new record in this database, the student ID number and course number are entered. By using a lookup from the Courses database to the Classes database for information like the instructor name and course credits, you have an archive from the time that the student took the course.

Testing Your Database

Now that you've divided your information into databases, figured out which fields belong in which files, and strategized the relationships and lookups, you re finally ready to create the FileMaker files themselves. Resist the temptation to think that your project is done. Once you have the architecture of the database set up, don't forget to test your decisions with some representative users before your structure is set in stone. You may discover that, although your structure makes perfect logical sense, it works at cross-purposes to some person's established workflow. Listen carefully to feedback, even it means reconsidering some aspect of your structure. No matter how experienced you are, you always have something to learn from the people who will work with your creation every day....