Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at the jQuery Conference 2009 (Cambridge, MA) with:

ColdFusion Calendar Event Scripting (Inspired By Peter Bell)

By Ben Nadel on
Tags: ColdFusion

Last week, Peter Bell started blogging about building an object oriented calendar system. I have done some blog posts on the display of event calendars, but I was inspired by Pete's OOP posts to look more deeply into the actual setup and querying of events in ColdFusion and SQL server. Naturally, I will not be doing any OOP in this - I leave that up to Peter. Who knows, maybe when I am done with this stuff, I can work with Pete to put this into his OOP world of magic.

When it comes to scripting an event calendar system in ColdFusion, we don't really care about single-day events. Well, we care about them, but they are just not interesting; anyone can build a calendar system that has only single-day events. It's only when we get repeating events that things start to get really interesting. These are events that start on one date, end on another date, and repeat in a given pattern for all the dates in between.

These are the event repetition types that I decided to tackle in my first go-round (yes, there will be more posts about this topic):

  • Daily
  • Weekly
  • Bi-weekly (every two weeks)
  • Monthly
  • Yearly
  • Monday - Friday
  • Saturday - Sunday

After I started coding, I came up with another one:

Every Nth day of the month (ex. Every Second Tuesday), but I figured I would address that in my next phase of development.

The database for a simple ColdFusion event calendar system can be quite easy. Assuming you are not dealing with things like user-specific calendars, security, event exceptions, and all that jazz (to be addressed in later posts), all you really need is one table, Event:

ID - Primary Key / Auto incrementer.

Name - VARCHAR( 100 ); Name of event.

Description - TEXT; Description of event.

Date_Started - SMALLDATETIME; Date on which the event starts.

Date_Ended - SMALLDATETIME; Date on which the event ends.

Time_Started - VARCHAR( 5 ); Time of day at which the event starts in the format of (TT:mm).

Time_Ended - VARCHAR( 5 ); Time of day at which the event ends in the format of (TT:mm).

Is_All_Day - TINYINT; Short-hand flag for no-time events.

Repeat_Type - TINYINT; The type of repeating this event will do (ie. Daily, Weekly, Monthly, etc.).

Date_Updated - SMALLDATETIME; The timestamp of the last update.

Date_Created - SMALLDATETIME; The timestamp on which the event was created.

I added the Is_All_Day column so that I wouldn't always have to check the time started/ended columns, but I actually haven't even used it yet in the code. It might get taken out eventually. I am storing the Time Started and Time Ended in 24-hour format VARCHAR field because I feel it will be used less often and I don't want to store an incorrect date in that field. This might be a bad practice, but it has worked fine for me so far and does not require "default dates"

Before we get into the code, you might want to check out the online demo:

ONLINE DEMO - 2007-07-30

Warning: The Add Event page was mad quick and dirty and might not have the best error handling. The labelling also sucks! I really just needed that page to populate a database table to test with.

Now, onto the code. Let me talk about it a bit first, so you can take notice of some things. I wanted to code the algorithm in such a way that I would easily be able to add new repeat-types if they ever presented themselves. As such, I am sacrificing some more efficient code for certain repeat types in favor of some code that was more easily factored out. For example, I am using a Conditional CFLoop which would be less efficient than a straight up Index CFLoop for daily repetition; however, since not all repeat types can do that, I went for the Conditional loop on all repeat types.

To try and be as efficient as possible, I am doing my best to only examine events that might occur during the given time period (the current month of a month-view). This is not always easy; for example, events that repeat every year, I found it easier to start looping from the year before. It was just easier than trying to determine the logic of getting the next year that was still within (if at all) the given time period.

The real magic was finding the "Start Date" for the Conditional loop of each type of repeating event. Once I had that down, I could then factor out the code for populating the event query by using a Start Date, End Date, Increment Type (ex. m, d, yyyy), and Increment Value (ex. 1, 7) variable.

