Hi, I’m looking to set up email alerts for two stages of a document review. I’ve currently configured IF functions so that, once the ‘Document Received’ checkbox is selected, the status in the ‘Primary Review’ column automatically changes from ‘Not Started’ to ‘In Progress’. Stakeholders then tick off the relevant boxes in the ‘Primary Review’ column as they complete their review. Once all items in the primary review are complete, the status automatically updates to ‘Complete’, and the ‘Secondary Review’ status changes from ‘Not Started’ to ‘In Progress’. A separate group of stakeholders then completes the secondary review in the same way. When all items in the secondary review are complete, the ‘Review Complete’ checkbox for that section (with sections defined by the yellow heading rows) is automatically selected. Once both yellow sections have been automatically ticked, the final checkbox for that document is selected on the light blue row. I’d now like to automate three notifications: * When ‘Document Received’ is ticked and ‘Primary Review’ changes to ‘In Progress’, an email is sent to the contact listed under ‘Primary Reviewer’ (to alert them to start their review) * When the primary review is complete and the status automatically changes to ‘Complete’, an email is sent to the contact listed under ‘Secondary Reviewer’ (to alert them that the first review is done, they need to start their review now) * When the secondary review is complete and the second row under ‘Review Complete’ is automatically selected, an email is sent to a pre-agreed list of contacts (to alert them that both stages of review are done) There are two caveats to consider. First, the contacts listed in the review columns (particularly for the secondary review) may change as the project progresses, so the automation needs to account for this. Second, the contacts vary by row, meaning there isn’t a single fixed contact (although it will only be between 3) that can be specified for each review tick box.

Automating Email Alerts for Multi-Stage Document Review

Hi,

I’m looking to set up email alerts for two stages of a document review. I’ve currently configured IF functions so that, once the ‘Document Received’ checkbox is selected, the status in the ‘Primary Review’ column automatically changes from ‘Not Started’ to ‘In Progress’. Stakeholders then tick off the relevant boxes in the ‘Primary Review’ column as they complete their review.

Once all items in the primary review are complete, the status automatically updates to ‘Complete’, and the ‘Secondary Review’ status changes from ‘Not Started’ to ‘In Progress’. A separate group of stakeholders then completes the secondary review in the same way.

When all items in the secondary review are complete, the ‘Review Complete’ checkbox for that section (with sections defined by the yellow heading rows) is automatically selected. Once both yellow sections have been automatically ticked, the final checkbox for that document is selected on the light blue row.

I’d now like to automate three notifications:

When ‘Document Received’ is ticked and ‘Primary Review’ changes to ‘In Progress’, an email is sent to the contact listed under ‘Primary Reviewer’ (to alert them to start their review)
When the primary review is complete and the status automatically changes to ‘Complete’, an email is sent to the contact listed under ‘Secondary Reviewer’ (to alert them that the first review is done, they need to start their review now)
When the secondary review is complete and the second row under ‘Review Complete’ is automatically selected, an email is sent to a pre-agreed list of contacts (to alert them that both stages of review are done)

There are two caveats to consider. First, the contacts listed in the review columns (particularly for the secondary review) may change as the project progresses, so the automation needs to account for this. Second, the contacts vary by row, meaning there isn’t a single fixed contact (although it will only be between 3) that can be specified for each review tick box.

Find more posts tagged with

Sheets

Formulas

Automations

Project Management

Cell linking

Accepted answers

All comments

Paul Newcome

You can set alert automations to send to contacts in a specific cell (on the triggering row), so if you set that and then select the contact column that has the person you want notified, it will be dynamic. You won't need to set up separate automations for each recipient.

The third automation you can "hard code" recipients by selecting the option to send to specific recipients and then entering their email addresses within the automation.

https://help.smartsheet.com/learning-track/level-1-foundations/alerts-reminders

rorylkeenan

Hi Paul,

Thank you for getting back to me.

The issue I’m facing is that the trigger point sits on the status row/cell — i.e. when the status changes from ‘Not Started’ to ‘In Progress’, and then from ‘In Progress’ to ‘Complete’. However, the relevant contacts aren’t listed on that status row; they’re listed on each of the individual rows that contain the tick boxes.

What I need is for an email to be sent to each of those contacts only when the status changes (which happens only once all tick boxes in that section have been selected), rather than an email being triggered each time a tick box is selected on an individual row.

Would this be possible? Open to both automation and hard coding - I'll have to do this across the sheet, so ideally what's easiest

Paul Newcome

You may be able to use a formula. Do you have a screenshot you can share with some sample data including assigned to's filled in (mock data is fine - just want to be able to visualize)?

rorylkeenan

Hey Paul,

Thank you very much for your help on this!

Please find the dummy data below - as mentioned some of the reviewers may change, so the automation needs to take that into account:

Paul Newcome

To make sure I understand…

When the "Formatting" status in [1. Primary Review] changes from "Not Started" to "In Progress" and from "In Progress" to "Complete", you want all of the contacts in the child "Task" rows to be notified?

