Defining Fields

As you know by now, every FileMaker database starts with a table. And since fields and tables are so fundamental to a database, FileMaker asks you to create them as soon as you start. FileMaker assumes your needs are pretty simple, and starts right off asking what fields you want in this single table.

1. Choose File New Database.
   Up comes the New Database dialog box, just like the one in Figure 1-4. This time you're not going to start with a template. Instead, you're starting with a clean slate, so you can build a database that's all your own. (Can't you just feel the excitement?)
2. Click "Create a new empty file," and then click OK.

   FileMaker asks you what to name your new database and where to put it.

3. Call this database People since that's what you're going to track.

   In the Save As box (Mac OS X) or the "File name" box (Windows), type People. Then click Save.

   FileMaker creates the new file and automatically adds the .fp7 file name extension to your database name.

---

Note: That's not a typo. The extension .fp7 from FileMaker Pro 7 is the same for version 8. Good thing, too: It means people who still have version 7 can use your version 8 files just fine.

---

Just as promised, FileMaker asks you what fields you wantyou see the Define Database dialog box shown in Figure 3-1. This little beauty is where you build and manage tables. There's a tab for the tables themselves, a tab for the fields in those tables, and a tab called Relationships, where you tell FileMaker how different tables work together. For now, you can simply ignore Tables and Relationships. Your one free table has already been created (it's called People, just like the database), and you're ready to give it some fields.

Figure 3-1. In the Define Database dialog box (File images/U2192.jpg border=0> Define images/U2192.jpg border=0> Database), the Fields tab is where you go to tell FileMaker about the fields in your database. The field list shows all the fields in the selected table.

Since you've already decided what fields you want, you need only to tell FileMaker. First, create a field to hold a person's first name:

1. In the Field Name box, type First Name.

   Every field has a nameyou can choose almost anything you want. (For some guidelines, see the box on Section 3.2.1.)

2. In the Type pop-up menu, make sure Text is selected.

   Every field has a type, which you'll learn about shortly. You're creating Text type fields right now.

3. In the Comment box, type the person's first name.

   Every field can have a comment to help you, the developer, remember what it's for. In this case, it's pretty obvious, so you could certainly leave the comment blank instead.

   ---

   Note: Field comments don't show up when someone's using the database. They only show in this window. If you need extra information on the screen for the user to see, you'll want to customize the layout as explained in Chapter 4.

   ---

4. Click Create.

   FileMaker creates the First Name field and adds it to the field list.

Now that you've created the first field, the rest are a snap. Following the same steps as above, create Text fields with the following names:

• Last Name
• Phone Number
• Street Address
• City
• State
• Zip
• Email Address

When you're finished, the field list should look like Figure 3-2. (See Section 3.3.2.1 to learn why your Zip field must be a text field.)

Figure 3-2. Once you've defined the necessary fields, the field list should look like this example. You may not see the field comments in the third column this column can show comments or options. To switch between these two possibilities, click the column header.

 

3.2.1. Changing a Field's Name

While you're here, you may as well try some more field-defining tricks. Say you want to change the name of the Zip field to Zip Code. It's very easy:

1. Highlight the Zip field in the field list.

   FileMaker fills in the Field Name, Type, and Comment boxes with the information about the Zip field.

2. Change the field name to Zip Code, and then click Change.

   When you have a field selected in the field list, the Change button becomes available. Instead of creating a new field called Zip Code, FileMaker just changes the name of the Zip field.

3.2.2. Duplicating a Field

The Duplicate button creates a new field that's an exact copy of what's selected in the field list. The new field's name says Copy at the end, which is the only difference.

FREQUENTLY ASKED QUESTION
Field Naming Conventions

I've seen databases with all kinds of funny field names. I've seen all caps, no spaces, lots of odd prefixes, and plenty of funny punctuation. How should I name fields?

People can get downright philosophical discussing the best way to name fields, and it seems everyone has an opinion. So how should you name your fields? Well, here are some of the common suggestions, as well as some discussion about why you would or wouldn't want to follow them.

 

