Working with Fields

Every table in any databaseFileMaker Pro or otherwiseis a collection of information stored in fields (or columns, if you're familiar with that terminology). It is by storing information in appropriate fields that a database is given meaning.

For example, by entering "124 Main Street" in a field called Address, we've identified what "124 Main Street" is. In the case of a street address, it's fairly easy to identify without a field definition, but what about "912.5" all on its own? That could be a price, a number of units, a chapter heading, or a thousand other things. When you place that number into a named column or field, your data becomes meaningful. If "912.5" sat in a field named Temperature, you'd likely conclude that it is pretty darn hot. Conversely, if it sat in a Kilobytes Available field, you'd look to be buying a new hard drive. Keep this ultimate goal of imparting meaning in mind as you create and name fields, and assign appropriate data types to them.

Field Naming Conventions

One of the nice things about FileMaker Pro development is the freedom developers have in naming fields. (FileMaker Pro is not unique in allowing developers field naming freedom, by the way.) That freedom also, unfortunately, gives rise to confusion and arbitrary naming conventions. Name_xTJm2 may mean something to someone, or Name might also, but both examplesthe overly specific and the overly generalrequire a strong familiarity with a given system. If you ever return to a database months after building it, odds are you will have forgotten your clever abbreviations.

For a complete presentation of Soliant Consulting's coding standards, see Chapter 27, "Documenting Your FileMaker Solutions," p. 841.

We encourage you to take advantage of FileMaker's allowance for long field names of up to 100 characters. Use full text names (like Street Address line1), avoid abbreviations (or if you use them, be sure to provide an obvious key!), and try to group things logically. Note that although FileMaker allows for spaces and special characters, we recommend using underscores, letters, and numerals only.

Here's an example of what we'd consider a fairly reasonable approach to naming fields:

• Address_City
• Address_Postal_Code
• Address_State
• Address_Street_line1
• Address_Street_line2
• Person_Name_First
• Person_Name_Last
• Phone_Home
• Phone_Work

These fields are quite simple to identify and are neatly grouped together when sorted alphabetically. This isn't such a big deal for small databases, but if you ever work on a large database, with multiple developers, a well-established naming convention is vital. We encourage you to adopt good programming habits right from day one.

Another common approach many developers use includes abbreviations for data types. Often it's handy to know the data type of a given field when working with it without having to refer to the Define Database dialog. Here we've used "t" for text, "n" for number, and "c" for calculation:

• ProductName_t
• Price_n
• TaxRate_n
• Tax_c

We'll cover indexing later in the chapter, but some developers also note whether a field is indexed ("x" for indexed, "n" for unindexed):

• Location_Name_tx
• Location_Desc_tn
• Location_Size_nn

Some naming conventions also break out a division between data fields and what are commonly referred to as developer fieldsthose fields that you need only to make your FileMaker Pro solution work. If you ever went to import your database wholesale into another system, these fields would probably be left behind. Here we have two abbreviations: "k" for key (or match field), and "z" (so that it sorts to the bottom of the list) for developer utility fields. We also use underscores to ensure that keys sort to the top of our field list, with the primary key coming first.

To understand how keys are used to identify records in tables and form relationships, see Chapter 5, "Relational Database Design," p. 129.

• __kp_primary_AlbumID
• _kf_foreign_ArtistID
• AlbumName
• Date
• z_SelectedPortalRow
• z_UserColor_Preference
• z_UserGenre_Preference

Finally, here's a real example from a database we recently were hired to modify (used with permission and good humor!):

• Bike Type
• Wheel Dm
• Bike Name
• Model
• Type
• Temp
• Date
• Bike
• Bike2
• Sp.99 Meas
• Tire Dm
• Bikeid
• Sku
• 2002 Tire Diam
• 1999 Tire Diam
• BikeMODEL
• SUMMARY
• zTempzzz
• Phils field (no lie!)

We're sure that we've belabored the point, but this database was difficult to modify not because it was complex, but because it was hard to interpret. As in all things, a little planning goes a long way.

If you're planning on using FileMaker Pro as a web back end, refer to "Problematic Field Names" in the "Troubleshooting" section at the end of this chapter.

For more information on using databases on the Web, see "Designing for IWP Deployment," p. 648, as well as Chapter 23, "Custom Web Publishing," p. 699.

For more thoughts on documenting and commenting in your database, see Chapter 27, "Documenting Your FileMaker Solutions," p. 841.

There are some restrictions on field naming in FileMaker Pro: A field name must be unique within its table, and must be less than 100 characters in length.

You can opt to use special characters, numbers, spaces, even the names of functions, but we recommend against using them. If you use, (comma), +, -, *, /, ^, &, =, images/U2260.jpg border=0>, >, <, (, ), ", ; (semicolon), : (colon), or :: (double colon relationship indicator), you need to enclose such special characters within a $() in calculation formulas to have them interpreted as field names. For example, the calculation $(Tax,special) returns the value of a field named Tax,special.

We recommend strongly that you name fields without using special characters, names of functions, or operators (AND, OR, NOT, XOR, TRUE, FALSE).

The same is true for fields that begin with a space, a period, or a number: You'll have to contort your calculations to deal with them. Don't use them. Begin each field with a standard alphabetical letter or an underscore.

Adding Field Comments

Notice also that you can add comments to your field definitions. We don't mean to be pedantic, but we want to drive home that establishing good programming habits will serve you well for the rest of your life as a developer. Use the field comments feature. Explain to yourself a year from now why a field exists, any dependencies or assumptions you made, and possibly how you intend to use it.

Creating New Fields

To create fields in FileMaker Pro, you need to enter some text in the Field Name area of the Define Database dialog and click Create.

One important aspect of databases to keep in mind is that it's important to establish a discrete field for each bit of information you want to store. If you create a field called Contact Information and cram an entire address and a set of phone numbers into it, technically it will work fine, but if it ever comes time to export that information, sort by area code, or run a report by city, you won't be able to cull the information you want from the field without suffering from a good headache.

To database wonks, the Contact Information example would be a violation of first normal form, or more colloquially, "one fact, one field." For information on relational data modeling and defining fields, see "Relationship Types," p. 136.

Working with Field Types