Same for [1. Secondary Review Test]?

And the status only changes when ALL child boxes are checked? Or does it change to "In Progress" when at least one box is checked?

rorylkeenan

So in essence correct, but to be really clear - the 3 automations we are looking for (while taking you through how the chain of events works):

Automation 1 – Primary Review notification

When the “Formatting” and “Legal Review” statuses in [1. Primary Review] change from “Not Started” to “In Progress” (this change is triggered when the [1. Document Received] checkbox is selected), ALL contacts listed in the [1. Primary Reviewer] column should receive an email notification.

Automation 2 – Secondary Review notifications (section-specific)

When the “Formatting” status in [1. Primary Review] changes from “In Progress” to “Complete” (this occurs only once ALL tick boxes under that status have been selected), ALL contacts listed in the [1. Secondary Reviewer] column for the Formatting section ONLY should be notified.
A separate automation is required for the “Legal Review” section. Contacts listed in the [1. Secondary Reviewer] column should be notified ONLY when their corresponding section is completed. Legal Review contacts should NOT be notified when the Formatting section is completed, and vice versa.

Automation 3 – Final completion notification

Once a status changes to “Complete” in [1. Second Review Test] (this is triggered only when ALL tick boxes in that column are selected), this automatically checks the corresponding row’s checkbox in the [Review Complete] column. When BOTH of these checkboxes are checked, the checkbox on “Section A” is automatically selected. At that point, an email notification should be sent to a pre-defined list of contacts.

And then as mentioned before, the caveat being that some of the contacts in automation 1 and automation 2 may change - so we'd need this to be factored in. If this is not possible, let me know and we can work around this as the important thing is that the automations works as per the above.

I hope that is helpful, however, let me know if you have any more questions! Thanks again very much for your help on this!

Paul Newcome

Ok. This may be easiest to manage with some helper columns.

Automation 1:
Insert a checkbox column (called "Automation 1" in this example) and apply this column formula:

=IF(AND(COUNT(CHILDREN(Stages@row)) = 0, [1. Primary Reviewer]@row <> "", PARENT([1. Primary Review]@row) = "In Progress"), 1)

Then you would set up your automation to send when this box changes to checked and the action would be set to send to contacts in a specific cell (this is where you select the [1. Plan Reviewer] column).

Automation 2:

This would be set up the same way as Automation 1, except you would change "In Progress" to "Complete".

Automation 3:

This would be a straightforward setup of the trigger being when the box is checked and the action having the contacts listed. You can list these in the automation directly, or if this needs to be variable from one sheet to the next (assuming this is meant to be a repeatable process), you can insert a contact type column, allow multiple contacts, and enter them in the cell on the same row as the checkbox. At that point you would adjust the recipients in the action of the automation to be from a specific cell and then select this new contact column.

rorylkeenan

Hi Paul,

This looks to have worked in identifying when the triggers I mentioned are selected — thank you very much!

When setting up automations 1 and 2, if I select the [1. Primary Reviewer] column as the recipient field, will the automation notify only the contacts on the specific rows that have triggered the workflow, or will it pull in all contacts listed in that column across the sheet?

I ask because the screenshot I shared previously only shows Section A. In the full tracker there will be around 10 additional sections (e.g. Section B and onward), and the reviewer contacts may differ by section. I therefore want to ensure that only the contacts within the relevant subsection (e.g. the Formatting rows under Section A, if that is the part that has progressed) are notified — rather than triggering notifications to reviewers in other sections.

Apologies if this is a basic question — I’m still getting to grips with Smartsheet automation!

Separately, would you be able to break down the formula you provided and explain how it works? I almost follow it, and understanding the logic would be really helpful going forward.

Paul Newcome

Automations that are set up to send to contacts in a specific cell will only send to the contact(s) listed in the cell on the triggering row at the time of the trigger. In other words… One row can't send to contacts listed in another row.

Formula Breakdown:

=IF(AND(COUNT(CHILDREN(Stages@row)) = 0, [1. Primary Reviewer]@row <> "", PARENT([1. Primary Review]@row) = "In Progress"), 1)

We have three requirements that need to be met to check the box for the automation.

First, we only want the lowest level child / we want to exclude parent rows. This is where we user the COUNT/CHILDREN combo. If that count is zero, it is not a parent row.

Second, we want to only trigger on rows where there is a [1. Primary Reviewer] assigned. This is not absolutely critical because an automation that gets triggered but does not have a recipient won't break or send to any defaults. It is just for some efficiency to prevent the automation from triggering on that row and wasting back-end resources.

Third, we only want it to trigger when status of the parent row in the [1. Primary Review] column is equal to to "In Progress". PARENT([Column Name]@row) monitors the parent row in the specified column, and we use the argument of being equal to "In Progress".

If all three of those things are true, the AND outputs a "true" value which in turn triggers the "value if true" portion of the IF statement which outputs a 1. Checkboxes are boolean, so you can use true/false or 1/0. I tend to prefer 1/0 because true/false comes with it's own catch.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.