5 Replies Latest reply on Aug 26, 2014 10:32 AM by philmodjunk

    Find requests returning intermittent results



      Find requests returning intermittent results


           Hi all,

           I have some strange find results occurring in a database since its conversion to fmp12.  It concerns two related tables, one called Events, the other called Bookings.  The Bookings table contains the individual dates and venue records for each Event record; they’re related via Events::EventNumber which is a unique number for each event.

           The Events table contains a field called MinDate, the calculation for which is =Min(Bookings::Date).  Obviously, it returns the lowest (or earliest) date for an event from its related Bookings records and it’s set to recalculate as needed.  When I perform a search based on this field, it returns patchy, yet consistent, results.  Some (and only some) event records I know exist are not being returned while others outside the search criteria are returned.  Taking a single troublesome record, if I search for it by EventNumber, it will return the record, displaying the correct MinDate.  If I then perform a find on MinDate for that exact date, it returns no results.

           I’ve tried in vain to find the cause but so far I’m at a loss. I’ve no idea why some records are affected and others not. Any thoughts or ideas?  We’re currently running FM13 on Win7 but the fault first appeared after fmp12 conversion about a year ago.  Prior to this, results from a find based on this field were not faulty.

           Kind regards, Jayson

        • 1. Re: Find requests returning intermittent results

               at first glance - are MinDate and Bookings::Date both defined as type Date ?

          • 2. Re: Find requests returning intermittent results

                 Hello Jayson

                 Default format of date in Filemaker is Systems' Date format (like MM/DD/YYY or MM/DD/YY). Please make sure the format of the fields which you are using to enter the data and find the data is same. Sometimes date formatting created problem.


                 Misha Mahto

            • 3. Re: Find requests returning intermittent results

                   But the date format is not the issue. You need to check Field Types in Manage | database | Fields. Your calculation field with the Min function should be of type calculation and when you open the specify calculation dialog, you should see that the result type is of type date as well.

                   Since this worked in a previous version of FileMaker we have to consider two scenarios: 1) The field type issue--maybe this was accidentally changed at some point and 2) The file may be damaged and the damage did not become observable until you converted the file to the new file format.

                   For 2, take a recent copy of your file and run a recover on it to check for damage. Then test the recovered copy to see if it works reliably when the original did not. (Do this even if FileMaker does not report finding an issue.)

              • 4. Re: Find requests returning intermittent results

                     Thanks for your responses.  Yes, Bookings::Date is defined as a Date and the result of the calculation for Events::MinDate is set to Date.  Phil, you may be on to something, it could be some sort of damage to the file after conversion. 

                     For a particular record, I can copy the data from MinDate and in Find mode, paste the data verbatim back into MinDate and it returns no results.  Search by EventNumber and the record shows up again.  So something is not right.  Hopefully i can get it sorted, the MinDate is an important field for populating some of the reports we generate.
                     Again, thanks all.

                     Regards, Jayson.

                • 5. Re: Find requests returning intermittent results

                            it could be some sort of damage to the file after conversion.

                       Minor quibble: I suggested that the file was damaged prior to conversion, but that it only affected behavior after conversion.