Indexes Not Working?

This particular problem arose in a FileMaker v9 solution that we inherited from another developer. The system had been in operation for several years, it was operating as expected and the client was happy. All seemed good in the world.

Suddenly and without warning, functions that had been working perfectly stopped performing as expected. The client contacted us and explained that Function X wasn't working. As per Murphy's Law, it was a business critical function that handled inbound enquiries and needed to be fixed ASAP. Due to high staff turnover at this company it was difficult to get a concise description of exactly what the function did so we set about investigating the relevant layouts and scripts. The scripts responsible were overly complicated and used methods more akin to a v6 solution but the function has been working correctly and we were assured nobody had modified the relevant Scripts.

After much debugging I tracked the problem to a series of "Go To Related Record" (GTRR) script steps that were failing. I checked the relevant Relationships and everything looked good. They were based on a multi-line key but were logically correct and should have displayed the correct records. Instead, the GTRR step was resulting in a found set of zero records, causing the rest of the process to fail. After much head scratching I performed a Recover of the files - still no change.

After further investigation I checked the index values for one of the key fields used in the relationship. It returned a blank, the index was empty. I couldn't even perform manual searches on the field! I checked the field definitions and Indexing was turned on so it was not un-indexed by design, certainly the relationships defined for this file required the field to be indexed.

To "rebuild" the indexes for that field I turned indexing off and on again on the relevant Field Options > Storage tab and then performed a "View Index" command on the field. The indexes were re-calculated, finding was restored and the GTRR script step was now performing as expected. The database has since operated without further fault.

Perhaps we should have rolled back to a back-up of the database, prior to this issue occurring however I'm not sure whether it would have made any difference. Certainly the database didn't exhibit any other problems and a Recover of the file found no issues. Just a glitch in the Matrix perhaps?

Insight gained
Even though everything might look as though it should work, the underlying data will always determine the outcome.