You can never be sure that a file is not damaged. You can only confirm that it passes a consistency check (verifies on backup is also a consistency check) and/or that recover reports no problems found. THe file could still be damaged and the issue could be one that those checks do not detect. The fact that your back up copy works strongly indicates that there is a problem with your file.
An issue that they cannot detect and which could explain the behavior you are seeing is that you have a corrupted index or two, But you've also indicated that this is an unstored calc. From the syntax given, there's really no need to make this calculation unstored and finds on it will be faster if it is stored, but maybe one of these fields is from a related table and you left out that syntax? If one of the two fields is from a related table, there are several other issues that you could check.
I suggest using recover to test yoru file in the following ways:
Do a recover on a copy of your file and test the recovered copy to see if finds work as expected even though you report that recover does not report finding any issues. If the recovered copy works for you, try the following less drastic version:
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.
Test this recovered copy and see if it also works. While I do not generally recommend using a recovered file if there is a back up available. One were only the indexes were rebuilt with advanced recovery options is my one exception to that rule.
If all else fails, save a clone of your back up copy that works and import all of your data from your current file into the backup, updating serial number settings as needed.
Update - Both a hard recover and just rebuilding theindexes completed without error. Both copies of the file work ok when not hosted on the server. Both copies fail in the same way when hosted on Server Advanced 22.214.171.124. So to my mind two possibilities remain:-
1. The file is damaged in a way that Recover does not recognise.
2. There is a bug in FileMaker Server or the server installation is somehow corrupted.
Next step I'll try is to upgrade system software to latest version.
You never explained why the calculation is unstored. Is this a a calculation field that references a field in a related table?
There are issues to check on a relationship if this is the case--but nothing I can think of is an exact match to this situation. If the relationship, for example, is based on an unstored calculation field, a find that specifies criteria in the related table's fields can trigger an error message even though the relationship works, but you are not getting an error message. Likewise, some users get confused over results when they reference fields in a related table in a find where the related record is from the "many" side of a relationship, but this also does not seem to be the case here.
If there is a relationship referenced in this calculation, is a match field used in this relationship a global field? There is a known bug that pertains to finds and is server specific that then might apply. I initially did not consider that a possibility as the syntax of your original calculation example did not indicate that you were referencing a related table.
Stock_Level_Check (the field in question) =
Stock_Level ≠ Stock_Level_index;
Stock_Total_at_Locations - Stock_Total_Confirmed_UnProcessed + 0
Sum ( vSL_vSI::Stock_Level )
Stock_Level_index is an indexed, non-global number field
Did you ever get a resolution to this issue?
I created an unstored calculation that references related fields in a file on my local drive. Last night I copied that field to the online version of the file on the FM Server. It appears to display the correct data but when I try searching or sorting by this field it fails. Searching comes back as "No Records Match This Find Criteria" and sorting just doesn't work. The records do not sort properly and there is no error message.
Found my own solution. I had the calculation set as number instead of text. No wonder I was getting odd behaviour.