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
)
)))