Ok, enough jabbering, let's take a look at the code. I am only showing you the Month View code as the code to add the events was totally Ghetto. I just wrote it fast so I could populate the database table.

  • <!--- Kill extra output. --->
  • <cfsilent>
  •  
  • <!--- Param the URL attributes. --->
  • <cftry>
  • <cfparam
  • name="URL.month"
  • type="numeric"
  • default="#Month( Now() )#"
  • />
  •  
  • <cfcatch>
  • <cfset URL.month = Month( Now() ) />
  • </cfcatch>
  • </cftry>
  •  
  •  
  • <cftry>
  • <cfparam
  • name="URL.year"
  • type="numeric"
  • default="#Year( Now() )#"
  • />
  •  
  • <cfcatch>
  • <cfset URL.year = Year( Now() ) />
  • </cfcatch>
  • </cftry>
  •  
  •  
  • <!---
  • Based on the month and year, let's get the first
  • day of this month. In case the year or month are not
  • valid, put this in a try / catch.
  • --->
  • <cftry>
  • <cfset dtThisMonth = CreateDate(
  • URL.year,
  • URL.month,
  • 1
  • ) />
  •  
  • <cfcatch>
  •  
  • <!---
  • If there was an error, just default the month
  • view to be the current month.
  • --->
  • <cfset dtThisMonth = CreateDate(
  • Year( Now() ),
  • Month( Now() ),
  • 1
  • ) />
  •  
  • </cfcatch>
  • </cftry>
  •  
  • <!--- Get the last day of the month. --->
  • <cfset dtLastDayOfMonth = (
  • DateAdd( "m", 1, dtThisMonth ) -
  • 1
  • ) />
  •  
  • <!---
  • Now that we have the first day of the month, let's get
  • the first day of the calendar month - this is the first
  • graphical day of the calendar page, which may be in the
  • previous month (date-wise).
  • --->
  • <cfset dtFirstDay = (
  • dtThisMonth -
  • DayOfWeek( dtThisMonth ) +
  • 1
  • ) />
  •  
  • <!---
  • Get the last day of the calendar month. This is the last
  • graphical day of the calendar page, which may be in the
  • next month (date-wise).
  • --->
  • <cfset dtLastDay = (
  • dtLastDayOfMonth +
  • 7 -
  • DayOfWeek( dtLastDayOfMonth )
  • ) />
  •  
  •  
  • <!---
  • Query for raw events. This is raw because it does not
  • fully define all events that need to be displayed, but
  • rather defines the events in theory.
  • --->
  • <cfquery name="qRawEvent" datasource="#REQUEST.DSN.Source#">
  • SELECT
  • e.id,
  • e.name,
  • e.date_started,
  • e.date_ended,
  • e.time_started,
  • e.time_ended,
  • e.repeat_type
  • FROM
  • event e
  • WHERE
  • <!--- Check end date constraints. --->
  • (
  • e.date_ended >= <cfqueryparam value="#dtFirstDay#" cfsqltype="CF_SQL_TIMESTAMP" />
  • OR
  • e.date_ended IS NULL
  • )
  • AND
  • <!--- Check start date constraints. --->
  • e.date_started <= <cfqueryparam value="#dtLastDay#" cfsqltype="CF_SQL_TIMESTAMP" />
  • </cfquery>
  •  
  •  
  • <!---
  • Now that we have our raw events, let's put together
  • a query that will contain all the events that actually
  • need to be displayed (repeated events will be repeated
  • in this query). Since our raw query may contain events
  • that only display once, let's build this query by
  • performing a query of query to get those single-day
  • events.
  • --->
  • <cfquery name="qEvent" dbtype="query">
  • SELECT
  • *,
  •  
  • <!---
  • We are going to add a column that will be the
  • lookup index for the day - remember the
  • date_started may be different than the display
  • date for most repeating events.
  • --->
  • ( 0 ) AS day_index
  • FROM
  • qRawEvent
  • WHERE
  • repeat_type = 0
  • </cfquery>
  •  
  •  
  • <!---
  • Update the day index and event index of the events
  • that we just put into our event query.
  • --->
  • <cfloop query="qEvent">
  •  
  • <!--- Update query. --->
  • <cfset qEvent[ "day_index" ][ qEvent.CurrentRow ] = Fix( qEvent.date_started ) />
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • Now, we will loop over the raw events and populate the
  • calculated events query. This way, when we are rendering
  • the calednar itself, we won't have to worry about repeat
  • types or anything of that nature.
  • --->
  • <cfloop query="qRawEvent">
  •  
  • <!---
  • No matter what kind of repeating event type we are
  • dealing with, the TO date will always be calculated
  • in the same manner (it the Starting date that get's
  • hairy). If there is an end date for the event, the
  • the TO date is the minumium of the end date and the
  • end of the time period we are examining. If there
  • is no end date on the event, then the TO date is the
  • end of the time period we are examining.
  • --->
  • <cfif IsDate( qRawEvent.date_ended )>
  •  
  • <!---
  • Since the event has an end date, get what ever
  • is most efficient for the future loop evaluation
  • - the end of the time period or the end date of
  • the event.
  • --->
  • <cfset dtTo = Min(
  • qRawEvent.date_ended,
  • dtLastDay
  • ) />
  •  
  • <cfelse>
  •  
  • <!---
  • If there is no end date, then naturally,
  • we only want to go as far as the last
  • day of the month.
  • --->
  • <cfset dtTo = dtLastDay />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • Set the default loop type and increment. We are
  • going to default to 1 day at a time.
  • --->
  • <cfset strLoopType = "d" />
  • <cfset intLoopIncrement = 1 />
  •  
  • <!---
  • Set additional conditions to be met. We are going
  • to default to allowing all days of the week.
  • --->
  • <cfset lstDaysOfWeek = "" />
  •  
  •  
  • <!---
  • Check to see what kind of event we have - is
  • it a single day event or an event that repeats. If
  • we have an event repeat, we are going to flesh it
  • out directly into the event query by adding rows.
  • The point of this switch statement is to use the
  • repeat type to figure out what the START date,
  • the type of loop skipping (ie. day, week, month),
  • and the number of items we need to skip per loop
  • iteration.
  • --->
  • <cfswitch expression="#qRawEvent.repeat_type#">
  •  
  • <!--- Repeat daily. --->
  • <cfcase value="1">
  •  
  • <!---
  • Set the start date of the loop. For
  • efficiency's sake, we don't want to loop
  • from the very beginning of the event; we
  • can get the max of the start date and first
  • day of the calendar month.
  • --->
  • <cfset dtFrom = Max(
  • qRawEvent.date_started,
  • dtFirstDay
  • ) />
  •  
  • <!--- Set the loop type and increment. --->
  • <cfset strLoopType = "d" />
  • <cfset intLoopIncrement = 1 />
  •  
  • </cfcase>
  •  
  • <!--- Repeat weekly. --->
  • <cfcase value="2">
  •  
  • <!---
  • Set the start date of the loop. For
  • efficiency's sake, we don't want to loop
  • from the very beginning of the event; we
  • can get the max of the start date and first
  • day of the calendar month.
  • --->
  • <cfset dtFrom = Max(
  • qRawEvent.date_started,
  • dtFirstDay
  • ) />
  •  
  • <!---
  • Since this event repeats weekly, we want
  • to make sure to start on a day that might
  • be in the event series. Therefore, adjust
  • the start day to be on the closest day of
  • the week.
  • --->
  • <cfset dtFrom = (
  • dtFrom -
  • DayOfWeek( dtFrom ) +
  • DayOfWeek( qRawEvent.date_started )
  • ) />
  •  
  • <!--- Set the loop type and increment. --->
  • <cfset strLoopType = "d" />
  • <cfset intLoopIncrement = 7 />
  •  
  • </cfcase>
  •  
  • <!--- Repeat bi-weekly. --->
  • <cfcase value="3">
  •  
  • <!---
  • Set the start date of the loop. For
  • efficiency's sake, we don't want to loop
  • from the very beginning of the event; we
  • can get the max of the start date and first
  • day of the calendar month.
  • --->
  • <cfset dtFrom = Max(
  • qRawEvent.date_started,
  • dtFirstDay
  • ) />
  •  
  • <!---
  • Since this event repeats weekly, we want
  • to make sure to start on a day that might
  • be in the event series. Therefore, adjust
  • the start day to be on the closest day of
  • the week.
  • --->
  • <cfset dtFrom = (
  • dtFrom -
  • DayOfWeek( dtFrom ) +
  • DayOfWeek( qRawEvent.date_started )
  • ) />
  •  
  • <!---
  • Now, we have to make sure that our start
  • date is NOT in the middle of the bi-week
  • period. Therefore, subtract the mod of
  • the day difference over 14 days.
  • --->
  • <cfset dtFrom = (
  • dtFrom -
  • ((dtFrom - qRawEvent.date_started) MOD 14)
  • ) />
  •  
  • <!--- Set the loop type and increment. --->
  • <cfset strLoopType = "d" />
  • <cfset intLoopIncrement = 14 />
  •  
  • </cfcase>
  •  
  • <!--- Repeat monthly. --->
  • <cfcase value="4">
  •  
  • <!---
  • When dealing with the start date of a
  • monthly repeating, we have to be very
  • careful not to try tro create a date that
  • doesnt' exists. Therefore, we are simply
  • going to go back a year from the current
  • year and start counting up. Not the most
  • efficient, but the easist way of dealing
  • with it.
  • --->
  • <cfset dtFrom = Max(
  • CreateDate(
  • (Year( dtFirstDay ) - 1),
  • Month( qRawEvent.date_started ),
  • Day( qRawEvent.date_started )
  • ),
  • qRawEvent.date_started
  • ) />
  •  
  • <!--- Set the loop type and increment. --->
  • <cfset strLoopType = "m" />
  • <cfset intLoopIncrement = 1 />
  •  
  • </cfcase>
  •  
  • <!--- Repeat yearly. --->
  • <cfcase value="5">
  •  
  • <!---
  • When dealing with the start date of a
  • yearly repeating, we have to be very
  • careful not to try tro create a date that
  • doesnt' exists. Therefore, we are simply
  • going to go back a year from the current
  • year and start counting up. Not the most
  • efficient, but the easist way of dealing
  • with it.
  • --->
  • <cfset dtFrom = Max(
  • CreateDate(
  • (Year( dtFirstDay ) - 1),
  • Month( qRawEvent.date_started ),
  • Day( qRawEvent.date_started )
  • ),
  • qRawEvent.date_started
  • ) />
  •  
  • <!--- Set the loop type and increment. --->
  • <cfset strLoopType = "yyyy" />
  • <cfset intLoopIncrement = 1 />
  •  
  • </cfcase>
  •  
  • <!--- Repeat monday - friday. --->
  • <cfcase value="6">
  •  
  • <!---
  • Set the start date of the loop. For
  • efficiency's sake, we don't want to loop
  • from the very beginning of the event; we
  • can get the max of the start date and first
  • day of the calendar month.
  • --->
  • <cfset dtFrom = Max(
  • qRawEvent.date_started,
  • dtFirstDay
  • ) />
  •  
  • <!--- Set the loop type and increment. --->
  • <cfset strLoopType = "d" />
  • <cfset intLoopIncrement = 1 />
  • <cfset lstDaysOfWeek = "2,3,4,5,6" />
  •  
  • </cfcase>
  •  
  • <!--- Repeat saturday - sunday. --->
  • <cfcase value="7">
  •  
  • <!---
  • Set the start date of the loop. For
  • efficiency's sake, we don't want to loop
  • from the very beginning of the event; we
  • can get the max of the start date and first
  • day of the calendar month.
  • --->
  • <cfset dtFrom = Max(
  • qRawEvent.date_started,
  • dtFirstDay
  • ) />
  •  
  • <!--- Set the loop type and increment. --->
  • <cfset strLoopType = "d" />
  • <cfset intLoopIncrement = 1 />
  • <cfset lstDaysOfWeek = "1,7" />
  •  
  • </cfcase>
  •  
  • <!---
  • The default case will be the non-repeating
  • day.Since this event is non-repeating, we
  • don't have to do anything to the envets query
  • as these were the events that we gathered in our
  • ColdFusion query of queries.
  • --->
  • <cfdefaultcase>
  • <!--- Leave query as-is. --->
  • </cfdefaultcase>
  •  
  • </cfswitch>
  •  
  •  
  • <!---
  • Check to see if we are looking at an event that need
  • to be fleshed it (ie. it has a repeat type).
  • --->
  • <cfif qRawEvent.repeat_type>
  •  
  • <!---
  • Set the offset. This is the number of iterations
  • we are away from the start date.
  • --->
  • <cfset intOffset = 0 />
  •  
  • <!---
  • Get the initial date to look at when it comes to
  • fleshing out the events.
  • --->
  • <cfset dtDay = Fix(
  • DateAdd(
  • strLoopType,
  • (intOffset * intLoopIncrement),
  • dtFrom
  • )
  • ) />
  •  
  • <!---
  • Now, keep looping over the incrementing date
  • until we are past the cut off for this time
  • period of potential events.
  • --->
  • <cfloop condition="(dtDay LTE dtTo)">
  •  
  • <!---
  • Check to make sure that this day is in
  • the appropriate date range and that we meet
  • any days-of-the-week criteria that have been
  • defined. Remember, to ensure proper looping,
  • our FROM date (dtFrom) may be earlier than
  • the window in which we are looking.
  • --->
  • <cfif (
  • <!--- Within window. --->
  • (dtFirstDay LTE dtDay) AND
  • (dtDay LTE dtTo) AND
  •  
  • <!--- Within allowable days. --->
  • (
  • (NOT Len( lstDaysOfWeek )) OR
  • ListFind(
  • lstDaysOfWeek,
  • DayOfWeek( dtDay )
  • )
  • ))>
  •  
  • <!---
  • Populate the event query. Add a row to
  • the query and then copy over the data.
  • --->
  • <cfset QueryAddRow( qEvent ) />
  •  
  • <!--- Set query data in the event query. --->
  • <cfset qEvent[ "id" ][ qEvent.RecordCount ] = qRawEvent.id />
  • <cfset qEvent[ "name" ][ qEvent.RecordCount ] = qRawEvent.name />
  • <cfset qEvent[ "date_started" ][ qEvent.RecordCount ] = qRawEvent.date_started />
  • <cfset qEvent[ "date_ended" ][ qEvent.RecordCount ] = qRawEvent.date_ended />
  • <cfset qEvent[ "time_started" ][ qEvent.RecordCount ] = qRawEvent.time_started />
  • <cfset qEvent[ "time_ended" ][ qEvent.RecordCount ] = qRawEvent.time_ended />
  • <cfset qEvent[ "repeat_type" ][ qEvent.RecordCount ] = qRawEvent.repeat_type />
  •  
  • <!---
  • Set the date index to this day. This
  • is the value we will use to display
  • the same event on different days of
  • the calendar.
  • --->
  • <cfset qEvent[ "day_index" ][ qEvent.RecordCount ] = Fix( dtDay ) />
  •  
  • </cfif>
  •  
  •  
  • <!--- Add one to the offset. --->
  • <cfset intOffset = (intOffset + 1) />
  •  
  • <!--- Set the next day to look at. --->
  • <cfset dtDay = Fix(
  • DateAdd(
  • strLoopType,
  • (intOffset * intLoopIncrement),
  • dtFrom
  • )
  • ) />
  •  
  • </cfloop>
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • The display of the calendar is going to be based on
  • ColdFusion query of queries. While these rock harder
  • than all you can eat buffets, they are not the most
  • efficient. Therefore, in order to minimize the query
  • of query acitivity, we are going to maintain an index
  • of days that even have events.
  • --->
  • <cfset objEventIndex = StructNew() />
  •  
  • <!---
  • Loop over the event query to populate the event index
  • with the day indexes.
  • --->
  • <cfloop query="qEvent">
  •  
  • <cfset objEventIndex[ Fix( qEvent.day_index ) ] = 1 />
  •  
  • </cfloop>
  •  
  • </cfsilent>
  •  
  • <cfinclude template="_header.cfm">
  •  
  • <cfoutput>
  •  
  • <h2>
  • #MonthAsString( Month( dtThisMonth ) )# Events
  • </h2>
  •  
  • <p>
  • <a href="#CGI.script_name#?action=add">Add Event</a>
  • </p>
  •  
  • <form action="#CGI.script_name#" method="get">
  •  
  • <select name="month">
  • <cfloop
  • index="intMonth"
  • from="1"
  • to="12"
  • step="1">
  •  
  • <option value="#intMonth#"
  • <cfif (Month( dtThisMonth ) EQ intMonth)>selected="true"</cfif>
  • >#MonthAsString( intMonth )#</option>
  •  
  • </cfloop>
  • </select>
  •  
  • <select name="year">
  • <cfloop
  • index="intYear"
  • from="#(Year( dtThisMonth ) - 5)#"
  • to="#(Year( dtThisMonth ) + 5)#"
  • step="1">
  •  
  • <option value="#intYear#"
  • <cfif (Year( dtThisMonth ) EQ intYear)>selected="true"</cfif>
  • >#intYear#</option>
  •  
  • </cfloop>
  • </select>
  •  
  • <input type="submit" value="Go" />
  •  
  • </form>
  •  
  •  
  • <table id="calendar" width="100%" cellspacing="1" cellpadding="0" border="0">
  • <colgroup>
  • <col width="10%" />
  • <col width="16%" />
  • <col width="16%" />
  • <col width="16%" />
  • <col width="16%" />
  • <col width="16%" />
  • <col width="10%" />
  • </colgroup>
  • <tr class="header">
  • <td>
  • Sunday
  • </td>
  • <td>
  • Monday
  • </td>
  • <td>
  • Tuesday
  • </td>
  • <td>
  • Wednesday
  • </td>
  • <td>
  • Thursday
  • </td>
  • <td>
  • Friday
  • </td>
  • <td>
  • Saturday
  • </td>
  • </tr>
  •  
  • <!--- Loop over all the days. --->
  • <cfloop
  • index="dtDay"
  • from="#dtFirstDay#"
  • to="#dtLastDay#"
  • step="1">
  •  
  • <!---
  • If we are on the first day of the week, then
  • start the current table fow.
  • --->
  • <cfif ((DayOfWeek( dtDay ) MOD 7) EQ 1)>
  • <tr class="days">
  • </cfif>
  •  
  • <td>
  • <a
  • href="##"
  • title="#DateFormat( dtDay, "mmmm d, yyyy" )#"
  • class="daynumber"
  • >#Day( dtDay )#</a>
  •  
  • <!---
  • Since query of queries are expensive, we
  • only want to get events on days that we
  • KNOW have events. Check to see if there
  • are any events on this day.
  • --->
  • <cfif StructKeyExists( objEventIndex, dtDay )>
  •  
  • <!--- Query for events for the day. --->
  • <cfquery name="qEventSub" dbtype="query">
  • SELECT
  • id,
  • name,
  • time_started,
  • time_ended
  • FROM
  • qEvent
  • WHERE
  • day_index = <cfqueryparam value="#dtDay#" cfsqltype="CF_SQL_INTEGER" />
  • ORDER BY
  • time_started ASC
  • </cfquery>
  •  
  • <!--- Loop over events. --->
  • <cfloop query="qEventSub">
  •  
  • <a
  • href="##"
  • class="event"
  • >#qEventSub.name#</a>
  •  
  • </cfloop>
  • </cfif>
  • </td>
  •  
  • <!---
  • If we are on the last day, then close the
  • current table row.
  • --->
  • <cfif NOT (DayOfWeek( dtDay ) MOD 7)>
  • </td>
  • </cfif>
  •  
  • </cfloop>
  • </table>
  •  
  • </cfoutput>
  •  
  • <cfinclude template="_footer.cfm" />

