May 31, 2022 01:47 AM
I am trying to setup a table which will contain reminders for when I want to connect with my clients.
Basically, I want to setup weekly/fortnightly/monthly reminders based on day of the week.
Like so -
Weekly - Thursday,
Monthly - 1st week Friday,
Monthly - 3rd week Tuesday
Now, the reminder should be mailed to me like this -
Daily reminder - Based on the given day (weekly and monthly)
Weekly reminder - All reminders in that week
Monthly reminder - All reminders with Monthly frequency
I’m feeling very confused as I’ve hit my brain on this for few hours wondering how to make it work.
I have read lots of support posts and docs as well, and have given up now.
Any help would be appreciated.
May 31, 2022 03:45 AM
Hi Mayank, I’m assuming you’ve got the daily and weekly reminders set up fine, and are facing issues with setting up the 1st week Friday and 3rd week Tuesday ones.
Someone else had a similar problem and the approach I outlined there will be the same for you
I’ve also put something together here for you that you can modify to your needs. To view the setup (formulas, automations etc), duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button
May 31, 2022 11:25 PM
Actually so far I am only clear on the Monthly reminder.
I will simply automate mailing of all the names.
However, for the weekly reminder - it should of course includes all the weekly reminders, but it should also include the Monthly reminders that fall WITHIN that week.
So lets say there’s a monthly reminder - 3rd week Friday - this will go on the 1st of the month, as well on the 3rd week of every month in the weekly reminder AND on the 3rd Friday of the month - as the daily reminder.
I am getting confused how do I make Airtable recognize that the 3rd week Friday falls in 3rd week of the month and how do I fetch the exact date of 3rd week’s Friday on every month.
Jun 01, 2022 12:50 AM
Ah okay. This seems pretty complex and I don’t really understand what we’re trying to do. Could you provide an example of the reminders you would want sent in a month perhaps?
Hmm, could you check out the base I set up in my previous post to see whether that helps?
Jun 01, 2022 03:35 AM
I’ll share a sample base with you here
I’ll also explain what I wish to achieve -
Objective: Stay connected to my clients; some daily, some weekly, some monthly.
So, Monthly reminder will include names of ALL the clients since they fall in the month.
Weekly reminder will include names of all clients falling WITHIN that week, and this can include monthly clients as well who are scheduled for that week. Say Client A is scheduled for 2nd week June, so he will come in the monthly reminder, as well as 2nd week’s weekly reminder, as well as on 2nd June daily reminder.
Daily reminder will include names of only the clients coming on that date. This should be simple, but actually since I am not noting exact dates - I am using 1st week Thursday (since the minimum frequency is weekly)- I need to convert it to a date each month and then send the reminder - I am stuck on this.
Jun 01, 2022 05:25 AM
Hey Adam, I think I’ve solved this.
I’ll explain in case anyone stumbles across same issue in future.
Basically, I had setup daily/weekly/monthly reminders - which are not based on a specific date, but based on specific week and day - say 1st week Friday or 3rd week Friday.
Now monthly reminder is the easiest part, it will simply send all the reminders during the month.
Weekly reminder - It should only send reminders within that week , so this may include Daily, Weekly and Monthly reminders falling within that week.
How do we find that? Well, we match the week number with the current month’s week number.
Daily reminder - Same as above. We match Date number of the day with the Date number of today.
When both weekly reminder and daily reminder match, it will go as a Daily reminder
And when only the weekly reminder matches, it will go as a daily reminder!