Help With Automation Notifications

Our company uses smartsheet to track our employees' birthdays. We use automation to send an email to remind us about upcoming birthdays four days prior to the birthday, and then the morning of the birthday. The problem is that when the notification is sent four days prior to the birthday it also sends an email everyday leading up to the birthday. I want to set the automation to send an email four days before and then the day of, not the other three days in between. Does anyone know the conditions to set to make this happen? Thanks in advance.

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are ways to do this within the automation itself, but I find it is easier to create a date type column and use a formula to output the date of 4 days prior then trigger your first automation to run on the date in this column.

    =DOB@row - 4

  • hvance
    hvance ✭✭

    Thank you Paul - are you also able to explain it using automations?

  • KPH
    KPH ✭✭✭✭✭✭

    Hi,

    In the automation there are some options that are a bit hard to find (you need to click the down arrows to reveal your options). You want to set it up like this:

    Then set a separate automation for the day of.


    BUT

    Smartsheet automations do not have working days as options for the alert date (and some date options don't exist such as, if you want your alert to be more than 1 week but less than 2, or more than 1 month but less than 2). So Paul's suggestion of putting the date in the sheet usually gives more flexibility.

    In your case, if the birthday is a weekend you might miss it. You can get around this by adding an extra column to your sheet as Paul described, but instead of 4 days before calculate 4 working days before, then use this in your automation.

    Assuming you have working and non-working days set on the Project Settings, then......

    This would give you the date 4 working days before the birthday

    =WORKDAY(Birthday@row, -4)

    This would give you the birthday, or the working day before the birthday (assuming day 1 (is Sunday) and 7 (is Saturday) and these are your only non-working days)

    =IF(WEEKDAY(Birthday@row) = "7", Birthday@row - 1, IF(WEEKDAY(Birthday@row) = "1", Birthday@row - 2, Birthday@row))