Posting this in case this can help anyone solve an issue or in case there is anyone who can think of how to do it better. I was asked to create a corporate event calendar that housed all 5 corporate event calendars in a single base and included 3 specific items:
- Enforce all event times have a time zone of PST
- Allow easy All Day or Continuous All Day Meetings
- Create recurring events that can be created daily, weekly, bi-weekly, monthly, quarterly, semi-annually or annually. Additionally there was a caveat that one calendar type needed to include weekends while the others did not
To solve for this I created a single table called Internal Calendars and included the following fields:
- Event Occurrence - Single Select that allows the user to pick the type of scheduling for their event(s): All Day, Continuous All Day, Conference Event, Recurring Event and Holiday
- Start Day - Date field used to select the date an event will start.
- Start Time - Single Select that has times in 30-minute intervals.
- End Day - Date field for event End.
- End Time - Single Select field with the same settings as Start Time.
- Frequency - Single Select for Recurring Event occurrences with the available options
- Run Until - Date field used to calculate when to stop calculating recurring events
- Other fields for the event details
Additionally I used record templates so I could create custom forms for each calendar type to pre-fill some of the other fields not related to the solution but so I could also filter the times they can pick in form as well as using conditional visibility so fields like Start/End Time, Frequency and Run Until only show for relative occurrence selections. For example if you select All Day you only see the Start Day field, if you select Continuous All Day you only see Start Day and End Day but when Recurring Event is select it shows all fields.
Once I set all of those fields I created an automation on record creation it would set the time field options to 12:00 AM and 11:59 PM if either All Day types were chosen or handled the recurring event functionality outlined further down.
I struggled with how to create a formula that combined the two dates while enforcing the same time zone for everyone and auto-adjusting for Daylight Savings time, but I believe I got it solved (will be able to test it in a few weeks when DST ends and make any adjustments) and wanted to share. The formula below is for the event start and is the same for event end just with the End fields instead:
IF(AND({Start Day} >= DATETIME_PARSE(YEAR({Start Day}) & "-03-" & (8 + (7 - WEEKDAY(DATETIME_PARSE(YEAR({Start Day}) & "-03-08"))))),{Start Day} < DATETIME_PARSE(YEAR({Start Day}) & "-11-" & (1 + (7 - WEEKDAY(DATETIME_PARSE(YEAR({Start Day}) & "-11-01")))))),DATEADD({🕒 Start}, -1, 'hour','MM/DD/YYYY h:mm A'),DATEADD({🕒 Start}, 0, 'hour','MM/DD/YYYY h:mm A'))
Finally with my calculated date fields being done and visible on the calendar correctly I was then able to create the automation action for when Recurring Event is selected. For this action I used the Run a Script option and designed the script to take do a couple of things:
- Looks at the selected Frequency option and Run Until date field which it uses to calculate the number of events needed.
- Additionally it is set so if the Start Day is on a Weekend it includes all days of the week into the calculation but if Start Day is a work day it only calculates using weekdays. Note: I am working to add additional holiday skipping into my calculations but will be much later on.
Finally once the above calculations and conditions are completed it creates the recurring event records carrying over the static fields we selected as well as setting the Event Occurrence field to Recurrence (so calendar Admins can easily determine the original event vs the created events).
// Get the triggering record's details
let table = base.getTable("Internal Calendars");
let records = await table.selectRecordsAsync();
let record = records.getRecord(input.config().recordId);
let runUntil = new Date(record.getCellValue("Run Until"));
let startDay = new Date(record.getCellValue("Start Day"));
let endDay = new Date(record.getCellValue("End Day"));
let frequency = record.getCellValue("Frequency").name;
// Determine if Start Day is a weekend
let isWeekend = (startDay.getDay() === 0 || startDay.getDay() === 6);
while (startDay <= runUntil) {
// Only create records after the initial date
if (startDay > new Date(record.getCellValue("Start Day"))) {
let newRecord = {
"Title": record.getCellValue("Title"),
"Purpose": record.getCellValue("Purpose"),
"Start Day": startDay.toISOString().split('T')[0],
"Start Time": record.getCellValue("Start Time"),
"End Day": endDay.toISOString().split('T')[0],
"End Time": record.getCellValue("End Time"),
"Event Occurrence": { id: "selG7czNrd7PEfF4n" },
"Frequency": record.getCellValue("Frequency")
};
// Create the new record
await table.createRecordAsync(newRecord);
}
// Increment startDay and endDay based on frequency
switch (frequency) {
case "Daily":
if (isWeekend) {
// Include weekends
startDay.setDate(startDay.getDate() + 1);
endDay.setDate(endDay.getDate() + 1);
} else {
// Skip weekends
do {
startDay.setDate(startDay.getDate() + 1);
endDay.setDate(endDay.getDate() + 1);
} while (startDay.getDay() === 0 || startDay.getDay() === 6);
}
break;
case "Weekly":
if (isWeekend) {
// Include weekends
startDay.setDate(startDay.getDate() + 7);
endDay.setDate(endDay.getDate() + 7);
} else {
// Skip weekends
do {
startDay.setDate(startDay.getDate() + 7);
endDay.setDate(endDay.getDate() + 7);
} while (startDay.getDay() === 0 || startDay.getDay() === 6);
}
break;
case "Bi-Weekly":
if (isWeekend) {
// Include weekends
startDay.setDate(startDay.getDate() + 14);
endDay.setDate(endDay.getDate() + 14);
} else {
// Skip weekends
do {
startDay.setDate(startDay.getDate() + 14);
endDay.setDate(endDay.getDate() + 14);
} while (startDay.getDay() === 0 || startDay.getDay() === 6);
}
break;
case "Monthly":
if (isWeekend) {
// Include weekends
startDay.setMonth(startDay.getMonth() + 1);
endDay.setMonth(endDay.getMonth() + 1);
} else {
// Skip weekends
do {
startDay.setMonth(startDay.getMonth() + 1);
endDay.setMonth(endDay.getMonth() + 1);
} while (startDay.getDay() === 0 || startDay.getDay() === 6);
}
break;
case "Quarterly":
if (isWeekend) {
// Include weekends
startDay.setMonth(startDay.getMonth() + 3);
endDay.setMonth(endDay.getMonth() + 3);
} else {
// Skip weekends
do {
startDay.setMonth(startDay.getMonth() + 3);
endDay.setMonth(endDay.getMonth() + 3);
} while (startDay.getDay() === 0 || startDay.getDay() === 6);
}
break;
case "Semi-Annually":
if (isWeekend) {
// Include weekends
startDay.setMonth(startDay.getMonth() + 6);
endDay.setMonth(endDay.getMonth() + 6);
} else {
// Skip weekends
do {
startDay.setMonth(startDay.getMonth() + 6);
endDay.setMonth(endDay.getMonth() + 6);
} while (startDay.getDay() === 0 || startDay.getDay() === 6);
}
break;
case "Annually":
if (isWeekend) {
// Include weekends
startDay.setFullYear(startDay.getFullYear() + 1);
endDay.setFullYear(endDay.getFullYear() + 1);
} else {
// Skip weekends
do {
startDay.setFullYear(startDay.getFullYear() + 1);
endDay.setFullYear(endDay.getFullYear() + 1);
} while (startDay.getDay() === 0 || startDay.getDay() === 6);
}
break;
}
}
This was the first script automation I have ever written and also the first time I tried using separate fields for my date and time selections. I hope this is able to help anyone trying to figure out something similar for recurring calendar events but also feel free to let me know if I made any mistakes in my formulas/script or if it can be simplified further.
