4 Replies Latest reply on Oct 29, 2008 1:10 PM by swc

    Finding records not in a date range



      Finding records not in a date range


      Really having a tough time with this one (and I'm new to filemaker).


      Possible solution 1:  In a table that has a date field, I know how to search for records within a certain date range.  However, is there any way to display the rest of the records instead.  That is, if I keep a log of dates of students I've met with as records, how would I find the students I haven't met with between a range of dates?


      Possible solution 2:  If the above is not possible, I could keep a date field next to each student record that just has the last date I met with the student.  Then would there be a way to search for student records that have meeting dates less than a specific entered date.  This way, if I could enter a new date such as 10/1/2008 in a find, then would I be able to see all students who I've met with before this date (and not after)?  This would allow me to know which students I haven't met with after a specified changeable date (in this case 10/1/2008) which is somewhat similar to the result above.


      Any help would be greatly appreciated (especially if spelled out for a beginner). Sorry if it's a bit confusing!


        • 1. Re: Finding records not in a date range
          Let's say that you want to search for all records where the dates are NOT between 1 January and 3 March this year:
          1. Enter Find mode (Ctrl-F or Cmd-F)
          2. In the date field, enter 1/1/2008...3/3/2008
          3. In  the status area (on the left of screen) click the checkbox to Omit
          4. Click the Find button in the status area
          And that should get you the records you want. 


          • 2. Re: Finding records not in a date range

            Do you have a modification timestamp field? If not, I suggest going into File > Define > Database and creating two fields named something like Timestamp_RecordCreation and Timestamp_Modification. Choose file type for each as timestamp, and in Options - choose the autoenter for creation & modification, respectively - for those two fields.


            If you write info in the records of the students you see when you see them, then you will automatically have record modification date & time. If you do a "show all records" and then, in table view you click on that field header, your records will be sorted in descending/ascending order by that field - click on field a second time - sorts in opposite direction (or if not in table view you select the Sort dialog box and select the modification timestamp field - and you can specify the direction of the sort by clicking on the bars in the lower section to indicate if you want the sort in ascending or descending direction.


            You can then  select an literally copy the timestamp info of the first record of the person "not" in the recently seen students that you want to use as a reference  then go into find mode, and then enter a < sign then paste the time stamp field and click find. Then you find the ones that are before that date & time (e.g., <10/28/08 9:34:19 PM).



            If I'm understanding you correctly, let's say you want to find the records between 10/22/08 and 10/28/08 - as in including 10/22 but before 10/28. In find command you put >=10/22/08<10/28/08. Click find. You can take this approach to finding straight out dates - or long-winded timestamps as date-oriented data. Since the date range may change for the date field, what you could do is: 1) If the main date field has a date menu, to keep this but still have access to the date data to use with a drop down menu as I later explain - you can create a 2nd date field that you name Date 2 (or whatever) - and specify that field as a calculation field - and the calculation you specify in Options is just the name of the original date field - so it just copies the date data from your main date field automatically; 2) go into File > Define Value list, and create a value list that just has a set of range symbols in it >= <, then below that > <, then below that >= <= , then below that > <=.  This way you have each of the 4 combinations. Then go into the layout mode and click on this second field that you put in this layout, and go into Format > Field > Set-up > Pop-up Menu, then select that value list you created and say okay (check option to add entries/change list; 3) With that field still selected, go back to Format > Field > Behavior > then unselect browser mode (this is necessary in order to access the drop down menu (value list) for a calculation field - as you know you can't otherwise enter data in a calculation field - so doing this allows you to enter data in find mode). Now - back in browser mode - when you click on that field, the drop down menu with the date range symbols will appear - and all you have to do is fill in the dates on the right side of each symbol or set of symbols and click Find. BUTTTT... if you are interested in the records that DON'T fit the criteria, as you seem to describe - before you click "FIND" on the left toolbar - what you want to select is OMIT: you want to find the records that do not fit that date range, right? The ones left out. The other option - if you just do the find for the students you saw, to get the same thing - is after you find those students - go into Records: "Show Omitted Only" - and you see the ones that don't fit the date range. This may be all you want. Sorry for the long winded explanation.



            Very lastly - you could create a script and a button to execute the script that would at least take you to the 2nd date field (copy of the original date or timestamp info) and put you in the find mode so that the drop down menu (value list) shows up as soon as you click on it. I can explain this later if you're interested.

            What I mainly did is just described options for  for using  Find Command to get what you want (don't want) using Find/Omit and drop down (value) lists. Hope this makes sense and sorry if I'm describing steps you already know... and if someone else has better - more to - the - point suggestions based on your objective.

            • 3. Re: Finding records not in a date range

              This is great! Thank you both so much!  That really cleared things up for me.


              One last questions...   So now I have a table for students that contains all their classroom information.  And I have a seperate table for the dates I meet with students.  In that table each record is a date and student's name.  I now know how to find students in the Met With table that I have NOT met with in a certain date range per my question above (using omit).  However, would there be a way to search my Student table so I can find for instance all students in Mr. Smith's class, who I haven't met with in a certain date range. I'm not sure how to search data from two tables to get one list of record results or combine the data somehow to get the desired results in one solo table.


              Many Thanks!  There are thousands of students in my database so this type of search is really important.

              • 4. Re: Finding records not in a date range

                You have two tables, related to each other:




                Go to a layout based on Student.  Add a field to that layout StudentMeeting::MeetingDate.


                You can then search as though the fields are in the same table.


                Enter Find Mode.

                In the StudentMeeting::MeetingDate field enter your date range in the form start...end.  ex: 1/1/2007...12/31/2007

                Perform find.


                Only comment I would make is you should relate based on a student_id field and not a name, if that isn't already the case, so as to avoid issues when you end up with two "John Smith"s.