Re: Creating a Timeline with Computed Calendar Data
I actually modified the setup since I wrote that, so it’s no longer one date driving everything. The new setup isn’t too different, and it’s actually a lot easier to maintain than my first attempt.
The class I teach meets twice per week—once for that week’s lesson, another for review—and I have both morning and evening classes. To set this up, I made a table named [AM/PM] that contains only two records:
I also have a [Sessions] table where I laid out the schedule for a single class for the entire term. In the {A/B} field, A represents the first session in the week (the review session), and B is the second (lesson) session of the week. (There are more fields, but I’m only showing what’s relevant to the calendar setup)
I then made a [Schedule] table, where I built both morning and evening classes by linking to appropriate records from [AM/PM] and [Sessions]. This part only shows the beginning of the morning class setup. A similar set of records is farther down the table for the evening class.
I use rollup fields to pull in the relevant datetime items from the [AM/PM] table, so that each of the records has the correct start time for the very first class of the term.
Because I’m only pulling in a single value, using values for the aggregation formula returns a single datetime item, not an array. I left them unformatted, as I need them to stay as datetime items for later calculations.
Now comes the formula fun. In my case, I also have systems in place to allow for a session to be canceled or rescheduled, and that’s all handled by this one formula as well. The core of it, however, just adds the appropriate number of weeks to the appropriate base datetime item based on the session’s specified week and A/B (review/lesson) status.
IF(
OR(
Override,
Cancel
),
IF(
Override,
Override
),
DATEADD(
SWITCH(
{A/B},
"A", {Review Session Base},
"B", {Lesson Session Base}
),
Week - 1,
"weeks"
)
)
With these dates calculated, I built separate calendar views for both morning and evening classes, along with iCal links which I use myself and share with my students.