As you can see (maybe) I am using a combination of ColdFusion query of queries as well as event-day-indexing. ColdFusion query of queries, while being one of the best things since sliced bread and the Maria Bello nude scene, are not exactly the most efficient actions to be taking many times on a single page. Therefore, I am also using event-day-indexing to make sure I only perform the ColdFusion query of queries on days that definitely have events.

Also, I grab the basic event definitions out of the database via the Raw Event query. These events, while useful from a theoretical standpoint, do very little to help us actually populate the month-view of the calendar. Therefore, I loop over the raw events and then populate an Event query that takes the raw event data and duplicates it as many times as is necessary for the given time period. Not sure if this is the best way to go, but it is the best that I could come up with over the weekend.

So that's where I am now. In my next post, I will try to tackle more repeat types and event exception handling - that's where it get's really exciting.




Reader Comments

Ben,

Very well-written and I like your approach. In the past, I've used an event table and a linked event date table. My thought at the time was that it would allow forward computability if I ever wanted to add recurring events.

For example, suppose we want to schedule a weekly status meeting. Now suppose we want to post a different agenda for each week's status meeting? Microsoft Outlook allows us to do this - you edit an event and it asks if you want to change every event in the series or if you just want to change this particular event. The latter choice lets us set the agenda for this particular meeting, the former sets the agenda for every meeting in the series.

