How to Automate emails based on dates

hughesjames7

Hey there - Pretty new to smartsheets. Here's my question...

In the above image, the values with tan background and blue font are inputs. The white background and black inputs are driven by formulas.

I want my team to receive emails when the dates in black are reached. How can I have emails triggered when specific cell dates are reached?

I tried building this using automated workflows, but having trouble building an effective smartsheet template for my team.

Any help would be greatly appreciated!

Kelly Moore

Hey @hughesjames7

Others in the community may have other approaches/suggestions/comments.

When I look at the screenshot above I notice that your input date cells and your trigger date cells are within the same column. Unless you have some conditions that can be described in the automation Condition window (dynamically) or you can add 'helper columns' and include a formula that will 'help' filter the trigger, I believe you will have trouble automating any process based on your date column. Again - if your process does have those built in conditions that can be added to the condition block, then you'll be ok. If this isn't the case then you might need two date columns so that the trigger is easily recognized.

https://help.smartsheet.com/articles/2479241-create-a-time-based-automated-workflow

https://help.smartsheet.com/learning-track/best-practice-webinars/intro-automation

https://help.smartsheet.com/topics/automated-workflows

I believe the first document I linked will provide the details you need. If you try the above, and get stuck at a specific step, please ask the community and we came then guide more specifically. We really don't mind lots of questions. 😊

Does the attached answer your question?

Kelly

hughesjames7

Thank you for your detailed answer!

I would like something more dynamic than what is shown in the first link.

"Unless you have some conditions that can be described in the automation Condition window (dynamically) or you can add 'helper columns' and include a formula that will 'help' filter the trigger, I believe you will have trouble automating any process based on your date column."

Are you able to point me in the right direction to build the template more dynamically? Can you clarify what you mean about separating dates into different columns?

Kelly Moore

Hey

As you are building your automation, how will smartsheet know that it isn't supposed to trigger off of the date in row 3 (I was assuming since it was input it was not included in the automation?) but you did want automation to potentially trigger off the dates in rows 5,7,10 and 13? As all of these dates are in the same column, if some are supposed to be included and some are not, that is what I was asking if could you tell smartsheet when to use and when not to. If you cannot, then you may consider separating these dates. I don't know what the dates pertain to in your process to know if there is a difference. Maybe these are all the rows you will ever have in your process - if that is the case it's not a problem as you can build automation around these columns. If the content of the rows will be constantly changing then that is the dynamic content that you would hope you could find a way for smartsheet to automatically recognize and act on.

To help you specifically begin building your automation - using the screenshot above can you describe when you would like an email to be triggered? What row(s)? What date column are you triggering off of? Or, maybe I misunderstood - are you wanting a specific date, like Day1 of month?

hughesjames7

Kelly - thank you SO MUCH for your response.

I'm more used to excel where it recognizes individual cells versus columns and I don't have the experience in Smartsheet to understand/execute on how to delineate them for it to work.

The only "triggers" I want to create are the ones in rows 5,7,10, and 13 (among many others outside of this screenshot). Can smartsheet not trigger based off specific cells (i.e. skipping rows 3 & 4)??

And yes, the dates will likely change (we're a real estate development company and dates are always moving around) so we need for the automation to be able to read the dates dynamically versus setting up an automation for a specific date (not super helpful).

In my screenshot. The two dates in tan are fixed dates in a contract we've signed. Those dates create many actions our company must take to complete a project. The numbers in the first column are # of days +/- the tan dates where we want triggers to occur. For example, the Study Period ends 120 days after PSA execution. I want our whole team to receive some type of notification (email/reminder/or something) to let us know this date is coming up as we have 15 separate projects going on at once.

In Row 13, we are trying to setup reminders to follow up with contractors on outstanding proposals. We want to be able to input a date and # of days and have an automatic reminder/email be sent based on those inputs.

Does that make sense? Sorry if this is unclear!

Kelly Moore

Hey

Yes, smartsheet can trigger off of individual rows. However, smartsheet needs something to tell it to include (or not include) a row. That is why I have asked about the differentiators, preferably dynamic so that, as I call it for my collaborators, the automation truly becomes 'hands free'. The differentiator(s) become part of the Condition block to filter the rows you want selected.

So if there is something that will always be present in another cell on that row to say yes include, or no, don't include, then a single date column is all you need. If that is not the case you can either 1. add another column (we call these helper columns and the column type is dependent on the particular need at that time) to manually indicate this column is included (or not included) or, 2. add a different date column so when a date is in that column (or is null), it becomes the condition that differentiates one row from another. If the manual designation is known when the row is created it is a much safer option than relying on someone to check a box at a later time in the process. Does this make sense? Continue asking questions until the answer is yes

Kelly

hughesjames7

Kelly - would you be open to jumping on a quick call to help me implement some of your suggestions? No pressure at all, but thought it was worth a shot!

Kelly Moore

Sure. You can find me here. https://www.linkedin.com/in/kelly-moore-1659592a/

Jared Wolf

Hi All, was this ever resolved?

@hughesjames7 would you be willing to share your sheet? Much appreciated!