Have you ran recover on the file to verify there are no errors in the databases? If you haven't I would recommend running recover.
If the recovered copy sorts correctly, it could be that the recover fixed the problem by rebuilding a damaged index. You can use advanced recovery options to rebuild your indexes without making any other changes to the file:
If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:
- With the file closed, select Recover from the File Menu.
- Select "Use advanced Options"
- Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
- The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.
I ran Recover & Advanced Recover as suggested & the problem became worse. I made a copy so I would not have to worry about my actual file. I have not added any more inventory ,but will soon.
I would have simply turned off indexing for the number field by which you're sorting, closed Manage Database, reopened it, and turned indexing back on. This should rebuild your index.
Which is a method that should produce results identical to the advanced recover method I described eariler. I recommend the advanced recover method most of the time here in the forum as it avoids the issue of correctly selecting the right field for which to rebuild the index as it rebuilds all indexes in the file.
The fact that "the problem became worse." when the file was recovered suggests that either there is some other issue invovled or that the file is damaged beyond the recover tool's ability to repair the file.
Deirdre, can you describe exactly what you mean by "the problem became worse"? There might be a clue there as to what's going wrong here.
is it possible that numbers 41 and 42 have a leading character (eg a space) - and we're all assuming that it's a number field, but is it possibly a text field ?
Good point. I had ruled that out in my own thinking because if this is a text field, you'd expect to get other incorrect sorting such as 11 sorting before 2, but perhaps these numbers are padded with leading zeroes...
Sorry for the slow response--by "got worse" I meant that more numbers were showing up out of order than before running the recover. Should I assume the "turning off indexing for a single field" method would fail as well? I don't know how to do that. Thanks!
It's just another way to rebuild the index for a field. It should not produce any different effect from using the advanced recover option. The only difference is that the Advanced Recover method rebuilds all the indexes for all fields while enabling, disabling indexing for a single field rebuilds the index(es) for just that one field.
Is this a field of type number?
The file type is "number" although it lets me enter whatever characters I want (all records currently have 4 digits--all numeric).
Then I must repeat what I posted earlier:
Either the file is damaged or there is some factor we can't see without examiniing an actual copy of your file.
I don't recall if I suggested this before, but since rebuilding the indexes did not resolve this, try a full up recover and see if any issues are discovered and even if no problems are reported by the recover process, test the recovered copy to see how records sort.
Things to keep in mind about Recover:
While Recover almost always detects and fully corrects any problems with your file...
- The recovered copy may behave differently even if recover reports "no problems found".
- Recover does not detect all problems
- Recover doesn't always fix all problems correctly
- Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.
And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).