One way to tackle this would be to have seperate tables for event "meta" information (e.g., event name, location, etc.), and another table for event details information (e.g., the date, description info).

Is there a better way to do this?

Reply to this Comment

@Christian,

I may be completely wrong on this, but this is something I want to try in my next iteration. I think when you edit "Just this occurrence", it is actually creating a NEW event for just that day and then adding an exception for the series. So, the series then becomes (for ex.):

- Every Friday EXCEPT 7/27
- 7/27 is new event with different agenda

I think it happens this way, because I believe (though have not really tested it) is that if you change the start TIME for entire series, it will NOT change the start TIME of the single event (I assume because it is not technically part of the series anymore).

Of course, this is all theory :) Let's see what I come up with.

Reply to this Comment

@Christian,

Also, I think the same thing applies to "all forward instances". That doesn't actually create excpetions for the forward events - I think it actually splits the event into two new events (or created a new event with that start date and updates the old event with the given end date). ... but again, I will be exploring this stuff shortly (hopefully).

Reply to this Comment

Ben, this is very handy and an interesting approach. I have been pondering this topic for the next version of our CMS and have been looking into the multi table approach that Christian mentions. The tough part about having a single record is the searching (with recurring events), which you seem to have done well with. Why is it that calendaring always seems so easy at first, but then all the exceptions and aberrations crop up to slop it up? :-)

