Alert Automation Question

Options

Does anyone have a good suggestion on how to setup an alert to remind someone to do something 10 days before a specific end date?

For example (using screen shot below). I want the "Assigned To" for the "Schedule Container" task to be reminded 10 days before the end date of ""Produce Fabric" to work with customer care to schedule a container. Also, what happens if the end date for "Produce Fabric" gets moved up or pushed out? How will that affect the reminder?

Any thoughts are greatly appreciated.



Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Anthony DAmbrosio

    To trigger an alert on a duration that is not built into the Automation wizard, you will need a helper Text/Number column. You will be able to hide the column once it is built.

    The formula in the helper column is

    =IF(ISDATE([End Date]@row), [End Date]@row-TODAY())

    Then, trigger your automation as a When Rows are added/changed. Note I added a condition that would not trigger if the task as complete. Use whatever indicator you have, if not %complete, to not trigger completed rows.

    Select the helper column you just made. You can add the number 10 as the value


    If the End Date is moved to a later date, the formula will readjust itself and would trigger when 10d was reached. If you move the End Date earlier, the 10d window could have already be passed.

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Anthony DAmbrosio

    To trigger an alert on a duration that is not built into the Automation wizard, you will need a helper Text/Number column. You will be able to hide the column once it is built.

    The formula in the helper column is

    =IF(ISDATE([End Date]@row), [End Date]@row-TODAY())

    Then, trigger your automation as a When Rows are added/changed. Note I added a condition that would not trigger if the task as complete. Use whatever indicator you have, if not %complete, to not trigger completed rows.

    Select the helper column you just made. You can add the number 10 as the value


    If the End Date is moved to a later date, the formula will readjust itself and would trigger when 10d was reached. If you move the End Date earlier, the 10d window could have already be passed.

    Does this work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!