Automating Quarterly/Annual Reminders

I need to setup automations based on a contract date that will notify me on a monthly/quarterly/annual basis so I can process paperwork at the appropriate time.

Ideally, I'd like to set it up to send an email 90 days before that date.

I have columns for the initial contract date and the frequency (one for the word "quarterly" and one for the number "4"), as well as some automated columns that give me the quarter/year of the date, but the tracking sheet will cover dozens of projects for decades, so I don't want to load it up with hundreds of blank rows just to pre-populate the dates.

The goal is to have the automation give someone a heads up that a new row will need to be created and all paperwork started 90 days before the next date in the series would happen.

Could anyone help me come up with helper columns and/or automation criteria that could look at the initial contract date and the frequency and have it spit out those emails at the appropriate times?

Find more posts tagged with

Automations

dates

Accepted answers

All comments

Paul Newcome

Have you tried setting up an automation triggered to run daily and then setting a condition of the date being 90 days in the future?

S.Stone

Well, the contract date is currently in the past. Let's say we signed it on January 1, 2020, and we need to do this annually. How can the automation look at January 1, 2020 and know to send an email on October 03, 2021; October 03, 2022… October 03, 2040 (however long the total term is - maybe 50 years).

S.Stone

(see previous comment)
I don't want to write out all 50 of the January 1, 2020 - January 1, 2070, date for every project on this tracker. That will create hundreds/thousands of blank rows just for the dates.

The rows don't need to be created until we approach the next term. We just want to know the next time we're approaching that month/date in whatever year it currently is.

Paul Newcome

Ah. Ok. I think I understand now. Sorry about that.

So when the reminder goes out, are users entering a new row which would then be used to send the next reminder (multiple rows per project), or do you need these reminders to go out on the same row on a recurring basis (single row per project)?

If users are getting the reminder and then inserting a new row, you could use a formula to look at the date on that row and just add the appropriate number of days.

=[Date Column]@row + IF(Frequency@row = "Annually", 365, IF(Frequency@row = "Quarterly", 90, 0))

S.Stone

Well, they will be entering new rows (multiple rows per project), but they're all nested by the projects, so trying to use the most recent row ends up being a moving target as they add rows here or there.

I will have set dates for the contract date, so I could lock in something around that, but it will need to be a new term each year (one for 365, one for 730… out to 1825 to give a new alert for each year….) and some are monthly and some are quarterly, so that would be a whole lot of automations.

S.Stone

So we'll have the initial date on the left, and each row is a new invoice nested under the project. Then we'll be creating new rows when payments are made/received and when corresponding paperwork is completed.

So we need to know when those transactions and paperwork processes will need to be initiated.

Paul Newcome

Would it be Annually / Quarterly based on the previous Payment Date? Basically, If I see it is for the same project, and it is marked as quarterly, can I basically say that the date on "this row" is 90 or 365 days after the previous date for this project?

S.Stone

Well, different invoices happen for different projects. I won't have a single invoice for one project, but I'll have multiple invoices for each project, all going out for many years. We might have 12 contracts for one project and 50 for another and just 1 for another.

So Project Beta may be on Rows 20-50, and the different contracts may start on Rows 20, 30, and 40, but if Project Alpha gets more invoice rows (every month or quarter), that switches the invoice rows for Project Beta to 21, 31, and 41.

Would it matter to the automation if those rows are a moving target?

I could use the date of the previous invoice to have a constant 90/365/whatever alert, but I'd need to make sure it's setup to know which rows are tied to which contracts/invoices.

Perhaps I need to nest invoices to a new hierarchy level and add a primary contract row for each of the projects. Then maybe I could run a formula that looks for the most recent date in the invoice column and copies that date to the new contract row. Then the automation could look to that date to find the most recent for the lot of them…

S.Stone

(more on my previous reply)

I did something similar with this formula before and some auto number columns. It'll look for the latest number and copy the details over to a new column. I might be able to use a version of this to get the latest invoice date to a designated place.

=IF([First Instance]@row = "✅", IF(Ancestors@row = 2, IFERROR(INDEX(COLLECT([Type of Contract]:[Type of Contract], [Project - Landowner]:[Project - Landowner], [Project - Landowner]@row , [Date Contract Executed]:[Date Contract Executed], MAX(COLLECT([Date Contract Executed]:[Date Contract Executed], [Project - Landowner]:[Project - Landowner], [Project - Landowner]@row ))), 1), ""), ""))

Would the automation care, though, if those dates keep moving to a different row as more rows are added above?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.