Almost every database platform allows fields to be indexed, with FileMaker being no exception. Indexing fields allows for faster search and retrieval within the database, particularly when it's hosted (rather than local) and regularly used by multiple people. Periodically, indexes also need to be rebuilt. As they grow over time and usage, they can become fragmented or corrupted. Record creation and deletion impacts indexes. To recover from these and prevent additional disruption, the indexed fields should be rebuilt. But how is that accomplished?

Step One: Identifying and Locating Indexed Fields

To begin, you’ll need to know which fields are indexed and which will need to be rebuilt.

1. Open the Tools menu

2. Click on Database Design Report…

3. In the dialog box that appears, select XML output

4. Click Create

5. Open FMPerception

6. Open your DDR within FMPerception

7. Look at the Fields (flat) option

8. This is a list of ALL fields in the database, so sort the results based on “Base Table”.

9. Click File > Export Results to CSV…

10. Open the CSV file -- you can remove unnecessary fields if you’d like, but make sure to keep the Name, Base Table, Index, and Validation fields.

There are three columns you’ll want to pay attention to:

1. Name: This it the name of the field

2. Base Table: This is where in the database the field is located

3. Index: This tells you if the field has been indexed, and what kind if so

Step Two: Removing Indexing

1. Open your database

2. Open the database manager

   1. Command + Shift + D, or

   2. File > Manage > Database

3. Go to the Fields tab

4. In the Options / Comments column, look for Indexed listed to indicate an indexed field

5. Select a field that’s indexed

6. With the indexed field selected, click the Options... button

7. Go to the Storage tab

8. Under the Indexing header, identify if the field has Minimal or All indexing

Step Three: Adding Indexing

1. Change the Indexing radio button to None

2. Uncheck the Automatically create indexes as needed checkbox

3. Click OK to exit the Options menu and return to the database manager

4. Confirm on the database manager that the field is no longer indexed

5. Repeat for all other index fields

6. Click OK to exit the database manager and return to your layout (see below for validation error message)

7. Reopen the database manager

8. Go to the Fields tab

9. Select a field that needs to be indexed

10. Click the Options... button

11. Go to the Storage tab

12. Change the Indexing radio button to Minimal or All as needed

13. Check the Automatically create indexes as needed checkbox (if needed)

14. Click OK to exit the Options menu and return to the database manager

15. Confirm on the database manager that the field is now again indexed

16. Click OK to exit the database manager and return to your layout

Troubleshooting: Validation Option Error Message

This validation error occurs when a field has had its indexing changed to none, but it has validation requirements that require it to be indexed.

1. Click OK on the error message

2. Return the indexing options to that the field originally had

3. Go to the Validation tab

4. Under the Require header, identify if the Unique value checkbox is selected

5. Uncheck it

6. Go to the Storage tab

7. Proceed as instructed above to continue deindex the field

8. When you reindex the field, reselect the Unique value checkbox in the Validation tab

PK Information is a FileMaker-certified development agency serving the Tampa Bay, Miami Lakes, and Knoxville regions. We believe software should work the way you do, with business priorities first and technology second.

LEARN MORE

Would you like to learn more about rebuilding index fields in FileMaker and the benefits from doing so? We’d love to discuss the possibilities with you! Please complete this form and we’ll connect shortly.