4 Replies Latest reply on Aug 23, 2016 4:42 PM by philmodjunk

    Date range comparison

    ldrolet

      Title

      Date range comparison

      Post

           Hello,

            

           I would like to know if anyone could give me some hints or tell me where to look for some info about how to script or filter for date range comparisons.  For example, construction equipments are scheduled in the future over various ranges of dates from 3 to 25 days. How could I filter or script a find that would show all available equipments over a specified range of dates?

            

           Thanks

        • 1. Re: Date range comparison
          philmodjunk

               Your most simple way to check availability is to record your date range as a list of dates from the date marking the start of the reservation to the date marking the end of the reservation. Such a list of dates can be a list separated by returns and used as a match field to match to other records for the same piece of equipment to see if it is available.

               The first step is to generate such a list. Do you have FileMaker Advanced? If so, a custom function can produce such a list from a pair of dates marking the start and end of the reservation. If not, a looping script can do the same.

          • 2. Re: Date range comparison
            Johan Hedman

            You create a script that could look something like this:

             

            Find

            Set Field (your date field ) date1 & "..." & date2

            Perform Find

             

            This will give you all records that have something starting on date1 and until date2

            1 of 1 people found this helpful
            • 3. Re: Date range comparison
              DavidJondreau

              How are the dates stored? There should be an Inventory table with a record for each piece of equipment and something like a Transactions table for each scheduled period. Like

               

              Transactions:

              Type | ID | Start Date | End Date

              Bulldozer 123 9/1/16  9/5/16

              Bulldozer 123 9/7/16  9/11/16

              Bulldozer 456 9/1/16  9/5/16

              Backhoe 333 9/1/16  9/5/16

              Bulldozer 334 9/1/16  9/5/16

               

              Then you can run a scripted find on the Inventory table to see which bulldozers aren't rented in a specific date range.

              1 of 1 people found this helpful
              • 4. Re: Date range comparison
                philmodjunk

                Find

                Set Field (your date field ) date1 & "..." & date2

                Perform Find

                 

                Not quite.

                 

                What if date1 is 8/23/16, date2: 8/24/16

                 

                and the equipment is already in use from 8/1/16 to 8/26/16?

                 

                The issue here is that you can have overlapping date ranges that overlap in three different ways. The start date of one range can fall between the date range of the second range, the end date could fall with in the start/end of the second range or one date range could "enclose" the range of the other--as shown in this post.

                 

                Another poster not much before this post originated, showed how a relationship based on a list of dates will successfully match to show an overlap in the date ranges in all three cases.

                1 of 1 people found this helpful