How to Fix Index Corruption in FileMaker

How do I know if my FileMaker database has index corruption?

There are two recognizable symptoms of index corruption in a FileMaker database:

1. In Find mode, you search for a term in a field you know should yield a result, but nothing comes up.
2. In Browse mode, you have a portal that is supposed to show some related records, but it doesn’t.

Suppose you have verified that both the data and the code are correct, but you cannot find a logical explanation for why one of the two symptoms above is occurring. In that case, chances are that the field you are trying to perform a find on or one of the matching fields used in the portal relationship has its index corrupted.

What is an index? Why would does index corruption cause issues?

Database indexes in Claris function just like book indexes. An index of a field usually consists of an ordered list of the unique entries in that field, with pointers to the records where those entries can be found.

An illustration of how indexes help locate records

Just like book indexes, a database index can be used to locate records more quickly. In a FileMaker database, searching on an indexed field can be significantly faster than searching on a field without an index. A field is also required to have indexes:

• To be on the right side of a relationship
• To have value lists based on it (in theory, but we all know the magic value list trick)
• To use the Auto-complete using existing values field control option
• To use the Unique value and Existing value field validation options

When the index of a field is corrupted, it makes it difficult to locate records, resulting in one of the issues mentioned at the beginning of the article.

How do I fix index corruption in FileMaker?

It is not too difficult to fix index corruption. We need to delete the corrupted index and rebuild it.

Here’s a step-by-step on how to delete the index of a field and rebuild it:

How to delete indexes

1. Identify the field that you suspect has its index corrupted.
2. Go to Manage Database, Fields tab. Then find the field in question.
3. Double-click on the field to open up its configuration dialogue.
4. Go to the Storage tab on the field configuration dialogue.
5. Change the Indexing value to None AND check the Automatically create indexes as needed option.
6. Save the changes made and quit Manage Database.
   • At this point, the corrupted index has been deleted.
7. Find a layout where the field in question can be searched on.
8. Enter Find mode, and perform a find with the field in question.
   • With the Automatically create indexes as needed option checked, this will rebuild the index.

Conclusion

Corruptions may cause your database to behave in unexpected ways. Additionally, corruption can be hard to detect using regular debugging methods. Without the awareness of corruption, it’s easy to find yourself in a rabbit hole, searching for a solution that can’t be found using regular methods.

I hope this article can help raise awareness and help you identify index corruption during your troubleshooting and fix it quickly. If you’d like to learn more from us, you can check out our upcoming classes or FileMaker coaching.