Database Reference
In-Depth Information
NOTE
FileMaker uses a field's index in other ways, too. You need an index if you want to use the “Unique
value” option in the field Validation screen. You also need indexed fields to create relationships
between table occurrences in your database (
Creating a Relationship Between Two Table Occur-
WORD TO THE WISE: REPEATING FIELDS: NO SUBSTITUTE FOR
RELATED TABLES
With very few exceptions, repeating fields cause more trouble than they're worth. The truth is, they
exist only because years ago when FileMaker was a youngster, it wasn't a
relational
database. That
means it didn't have any way to tie multiple tables together so that they could share information and
make connections between things in each table. In a database that's not relational (called a
flat file
database
), you have no good way to track lots of different kinds of entities in one database, so in the
bad old days FileMaker came up with the concept of repeating fields to let you store more than one
kind of thing in a single record. For example, you could have an Invoice table with one record for
each invoice. The line items on the invoice needed a place to live, too, so you created repeating
fields. That way, each individual invoice could hold, say, 20 invoice items.
But this is the 21st century, and FileMaker has come a long way. Nobody in her right mind would
build an invoice database that way anymore (she'd hook together two tables instead—see
Creating a
Relationship Between Two Table Occurrences
)
. Since FileMaker had repeating fields 15 years ago,
and since it's really friendly about supporting all your ancient databases, it still supports repeating
fields today.
That said, repeating fields are
occasionally
useful when writing calculations (
Chapter 9
) and scripts
or for some very advanced uses like storing temporary data in arrays or storing resource graphics for
use elsewhere in your file. Other than that, you should avoid them when storing most data for a few
reasons:
▪
Repeating fields are uniquely FileMaker
. If you want to take data out of your database and
put it in some other program (like a spreadsheet or another database program), repeating fields
can produce one serious pain in the neck. Likewise for getting data from other programs
into
re-
peating fields.
▪
Repeating fields are inherently limited
. If you create a field with 20 repetitions, then you get
20 spots for data. If you need less, you're saving space for empty fields all the time. If you need
more, you have to modify your fields and then make room on your layout. A relational data-
base, on the other hand, can grow and shrink as needed without modifying the database struc-
ture at all.
