Replacing fields to fix indexes

[amgdba]

I have a system I'm working on that could very well be the most mistreated system I have ever be exposed to. Too many relations, unstored calcs, legacy junk that nobody can remember if it's useful or not, etc, etc.

Couple of days ago it just stopped indexing a couple of fields, and then the next, and then the next, to the point now where it is clear we have a handful of fields that simply WILL NOT function after a recovery on the indexes is performed. They work for a few hours and then die.

So, my thoughts are that since they have no clean copy, backups are indicating damage as well, and a rewrite would take a month or two, to keep them afloat I was going to document the system, identify where all the fields are used, create new ones, export the data, import the data into the new fields, repoint everything to the new fields, delete the old ones, and cross my fingers the new indexes are stable and they don't have any other issues with the tables that would continue to cause problems.

Anyone ever try this and did it work?

[aholtzapfel]

What version are they running? anyone accessing the files with an unpatched copy of filemaker 8? That would destroy the indexing in the manner that you describe.

Just a thought.

EDIT: BTW if that is the issue, there may not be any serious to be done to get them back up and running other than preventing the unpatched copy/copies of FM8 from using the files and reindexing the fields one more time. (fm8, unpatched, seems to destroy indexing on whatever fields it touches, example, if they do a find on a field, that fields indexing is damaged and will affect other users reguardless of the client version they are using.)

Edited May 7, 2010 by Guest

[amgdba]

No, it's running in a 10 environment.

Anybody have any thoughts of 11 maybe being better at cleaning up messes? I haven't checked to see if any new features or improvements were included with the new release.

[Lee Smith]

Indexing problems have come up in the past.

IIRC correctly, one thing you might try, is to turn off indexing for the field, exit the Manage Database. Then reopen Manage Database and turn on indexing.

or, save the file as Compacted.

or both.

HTH

Lee

p.s.

I don't think a change to 11 will fix anything.

Edited May 7, 2010 by Guest
p.s.

[amgdba]

Thanks Lee,

That is good sober advice, but I fear that in spite of the indexes being turned off, the file compacted, and then turning the indexes back on, there will be legacy structural items within the file that are related to the field that will simply cause the same issues.

It's very frustrating to not have more knowledge about the internals of FM files so I can be sure if the field itself will be the problem or if the index is the problem independently.

I'm going to keep that one on the list as the client has "ixnay'ed" the down time on the field replace for now, so maybe that will be the best option.