10 Replies Latest reply on Nov 8, 2016 3:20 AM by ccc

    Finds which include unstored calculation fields fail

    MichaelKirby

      Summary

      Finds which include unstored calculation fields fail

      Product

      FileMaker Server

      Version

      12

      Operating system version

      Windows Server 2008

      Description of the issue

      We have seen three issues with this over the last couple of days.  Unstored calculation fields behave as if there is no data in them when referenced by find requests either in scripts or when performing a find in Filemaker Pro.

      Example one, Stock.

      A script performs a find for all stock items where the quantity on hand is negative (<0) to indicate purchasing requirements.  This now returns all stock items regardless of the stock level.

      Example two, Invoices.

      The scripted find references a field in the invoice header which counts the number of lines on the invoice.  This now returns no results using greater than or less than/equal to operators.

      Example three, Orders.

      A housekeeping script references an unstored calc which counts the number of lines on an order using = to find only those orders with no lines in order to mark them as cancelled at the end of the day.  This now finds ALL active orders as if the value visible in the field is not present.

      This problem is unpredictable and occurs very infrequently, the only cure we have found is to reboot the server.  We have seen this problem in previous versions of Server although I can't recall exactly when we first saw it.

      Steps to reproduce the problem

      Sit and wait, the problem occurs very infrequently and with no obvious warning signs.

      Expected result

      Finds work as normal.

      Actual result

      Finds return results as though the unstored calculation fields contain no data.

      Workaround

      Reboot the Server.

        • 1. Re: Finds which include unstored calculation fields fail

          Michael Kirby:

               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?

                

               TSFalcon

               FileMaker, Inc.

          • 2. Re: Finds which include unstored calculation fields fail
            MichaelKirby

                 Hi TSFalcon,

                 Examples one and two are run by Filemaker Pro 12v4 clients, our server is Server Advanced 12.0.4.405, 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. 

                 Thanks

                 Michael

            • 3. Re: Finds which include unstored calculation fields fail
              philmodjunk

                   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.

              • 4. Re: Finds which include unstored calculation fields fail
                MichaelKirby

                     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.

                • 5. Re: Finds which include unstored calculation fields fail
                  MichaelKirby

                       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.

                  • 6. Re: Finds which include unstored calculation fields fail
                    MichaelKirby

                         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.

                    • 7. Re: Finds which include unstored calculation fields fail
                      philmodjunk
                           

                                there's a thread on the Technet forums about it

                           It would be helpful to post a link to that here.

                      • 8. Re: Finds which include unstored calculation fields fail
                        MichaelKirby

                             Link to "Finds on Unstored Calcs Don't Work On Hosted File" - https://fmdev.filemaker.com/message/68741

                        • 9. Re: Finds which include unstored calculation fields fail
                          JanStieperare

                          Hi everybody,

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

                          Grts,

                          Jan

                          • 10. Re: Finds which include unstored calculation fields fail
                            ccc

                            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.