An introduction to basic fields
This month we’ll go into some of the fields, concentrating on the ones you’ll need later on to add functionality to your database.
One of the most important fields to put in any database is one for serial numbers. They uniquely identify each record with a tag that stays the same. The default Record Number given by FileMaker will change whenever an earlier record is deleted, so it’s useless for most operations.
First go to Define Fields (Command-D), and create a field, either number or text. Then hit the Options button (or double-click the field name, or click Enter or Return), to bring up the Auto Enter choices. There is a checkbox for “Serial number.” Put 1 in the number box if it’s not already, and increment by 1.
Now every new record will get its own serial number. If you import records, be sure to check the “Perform auto-enter” box, so they get serialized as well.
If you’ve already got records in your DB, don’t worry. It’s pretty easy to redo them. First you might want to sort all the records in the order you normally view them; but it doesn’t really matter.
Now type a “1” in the serial number field of the first record. Then choose Replace from the Mode Menu (Command-=, equal sign). Then hit the radio button for “Replace with serial numbers.” Type 1 in the “Initial value,” if it’s not there already. Check the “Update entry” box, so new records will begin after your last serial number. Then hit “Replace.” You will notice that “Cancel” is the default choice. This is a precaution, because Replace is not undoable. Once done it can’t be reversed, so take your time with those choices.
Serial numbers can be either text or numbers. I’ve read that Text is usually better, because of the way relationship keys work (later for that). It has to do with the size limit of number fields, 255 characters, whereas text fields can have 64,000. For now, though, either will do.
Text has one disadvantage though, in that a Find on a text field will find all matches that begin with the characters, and ignoring zeros after numbers. So, it will find both 20 and 2. This is easily fixed by beginning the text serial numbers with a few zeros. The first would then be “0001”, depending on how many records of that type you expect to have (3 zeros would allow 9999 records with no problems). You can even put a letter to identify which field it is, for example Contact ID’s could be C0001. This helps later if you have several ID fields in one area (such as a portal row), and can’t easily tell which is which.
Another kind of field is a Calculated field. These are amazingly powerful. One of the simplest and most useful is a concatenated (added together) field combining two other fields. This provides a quick way to get unique identifications for unserialized records.
The most obvious is to define a field combining a First Name field with a Last Name field. Click the Calculation radio button. In the Options (Specify Calculation) dialog that pops up, type (or choose from the field list); First & Last *(see below) with a text result (in the box down at the bottom).
If you want to use this to identify duplicates, you might want to add something else, in case two people had the same name. You could also add in a few numbers of their address. This might look like; Left (First, 4) & Left (Last, 4) & Left (Address, 4) The Left function was used to get just the first four characters of each field; it keeps things reasonably short.
*I’m not going to put the usual double-quote marks or punctuation around calculations I’ve written here, for two reasons. Field names and operators (such as &) are entered in calculations without double quotes. Quotes are reserved to identify actual text. Calculations are written here the same as they would be in FileMaker; they would generate an error if I put them in quotes and you copy/pasted them into the dialog. The asterisk ain’t part of it either; it would be considered a multiplication operator. Confusing? Yes. Logical? Maybe.
On the plus side, you can have spaces between terms in calculations, so it’s not all bunched together. Don’t worry too much, FileMaker will not only tell you if the thing won’t work, but can usually point out not only what’s wrong, but where the error most likely is. Strangely, if you want actual text double quotes, you have to enclose them with other quotes, such as “””” (that’s four double-quotes to get one). Carry on.
You can now use this field to check for duplicate records. It’s much safer than using just names, as there could easily be two people with the same name, but they’re not likely to be living at the same address.
Another use of a concatenated calculation field is for simple text layout. It’s a more foolproof way to do such things as addressing envelopes, without having to rely on the “sliding” options in the Printing/Sliding dialog. It’s not that they don’t work, but they don’t always slide exactly how you want. It’s easier to add spaces and punctuation in a calculated text field.
Here’s an example (field names are written as they are, all other text must be enclosed in quotation marks, even spaces; the & symbol joins things together):
First & ” ” & Last & “¶” & Address & “¶” & City & “, ” & State & ” ” & Zip
(¶ is a paragraph symbol, in case it doesn’t show properly here, and it needs the quotes. Notice that the “, ” is a text comma and space; the other commas are term separation punctuations, which are part of the calculation structure itself.)
You can get fancier, using logical “If” statements to check for other additional fields:
If (not IsEmpty (Prefix), Prefix & ” “, “”) & First & ” ” & If (not IsEmpty (Middle), Middle & ” “, “”) & Last & “¶” & Address & “¶” & City & “, ” & State & ” ” & Zip
This checks for an entry in a Prefix field (such as Mr., Ms., Sir, etc.); also in a Middle name field. If there is one, it puts it, followed by a space. If not, it leaves it out. Double quote marks, with nothing between them, mean “nothing” or “don’t do anything,” and are a common way of ending an “If” statement.
The “not IsEmpty” is a backwards ways of saying “has something in it,” which there’s no direct way to say in a calculation. The “not” operator changes anything into its opposite. (Hopefully these all work; experiment with your own.)
Other useful fields are creation date and modification date. These can be set up to “auto-enter” the above dates when you create or modify a record. First make sure you’ve chosen Date for the field type, then hit Options. The first screen is the Auto-enter choices (which are numerous). Check the first box, and choose from the drop-down menu. The Modification Date choice is unique, in that it calculates whenever you change any field in that record. Usually the auto-entry choices only calculate when you create a new record, so this one is special.
The date fields are useful for sorting records. In my Job-tracking DB, I use a reverse sort, so that the jobs I’ve either created or modified most recently end up at the top of the list. You can sort by up to three fields at a time.
Next month I’ll go into some more calculation fields for showing record numbers, and a few calculation techniques for text formatting. Maybe we’ll make it as far as “global” fields.
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.