The FileMaker® Pro Indexes and Related Problems
"Ooh! My Record shows just question-marks in all fields, what's that?"
What you should know about FileMaker Pro Indexes
In the Options for a Field the user can choose between an Indexing of None, Minimal, and All. While None seems clear (no index exists) this does not mean, you can't search in this Field - it just takes significantly longer, since the search field of every record has to be compared to the search criteria.
By contrast the options "Minimal" and "All" are not clearly explained in the FileMaker Help (quote):
| Select | To |
|---|---|
| None | Prevent FileMaker Pro from indexing the field. |
| Minimal | Create a value index of a text field’s contents or a calculation field returning text results. |
| All | Creates both word and value indexes for text fields or calculation fields returning text results. For number, date, time, and timestamp fields, as well as calculation fields returning results of these types, All creates an index of a field's values. |
The meaningless "Minimal" would be better substituted by "Value Index" and "All" by "Value and Word Index". So what exactly is a Value and a Word Index?
| Index type | Description |
|---|---|
| Value Index | This index is needed only for Relationships. In a text field, the first 100 characters of each text line - regardless of being spaces or "real" characters - are taken as the key entry. The remaining characters and words are ignored. Numeric fields - these include date, time, time stamp, and number fields - use up to 400 digits for the value index. Using numeric indexes (containing no spaces or other characters) is therefor very space efficient. |
| Word Index | This index is used to Find all records in a table that (for example) contain specific words (or parts thereof) within a field. |
Technically defined:
An index is stored as one bit per corresponding Record ID. So for example, in a Base Table, every new Record sets exactly one bit at the position corresponding to its ID. Deleting a Record clears that bit. Deleted Record IDs are never used again. There are many kinds of indexes:
| Index Type | Bits representing the Record IDs in a Base Table of |
|---|---|
| Record | all existing Records |
| Value | indexed Fields used in Relationships |
| Word | a unique Word per Field, used in Find |
| Found Set | the current Found Set. |
So what problems can arise, how are they recognized, and possibly cured?
As we have seen, a single bit can make a big difference for the functionality of a file. How do I know there is an index problem with my file? Depending on the Index Type there are different symptoms indicating a flaw.
| Index Type | Symptoms indicating this error are: | |
|---|---|---|
| Record | After Show all Records, a Record entered earlier is not shown or all fields appear as a question-mark. | |
| Value | A Portal or a Go to Related Record either shows more than or less than the expected number of matching records, or all Fields of one Record appear as question-marks. | |
| Word | After a Find either more than or less than the expected number of records containing the searched Word are shown, or all Fields of one Record appear as question-marks. | |
| Found Set | This case has no practical relevance. |
Depending on the Index Type there are different methods to salvage the index - if at all possible.
Before any further action, make sure to use FileMaker Pro 9.0v3 or later, which fixes a bug that might make FileMaker Pro under specific conditions "forget" to reset an index bit and hence such non-existent Records show up with question-marks in all fields. Update occasionally used NoteBooks too.
Please note: simply updating FileMaker Pro does not cure any pre-existing index flaws. These have to be "repaired" (see below).
| Index Type | Method to repair the Index |
|---|---|
| Value or Word | Make a Clone of the original file and Import the data from the original file, table by table. This makes sure all indexes are being rebuilt and are up-to-date. This problem is a frequent issue. |
| Found Set | This index is rebuilt with every new Find and hence never an issue. |
| Record | This Index can NOT be repaired. If a Record (that would otherwise be lost) can be shown through a Find or Go to Related Record, Export it to a new table or file, delete it in the original table, and then re-Import it (auto-enter option off!). The original data is rescued although it now has a new Record ID. This problem almost never appears though. If you can't delete a Record with all fields containing question marks - and you usually can't - make a Clone of the original file and Import the data from the original file, table by table. This makes sure all indexes (including the Record Index) are being rebuilt and are up-to-date. |
I hope this helps to better understand index problems, also known as Ghost- or Phantom-Records.
Suggestions, opinions, experience reports and other hints are always welcome via our Contacts page.
This page will be extended/completed as time permits.
Examples are provided "AS IS" without warranties of any kind. Use at your own risk.
© 2005 - 2015 Winfried Huslik †. © 2024 Jürgen Geßwein. All Rights Reserved. FMDiff and FMVis are trademarks of Jürgen Geßwein, Augsburg, Germany. FileMaker is a trademark of FileMaker Inc., Santa Clara, CA, USA. Other trademarks mentioned are property of their respective owners. This web site has not been authorised, sponsored, or otherwise approved by FileMaker, Inc.