1 2 3 Previous Next 30 Replies Latest reply on Nov 17, 2016 8:48 AM by TSGal

    Unstored calc field search unreliable if file hosted by server

    VincentL

      Summary

      Unstored calc field search unreliable if file hosted by server

      Product

      FileMaker Server

      Version

      11.0v5 (bug people noticed it in earlier version also), maybe 12 didn't test

      Operating system version

      Mac OS X 10.8.3

      Description of the issue

      This file is to illustrate a bug on unstored calc search.

      http://forums.filemaker.com/posts/b5e0e3a384

      On client there's no problem the "Normal script" will work ok. If the file is hosted on FMS 11v5 search result will be unreliable : that's to say that regardles of the changes of the Global key field, the founcount will be the same. But upon restarting that erroneously "constant" founcount may vary (it'd be either the correct foundcout of one of the prevoius search).

      So locally there's no problem, only if hosted on server which leads us to the Knowledge base article
      "How Functions Evaluate Differently on the Host Versus the Client in FileMaker Pro"
      Answer ID: 6996

      Conditions that prevent those finds from being performed on the host include:
      … An unstored calculation that uses a related field that is from a table in a different file.

      This is a bad limitation in my opinion, because it deefats the point of that optimization for many solutions BUT from it we can get our workaround.

      The workaround is this : include in your unstored calc a filed from another file to force Filemaker to do the search on client by preventing it to do it on host

      So there's no problem in the calc, only on the search done server side

      Please note the terrible speed degradation of the workaround (from 9 sec to 25 on my setup), because of course teh search is done on client.

      File is unprotected if Login / pass asked  : Admin / leave blank

      Steps to reproduce the problem

      File is here

      https://www.dropbox.com/s/8htzhwszommuigk/BugTest.zip?dl=0

      How to use the file :
      0. Host the files on Filemaker server 11 (11v5 also)
      1. Change the Global ky field with the pop-up menu
      2. Press "Normal Script" look at found count
      3. Press "Woraround Script" look at found count. It may be different.

      Expected result

      Correct found count are for Global key value
      3 - > 1931
      4 -> 19
      5- > 18

      Actual result

      Wrong foundset if using normal script

      What the script does is to do a search where the calc field is ≠ 1
      So you'll see using the normal script on server when you get not correct found count, that amongts the foundset, instead of just 0 you'll find records with 1.

      Workaround

      in unstored calcs you may search, make sure you call a related field that's in another file to force FMS not to perform the search on the server but on the client.

      But this is slower, unpractical to change all the calcs in the solution, and this bug can cause terrible consequences

        • 1. Re: Unstored calc field search unreliable if file hosted by server

               Vincent L:

               Thank you for the post.  

                

               The article mentioned indicates this is expected behavior. If you would like to see a change in this behavior, I would encourage you to enter this as a suggestion into our Feature Requests web form at:

                

          http://www.filemaker.com/company/contact/feature_request.html 

                

               These web form suggestions are monitored and read by our Development and Product Management departments and then discussed and considered for a future release. Although I could copy your post and paste it into the web form, there are some questions asked that only you can answer.

                

               TSFalcon

               FileMaker, Inc.

          • 2. Re: Unstored calc field search unreliable if file hosted by server
            VincentL

                 Hi TSFalcon,

                 I Think you did not try the file, nor read the linked forum discussion.

                 What the tech note says is that, as an optimisation feature, FMS will do the search server side. But it does not says that this will lead to completly false results.

                 It's impossible that someone at FMinc thought it's ok to have completly different foundset if you use the same file either locally or hosted. 

                 This is a serious bug that compromise data integrety (for instance reports can have a totally different results if solution executed locally or hosted).

                 Please take the time to read teh discussion I posted, and to try the file.

                 Thanks

                  

            • 3. Re: Unstored calc field search unreliable if file hosted by server
              VincentL

                   For the record te problem is this :

                   Do a search on local file -> foundcount 1931

                    

                   Do the same search with file hosted on FMS 11.0v5 -> foundcount 18

                   => It's not expected behaviour

                    

                    

                    

              • 4. Re: Unstored calc field search unreliable if file hosted by server

                     Vincent L:

                     Thank you for the reply.  

                      

                     I am testing the sample file now. I will update this thread with my results. Thank you for the clarification. 

                      

                     TSFalcon

                     FileMaker, Inc.

                • 5. Re: Unstored calc field search unreliable if file hosted by server
                  VincentL

                       Thanks, 

                       To test the file, do it first locally. You'll see that "normal script"will always end to the correct foundcount when you chnge global key field with the popup.

                       Then host it on server, try "normal script" changing the pop up selection, you'll see that you won' get the correct results. If hosted Filemaker seems stuck to same foundcount.

                       Hosted you see that the workaround script always gives good result

                  • 6. Re: Unstored calc field search unreliable if file hosted by server
                    philmodjunk

                         I found a reference in the Known Bugs List that sounds similar to what you have reported. Does it look similar to you?

                         For More Information see:     Finds on calculation fields computing values from global field based relationship fail

                         This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

                         It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

                    • 7. Re: Unstored calc field search unreliable if file hosted by server
                      VincentL

                           Yes looks like the same issue. But now with my file we have full understanding and workaround, so maybe this time Filemaker can correct it asap. It's a MAJOR bug (I don't agree with your moderate rating).

                           It seems to affect all unstored calcs (depending on on global at least, I didn't test it without global) which don't call fields from another file. That's a pretty big issue, that can lead to false reports etc…

                      • 8. Re: Unstored calc field search unreliable if file hosted by server
                        philmodjunk
                             

                                  (I don't agree with your moderate rating).

                             If you read the definitions at the start of the thread, you'll find that that "risk level" has a very specific context. Even a "nuisance" bug can have major consequences for a given solution and yet meet the definition for "nuisance" as it is solely focused on risks to file/data integrity. The only rating above moderate is "High" and I reserve that for bugs that crash FileMaker or have a significant likelyhood that they will result in large numbers of records being trashed with incorrect. I judged it "Moderate" as thought that is not an impossible outcome, it didn't strike me as a likely outcome when this bug affects the function of the typical database.

                             And it is quite subjective--my personal opinion modified by the opinions of others and has no bearing on how/when/if FileMaker Inc will or will not fix the issue in a future update.

                        • 9. Re: Unstored calc field search unreliable if file hosted by server
                          VincentL

                               Ok Phil,

                               By the way, I just retried without global, and there's no issue.

                               So conditions for the bug to appear :

                               involves a global field (which is a matching field of a relationship involved in the calc I guess, didn't try otherwise)

                               and all involved fields in the same file

                          • 10. Re: Unstored calc field search unreliable if file hosted by server

                                 Vincent L:

                                 Thank you for the additional information.  

                                  

                                 I apologize for jumping to conclusions earlier and missing the issue. My focus was on Knowledge Base Article #6996, How Functions Evaluate Differently on the Host Versus the Client in FileMaker Pro.

                                  

                                 Testing the sample files provided, I can confirm the report that this behavior exists when hosted from FileMaker Server 11.0v5. 

                                  

                                 Testing the files after conversion with FileMaker Pro 12 and hosted on FileMaker Server 12.0v4, I can confirm the issue no longer seems to occur, and both find scripts return correctly.

                                  

                                 I've added these new findings to the original report made to Testing and Development. 

                                  

                                 TSFalcon

                                 FileMaker, Inc.

                            • 11. Re: Unstored calc field search unreliable if file hosted by server
                              philmodjunk

                                   And thanks TSFalcon for reporting that it doesn't happen in FMP 12! I'll note this fact in The Known Bugs List....

                              • 12. Re: Unstored calc field search unreliable if file hosted by server
                                rkesk8

                                I am having this same issue with v12 and v13.  It is resolved when the server is rebooted, but tech support at our hosting company is now saying, "We are not able to continue rebooting the server because of this bug, as it causes service interruptions to other customers.  I would recommend implementing a workaround in your solution to not have to search on an unstored calculation instead, which is what other customers have done to address such.  It may also be possible that upgrading to FileMaker 13 would resolve the issue, but I can't be sure of that."   He's wrong about upgrading correcting the problem bc I experienced it with v13 as well with a different hosting company.  So tech support at both my hosting companies (pointinspace.com & 888.net) is aware of this issue and they have been experiencing it with other clients.  I have been working on replacing unstored calcs with number fields populated by scripts, but I have these types of calculation fields all over the program and it will be a huge task to replace them all.  I've been working with filemaker for close to 20 years and have never seen anything like this .. a bug that makes virtually all of my reports unreliable.  I hope filemaker will pay attention and fix it soon!

                                • 13. Re: Unstored calc field search unreliable if file hosted by server
                                  VincentL

                                  Hi Rebecca,

                                  Your are right, this bug IS STILL NOT FIXED IN 13 even though my file depicting it has no issue in 13 while it has in 11, I stumbled on this bug in some other situations in 13. So the situation improved in 13 (cause my file worked) but there's still issue.

                                  However my workaround works.

                                  Please try it.

                                  In the unstored calc, make sure to call a related filed from another FILE (not just table) for no purpose jut toy trigger the workaround.

                                  Let's say you have that calc

                                  related_same_file_table1::value * related_same_file_table2::value

                                  For the work around to work you have to then to write this

                                  Let(mytrigger=another_file_related_table_just_for_fun::value;

                                  related_same_file_table1::value * related_same_file_table2::value

                                  )

                                  Then this will work because it would force the server to avoid it's due (but broken) optimisation.

                                  P.S : I stress that we need that optimization to work, an even make it so it works even with separated files. The workaround introduces slowness.

                                  • 14. Re: Unstored calc field search unreliable if file hosted by server
                                    Fred(CH)

                                    Hello Rebecca,

                                    I also ran into this one several times, and we have at least 2 other related report on this forum :

                                    Finds on unstored calcs stop working in hosted file

                                    Search on aggregate related calc fail if hosted

                                    Recently, i realized (thanks to Jeremiah) that you only need to restart the Database service and not the entire machine, in order to solve it.

                                    But I am afraid that, as far i know, it never was replicated by FileMaker inc., since it occurs "rarely".

                                    However, it seem to be a long standing issue.

                                    Bye, Fred

                                    1 2 3 Previous Next