Oct 25, 2023 07:59 AM
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:
To solve for this I created a single table called Internal Calendars and included the following fields:
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:
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.
Dec 19, 2023 05:28 PM
Thank you so much Jeff for this insightful post.
I have been looking at a formula for recurring events with different frequencies but am struggling to have an end date for the recurrence. I like your idea of "Run Until" but I am still unsure of how to apply it as an Airtable formula or automation.
Do I have to build the table from scratch with coding script or do you know if there is a way to do it with existing Airtable formulas?
Appreciate your support.