Field Types

One item you may have noticed last month is that all of the fields we used were text fields. That is, they stored textual information. FileMaker provides other types of fields when you wish to store other types of information. If you’ll take a look below, you’ll see that the field types include Text, Number, Date, Time, Timestamp, Container, Calculation, and Summary.

The first four are probably self explanatory. Text will store any text, up to 2 GB of data (a massive upgrade from the 64K that could be stored in FileMaker 6), allowing you to store pretty much any kind of text information you can conceive. Number fields store, um, numbers. Unlike many programming languages, FileMaker doesn’t differentiate between integers and reals. Both of them are simply numbers.

Date fields can store any date between 1/1/0001 and 12/31/3000. Dates are stored as numbers internally, which means that the range of values is 1 to 1,095,727 (December 31, 3000 is the 1,095,727th day when counting from January 1, 0001 with the Gregorian calendar). Since dates are just numbers, you can use addition and subtraction on date fields to alter them. For instance, you can add 7 to a date field to get the date one week from the original.

Time fields work similarly, storing the number of seconds since midnight, and therefore have a range of 0 (for midnight itself) to 86399 (for one second before midnight). For instance, I’m writing this at 11:45:53 AM, which FileMaker would store internally as 42,353 because there have been 42,353 seconds since midnight. Time fields can have fractional portions up to six decimal places, allowing you to track fractions of a second to the nearest millionth.

Timestamps are a combination of a date and a time, and are stored internally as the number of seconds since January 1, 0001, giving a range between 1 and 94,670,812,799. The above time (11:45:53 AM) for today (July 25, 2005) is stored internally as 63,257,888,753.

Container fields are generic holders for all kinds of binary data. You can store pictures, movies, or files of any type in a Container field, allowing you to create a database of images, videos, or files associated with a project that is tracked in FileMaker. Container field contents are limited to 2 GB in size, which should be sufficient for all but the largest video files.

Calculation fields are those that take existing data, perform a calculation, and return a result of any of the above types. For instance, using our Date field example above, we could have a date field called InvoiceDate. We could then create a calculation field with a date result called DueDate where the calculation is InvoiceDate + 30 and DueDate would provide the date that is 30 days after the date in InvoiceDate. FileMaker 7 provides a rich library of functions to apply to field data, and although we will create a Calculation field today, we’ll cover Calculation fields in much greater detail in a future article.

Calculation fields are limited in that they can only reference data within the current record or records related to the current record. What if you have a database of invoices and want to know the total of all of the invoice records? A Calculation field won’t do the job for you because it can only reference data within a single record, but a Summary field can reference data across records, allowing you to create a field for a report that will calculate the total of values in a field across records. Like Calculation fields, Summary fields are a complex topic, and we’ll cover them more fully in a future article.

Field Options

In addition to giving a field a name and a type, you can provide other information to increase the integrity of your database system. For instance, last month we saw how you can have FileMaker automatically enter an auto-incrementing serial number. In addition to this and other auto-enter options, you can have a field validated to have only certain kinds of values. We’ll see this in practice in a few moments.

You can also specify options for how the field is stored. Your first option is to store a field as a Global field. A Global field has the same value regardless of which record in the table is being viewed. Global fields are often used as variables within scripts and as holders of interface elements. For instance, if you have a script (a small program that performs a series of automatic steps) and need to take information from one record to another record, you could store that information temporarily in a global field.

A field can also have repetitions. For the most part, this is a hold-over from the days when FileMaker wasn’t relational, but it can still be useful when storing interface graphics for a system, which is pretty much the only time I use repeating fields.

Finally, you can specify how a field is indexed. FileMaker can create an index of a field’s contents for faster searching, using the index to find records that match search criteria rather than manually searching every record. Indexes are also needed for fields that are used in relationships. Usually, you don’t need to manually set the index options, although you can if you know that a field will be searched by users or will be used as a match field in a relationship. The reason you don’t usually need to set the indexing options manually is that FileMaker will index a field (if it can) as soon as you perform an operation that needs to have the field indexed. For instance, if you look at the field options for any of the foreign key fields (such as the ProductID field in the SerialNumbers table), you’ll see that FileMaker has turned indexing on for you because for such a relationship to work properly, the field needs to be indexed. A similar automatic indexing will occur if you perform a find on a field.

Set a Preference

Before we take all this theory and apply it, there’s a preference you will need to set if your experience with FileMaker is to be exactly like what I describe. FileMaker, by default, adds new fields to the current layout when they are created. Many of the fields I add to a system are “background” fields, fields that my users never see. Also, my layouts are usually very complex, and newly added fields often mess up the layout I have, requiring that I not only remove the field from the layout, but also edit the layout so that it returns to the look I want for it. I don’t like this behavior, and one of the first things I do when I install FileMaker is turn this preference off.

Choose FileMaker ‣ Preferences from the menu bar or type Command-Comma and click on the Layout tab. Uncheck the checkbox labeled “Add newly defined fields to current layout.” You may also want to check the checkbox for “Save layout changes automatically (do not ask)” if you haven’t already set this preference when the software originally asked you during the last tutorial.

Adding Fields

Now that our theory is behind us (for the moment) and we’ve set our preference, we can add a couple of non-Text fields to our database. Choose File ‣ Define ‣ Database from the menu bar or press Shift-Command-D. Click on the Fields tab if it isn’t already selected and choose SerialNumbers from the Table menu in the top left of the window.

Some software can be licensed for multiple users with a single serial number, such as Mac OS X Server, which can be purchased with either a 10-user or unlimited license. We’re going to add a field to the SerialNumbers table that specifies how many users the license is for. Enter “LicensedUsers” as the name of our new field in the “Field Name” field in the “Define Database” dialog. Choose Number from the Type pop-up menu (or press Command-N) and click Create.