Field names should have a prefix or suffix indicating their type.

FileMaker Pro developers often indicate the type of field in the field name. The reasoning is simple: You often have to pick a field from a list when you're building a database. All you see is its name, and you often want to know the type as well. In that spirit, people may name the First Name field something like "tFirst Name" where that "t" at the start means "text."

For most ordinary databases (in other words, not the really complicated stuff), you can usually figure out the type of a field by its name. For example, a field called Birth Date is probably a date field, while First Name is almost certainly text. You also don't often care about a field's type. For these reasons, you can usually ignore this advice and keep your field names prettier and more readable.

 

Field names should have a prefix or suffix indicating their purpose.

When a table starts to get a lot of fields, it can really be hard to keep track of which one does what. For example, a big database that keeps track of products might have dozens of fields.

Some would be about inventory levels (like Quantity on Hand, Quantity on Order, Quantity Desired, and Quantity Backordered). Others would be about pricing (like Base Price, Price for 10 or More, Price for 20 or More, Price for 30 or More, and so on). Some people like to come up with special codes to represent different types of information, to keep the field names more compact: QTY In Stock, QTY Order, QTY Desired, QTY Backorder, QTY Average. Again, for most databases this problem doesn't present itself very often. Usually the field names, if consistent, tend to develop their own English-language prefixes anyway (like all the Quantity fields). It's true, though, that field names can start to get long, and long names are more difficult to work with. In this case, abbreviations make good sense.

 

Some database programs aren't so flexible withnames. To be safe, FileMaker field names should follow the same rules as everybody else.

This rule usually doesn't apply. Why settle for names like "FNAME" and "LNAME" when FileMaker happily accepts "First Name" and "Last Name" instead? If you actually need to create the same database in another program some day, you'll have a lot more to worry about than renaming the fields. You can just ignore this rule unless you simply prefer geeky-sounding names.

Fields can have many special options in addition to the name, type, and comment. You'll learn about them later in this chapter. Duplicate comes in handy when you want a second field with many options in common with the first. Just click Duplicate, and then change the new field's name.

3.2.3. Deleting a Field

As you define your fields, you may decide to name all your fields differently, say, or combine two fields into one. You may find it easier to delete some fields and start fresh than to painstakingly modify each one. Simply highlight the field you no longer want, and then click Delete.

---