Reply to this Comment

@Joshua,

One of the things I have tried to do here is define all of my queries not as "month start" or "month end" but as "first day" and "last day". This way, the time frame we are examining shouldn't make any difference to the algorithm, which should make our lives a whole lot easier.

As far as all the exceptions that crop up to muck up the works... that's the battle I will fight next :D

Reply to this Comment

I happen to have a public-facing calendaring application, so I have had the pleasure of running up against all of these issues and more.

The toughest one is a recurrence type of what I call "Monthly, same days of the week." That's not the greatest description, but it takes into account things like "1st and 3rd Friday each month" or "Last Thursday of the month".

You have to worry about things like "does this month have a fifth Friday" or "which week in the month has the last Tuesday in it." There is just no way to make this code look elegant.

I do have two tables to contain events, one for the actual single day events, the other a linked table which contains metadata for the recurring event groups.

When editing an event that is part of a recurring group, I offer the option to "detach" it from the recurring group, or keep it "attached". If you detach it, subsequent changes to the recurring group will not affect the edited event.

Anyway Ben, nice post and don't drive yourself too crazy, it's easy to do when dealing with calendars.

Reply to this Comment

@Josh,

I like the idea of giving people the option to "detach" exceptions from the series OR not to. I had always just assumed detach by default, but I guess this is not a requirement.

Here's a follow up question: Let's say you have an event that repeats every Friday. Then, one Friday, you detach one event to be on Thursday. So, now, you have a repeating even on Friday except for one week. Then, let's say you edit the "entire" series and set the repeat day to Monday instead of Friday. Does the original exception still exist? Or does the fact that the updated series is now on a different date then void the series exceptions that were based on dates?

I am sure I will help answer these for myself, but I would love some feedback :)

Reply to this Comment

Ben,

I will definately be following this series. Remember the shift calendar item we, well rather I should say you, figured out? That is an example of the repeating every nth day. The shift name would be a parent event for the day and then you could have child events (for example who is off duty) for that day and then the meta data for the event (from when to when, was it vacation, sick leave, comp time etc).

I am still trying to piece togethor every thing for this calendar program. The multiple variables will drive me nuts before I am done.

I eagerly await your posts on this subject

Jim

Reply to this Comment

@Jim,

I am working right now on piecing all the parts of this post together as well. Right now, I am putting a nice interface on the calendar and adding some different views (year / week). Then I have to clean up the logic and then I will just make the code available.

Reply to this Comment

Ben - You are my hero. This is fantastic. I google a problem I'm having and your blog ALWAYS comes up.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.