- Knowing Your Data
- FileMaker Field Types
- Importing Data
- Exporting Data
This chapter is from the book
This chapter is from the book
This chapter is from the book
FileMaker Field Types
In the previous chapter, on FileMaker specifications, we called out a number of the raw capacities of FileMaker’s field types, in the briefest possible format. Here we dwell on each data type and its characteristics in a bit more detail.
→For more information on FileMaker fields and field types, see Special Edition Using FileMaker 8, Chapter 3, "Defining and Working with Fields.
Text
FileMaker’s text field type is able to hold up to 2 gigabytes of character data per text field. FileMaker 8 stores text internally as Unicode, which requires about 2 bytes per character (the exact number varies by the specific character and encoding). So a single FileMaker text field can hold about a billion characters of data (nearly half a million regular pages of text).
There’s no need to specify the "size" of the text field in advance; FileMaker will automatically accommodate any text you enter, up to the field size maximum.
FileMaker’s text field can be indexed on a word-by-word basis, so it’s possible to search quickly for one or more words anywhere within a text field.
In other database systems, such as those based on SQL, it is often necessary to specify the maximum size of a text field in advance. Many SQL databases do go beyond the SQL standard in offering a text data type of flexible size.
Number
The number field type is FileMaker’s only numeric data type. Unlike some other tools, FileMaker does not have separate data types for integers and floating point numbers. All numbers are capable of being treated as floating point.
FileMaker 8’s numeric data type can store numbers in the range 10-400 through 10400, and -10400 through -10-400, as well as the value 0. The data type can account for 400 digits of precision. By default, though, numeric values will only account for 16 digits of precision. To achieve higher precision, you’ll need to use the SetPrecision() function.
Numeric fields can accept character data entry, though the character data will be ignored. If the field’s validation is also set to Strict Data Type: Numeric Only, even the entry of character data will be disallowed.
In other database systems, such as those based on SQL, integers and floating point numbers tend to be considered as two different data types, and it’s often necessary to choose in advance between lower-precision and higher-precision floating point numbers, though SQL does also offer arbitrary-precision numbers as well.
Date
FileMaker’s date type can store dates from 1/1/0001 to 12/31/4000. Internally, these dates are stored as integer values between 1 and 1,460,970, indicating the number of days elapsed between 1/1/0001 and the date in question. This is significant in that it means that integer math can be performed on dates: 12/31/2001 + 1 = 1/1/2002, and 12/21/2001 – 365 = 12/21/2000.
FileMaker will correctly interpret oddly formed dates, as long as they’re within its overall date range. For example, 4/31/2005 will be interpreted as 5/1/2005, whereas 12/32/2005 will be interpreted as 1/1/2006. But 12/31/5000 will be rejected outright.
FileMaker’s date type is stricter than the text and number types, and will reject any date outside the range just mentioned, including those containing textual data. Field validation options can also be configured to force entry of a full four-digit year.
Though the date type is stored as an integer, it can be entered and displayed in a wide variety of well-known or local date display formats. By default it will use settings it inherits from the current user’s operating system, but the date display format can be overridden on a field-by-field basis on each individual layout.
By comparison, SQL-based database systems also offer a date data type, measured in days.
Time
Like the date type, FileMaker’s time data type stores its information in an underlying integer representation. In the case of the time field type, what’s being stored is the number of seconds since midnight of the previous day, yielding a range of possible values from 1 to 86,400. As with the date type, it’s possible to perform integer math with time values: So Get(CurrentTime) + 3600 will return a time an hour ahead of the current time. And 15:30:00 – 11:00:00 returns 4:30:00, so "time math" can be used to compute time intervals correctly as well.
Also like the date type, the time data type can accept entry and display of time data in a variety of formats. "Overlapping" times work in the same way as overlapping dates: FileMaker will interpret the value 25:15:00 as representing 1:15 a.m. on the following day. (This is exactly what happens when you store a value greater than 86,400 in a time field, which FileMaker will certainly allow.)
By comparison, SQL-based database systems generally offer a time data type, sometimes with the capability to track a time zone as well.
Timestamp
FileMaker’s timestamp data type is like a combination of the date and time types. A timestamp will be displayed as something like "11/20/2005 12:30:00." Internally, like both dates and times, it is stored as an integer. In this case the internally stored number represents the number of seconds since 1/1/0001. The timestamp can represent date/time combinations ranging from 1/1/0001 00:00:00 to 12/31/4000 11:59:59.999999 (a range of over a hundred billion seconds).
As with the related date and time field types, timestamps can be displayed and entered in a variety of formats. It’s also possible to perform math with timestamps as with dates and times, but since timestamps can measure time in days but are stored in seconds, the math may get a bit unwieldy.
The timestamp data type is commonplace in the database world. Some flavors of timestamp offer the capability to track a time zone as well.
Container
The container data type is FileMaker’s field type for binary data (meaning data that, unlike text and numbers, does not have an accepted plain-text representation). Binary data generally represents an electronic file of some sort, such as a picture, a movie, a sound file, or a file produced by some other software application such as a word processor or page layout program.
A single container field can store a single binary object up to 4 gigabytes in size. Though any type of electronic file can be stored in a container field (one file per field), FileMaker has specialized knowledge of a few types of binary data, and can play or display such objects directly by calling on operating system services. Specifically, FileMaker can play or display pictures, sounds, and movies (and in fact any "movie-like" file supported by QuickTime, if QuickTime is installed). Additionally, on Windows, a container field can store and display Object Linking and Embedding (OLE) objects.
When working with container fields, it’s important to understand the distinction between storing the data directly (embedding the entire file in the FileMaker database and increasing its file size by tens or hundreds of K or indeed by megabytes) and storing it by reference (storing just the path to the specified file). The former leads to larger database files; the latter relies on the binary files being stored in a fixed location and not moving relative to the database.
Many other database systems are able to handle binary data, but there’s little in the way of an agreed-upon standard for doing so. In general, each database tool has its own means for dealing with binary data.
Calculation
From the point of view of data types, calculations are not a data type at all. They are instead a field type, and can be constructed to return their results as any of the six fundamental data types: text, number, date, time, timestamp, or container.
Summary
Summary fields are another example of a field type rather than a data type. Summary fields perform summary operations on sets of grouped records, and always return a numeric result.