Queries on Unstored Calculation Fields Return Erroneous or No Results with File Hosted on FileMaker Server 13
Operating system version
OS X 10.9.5
Description of the issue
We've been running on FMS 13 for several months now and have never experienced this issue. Suddenly, last week, we discovered that queries on unstored calculation fields return erroneous or no results.
I am under the general opinion that unstored calculations should be avoided in most (nearly ALL) situations (especially on fields that users have the ability to query on), but the fact is that the system uses a lot of them and that is the reality right now.
In searching the forums, I found that other users have experienced similar strange behavior and that the issue did not present itself on the same file when run locally (only when it was on server did the behavior exist). I confirmed this finding with our file. Furthermore, I uploaded the file to a different machine running FM Server and the queries worked fine. So, I decided to restart the DB server running the actual file, and lo and behold, the queries worked afterwards. That was 3 days ago. Today, the issue has reappeared. I don't know what could be causing the issue, and I don't want to have to restart the DB server every other day for the rest of my life. I am working on some work arounds now to help the users, but the short of it is that the queries should work but they don't (consistently).
Steps to reproduce the problem
In our system, I can do a query for Order 1000 . . . thus, I have one found record. There is an unstored calculation on the Orders table that adds up the total of the line items on the order. Let's say the calculation shows a total of $525. If I do another query and enter "525" (or "<500", or "<600", or any other value that should return the order), and then constrain the found set, I get the lovely "No found records..." message.
In certain scripts that do complex queries that include unstored calculations, there are found records, but they don't match the request (i.e. the found records include those that should have been filtered out by the parameter on the unstored calculation field during the query).
The correct found set.
Incorrect found set.
Restart FM Server