6 Replies Latest reply on Sep 27, 2016 9:29 AM by TSGal

    Queries on Unstored Calculation Fields Return Erroneous or No Results with File Hosted on FileMaker...

    honeycomb

      Summary

      Queries on Unstored Calculation Fields Return Erroneous or No Results with File Hosted on FileMaker Server 13

      Product

      FileMaker Server

      Version

      13.0.4.400

      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).

      Expected result

      The correct found set.

      Actual result

      Incorrect found set.

      Workaround

      Restart FM Server