I would suspect the file over the server. Filemaker indexes do get corrupted from time to time and you may need at least to re-index the problem field. In you case, your ustored calculations may draw data from a stored field with a bad index.
You ultimately may need to replace the file with an older back up as there could be problem with the file that causes the index to go bad.
You can re-index fields individually by turning indexing for that field on and off. You can re-index all fields by importing your records into a clone of your file. Recover also rebuilds your indexes though I don't recommend using a recovered copy for regular use.
Try to reindex the key fields in the relation that your unstored calc referrers too.
Sorry tried that (reindexing that is). All the databases (all 124 files) show this issue. I can create a new database, upload it and it will exhibit the same issue. It is only resolved by restarting the FMSA database server. It is not an index issue (not on the file level anyway.) When it starts happening again in a month or so, I will have a chance to do some more exploration and get back to you all.
A more detailed description of the calculation fields involved would also help us explore the issue.
Any find on any unstored calc will fail as long as all of the references are contained within the file that contains the calc. This is what leads me to believe that the Server is at fault because:
A: Only a server restart will fix the problem (other than opening the files locally with a client -Win or Mac)
B: In the above scenario, it is the server, not the client that solves the calculation prior to searching the results.
When I created my test database (this was done to remove any variables introduced by using a corrupt file), I just created 2 tables (Master and Items). I then created a key field in each table and created a relationship based on those fields. I then added a number field called "QTY" in the items table. I then created a calc field in master that Sum(Items::QTY) with its result being a number. Prior to restart I could not search that field and get results, after restart of server, it perform as expected.
Our server hosts 124 files, about 10 of which are multiple table files. Initially this solution first began life in FMP 3.0, hence the number of files required to normalize the data. The issue only arises in the multi-table files that have been either developed or consolidated post FMP 7