1 2 Previous Next 15 Replies Latest reply on Mar 22, 2011 7:59 AM by mps1773

    Can not search on calculated field

    alan_barker

      Title

      Can not search on calculated field

      Post

      I've just discovered a significant change in v10.... I can no longer serach on a calculated field.  I have a Parent/Child set of tables.  Within the Parent table, I have a count calculated field that simply provides a calculated value of the number of children.  Works fine, and in version 9; I could search for all parents with <5 children (for example). 

       

      Now within Filemaker v10 (both Pro and advanced), the search will fail and report back that there are no records that meet your criteria. Even when you are viewing a record that fits that criteria.

       

      This strikes me as a bug, has anyone else seen this behaviour?  Anyone found a way around it?

       

      Thanks,

      Alan

        • 1. Re: Can not search on calculated field
          philmodjunk
            

          AlanBarker wrote:

          I've just discovered a significant change in v10.... I can no longer serach on a calculated field... has anyone else seen this behaviour?  Anyone found a way around it?


          I haven't had any problems searching calculated fields using FMP 10.

           

          Whenever I encounter finds or sorts that produce obviously incorrect results, I test a recovered copy of the file to see if I get the same results as sometimes an index has gotten corrupted.

           

          You can also carefully compare the field's definition in both original and converted files. When I converted my files, from 5.5 to 10, I found some unexpected differences that affected the field's behavior in a few rare cases.


          • 2. Re: Can not search on calculated field
            TSGal

            AlanBarker:

             

            Thank you for your post.

             

            I have tried this on both a Mac (OS X 10.5.6) and Windows (XP) machine, and I cannot duplicate the problem.  Here are the steps I took:

             

            1. I created a database file, PARENT.fp7, with the following fields:

            ID (Text)

            Name (Text)

             

            2. I entered two records:

            ID - Name

            1 - TSGal

            2 - AlanBarker

             

            3. I created another table, CHILD, with the following fields:

            ID (Text)

            Name (Text)

             

            4. I entered the following records:

            ID - Name

            1 - Anne

            1 - Barbara

            1 - Connie

            1 - Denise

            1 - Erica

            2 - Frank

            2 - George

            2 - Harry

             

            5. I then went into "Manage -> Database..." and clicked on the Relationship tab.  I connected the ID field from PARENT to the ID field from CHILD.

             

            6. I went to the PARENT Layout and created a portal into CHILD showing 8 rows.

             

            7. In Browse, I see the 5 records for TSGal, and the 3 records for AlanBarker.

             

            8. I returned to "Manage -> Database..." and clicked on the Fields tab.  I selected the PARENT table and created, COUNTER, a calculation field with the formula:

             

            Count ( CHILD:: Name )

             

            9. I returned to Browse, and performed a Find on COUNTER for: <5.  Only one record is found (AlanBarker).

             

            Please let me know what I'm doing different than you, and I'll try to duplicate it here.

             

            TSGal

            FileMaker, Inc. 

            • 3. Re: Can not search on calculated field
              alan_barker
                

              The failure occurs when you host that file on Server 10 Advanced.  I should have elaborated on that aspect, sorry.  If you take your example file that you created, host it on Server, and then open it as a remoted database, your find will not work.  Strangely enough, it will work, if you open the very same hosted database using FileMaker 9.

               

              Alan

              • 4. Re: Can not search on calculated field
                philmodjunk
                  

                AlanBarker wrote:

                The failure occurs when you host that file on Server 10 Advanced.  I should have elaborated on that aspect, sorry.  If you take your example file that you created, host it on Server, and then open it as a remoted database, your find will not work.  Strangely enough, it will work, if you open the very same hosted database using FileMaker 9.

                 

                Alan


                 

                Alan,

                 

                I search calculated fields all the time on files hosted with FMS 10. I don't experience a problem. You're going to need to provide more detail to help run this down.


                • 5. Re: Can not search on calculated field
                  TSGal

                  AlanBarker:

                   

                  The file was moved to FileMaker Server 10 Advanced.  As a guest on one machine, I searched for <5, and the one record appeared.  On another machine as a guest, I searched for >4, and only the other record appeared.

                   

                  If you have a file where this is failing, I would definitely like to see it.  I have sent you a private message (top of this screen - right side - X Messages) with instructions where to send the file.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Can not search on calculated field
                    alan_barker
                      

                    Well, I've sent the example file to TSGal, it is fairly awkward sending it via some intermediary, who then forwards it onto you... si there not a more direct method of communicating?

                     

                    I gather that the implication is that my installation of FMP10SA is not right?  It is not like there is a setting where you turn off the ability to search on a calculated field, it worked before I upgraded to FMPS10A.....  my question to you was should I reinstall?  Obviousl I can not do that during busniness hours, so I need to be convinced taht this will solve my problems.....

                     

                    Has anyone else experienced issues like this?

                     

                    Thanks,

                     

                    Alan Barker

                    • 7. Re: Can not search on calculated field
                      jsalzer_1
                        

                      Hey, Alan,

                       

                      I don't have FM10, but I do recall that one version (I believe it was 7.0v2) had an issue where unstored calculations couldn't be searched, because the server thought that the desktop was performing the calculations, and the desktop thought the server was performing the calculations.  It was an attempt at distributing labor that left the labor undone.

                       

                      Could this problem have re-surfaced?

                      • 8. Re: Can not search on calculated field
                        TSGal

                        AlanBarker:

                         

                        I sent a reply back through our Technical Support liaison, but obviously, you haven't received it yet.

                         

                        I still cannot duplicate the problem.  I am able to search without a problem.  Can you tell me more about your setup?  That is, what OS are you using for server and client?  I want to try and set up the computers here in a similar manner.  I really would like to duplicate the problem.

                         

                        There does seem to be an issue recently reported where access privileges based upon a global variable will not find records, but looking at your sample file, this isn't the case.

                         

                        TSGal

                        FileMaker, Inc. 

                        • 9. Re: Can not search on calculated field
                          alan_barker
                            

                          I am not at work today, we have God Friday off... never-the-less, here is my setup:

                           

                          I'm running FMSA v10 on a hefty Dell PowerEdge with 4 Gbytes of Ram, and a terrabyte of HD.  OS is MS Server 2003.

                           

                          Alan

                          • 10. Re: Can not search on calculated field
                            TSGal

                            AlanBarker:

                             

                            Thank you for the information.

                             

                            I found a Dell running Windows Server 2003 with 512 MB of RAM running FileMaker Server 10 Advanced, so that is about as close I can get.

                             

                            I loaded your testfile.fp7.

                             

                            Although you didn't provide me the client setup, I logged on as a guest from a Dell Dimension running Windows XP, and also from an iMac running OS X 10.5.6.  In both cases, I am able to search the "count" field for <2, <5, and >3, and all three found sets are correct on both computers.

                             

                            I really do want to duplicate the problem.  It's much easier when I can duplicate the problem, because I can then find the cause.  Trying to come up with a way to make it fail seems to be much harder.

                             

                            TSGal

                            FileMaker, Inc. 

                            • 11. Re: Can not search on calculated field
                              alan_barker
                                

                              Well, here is an update on my status.  Last week the Server-2003 machine hosting my FMPSA-10 files started acting squirelly, the FMP-Admin console wasn't refreshing properly, it was not even showing that I was logged in, so I decided to reboot the server.  With close to a 100 people connected to it, I had some hacked off users!

                               

                              After the reboot, and subsequent file verification steps that takes hours, I now have all my databases back, and suddenly I can once again search on calculated fields.  Apparently the inability to search on calculated fields is a bit like the canary inthe coal mine.  I should have taken that as a big warning sign.  It would be nice if FileMaker had suggestedI try a reboot.  I was prepared to go through and reinstall the server software, turns out it wasn't necessary.

                               

                              So, my issue is resolved, thanks to all that made helpful suggestions!

                               

                              Alan

                              • 12. Re: Can not search on calculated field
                                ClayTarver

                                We have seen a variation of his behavior over the last few months.  In our case, users suddenly lose the ability to find a list of students whose attendance is below a chosen threshold (85%, 90%, etc.), even when the percentage calculation is present and accurate.  Restarting FM 11 Server Advanced solves the problem, not necessary to restart the OS 10 server.  This occurs at perhaps two-week intervals, with no apparent cause. 

                                I believe the problem is within the database, because both the OS and Filemaker have been upgraded (OS 10.5 -10.6, FMSA10-FMSA11), with no reduction of this intermittent idiopathic irritant.

                                • 13. Re: Can not search on calculated field
                                  mps1773

                                  I find I regularly have to reboot FMS, otherwise we begin to see unusual behaviors in hosted files. A two week interval sounds just about right. I have made a case for preemptive restarts on a regular basis with my supervisor. He is beginning to see my point :)

                                  • 14. Re: Can not search on calculated field
                                    ClayTarver

                                    Do you have any ideas what makes the restarts necessary?  

                                    1 2 Previous Next