4 Replies Latest reply on Feb 1, 2014 9:46 AM by philmodjunk

    Find is finding too much



      Find is finding too much


           OK, this has bugged me all day and I've got to come for help.

           On a layout, there's a begin_date field.  It is of type "date" and is auto-entered.  Very simple stuff.  The table itself contains 4300+ records.  I want FM to show me -only- records with dates later than 1/1/2012.

           I go to Find Mode, click in my dates field and type this string:  > 1/1/2012, Perform Find.  It finds 1850 records but the dates are not "> 1/1/2012" but extend all the way back to 2003.  I don't understand why FM would present any record before 1/1/2012.  I've read through the Help and followed the syntax pretty carefully.

           Is this because there's a relationship between the TO containing "begin_date" and the TO the layout is based on?  I felt that shouldn't be an issue but maybe it is???


        • 1. Re: Find is finding too much

               Google "search date range filemaker"

               Link #7  The Secret Life of Find Mode: Dates and Times

               Link #11    Date Range Searching Made Easy    12/15/1997

          • 2. Re: Find is finding too much

                 I would guess that thisis the issue:

                 If you enter find mode and specify criteria in a field from a related table instead of a field from the layout's table occurrence, you are telling FileMaker to "Find all records in the layout's table occurrence that have at least one related record matching the specified criteria." Once the find is performed and the window returns to Browse mode, your layout functions as it was designed to do and the data from the related table may not appear to match the specified criteria as it may show data from a different related record than the one that matched your find criteria.

                 See this link to learn more:

            searches on related table

            • 3. Re: Find is finding too much

                   OK, Phil - good explanation and I've bookmarked that link.  It's clear I too failed to understand the impact of doing a "related table" search.  To the beginner (me) - if I see it on a layout, I can search for it.  Not exactly true; and now I know better.

                   I then need to create a new field in the "Main" TO, that points to and gets its value from "Details" (I'm using your scenario here).

                   Have I got that right?



              • 4. Re: Find is finding too much

                     It depends on the results that you want. More likely, you need to perform your search on a layout based on the related table instead of the main table.

                     Let's say that your relationship looks like this:

                     Main-----<Details   (----< means one to many )

                     You want to find all detail records where Main::Name = "Apple" and Details::Date has today's date. If you set up a layout based on details, you can Put Main::Name on it as well as Details Date. Perform your find here (either manually or scripted) and you'll get all Detail records that meet this criteria. The trick is to perform such a find from the "context" of the "many" side of your relationship to keep from seeing data from records that don't match your specified criteria.