11 Replies Latest reply on Jul 12, 2013 10:29 AM by philmodjunk

    Find on calculated field returns no matching records

    DavidAyer

      Title

      Find on calculated field returns no matching records

      Post

           I'm using Filemaker Pro 11.0v3.  I have a calculation field which uses data from related tables, so the calculation result is unstored. When I perform a find on the field it returns no matching values although I'm certain those values exist in the data.

           I've done some Googling, but I can't find a solution to this exact problem.

           Other information:

             
      •           The calculation result is stored appropriately as text.
      •      
      •           The field shows a magnifying glass, indicating that it is searchable.
      •      
      •           The QuickFind indicator is yellow, so I would expect slow searching but not no searching. Ticking or unticking the QuickFind box doesn't change things.
      •      
      •           Ctrl-I in the field to show the index shows nothing (as expected)--but should it show an error message or just no result?
      •      
      •           The file is hosted on a server in our organization, not on my local machine--some threads I have found suggest this might be a problem.

           Anyone have an idea on this?

        • 1. Re: Find on calculated field returns no matching records
          philmodjunk

               Can you provide some more detail about the calculation itself, how you are searching your records (quick find? Regular find? Manual? Scripted?...)

               Is the calculation field defined in the same table as that on which your layout is based? Or is it from a related table?

               Some sample values returned by your calculation and some sample criteria that fails would also help.

               As an initial "smoke test", what do you get if you enter find mode, enter a lone * in the field and perform a find? This should find all records where the field is not empty.

          • 2. Re: Find on calculated field returns no matching records
            DavidAyer

                 Thanks for the quick response!

                 It's a school records database.  There are two tables, "families" and "children".  Children can have a EnrollmentStatus of  "applied", "accepted", "enrolled", etc.  Since a family can have multiple children, related by an index, families can have a FamilyStatus of  "enrolled", "alumni", etc., as given by the calculation:

                 FamilyStatus=

                 Case(
                 PatternCount (  List ( children::EnrollmentStatus ); "enrolled")>0; "enrolled";
                 PatternCount(List(children::EnrollmentStatus ); "alumni")>0; "alumni";
                 PatternCount (  List ( children::EnrollmentStatus ); "donor")>0; "donor";
                 PatternCount (  List ( children::EnrollmentStatus ); "staff")>0; "staff";
                 (PatternCount (  List ( children::EnrollmentStatus ); "inquiry")>0
                  or
                 PatternCount (  List ( children::EnrollmentStatus ); "applied")>0
                 or
                 PatternCount (  List ( children::EnrollmentStatus ); "accepted")>0
                 ); "prospective";
                 PatternCount (  List ( children::EnrollmentStatus ); "declined")>0; "declined";
                 "other"
                 )
                  
                 The calculation field is defined in "families", and the layout is based on "families".
                  
                 I'm quite sure the calculation is returning appropriate values--I can see records where the children's status are giving the appropriate FamilySatus.
                  
                 Searching on * yields all 1050 records.
                  
                 Searching on ! yields all 1050 as well, which is reasonable.
                  
                 Searching on = yields "No records match this criteria" which is believeable.
                  
                 Searching on any of the calculation field results, or a single letter yields "No records match this criteria".
                  
                 BTW, I noticed that the comments box here supports bullets, #s, etc., but they are stripped out in the forum display.  No big deal of course.
            • 3. Re: Find on calculated field returns no matching records
              philmodjunk

                   I set up a small demo file to test this where I copied/pasted the above calculation and cannot reproduce the same behavior you are reporting. You can download the file from here: https://dl.dropboxusercontent.com/u/78737945/FIndOnUnstoredCalcDemo.fp7

                   See if you can spot anything different between your file and mine.

              • 4. Re: Find on calculated field returns no matching records
                DavidAyer

                     OK.

                     No difference.  But, I think this is it.  We are sharing the file.  When I open a copy without sharing, I can search on the field just fine.

                     An undocumented bug/feature?  Any workaround?

                     Thanks.

                • 5. Re: Find on calculated field returns no matching records
                  philmodjunk

                       I don't have FMS 11, but I do have FMS 10. When I host this file from FMS 10, I see no change in behavior. I can find the files just as from a non-hosted copy. I would not expect this to fail or many, many developers would be flooding the Report an Issue site with impassioned please that FileMaker fix the bug.

                       However, if you upload this test file to your server and it fails from there, I suggest posting an Issue Report in Report an Issue. If you do that, feel free to post the download link to this file as an example file so that a TS person can use it for testing to reproduce the issue.

                  • 6. Re: Find on calculated field returns no matching records
                    DavidAyer

                         OK, now I think I really have it.  The machine sharing the file is running our oldest versrion of FMP--7 or something like that (I'm not in front of it right now).  FMP 7 dos not know one of the functions in the Calculation (PattenCount maybe).  Evidently the calculation is evaluated locally for the purpose of putting data in the field, since I see data on my machine.  (Otherwise I would have stumbled onto this long ago.) But it seems to go back to the host machine for the Find request.

                         When I shared the file from a machine with a more current version of FMP, the Find requests go through.

                         So, thanks for your help in getting to the bottom of this!

                    • 7. Re: Find on calculated field returns no matching records
                      philmodjunk

                           According to what I see in FileMaker 12 help, it's the list function, not PatternCount. PatternCount predates version 6, but the list function was added with version 8.5.

                      • 8. Re: Find on calculated field returns no matching records
                        DavidAyer

                             Right, that must be it.

                             Is my algorithm for the calculation irredeemably clunky? Is there a more elegant way?  (Bearing in mind,if it's not evident in the code, that just one enrolled child makes a family  "enrolled", so we check for that first, and then cascade to the others.)

                        • 9. Re: Find on calculated field returns no matching records
                          philmodjunk

                               I think you should consider an upgrade--either to server 11 or a full up upgrade to 12 for both clients and your server. Server 7 is now a pretty old server on which to rely on for your database system. You never know but that a need to upgrade your server hardware (say the current unit fails) might leave you with options that don't work with server 7 at all.

                               The thing that jumped out at me when I analyzed your calculation is that you can have multiple students--each with a different status but the family status will be determined by just one of those students--the first one to result in a result of true as the calculation evaluates from the first boolean expression listed in the case function to the last one.

                               Maybe that is exactly what you want here, maybe it isn't as a student could be listed as "declined" but the family might be listed as "prospective" due to a second child of that family having a status of "applied".

                               While there is no need to change your expression for this specific calculaition--except for the fact that it doesn't work for server side processed events on server 7, I usually use FIlterValues where you have PatternCount. That's because PatternCount ( List (  "Red" ; "AppleSauce" ) ; "Apple" ) will return a nonzero result even though "Apple" is not an item in the list. Not IsEmpty ( FilterValues ( List ( "Red" ; "AppleSauce" ) ; "Apple" ) ) on the other hand, will return False.

                               As far as coming up with a fix for this for your current system, the only thing that comes to mind is a script that captures this value and stores it in a simple text field. Your FMP 10 and 11 clients can do this from a script trigger performed script every time a student status is changed and then you can do your searches on a simple text field.

                          • 10. Re: Find on calculated field returns no matching records
                            DavidAyer

                                 Thanks again.

                                 Will definitely be upgrading.  I think that will be the fix.

                                 Thanks for the tip on FilterValues.  I have tried to set up the Case function so it checks statuses in the right order (if any child is enrolled, it goes to enrolled and stops looking, etc.).  I think this works OK.

                                 Another question (should I start a new thread? Haven't searched on this yet.):  

                                 I have a field set to autofill with previously entered data.  One value will not autofill, nor does it appear in the index, although numerous records with this data in this field exist.  It's pretty simple: the field is Children::ProgramClassroom, All Indexing is selected, the layout is based on Children, nothing is in a portal, the value that won't autofill has been manually entered (numerous times) as have the others, yet it maddeningly won't index or autofill.

                                 Thoughts?

                            • 11. Re: Find on calculated field returns no matching records
                              philmodjunk

                                   A new thread would be a good idea. Once a thread gets past the first 5 or so posts, a lot of other potential posters stop reading the thread due to the time invovled. A new thread, when the issue is not connected to the original question, re-invites those other folks back into the discusssion.

                                   That said, this sounds very similar to something recently asked in another thread. In that thread, I suggested that they see if rebuilding the field's index resolves the issue.

                                   To rebuild the index of a single field:

                                     
                              1.           Open Manage | Database | Fields and double click the field
                              2.      
                              3.           Use either the storage tab or the storage options button to turn off indexing.
                              4.      
                              5.           Exit Manage | Database, then return and turn indexing back on.

                                    

                                   You can also rebuild all your file's indexes by importing all the data into an empty copy (clone) of your file.

                                   If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:

                                     
                              1.           With the file closed, select Recover from the File Menu.
                              2.      
                              3.           Select "Use advanced Options"
                              4.      
                              5.           Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
                              6.      
                              7.           The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.