Note: If you select a field and make modifications to any of the settings at the bottom of the Define Database window, FileMaker guesses you may want to click Change. If you then try to select another field (which would overwrite the changes you've entered), FileMaker asks if you want to Save first. Clicking Save is the same as clicking Change: It updates the selected field with the new settings.

---

FREQUENTLY ASKED QUESTION
Field Naming Problems

When I try to name my field "Customer Order/Preferred Delivery" FileMaker gives me some guff about not being able to "easily" use the name in a formula. Should I worry?

Well, maybe. Later on, when you read about calculation fields (Section 3.2.6.5), you'll see that you can add the contents of two fields together, for example, or multiply the contents of one field by the contents of another. In fact, you can make field contents do just about any arithmetic dance you want them to do. When you perform math on fields, you're probably using number fields, but you can also do a surprising amount of work with calculations on text fields.

The specific problem with the field name above is that pesky "/" sign, which, in the language of math, means divided by. So if you try to use the contents of that field to do math, FileMaker gets confused about where the field name ends and the calculation begins.

Some people religiously avoid the use of certain characters. But you don't have to limit yourself if there's no reason to. The rule of thumb is that if there's a chance you'll need to do some math on a field, don't use any character that looks like a mathematical operation in the field's name. Characters to avoid are: + - * / ^ = < > _ _ ( , ; ) [] :: $ { }. You also want to avoid using the names of functions in your field names. See Section 9.2.4.1 for more about number functions. FileMaker's Help file also has a complete list of all functions, listed by name.

There are some other reasons, though, to avoid spaces and special punctuation in your field names. If you plan on building a Web site that interacts with your database using FileMaker's Web Publishing Engine, or that share data with other database systems using ODBC or JDBC (See Section 2.1.2), you'll save yourself a lot of headaches if your field names don't start with numbers, don't contain any spaces, and contain only alphanumeric characters.

 

3.2.4. Reordering the Field List

Now that you have several fields, you might decide you don't like the order they're listed in. Perhaps you want all the address fields first, or maybe you're a big fan of reverse-alphabetical lists. You can rearrange the fields in the field list any time you want.

To manually arrange the fields, just drag the little up-and-down arrows beside a field name. Figure 3-3 shows you how.

Figure 3-3. This series of pictures shows how you can manually rearrange fields in the field list. When you mouse over the up-and-down arrows to the left of the word Email, the pointer changes to a new symbol that lets you know you can drag the item up or down (top right). As you drag, other fields in the list move out of the way to make room (bottom left). Use this maneuver to move all kinds of things in FileMaker.

If you'd rather let FileMaker order them for you, make a selection from the "View by" pop-up menu. This menu gives you four choices:

• Creation order is the order in which the fields were originally created. Sometimes you have a lot of fields, and you just can't remember what one of them is called. If you do remember that you created it relatively recently, you can view by creation order and look for it near the end of the list.
• Field name orders the fields alphabetically by name. This view is an easy way to find the field you want when you do know its name.
• Field type groups fields of the same type together. It puts the different types in the same order as they appear in the Type pop-up menu: text fields first, then number fields, then date fields, and so on. If you know you're looking for a calculation field (and that's all you know), View by type can help.
• Custom order is the order you put things in when you drag fields around in the list. Since you sometimes spend a lot of time getting the fields arranged in just the right way, you might be afraid to sometimes sort them by name instead, ruining your perfect custom order. But that's not the case. After viewing them by name, you can easily switch back to custom order and see your perfect arrangement again.

---

Warning: If you're one of those funny people who like to keep everything organized just right, beware this classic bugaboo: If you sort the field list another way (like by name) and then move a field manually, the new order becomes your custom order. You'll lose all your hours of hard work. Thus, once you've set up a Custom order just the way you want it, make sure you switch back to it before you move any fields.

---

You can also click the Field Name or Type column headers to sort the list by that column. If you want to reverse the sort order, click the Sort toggle button. This button is at the very right edge of the column headers, just above the scroll bar. Clicking the Options/Comments column header doesn't sort; instead, it switches what you see in that column. Normally FileMaker shows you the options for each field. Clicking the header shows you comments instead.

3.2.5. Printing Field Definitions

Finally, if you click Print, FileMaker prints all the details about the fields you've selected. The printout resembles the field list, except that it's expanded to make room for everything about a field. You might print such a list if you want to keep your field names and types handy while you're working with your database, or if you need to send a list of fields to an associate so she can send you the right information.

---

Tip: You have to select at least one field for the Print button to become clickable. Then, when you click Print, you'll get details for any field that's highlighted. To print just a few, Ctrl+click (-click on the Mac) each field you want to print. If you want a printout of all the fields in the table, first select one field, then choose Edit Select All. Now every field is selected, and the Print button prints them all.

GEM IN THE ROUGH
Use the Keyboard

You can get to almost everything in the Define Database window's Fields tab from the keyboard alone. If you are a speed freak, you can almost entirely avoid the mouse. You can do these things with the keyboard:

As usual, on Windows you can use the Tab key to move among buttons, text boxes, and pop-up menus. Unfortunately, FileMaker doesn't honor Mac OS X's Full Keyboard Access settings.

On Mac OS X, you can press the Tab key to move between the Field Name, Comment, and field list.

While the field list is active, the up and down arrow keys select the next and previous fields.

If you hold down Ctrl () while pressing the arrow keys, you move the selected field up or down in the list.

Each field type has a keyboard shortcutlook in the Type pop-up menu to see themthat you can use instead of selecting the type from the list.

To see the field options dialog box for the selected field, press Ctrl+O (-O).

