Indexing Of A FileMaker Field Explored

From Dwayne Wright PMP, PMI-PBA, PMI-ACP, CSM
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Indexing is one of the most fundamental aspects of relational databases. It's what makes searches possible, relationship key fields to function, sorting happen faster and a number of other technical things happen in the database system.

If you were reading a FileMaker book, it would likely have an index in the back. This index would tell you on what pages you may find a topic or sub topic. For example, the topic "indexing" may be listed on pages 12, 50, 79 and 112. Indexing, as a topic, would be spread out over that many pages because it may be discussed in detail or only in passing as other FileMaker related topics are discussed. In theory, if the index was not there, you would have to scan each page of a book (front to back) to see if the word or topic you're looking for appears. One can see why an index is a critical addition for books, especially those which cover a multitude of topics. On the flip side, we should look at the drawbacks of having an index in the back of a book First, it makes the book bigger since the printed index takes up physical space. The more words you index for the book, the more pages the book will be. Also, if you decide to add or delete a sentence, paragraph, page or chapter in the book, chances are that you will need to rebuild your index to make sure it's up to date.

FileMaker is similar to a printed book except that it's also completely different. (Indulge me, I just love saying things like that!) FileMaker indexes focus on the data within a field. So if you have a FileMaker field indexed, FileMaker can look at it and go to the associated records quicker than it can if it is not indexed. For this reason, indexing is a key player in searches, sorts, reports and relationships.

Now FileMaker 7 makes a distinction between indexing words and indexing values. An indexed value can and often is a collection of words. For relationships and calculations, the index works on the value. Word indexes are not used in relationship keys. Word indexes are typically used in searches.

So if a database has two records with one field. In one record the field contains “I win” and the other contains “I lose.” The value index would return “I win” and “I lose.” The word index would return “I”, “win” and “lose.”

As a default, FileMaker fields are not indexed when you create them. You can manually set the indexing options for a field by accessing the fields options ( File Menu - Define Database - Fields Tab - Options button - Storage Tab ). There are 3 radio button options for indexing which are All, Minimal and None.

All - The most complete ( and storage intensive ) option that uses both word and value indexing.

Minimal - FileMaker can index a field by each word or each value and the minimal index setting is using one of those choices. There is no clear indication to the user within this dialog box if the minimal index option is using the word or the value index. When you select the automatic index creation option, it will generally select the minimal index option.

None - Does not allow the field to be indexed.

The "Automatically Turn On" option is done via a check box and will index a field when needed but will not store the index in the database. FileMaker will automatically index a field when a find or save operation is executed. The more you use your database, the more it will grow as you build more fields, add more data and do find/sort operations on new fields. This increase in size could be a problem if your about to run out of hard drive space and have no way of increasing the available space on the hard drive the database resides upon. As you can probably guess, running out of hard drive space on a "live" database is not an experience you want to have.

© 2010 - Dwayne Wright -
The material on this document is offered AS IS. FileMaker Pro is the registered trademark of FileMaker Inc.