8 Replies Latest reply on Nov 14, 2012 12:28 AM by comment

    Set Occurrences by Day(s) Of Month


      I'm about to modify an old database project that tracks upcoming events.


      Usually, the event is just a one-off and its record includes a unique Start_Date, an End_Date (usually, but not always, the same as Start_Date) plus a Start and End Time. Dates are currently set by clicking on the Start_Date and End_Date fields with a drop-down calendar.


      Some of them, however, might be recurring events that happen "on the first Tuesday of each month", or "the first AND third Saturday of each month".


      What I need is a way to set the Date fields to account for such possibilities and a simple way for the user to enter those occurrences.


      The Start/End_Date fields would store the occurrences as a return-delimited list of numbers.


      I wonder if a separate Calendar field with the instruction "Set by Day(s) Of Month" could be devised where clicking a Date sets the list to the chosen Day Of The Month for every month within a specified Date Range. Clicking another Date would add another bunch of dates to the existing list.


      A bit of a brain-boggler! Can you think of a way to approach the problem?


      Kind regards to All,


      Bob Stuart


      Lord Of The Files
      Think Data Pty Ltd


      +61 7 5442 5624
      +61 413 350 993

        • 1. Re: Set Occurrences by Day(s) Of Month

          Hi Bob


          My initial suggestion is that you should not try to add return-delimited dates to your start and finish dates.


          You have a system that works with individual start and finish dates - just add a routine to duplicate specific event records, setting the Start and Finish dates in a script that is controlled by a 'Repeat Details' layout.


          It is on this layout that your users enter the repeating parameters for a particular event. You will have to provide them with a flexible entry system using Global Fields: 'Repeat Until Date' or 'Number of Repeats', 'Number of Days/Weeks/Months until next Repeat', 'Event Length Days' etc.  When they submit these details, the script generates the requisite number of records, dated as per their instructions. 


          If necessary, they could then check the new records in a portal and adjust any that have been generated incorrectly.  Don't get confused with "The First and Third Saturday of each month" - these are two separate repeats, one a month on from the first Saturday and a second, a month on from the third Saturday. The first two records will already exist (or have to be added) and then the Generate Repeats script can be run separately on each of these records.


          Best wishes - Alan Stirling - London UK.

          1 of 1 people found this helpful
          • 2. Re: Set Occurrences by Day(s) Of Month

            I am not sure what your question is. Sure, you can generate a list of occurrences, given a StartDate, EndDate and a requirement like "the first AND third Saturday of each month". Will the requirement always be in the form of "the n-th [and the k-th] weekday of each month"?


            And what exactly do you intend to do with a bunch of dates in a text field, once you have them?

            • 3. Re: Set Occurrences by Day(s) Of Month

              Alan's suggestion is good for repeating events with a known duration, ie, an end date. The problem is, how do you create a new record for every event for a item that repeats every tuesday? What about birthdays? They occur every year but we don't know when they'll end.


              I believe the solution (which I haven't had to implement yet ) is to create the calendar display in a virtual table. I'm sure it's easier to say that to do. The calendar display will always be for a finite period, such as a month. You can grab all the static dates easily. The trick is to have a table of events that repeat. Look at the iCal specifications for a good system for describing repeating events in a reliable fashion. Use the period of the calendar display to calculate which of the abstract events have an actual date within that range. Pop them all into a virtual table, sort, etc and display.

              1 of 1 people found this helpful
              • 4. Re: Set Occurrences by Day(s) Of Month

                Hi Bob,

                I have something similar working in a surgeons calendar which is web based. I have a table called cycles where I record the repeating event cycles. There is a start date entered for the first event and if it terminates they can also enter a date for that so you get a limited number of cycles. The table then calculated a return delimited list of possible dates with a max of 2 years at any one time.

                The calendar then simply shows those on the calendar in relation to the cal date. It does not actually create an event (appointment) for those but it could be used to script the creation of the event records by script a few months ahead.


                - Lyndsay


                My-iPhone 0423 831 897

                Lyndsay Howarth



                • 5. Re: Set Occurrences by Day(s) Of Month

                  Hi Michael,


                  A bunch of tab-delimited dates in a text field can be used to display Events that occur Today in a portal, through a relationship using today's date as a matching key.



                  • 6. Re: Set Occurrences by Day(s) Of Month

                    Hi Alan,


                    It strikes me as inefficient to duplicate entire records, when a return-delimited list could contain all of the dates on which the event occurs.


                    I'm not dismissing your helpful suggestion; just looking for the leanest way to create a key-field of dates that could be used to match Today's Date and display the day's events in a portal. The portal item could be clicked on to display the record through a GTRR.


                    Thanks, Alan.



                    • 7. Re: Set Occurrences by Day(s) Of Month

                      Hi again, Michael,


                      The requirement for typical events will be for the Start and End dates to be one single day. That's easy.


                      Some events might be "the n'th day of every month", e.g. every First Of The Month. That's easy, too.


                      Here's the hard bit:


                      Other events might be "the n-th weekday" every month for a range of months, say, for a whole year.


                      Or even "the n-th weekday and the k-th weekday of the month", for the coming year.


                      I'm still not convinced that the entire record should be duplicated n-number of times, each with a different Start_Date. The act of selecting a day on a sample calendar layout should collect the Dayname of the month, ascertain which of the month's Thursdays (or whatever day it is) has been selected and then populate the Date_List with the corresponding dates for the period of the specified date range, e.g. for the next 12 months.


                      I'm sure there must be an efficient way to achieve this.





                      • 8. Re: Set Occurrences by Day(s) Of Month

                        I would use a repeating calculation field for this. The exact formula depends on what options are available to the user, since it must be cased. In the case of n-th weekday of each month, the formula could be something like (untested) =


                        Let ( [

                        s = Extend ( StartDate ) ;

                        m = Month ( s ) + Get ( CalculationRepetitionNumber ) - 1 ;

                        d = Date ( m ; 1 ; Year ( s ) ) + Mod ( Extend ( Weekday ) - DayofWeek ( firstOfMonth ) ; 7 ) + 7 * ( Extend ( n ) - 1 )

                        ] ;

                        Case ( s ≤ d and d ≤ Extend ( EndDate ) ; d )



                        where Weekday is a number between 1 and 7, inclusive. The result type is, of course, Date.



                        Bobster wrote:


                        I'm still not convinced that the entire record should be duplicated n-number of times, each with a different Start_Date.


                        Well, generating a list of occurrences does have one advantage: each individual occurrence can be edited independently.