1 Reply Latest reply on Nov 16, 2009 8:56 AM by philmodjunk

    Scheduling dilemma with multiple start and stop dates



      Scheduling dilemma with multiple start and stop dates


      I am building a database for a company that has screens in restaurants that run multiple video ads. Nine ads that run simultaneously on each screen. The position of the ads on the screen does not matter. The advertisers contract for a block of months to be displayed on the screen, and can start at either the first or middle of the month and can run for any number of months. The sales reps are going to use this in the field to schedule the ads. They need to know if there is an open slot for the length of time that the advertiser requests. The Sales Reps also book screens in the future, they may book for several months down the road. If the screen fills up for a particular range of dates, I need to be able to notify the users that the requested start and stop dates are not available, and ultimately, return the open dates that are open.


      My issue is how to write a routine for checking multiple start and stop dates and compare it to the requested dates. For example, let's say there are only three ads running at a time, Advertiser A wants Jan. - April, Advertiser B wants May - July, Advertiser C wants Jan - December. If Advertiser D wants Jan - December, it's open (Jan.- April from Advertiser's B and May - December from Advertiser's A) but guys, I am stumped on how to write an algorithm for combining open dates and adding them together to see if a requested start and stop date is indeed open.


      My contract record holds the start and stop dates, and a related "Screen" table holds the advertiser's names, start and stop dates. I currently only have nine separate fields in the Screens record, but I assume I will build another related table to hold more than 9 advertisers, as the Sales Reps can book a screen for future dates.


      It goes on, there are multiple screens in one restaurant (these are in the bathrooms and bar) and an advertiser can advertise on any screen, and so forth, but I've got that written.


      I've been using FileMaker for well over 15 years, but this one has popped my cork, and any assistance would be tremendously beneficial.





        • 1. Re: Scheduling dilemma with multiple start and stop dates

          A fairly straight forward "dual request" find should be able to pull up the contract records that overlap a given date interval. A count of the resultant found records will then tell you if the specified interval has an available slot for a given display venue.


          If I read this correctly, you need to find all contract records where either the start date or the end date of your proposed time interval falls between the start/end dates of your existing contract records. Using two requests handles the "OR" part of the find logic.


          You can do this manually or in a script:

          Enter find mode

          Create your first find request:

          Specify whatever fields needed to find a particular venue ( a restaraunt or chain of restaraunts I would presume )

          Enter "< ProposedStartDate" in the contract start date field.

          Enter "> ProposedStartDate" in the contract End Date field.


          Create a second request

          Specify the same venue specific fields as the first request. You can even duplicate the first request if you wish.

          Enter "< ProposedEndDate" in the contract start date field.

          Enter "> ProposedEndDate" in the contract End Date field.


          Perform the find and count the records.



          Replace "ProposedStartDate" and "ProposedEndDate" with literal dates if you are doing this by hand. If you script it, enter these dates into global date fields and use Set Field to enter the criteria while in find mode.

          In a script, get ( FoundCount ) can count the number of records in your found set.