6 Replies Latest reply on Jan 22, 2010 8:04 PM by smithp

    Do not allow overlapping times

    smithp

      Title

      Do not allow overlapping times

      Post

      Need help figuring this out:

       

      I am using FileMaker 10 running with Windows Vista.

      I am a mental health therapist and I use FileMaker to keep my progress notes.

      I create a new record for each note with the: Date, Start Time, and End Time

       

      Here's the problem:

      I want to make sure that the time recorded on one note (reocord) does not overlap the time recorded on another note for the same date. I would like to have the program give me an error message when/if I enter a time that overlaps with another note for that same date.

       

      I am not using time stamping because of some other billing issues. Instead I choose the time for the Start Time and End Time from a drop down menu (from a list)

       

      Any help would be greatly appreciated.

      Peggy

        • 1. Re: Do not allow overlapping times
          philmodjunk
            

          Do you know how to create a "self-join" that is, a relationship that links records in the same table to each other?

           

          That, plus some conditional formatting can do what you want.

           

          Let's call your table "ProgressNotes".

          Open Manage | Database | Relationships

          Drag from ProgressNotes:: Date to outside the box and then back to this same field.

          You'll get a dialog asking you to name this "instance" of a new Table Occurrence. Let's call it SameDateTime.

          Double click the line linking your two table occurrences and add two more pairs of fields to this relationship.

           

          ProgressNotes:: Date = SameDateTime:: Date AND

          ProgressNotes::StartTime > SameDateTime::StartTime AND

          ProgressNotes::EndTime < SameDateTime::EndTime

           

          Now, on your layout, select the date, time fields and give them all the following conditional format expression:

           

          Count(SameDateTime:: Date) > 1

           

          And select a fill color like red or yellow to highlight the fields when more than one note record overlaps the same date and time.

           

          Note that I am assuming a single therapist with this example. If there are more than one recording notes, you'll need one more pair of fields to keep the notes from one therapist triggering a warning on notes entered by another.

          • 2. Re: Do not allow overlapping times
            comment_1
              

            PhilModJunk wrote:

             

            ProgressNotes:: Date = SameDateTime:: Date AND

            ProgressNotes::StartTime > SameDateTime::StartTime AND

            ProgressNotes::EndTime < SameDateTime::EndTime


            No, that's incorrect: it will only catch records that are entirely contained by the current record. To catch all conflicts, the relationship needs to be:

             

            Notes:: Date = Notes 2:: Date

            AND

            Notes::StartTime < Notes 2::EndTime
            AND
            Notes::EndTime > Notes 2::StartTime

             


            • 3. Re: Do not allow overlapping times
              philmodjunk
                 Thanks.
              • 4. Re: Do not allow overlapping times
                smithp
                  

                Phil,

                 

                Thank you so much! This worked beautifully!!

                • 5. Re: Do not allow overlapping times
                  philmodjunk
                    

                  And let's thank comment also or my suggestion wouldn't have been at all "beautiful" :smileywink:

                  • 6. Re: Do not allow overlapping times
                    smithp
                       Yes, absolutely thank you too Comment!!:smileyvery-happy: