Dynamic Reminder Notification
Question:
I would like to automate an email notification to the assignee based on a date column, which has intervals of Monthly/Quarterly/Bi-Annually/Annual for the subsequent notification.
Technical Challenges:
Based on my findings, there are no direct features to support this.
- Automation> Alert someone
- Notification based on Custom Recurrence: This will not work as its using a single fixed date for all the tasks. But the requirement is that each task has it's own reminder date and date intervals.
- Notification based on Date Field: This does allow us to send a notification based on the date field, but updating the new dates dynamically with the date interval seems to be a challenge or may be impossible. Since we can't replace the old date with the new date using automation. I was thinking maybe we may need another sheet and using the "move row" or "copy row" function with "index".
Also, i'm about to generate the "Following Reminder Date" with the below formula, my question is how can I update the Following Reminder Date as the new updated date(Upcoming Reminder Date) once a task has sent a notification and is complete.
=IF
(
CONTAINS
(
"monthly", LOWER
(
PARENT(
Task@row
)
))
, DATE
(
YEAR(
[Upcoming Reminder Date]@row
) + IF
(
MONTH(
[Upcoming Reminder Date]@row
) + 1 > 12, 1, 0
)
, MOD
(
MONTH(
[Upcoming Reminder Date]@row
), 12
)
+ 1, DAY(
[Upcoming Reminder Date]@row
)
)
, IF
(
CONTAINS
(
"quarterly", LOWER
(
PARENT(
Task@row
)
))
, DATE
(
YEAR(
[Upcoming Reminder Date]@row
) + IF
(
MONTH(
[Upcoming Reminder Date]@row
) + 3 > 12, 1, 0
)
, MOD
(
MONTH(
[Upcoming Reminder Date]@row
) + 3 - 1, 12
)
+ 1, DAY(
[Upcoming Reminder Date]@row
)
)
, IF
(
CONTAINS
(
"bi-annually", LOWER
(
PARENT(
Task@row
)
))
, DATE
(
YEAR(
[Upcoming Reminder Date]@row
) + IF
(
MONTH(
[Upcoming Reminder Date]@row
) + 6 > 12, 1, 0
)
, MOD
(
MONTH(
[Upcoming Reminder Date]@row
) + 6 - 1, 12
)
+ 1, DAY(
[Upcoming Reminder Date]@row
)
)
, IF(CONTAINS
(
"annually", LOWER
(
PARENT(
Task@row
)
))
, DATE
(
YEAR(
[Upcoming Reminder Date]@row
) + 1, MONTH(
[Upcoming Reminder Date]@row
), DAY(
[Upcoming Reminder Date]@row
)
)
,
[Upcoming Reminder Date]@row
)
)))
Best Answer
-
Hi @zen1024
Quick thoughts on overcoming the challenges to what I believe you are trying to achieve:
- How about an automation that runs each day (Custom Recurrence) and sends a reminder to those where the date the reminder is due is the current day.
- The final step of the automation is to record the date that the automation ran.
- Use a formula to update the reminder date based on the date the automation last ran and the interval.
To get it started, instead of populating the reminder date column you will need to populate the date the automation last ran column with the reminder date minus the interval and have the formula create the reminder date.
The automation would look like this:
Hope that helps.
Answers
-
Hi @zen1024
Quick thoughts on overcoming the challenges to what I believe you are trying to achieve:
- How about an automation that runs each day (Custom Recurrence) and sends a reminder to those where the date the reminder is due is the current day.
- The final step of the automation is to record the date that the automation ran.
- Use a formula to update the reminder date based on the date the automation last ran and the interval.
To get it started, instead of populating the reminder date column you will need to populate the date the automation last ran column with the reminder date minus the interval and have the formula create the reminder date.
The automation would look like this:
Hope that helps.
-
Hi @zen1024
I answered a similar question in this thread.
I modified the settings to suit your column names, and it worked as expected.
In the setting, I have a copier sheet that looks up the Scheduler sheet for tasks, [Following Reminder Date], etc.
The lookup formula puts the [Following Reminder Date]'s value into [Upcoming Reminder Date].I have an automation to record the date when the Status changes to Complete, and I modified your formula to use the [Complete Date] in place of the [Upcoming Reminder Date].
The copier sheet utilizes time-based automation to copy a row when the "Copy" option is checked, which is checked by formula if the [Upcoming Reminder Date] contains a date.
The Scheduler sheet has a helper column, [rankeq], which counts the number of duplicate name task rows. When we have a duplicate by the copy-row-automation from the copier sheet, another automation moves the row to an archive sheet.
My setting uses hierarchy, but a dropdown column Cycle.
So, in short, the process is as follows: Status = Complete > Complete Date > Following Reminder Date > Copier Sheet Upcoming Reminder Date > Copy row > Archive duplicate row.
You can use a report to make the sheet look like your setting.
Below is the sheet setting and formulas. I use an archive sheet as well.
Regarding automation setup, please refer to the original comment above.
[Row] =MATCH([Row ID]@row , [Row ID]:[Row ID], 0)
[Following Reminder Date] =IF(ISDATE([Complete Date]@row ), IF(CONTAINS("monthly", Cycle@row ), DATE(YEAR([Complete Date]@row ) + IF(MONTH([Complete Date]@row ) + 1 > 12, 1, 0), MOD(MONTH([Complete Date]@row ), 12) + 1, DAY([Complete Date]@row )), IF(CONTAINS("quarterly", Cycle@row ), DATE(YEAR([Complete Date]@row ) + IF(MONTH([Complete Date]@row ) + 3 > 12, 1, 0), MOD(MONTH([Complete Date]@row ) + 3 - 1, 12) + 1, DAY([Complete Date]@row )), IF(CONTAINS("bi-annually", Cycle@row ), DATE(YEAR([Complete Date]@row ) + IF(MONTH([Complete Date]@row ) + 6 > 12, 1, 0), MOD(MONTH([Complete Date]@row ) + 6 - 1, 12) + 1, DAY([Complete Date]@row )), IF(CONTAINS("annually", Cycle@row ), DATE(YEAR([Complete Date]@row ) + 1, MONTH([Complete Date]@row ), DAY([Complete Date]@row )), [Complete Date]@row )))))
[Rankeq] =RANKEQ([Row ID]@row , COLLECT([Row ID]:[Row ID], Task:Task, Task@row ), 0)
[Task] =IFERROR(INDEX(DISTINCT({Task}), MATCH([#]@row , {Row}, 0)), "")
[Description] =IFERROR(INDEX({Description}, MATCH(Task@row , {MQA Task Scheduler : Task Name}, 0)), "")
[Assigned To] =IFERROR(INDEX({Assigned To}, MATCH(Task@row , {Task}, 0)), "")
[Upcoming Reminder Date] =MAX(COLLECT({Following Reminder Date}, {Task}, Task@row ))
[Cycle] =IFERROR(INDEX({Cycle}, MATCH(Task@row , {Task}, 0)), "")
[Copy] =ISDATE([Upcoming Reminder Date]@row ) -
Thank you both. I managed to get it working. Really appreciate the help here.
-
Excellent news. I'm pleased we were able to get you moving.
Help Article Resources
Categories
Check out the Formula Handbook template!