9 Replies Latest reply on Apr 17, 2012 12:31 PM by Stephen Huston

    Error performing find on calculated field

    user11685

      I have an odd problem with find requests returning different results on different PCs, in fact it looks like just one client has the problem at the moment but that PC runs lots of automated tasks so I need it working reliably. I have a hunch it happened to another user the other day but I cannot be sure. Plus I am really confused by what is causing the problem.

       

      Config on the PC with the problem is

      - Win XP SP3

      - FMP 11v3

       

      The field I am finding on is a calculated field which is the count of a related set of records. i.e. I am finding on lines on orders. I have stock items related to each order line so I can count how many stock items I need to put on order by taking the required quantity and subtracting the number of related stock items that I have already created. I call this field ngi_ToOrder

       

      When I do a find on this field such that

      ngi_ToOrder > 0

      I am finding records where ngi_ToOrder = 0

      In fact based on find results ngi_ToOrder for these records = 1 !

       

      If I loop through the items in this incorrect found set and omit if

      ngi_ToOrder = 0

      then that works fine.

       

      The find

      ngi_ToOrder > 0

      works fine on other PCs for the time being...

       

      Restarting Filemaker and/or the PC makes no difference.

       

      I thought it might be something to do with the file being broken so I did a "Save As Copy" and put it back on the server but this made no difference.

       

      I then did a restart on the FIlemaker Server and this DID fix the problem but only temporarily! It was back to going wrong within a few hours.

       

      I would be grateful for any tips on what to look at next. Many thanks in advance.

      Michael

        • 1. Re: Error performing find on calculated field
          psijmons

          Did you try to make this a number field that is calculated, instead of a directly calculated field?

          (UNcheck: Do not replace existing value...in the field definition)

          • 2. Re: Error performing find on calculated field
            user11685

            Thanks psijmons. To clarify, the field is an unstored calc so that it can work with a live tally of the related records.

             

            ngi_ToOrder = ngi_Qty - ngi_OnOrder

             

            ngi_Qty is a number field

            ngi_OnOrder = count ( stockItems::_k_StockItemID )

             

            Many thanks

            Michael

            • 3. Re: Error performing find on calculated field
              psijmons

              Are all fields involved set to be indexed?

               

              The fact that the issue creeps back in after a restart might be worrying.

              Did you try to run File Recovery on a copy of your file? Does this pass or do you get a message that there are problems?

              • 4. Re: Error performing find on calculated field
                user11685

                Hi there

                 

                Thanks for your reply.

                 

                I did not do a recovery as the file does not report any errors on verification. I did a "Save a Copy" instead to re-create the tables and indices.

                 

                The fields I am searching on an unstrored calcs so they cannot be indexed. The stockItems::_k_StockItemID field which I am counting is indexed automatically as part of a relationship.

                 

                Best Regards

                Michael

                • 5. Re: Error performing find on calculated field
                  Stephen Huston

                  Recommend you check:

                  • that the relationships  keys are all indexed on both ends
                  • that the calculations for the fields  are written in the context of the base-table for the layout on which you are performing this find
                  • that the calcualtion themselves reference only fields from the Table Occurances (TO) used in the relationship per the TO graph

                  If these are all verified and it still is unreliable (sometimes right, sometimes wrong), then something may be wrong with the file's Indexes.

                   

                  Stephen Huston

                  • 6. Re: Error performing find on calculated field
                    jeepboy

                    We're having a very similar problem.  Occasionally scripted finds on unstorred calcs will return the wrong results.  Actually the results it returns are correct, but incomplete (additional records should have been included the resulting found set).

                     

                    Restarting the server appears to solve the problem temporarily.  We usually get a week or so of good running before the issue comes back and a restart of the server is required.  To work around this we have scheduled a weekly automatic reboot of the server.

                     

                    However, yesterday (Monday) the problem showed up but it had only been about 26 hours since the most recent reboot.  This morning another user complained about results being incorrect, so we are going to reboot it again tonight in hopes of getting things back to normal.

                     

                    I'm concerned because we hadn't had this problem at all since scheduling the weekly reboot of the server - until yesterday.

                     

                    (BTW - Unfortunately we cannot index/store the calc results that we are searching on because the calc references related tables, which means it cannot be storred/indexed).

                     

                    Anyone else having this kind of problem or any suggestions would be most appreciated!

                     

                    We are running FileMaker Server Advanced 11.

                    • 7. Re: Error performing find on calculated field
                      Stephen Huston

                      If Michael's still reading, please let us know what happened in the previous instance of this problem.

                       

                      In addition to the things I recommended to Michael last year, check that the calcs are set to return the correct data type.

                       

                      Also check the key fields between the related tables on which the calcs depend are matching data types.

                       

                      Another issue may be indexing of some of the underlying fields used in the calcs. Reindexing may help.

                       

                      The fact that a server restart seems to clear this is puzzling to me. Are the clients staying connected for long periods without the server disconnecting them? If so, it is conceivable that the caches on a client machine could be stale, or the cache setting on the server needs to be smaller, more frequent.

                      • 8. Re: Error performing find on calculated field
                        jeepboy

                        Stephen,

                        Thanks for the suggestions!  We've checked the items you've mentioned and found no problems (prior to my posting here).  One additional data point - if we take the solution off the server and run it locally things work just fine - this problem only arrises when hosted on Server.  The fact that it works most of the time and occasionally stops working makes me think we have a server issue and the solution is likely not located within our database files themselves.

                         

                        Another interesting thing - if we know that certain records should be returned by the scripted find but are not, I can find one of those records another way and observe the field I'm searching on does indeed contain the correct data.  However, if I do a search and enter the value for that calc field as well as the serial number of one of the records known to exhibit the problem, FileMaker returns No Results Found (in an effort to isolate one particular record).

                         

                        I read on another forum that uninstalling FileMaker Server and re-installing it has resolved this issue for some folks.  I'm going to give that a try on Saturday, May 5 unless we find a better resolution to this issue before then.

                         

                        Additionally, I've found various posts across the net regarding this issue (oddly most are not part of FM Dev Community).  The conditions seems the same - searching on unstorred calc sometimes returns bad results, rebooting server restores capability for a limited period of time; searches work just fine and then they stop working even though no developer activity was going on in the system and the data set remained largely unchanged.

                         

                        Thanks!

                        • 9. Re: Error performing find on calculated field
                          Stephen Huston

                          And let us know how your efforts result. It's always important to hear back when there is a solution found.

                          Thanks.