To delete the selected field, press the Delete key. In any of FileMaker's alert message windows, you can press the first letter of a button name to click that button. When it asks if you're sure you want to delete the field, press D.

Press the Escape key to close the Define Database window and throw away all your changes.

Now just click OK. FileMaker adds your fields to the database window. Congratulations! You've just built a database. It isn't prettyas Figure 3-4 provesbut it works. The status area shows that your database has one record. Go ahead and edit this record and create some more, using any of the techniques you learned in Chapter 1. You can even find, sort, print, and Find/Replace in this simple database.

Figure 3-4. Your first database! It has the same features as every other database you've seen, and you should have no trouble adding and editing records. It may not be pretty, but it works. You'll learn how to improve its looks in the next chapter.

 

3.2.6. Field Types

Every field you create has a type, which determines what kind of information it holds. All the fields you just created are text fieldsthey're designed to hold text of any kind. You've also been introduced to container fields, which hold pictures, movies, sounds, and files. But FileMaker actually has eight different field types, each designed for something a little different. For just a moment, you'll step away from the database you're building to get a brief introduction to each field type. You'll actually use these field types in various databases throughout the rest of this book.

You specify the field type when you create a field by choosing it from the Type pop-up menu. The pop-up menu also shows handy keyboard shortcuts for each field type, so you can quickly define a series of fields without clicking the mouse.

3.2.6.1. Text

Text fields are the most common typethey hold any kind of text information like a name, phone number, or email message. A text field can store up to two gigabytes of information, so there's plenty of room for any realistic purpose. Whatever you type into a text field shows up just as you typed it, and you can use any of the formatting options described in Chapter 2.

3.2.6.2. Number

As if it isn't obvious, number fields hold numbers. Like a text field, you can type in just about anything you want, but as soon as you leave the field, FileMaker does its best to turn your data into a number, and that's what it shows you. What's not so obvious is that not all numerical values are numbers. Use a number field if you're going to be performing math with the numbers in your database. But for things like Zip codes and telephone numbers, use text fields instead. See the box on Section 3.3.2.1 for the full explanation.

Numbers can be very large or very small, and they're very precise. The actual limits are thus:

• Numbers must be between 10400 and10400. If you're counting something reasonablesay, the number of protons in the universeyou'll be just fine.
• Numbers are precise to 10-400. In other words, you can have 400 digits to the right of the decimal point.
• You get 400 significant digits in all. If you don't know what that means, ask a high-school student. They know everything.
• Only 400 digits are indexed in all, significant or otherwise. So if you're searching for numbers, you have a little less precision to work with.

FileMaker's number-handling capability is absolutely top-notch. If your work requires very precise mathematics, you'll be thrilled. For example, FileMaker is much more precise with numbers than, say, Microsoft Excel.

You can enter numbers in FileMaker as you would in most any program. Type the number and any symbols that define it, like a negative sign or a decimal point. You can also use scientific notation. For example, if you have a burning desire to count the air molecules in your living room, you can put 6.02E23 into a number field. That means 6.02 times 1023 and it's a lot easier to type than 60,200,000,000,000,000,000,000,000. Of course the exponent part (after the E) can be negative if really small numbers make your socks go up and down.

3.2.6.3. Date

At the risk of being obvious, a date field holds a date. FileMaker thinks of dates in numerical form by month, day, and year. You can put just about any punctuation between these numbers, but most people use a slash, a dash, or a period. For instance, 2/25/1975, 2-25-1975, and 2.25.1975 are all valid dates. If you type it in the wrong way, you see the error message in Figure 3-5.

Figure 3-5. If FileMaker doesn't like the date the way you entered it, it tells you so. The message ends with an example of a correctly formatted date.

---

Tip: If your date's in the current calendar year, you can save time by typing just the day and month. FileMaker fills in the current year for you. Don't add that second puntuation mark, though, or FileMaker will bark at you.

---

