Help

Re: Create Recurring Events on the 1st and 3rd Saturdays of the month

599 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Mike_Scheid
4 - Data Explorer
4 - Data Explorer

We have volunteer events that we need to reoccur on specific days of the month. For example, Zuma Ranch has volunteer orientation on the 1st and 3rd Saturdays of every month. How can I automate the date to update to the next Tuesday it will happen so the date updates on the listing? Any ideas would be greatly appreciated!!!

Screen Shot 2021-02-20 at 11.00.00 AM

4 Replies 4

I solved this problem (or at least one similar to it) a while back, initially using a series of formula fields (really messy), and later converting that logic into JavaScript as part of an automation script action. I no longer have the formula-driven setup, but I’m happy to share the script version. The only issue is that your base needs to be in a Pro plan workspace (or higher) in order to use automation script actions. If that will work, let me know, though be aware that I won’t have time to alter my current script (which only looks works for one such ordinal option per month) for a few weeks. We’re moving across town a week from today, and my time is mostly taken up prepping for the move.

Justin I would be grateful for the JavaScript and can work to edit it as needed.

Okay. I’ll make a note to post what I’ve got on Monday.

@Mike_Scheid Here’s the portion of my script that parses the next ordinal date (e.g. first Monday, second Thursday, etc.) after the current one passes. Right now it’s only designed to work with one such date per month, but I’m confident that a tweak to this setup could support multiple ordinal dates per month (e.g. first and third Saturday).

let due = new Date(record.getCellValue("Due") + `T00:00:00-08:00`);
const freqItems = record.getCellValue("Freq.");
const freq = freqItems.map(item => item.name).join("");
const weekdays = ["Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"];
const ordinalOpts = ["First", "Secon", "Third", "Fourt", "Fifth"];

// Set the ordinal value
const ordinalValue = ordinalOpts.indexOf(freq.substr(0, 5)) + 1;
const ordinalWeekday = ordinalValue > 0 ? weekdays.indexOf(freq.slice(-2)) : null;
let ordinalDate = null;
let intervalDate = null;

// If we have both ordinal options, calculate the new date accordingly
if (ordinalValue > 0 && ordinalWeekday) {
    console.log(`Found an ordinal item: ${record.name}\nFrequency: ${freq}`);
    // Calculate the next month 
    let nextMonth = new Date(due.getTime());
    // Add one month, then reset the day back to the 1st
    nextMonth.setMonth(nextMonth.getMonth() + 1);
    nextMonth.setDate(1);
    let firstWeekday = nextMonth.getDay();
    // IF({Next Mth 1st Day Wkday} < {Ordinal Wkday}, {Ordinal Wkday} - {Next Mth 1st Day Wkday}, 7 - ({Next Mth 1st Day Wkday} - {Ordinal Wkday})) + (7 * ({Ordinal Value} - 1)))
    let ordinalOffset = (firstWeekday < ordinalWeekday ? ordinalWeekday - firstWeekday : 7 - (firstWeekday - ordinalWeekday)) + (7 * (ordinalValue - 1));
    let newDatePre = new Date(nextMonth.getTime());
    newDatePre.setDate(newDatePre.getDate() + ordinalOffset);
    if (newDatePre.getMonth() == nextMonth.getMonth())
        ordinalDate = new Date(newDatePre.getTime());
    console.log(`New ordinal date: ${ordinalDate}`);
       
}

This is nested inside a loop that processes all of my scheduled tasks. Each of these tasks has unique frequency options set in the{Freq.} field (multiple select). The earlier part of this script chunk reads that field and mashes the selected options together, then parses the resulting string (I could probably do this via the original array, but this is how I last left the script when I wrote it last year).

The ordinal options all have at least five characters, so I check the first five characters of the string to see if it’s one of the listed ordinal options, and get its position in the array (plus one) if found. If the mashed string is something like “FirstSa”, for example, then ordinalValue becomes 1 and ordinalWeekday becomes 6. It then goes into the if statement and calculates the next date. I used the logic that I’d worked out from my original formula field setup (including putting the original formula as a comment above the main line that does most of the heavy lifting). This is where the tweaks would need to go to support more than one ordinal option. I’m thinking about putting all of the ordinal choices first, then the selected weekday—e.g. ["First", "Third", "Sa"]— and probably parsing the original array instead of the mashed string.

I can see this taking me at least an hour to update and test, and I’ll probably update it for my own use anyway, but like I said above, I just don’t have the time to invest to tweak the code until late next week at the earliest. If you haven’t got it worked out by then, I’ll let you know when I get my version working.