Thank you for the post.
To clarify, the scripts are being run by FileMaker Pro 12.0v4 from the file hosted by FileMaker Server 12.0v4?
In a new file, I tested the first two of your examples with 2 new scripts:
1. Performed a find (both scripted and unscripted) on an unstored calculation field for "<0"
Result: A single negative test result is correctly returned.
2. Performed a find (both scripted and unscripted) on an unstored calculation "count" field located in the header for ">3"
Result: All 6 records correctly returned as the field's value was 6.
My finds worked as expected. I can test the 3rd example, but I need further clarification on the following:
"counts the number of lines on an order using = to find only those orders with no lines"
What does the count calculation look like? What are you searching "=" to? For example, the value of the field is "5" but when I search "=5", then no results are returned.
Anything else that might help me replicate?
Examples one and two are run by Filemaker Pro 12v4 clients, our server is Server Advanced 188.8.131.525, example three is a server-side script run as a scheduled task each evening.
Example three performs a find where the order status is 'Active' and the number_of_lines (the unstored calculation) is '=' as per the attached image. Normally this would find only those orders where no lines exist but on Friday evening it resulted in all Active orders being found and marked as cancelled as though the number_of_lines field was empty.
As I mentioned above, this problem is infrequent at best and can be cured by rebooting the server. We have seen it with previous versions but I can't remember when it first appeared although it was at least a couple of years ago as the person it affected left the company in late 2011. Post-reboot our server behaves as expected but this problem may re-occur at some point further down the line.
I can't help but wonder if your file might be the victim of some latent, longstanding file damage--something that could be very difficult to detect.
You might run a recover on a recent back up copy of your file to investigate that possibility.
It's a possibility I suppose but each of the examples I gave comes from a different file so I hope that's not the case! I'll try recovering a backup of one of the files and see what results I get.
Other people have seen the problem as there's a thread on the Technet forums about it which has the same solution, reboot the server and everything goes back to normal.
I have checked a couple of the affected files and happily (for me at least) a Recover didn't uncover any problems.
That said, having stated that it was an infrequent occurrence, we have the same problem again this afternoon, less than 48 hours after the restart.
Some further testing has revealed that the problem seems to lie with the Aggregate functions, I have tested Sum, Count, Max and Average and they all fail. I changed a test field from Sum( related_field ) to Month( related_date_field ) and finds on that field work as expected.
Interestingly, sorts on the affected fields work perfectly, it only appears to be find requests which fail to evaluate the calculation and therefore return null values.
there's a thread on the Technet forums about it
It would be helpful to post a link to that here.
We are having this problem for two years now.
Still a bug.
Only solution is to replace the search on the unstored calc in a loop over the found set and omit the unwanted records...
I have made a short movie about the bug... http://youtu.be/vXTPrKgnCeo
After restarting the server: everything OK ( for about three months )
Any better solutions always welcome...
We have exactly the same problem with FileMaker Server 14 on a third-party hosted server, which is a real pain because the hosting provider understandably dislikes restarting a server that hosts multiple client solutions. If we take the solution off the server and open it locally, it works fine... so the problem is clearly a server issue.
Apparently, this issue has been resolved in Server 15.0.2 - according to a post by TSGal at the end of this thread - Unstored calc field search unreliable if file hosted by server - but Server 15 is single customer only due to the licensing change, which would increase my customer's hosting costs fivefold or more.
Extremely disappointing that this fix has not been applied to Server 14, even for customers on annual licences.