Need to create a date based reminder

Question

Hello,I have a start date field in my Position table.&nbsp; I need to create a reminder that sends an email to the email address in the Supervisor Email Address field 30 days before the start date in the current year to remind them to complete and submit an annual evaluation.&nbsp; I can't figure out how to write a formula to calculate the number of days from the start date, but in the current year. Also, I'm finding that the reminders won't allow me to pull in the Supervisor Email Address -- while a notification will only fire if the position is added or modified, which wouldn't be the case in this situation.&nbsp; Suggestions on how I might accomplish this? Do I need to parse the date into separate fields like month and day and then try to create the same month and day but with the current year? If so, how do I do that?Thanks in advance for any help/advice!------------------------------Andrea Johannes------------------------------

paulpeterson1 · Answer

You could try something like this:var number serviceYears = If(Year(Today()) &gt; Year([Start Date]), Year(Today()) - Year([Start Date]), 0);If($serviceYears &gt; 0, AdjustYear([Start Date], $serviceYears) - Days(30), null)------------------------------Paul Peterson------------------------------

andreajohannes · Answer

Hi Paul,&nbsp;Thanks for responding!&nbsp; Would that be a separate field that I am creating and that is what I would use? or is that something I would embed in either the notification or reminder?------------------------------Andrea Johannes------------------------------

paulpeterson1 · Answer

Yes, it would need to be a new field. And looking at the Reminder setup options, you could omit the "- Days(30)". Suppose you name the new field Anniversary Date, when you create the reminder set it to Remind When 30 days Before the date in the field Anniversary Date.

------------------------------
Paul Peterson
------------------------------

andreajohannes · Answer

Thank you Paul! I was able to create a field using that formula and it's working great.&nbsp; Now I need to be able to send an email to the email address in the superivsor email field.&nbsp; The reminder won't let me do that - and the notification requires something to happen to the position record in order to fire.&nbsp; Is there another way to generate that email to the email address in the supervisor field other than using reminders or notifications?------------------------------Andrea Johannes------------------------------

paulpeterson1 · Answer

You can create a formula - user field, Reminder User, and use this formula to convert the email to a user:ToUser([Supervisor Email])Then in the reminder for Remind Whom select "The user listed in the field: Reminder User"If you want to send a notification instead, you can create a Formula - Checkbox.&nbsp; Depending on which of the options you used above.&nbsp; If you used the Anniversary Date idea, the formula checkbox would be:[Anniversary Date] - Days(30) = Today()If you used the first suggestion to calculate the reminder date your formula would be:[Reminder Date] = Today()You will need one more checkbox field, say Trigger Notification.&nbsp;&nbsp;Then create scheduled a pipeline to run every night (ok, early morning.&nbsp; I normally schedule mine to run around 2:00 am) that will search the table for any records with a check in your formula checkbox.&nbsp; In the Do loop, update the record to add a check in the trigger notification checkbox and have your notification fire when the trigger notification checkbox is checked.&nbsp; You will need one more step to get ready for next year.&nbsp; Do another search, this time search for records with both the formula checkbox and the trigger notification checkbox checked.&nbsp; In the Do loop, update the record to remove the check from the trigger notification checkbox.&nbsp; Without that step the notification will only be triggered on the first year.I think the reminder method is much less work.------------------------------Paul Peterson------------------------------

Forum Discussion

Need to create a date based reminder

5 Replies

Related Content

Send Weekly Reminder Email based on Criteria

Filter records based on table connection

Email Reminders

New Form - Show Create Record button

Create a formula duration over 5 years

Recent Discussions

Save & Redirect in a Formula URL Using API_GenAddRecordForm

Many to Many too Many...

Are all QB notifications "No-Reply" emails

Bulk create child records from selection list

Selecting which fields to display from Formula URL searches