I doubt that you've hit any kind of limit here. Hidden damage to your file is more likely.
Test a compacted copy (save a copy as... with the compacted copy option).
Test a back up copy
Save a clone of the file and import all your data into the clone (this rebuilds all field indexes), and test it.
Recover the file and test the recovered copy. If the recovered copy works, replace your file with an undamaged back up copy if at all possible.
Are you working on the database locally, or on a server? Are you using global fields on the parent side of the relationship to limit the related records sum'd on the child side?
Hidden damage it is! I saved a compacted copy and was able to perform finds successfully. Thanks for the advice.
...and, of course, knowing the solution makes it easier to find the information that would lead you to it. "About recovering FileMaker Pro files" from the FM online help mentions using compacted copies to troubleshoot bad find results, and I'll be quicker to check for database damage in the future.
In answer to etripoli's questions: the database is hosted on a server, and the relationship did use an unstored field on the parent side to limit results -- one good thing about this episode is that it's pushing me to find ways to keep as much of the relationship indexed as possible.
Thanks for the response.
Glad you got it fixed.
These days, I don't trust that re-indexing or compacting a file fully fixed the problem--it might have or it might have fixed the symptom of a deeper problem.
I usually go ahead and run a recover on a copy of the file just to see what the recover operation reports. If it says no problems found, great, toss the recovered file and keep using the original. If it still finds a problem to fix, I take my newest backup file and start testing it with the recover tool, moving backwards through the back up copies until I get a file that recovers cleanly. I then take a clone of the unrecovered back up file and import my most recent sets of data into it.
It looks like I spoke too soon. I'd been working with an offline copy of the database (made after the search stopped working), which recovered without errors and was able to run searches on the Sum fields. I thought that was the fix, but I had to wait to schedule a time to take the live copy of the database on the server down. Now that I've done a recovery on the current file and copied it back to the server, I can see that it's definitely a problem with opening the file via the server (9.03). The same file when opened locally can perform the find, but when opened via the server returns no results.
Any advice? I've tried increasing the RAM cache on the server (up to 90 MB).
Thanks again for the help so far.
Do you have any global fields involved in your calculations?
They behave differently on a networked DB as opposed to a stand alone copy.
After you did a recovery on the copy from the server, did you open it locally? This seems to be a required step to get the database to open properly on the server again. Also, if you didn't use the Upload... function in the Admin Console, verify the file permissions.
Thanks for the questions! Answers:
1) No global fields involved in this particular calculation. Just a sum over related records using literal data or stored calculations on both sides of the relationship.
2) I did open the recovered file locally before copying it back to the server (this is how I know that a find on these fields got results when opened locally). That's an interesting fact to know, though, and I'm definitely going to remember it.
3) It's true that I copied the file to the server database folder instead of using the Upload Wizard -- however, I just uploaded a test database to that server and verified that the filesystem permissions (Windows 2003 Server) are the same as the file in question.