This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Fields
Every table in any database—FileMaker Pro or otherwise—is a collection of information stored in fields (or columns, if you're familiar with that terminology.) It is by storing like 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 such 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 examples—the overly specific and the overly general—require 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.
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.
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. Right from day one we encourage you to adopt good programming habits.
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 or not (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 fields—those fields that one needs only to make one's FileMaker Pro solution work. If you ever went to import your database wholesale into another system, these fields would 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.
AlbumName
Date
k_primary_AlbumID
k_foreign_ArtistID
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 Diam
Bike Name
Model
Temp
Date
Bike
Sp.99 Meas
Tire Diam
Bikeid
Sku
2002 Tire Diam
1999 Tire Diam
BikeMODEL
SUMMARY
zTempzzz
Phils field (no lie!)
We're sure we've belabored the point, but this database was difficult to modify not because it was complex, but because it was hard to read. As in all things, a little planning goes a long way.
If you're planning on using FileMaker Pro as a Web backend, 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," (Chapter 21), as well as Chapter 23, "Custom Web Publishing."
There are some restrictions to field naming in FileMaker Pro: A field name must be unique and must be less than 100 characters.
You can opt to use special characters, numbers, spaces, even the names of functions, but we recommend against using them. If you use: ,(comma), +, -, *, /, ^, &, =, ≠, >, <, (, ), ", ;(semicolon), :(colon), ::(double colon relationship indicator), you need to enclose such special characters with 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, the 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 at least an underscore.
Adding Field Comments
Notice also that you can add comments to your field definitions. We don't mean to be pedantic, but 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 to 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 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."