Now we want to set some field options for our new field. Click on the Options button, or, if you’re a keyboard junkie like me, and like to keep your hands off the mouse as much as you can, press Shift-Tab (to move from the “Field Name” field to the field list) and press the space bar. Pressing the space bar while a field is selected in the field list and the field list has focus (as indicated by a blue outline around the field list) will also bring up the Options dialog box.

Most licenses are for a single user, so we’ll set this field to auto-enter 1. Click the Auto-Enter tab of the Options dialog box and click the checkbox for Data, entering a 1 into the field enabled after you do so.

As far as I can think, a license should always be for at least one user, so we’ll validate our field to make sure that it always has a value of at least 1. Also, a license should be for a whole number of users or “Unlimited.” Text information can be stored in number fields, but is generally ignored, so allowing the user to enter “Unlimited” into our LicensedUsers field is perfectly fine. Click on the Validation tab and click the checkbox for “Validated by calculation.” As soon as you do, a “Specify Calculation” dialog box will appear. For now, just enter the following calculation in the large text field near the bottom of the window:

( ( LicensedUsers ≥ 1 ) and 
 ( Int( LicensedUsers ) = LicensedUsers ) )
   or
( LicensedUsers = "Unlimited" )

FileMaker doesn’t care how you use spaces and carriage returns in your calculations, so the above could have been entered as:

((LicensedUsers≥1) and (Int(LicensedUsers)=
LicensedUsers)) or (LicensedUsers="Unlimited")

However, using spaces and carriage returns in your calculations will make them much easier for you to read later, so I highly recommend that you format your calculations as I have above. You’ll thank me later when you have to return to a calculation to edit it.

The “greater than or equal to” symbol can be inserted either by double-clicking it in the list of operators or by typing Option-Period. This calculation says that the LicensedUsers field must be at least 1 and be an integer, or must contain the word “Unlimited.” If either of these conditions is true, the field is validated, and if both are false, it is not. Click OK to save your calculation.

When implementing field validation, you can allow the user to override the validation (the default) or make it “strict,” requiring that the field satisfy the validation before accepting the input from the user. We will make this “strict,” so uncheck the checkbox for “Allow user to override during data entry.” Also, field contents can be set by the user or by scripts and imports, and you can specify when to validate. The default is to validate only during user data entry, but if you wanted validation to always occur, you would click the Always radio button under the “Validate data in this field” label.

If validation fails, FileMaker will present the user with a default message, but the default message isn’t very useful as it doesn’t tell the user how to ensure that the validation succeeds, only that it failed. We will provide more information by clicking the checkbox for “Display custom message if validation fails” and entering “You must enter a positive integer or ‘Unlimited’ for the number of licensed users.” into the field below it. You’re finished with the field options, so click the OK button to get back to the “Define Database” dialog.

Some licenses have expiration dates, so we’ll add a Date field to store that information. Type “ExpirationDate” into the “Field Name” and either choose Date from the Type pop-up menu or type Command-D, and either click Create or press the Enter key. Date fields are automatically validated to be dates, so we don’t need any auto-enter options (the field will most likely be empty) and we don’t need any validation (FileMaker will already validate that it is a proper date).

However, it might be useful to know how many days before the license expires, so we’ll add another field to calculate this. Enter “LicensedDaysLeft” and choose Calculation from the Type pop-up menu or press Command-L. As soon as you either click Create or press the Enter key, a “Specify Calculation” dialog box will appear. Enter the following calculation in the large text field near the bottom of the window:

ExpirationDate - Get(CurrentDate)

You may notice that the checkbox “Don’t evaluate if all referenced fields are empty” is checked by default, which in this case is good, as we don’t need to perform the calculation if the ExpirationDate field is empty. You’ll also notice that the default result type is Number, which in this case, even though we’re working with a Date field, is correct, as we’re subtracting one date from another to get the difference between the two.

Get(CurrentDate) is a built-in function in FileMaker that will return, of all things, the current date. However, unless we specify otherwise, this calculation will only evaluate when it is created or when data upon which the calculation is based (i.e., the contents of the ExpirationDate field) change. We would like this instead to always show the correct value, so that if we view the record in a week, we’ll see how many days are left from then rather than when the record was created. To do this, we need to specify the Calculation field as “Unstored.” Click the “Storage Options” button near the bottom right of the window and click the checkbox for “Do not store calculation results—recalculate when needed” and then click OK. You’ll notice that when you specify a calculation to be unstored, you can no longer index it, which makes sense; the calculation isn’t being stored in the database file, so there’s nothing to index. Rather, it’s recalculated whenever it’s being viewed on the screen.

Click OK again to return to the “Define Database” and OK again to exit it.

Add your new fields to the SerialNumbers layout and try entering some data. Enter a number with a fraction into the LicensedUsers field and see if you get the validation failure dialog box. Enter a date into the ExpirationDate field and make sure the LicensedDaysLeft field calculate correctly. Add some new records and double-check that a 1 is entered automatically for the LicensedUsers field.

That’s it for this month. As always, please feel free to send me a message with questions or comments (or expressions of extreme gratitude). Thanks for joining me, and I’ll see you next month.

Copyright © 2005 Charles Ross, chuck@chivalrysoftware.com. Charles Ross is a Certified FileMaker 7 Developer and the Chief Technology Officer of Chivalry Software, LLC, a company specializing in custom database, web and automation software and publisher of Function Helper, a FileMaker calculation debugging tool. He was a contributing writer and the technical editor for The Book of FileMaker 6 and has contributed to ISO FileMaker Magazine and Macworld in addition to his series on AppleScript for ATPM.