Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Alert or notification based on date
I am searching for way to create an notification or a reminder based on a contract's expiration date. I have checked SmartSheet's help data but have found no way to make this happen.
Can anyone assist me with how to do this .... all we want to be able to have, is when a contract is within 3 months of its expiration date, a notice or reminder gets sent to a user, alerting them to the expiration, so they can take appropriate action.
Thank you for your help!
Comments
-
- You will need a contact list column that contains the user's email. They will have to be shared on the sheet-- which could pose some problems.
- You will need a date column with the formula =[expirationdatecolumns]3-90 which will give you the date 90 days before the expiration date. This assumes you are placing the formula in row 3 and you should substitute the name of your expiration date column.
- Click on Alerts > Reminders then, Send "User Column" an email on "Reminder Column."
That should do the trick. if you don't want to share the smartsheet with the user then you could have it email you and you could send out emails with a canned script when you are reminded.
-
If you absolutely positively need to send the email to an email address that is not shared to the Sheet, there are ways to do it using Zapier (www.zapier.com) and several of the email App's that can be integrated with Smartsheet.
Craig
-
Here's a different take based on a similar problem with patent filings.
Without getting into the definitions (Google it!), upon filing a "provisional" patent application it's then a year to file a "non-provisional" before the application expires. I deal with enough of these that I need to scan a good number of "provisional" filing dates to see if there are any close to expiration and notify the legal department.
So my problem was to set an alert to a relative future date. But I also wanted that alert to go away when action was taken.
Using three columns: a "date" column [Filed Provisional], a symbol "Flag" column, and another "date" column (Filed Non-Provisional).
In the symbol "Flag" column I entered the following formula.
=IF(AND(TODAY() >= [Filed Provisional]1 + 351, ISBLANK([Filed Non-Provisional]1)), 1, 0)
The action is - if today is two weeks short of a year since the initial Provisional filing AND a Non-Provisional filing hasn't yet occurred (ISBLANK) then set the symbols flag (1 = Red). The follow-up is after the flag change to red, the setup Alert goes out to legal and they then file the Non-Disclosure - that date gets populated in the smartsheet and the red flag goes away ----- and that's a task now I no longer have to worry about.