FileMaker accepts dates from the year 1 (AD) to the year 4000, but since most databases don't have that long a lifespan, the program lets you enter a two-digit year and takes its best guess as to which century you're talking about. Here's how it works: If you type a two digit year, FileMaker tries putting 20 in front of it. If that year is more than 30 years from now, FileMaker assumes you really meant 19-something and adds the 19 for you. Otherwise, it adds 20 for the first two digits. For example, if it's 2005 and you put 34 in for the year, you get 2034, but if you enter 36, you get 1936. Confusing? Avoid all this runaround by entering four-digit years.

UP TO SPEED
System Formats

The way FileMaker wants to see numbers, dates, and times depends on how you've configured your computer. In Windows, you configure in the Date and Time control panel. In Mac OS X's System Preferences, you use the International pane. When you create a database, FileMaker notes what formats the computer is set for and remembers them. If you then open the same database on a computer that's configured differently, you'll get a message asking if you want to use the system format, or the format stored with the database.

If you don't want this message to appear, you can go to the File Options dialog box and pre-select your preference.

1. Choose File File Options. This window lets you control a handful of settings for the current database.

 

3.2.6.4. Time

You can probably guess what goes into a time field. Time fields actually serve two purposes, though. First, you can enter a time of day, like 4:30 PM or 1:27:03 AM. But you can also enter a duration instead, like 123:38:22 (meaning 123 hours, 38 minutes, 22 seconds). Even though this sequence isn't a valid time of day, it's an acceptable time value.

Times are easy to enter. They always go in like so: hours:minutes:seconds, and the seconds can also have a decimal part for fractions of a second. If you don't need seconds, you can just type in hours:minutes. Likewise, if you don't need minutes either, you can just enter a number of hours.

If you're entering a time of day, you can put an AM or PM after the time if you want, or you can use 24 hour notation. Either way, FileMaker can fix weird time entries, like 1:82:17, for you. In this case, you can't have 82 minutes because that's the same as 1 hour, 22 minutes, so FileMaker would change it to its more normal equivalent: 2:22:17. To have FileMaker check and repair time values, apply a time format applied to your time fields (Section 6.6.3).

---

Note: When entering time values, you always start with hours. If you're trying to enter just 12 minutes, 37 seconds, you have to enter 00:12:37 or 0:12:37 so FileMaker doesn't think you mean 12 hours.

---

Time values are also quite precise. If you're recording track event times for your school, you can enter 00:00:27.180 for 27 seconds, 180 milliseconds. You can put up to six digits to the right of the decimal point.

3.2.6.5. Timestamp

A timestamp field is kind of like a date field and a time field combined. It holds both a date and a time. You use this kind of field to record when an event occurred, or when it will occur. If you want, you can create two fields, one for the date and one for the time. But FileMaker has all kinds of special abilities when it works with timestamp values that you'd have to otherwise try to figure out yourself. For example, it can figure out how much time has passed between two timestamp values with a simple calculation (End TimestampStart Timestamp). If you use separate date and time fields, the necessary calculation is anything but simple. If something happens on a specific date and time, you should use a timestamp field for it.

Timestamps support the same less-than-a-second accuracy as time fields. They show up as a date followed by a time with a space in between. All the rules for date and time entry apply: 2/25/1975 2:45 AM.

3.2.6.6. Container

You've already learned what a container field can do (Section 2.7). It holds pictures, sounds, QuickTime multimedia files, and ordinary files. Containers can hold up to two gigabytes, just like text fields.

3.2.6.7. Calculation and summary

Unlike the other field types discussed so far, calculation and summary fields don't hold your stuff at all. Instead, they make up their own values based on the settings you give them. For calculation fields, you can specify a special formula that it uses to determine its value. For instance, if you had a field called Birth Date, you could create a calculation field that shows the person's age. The Age field automatically updates to stay correct as time goes by, so you don't have to change it. You'll learn all about calculations in Part 3.

Summary fields are the strangest of all. They aren't even associated with a record. Instead, their value depends on the values of other fields across some set of records. For instance, a summary field could show you the number of people in the database who are from California, or the total dollar value of all outstanding invoices for a customer. You'll learn about summary fields in Chapter 6.