4 Replies Latest reply on Jun 7, 2012 12:48 PM by distingo

    Checking for time overlap

    allenbr

      My database is for time-based billing. I would like to add an internal verification mechanism that verifies that none of the entered times are overlapping. (We can't be in two places at once, now can we?)

       

      Each record contains a start time, a stop time and the date of service. I just need to ensure that there aren't any conflicts.

       

      At first this seems like a simple task, just check that

       

      STARTTIME(this record) > STOPTIME(previous record)

       

      and that

       

      STOPTIME(this record)<STARTTIME(next record)

       

      And, in fact a simple check like that works - most of the time. When billing periods extend beyond midnight into the next day and the stop time is now likely to be numerically smaller than the start time, things get more complex.

       

      I solved most of the troubles by converting all the start and stop times into the number of elapsed minutes from the stroke of midnight on new years eve. Then it is a simple comparison. It works fine except for a billing period that spans midnight on new years eve. In that situation I run into the same sort of problem.

       

      I'm wondering if there is some simpler solution to this problem that I'm missing. I'm thinking along the lines of how the Mac operating system is referenced to a date in 1906 (or similar) and all time calculations use the elapsed time from then. Does FM use a similar reckoning system internally? If so, can this be accessed?

       

      Or is there something else I could do entirely?

        • 1. Re: Checking for time overlap
          comment

          First, why don't you use  Filemaker's native timestamps - if not to actually record the start and stop points, then at least for the calculations?

           

          Once you have that (or even a similar mechanism) in place, set up a self-join relationship as:

           

          Items::StartTS < Items 2::StopTS

          AND

          Items::StopTS > Items 2::StartTS

          AND

          Items::ItemID ≠ Items 2::ItemID

           

          If there are any related records in Items 2, they will be overlapping the currently viewed item.

          1 of 1 people found this helpful
          • 2. Re: Checking for time overlap
            erolst

            Your guess is spot-on; FM does in fact use a similar reckoning system. Check the Help system for Timestamp functions and field type.

            1 of 1 people found this helpful
            • 3. Re: Checking for time overlap
              allenbr

              I ran across references to the Timestamp just after I posted. I don't know how I missed it before! This is the obvious solution to this problem. I knew there had to be an easy way!

               

              Thanks all!

               

              Brandon

              • 4. Re: Checking for time overlap
                distingo

                Thank you!

                Ive had this problem too and was redirected to this thread by another user and thu came over this very clean and simple answer!

                 

                With a relation as the one above and a boolean calculationfield counting the related records it works like a charm!

                 

                Thank you.