Can I use a sheet to define notification contacts in another sheet?

KatHenley

I have multiple people wanting to be notified when certain criteria occur. I have that information in another sheet. For example Mary wants to be notified if A, D and Z occur and Steve when B, C and D occur. Mary is a row in the sheet with checkboxes for the criteria she wants to see, Steve the same.

Any thoughts?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317582#Comment_317582

I see what you're saying. To do this, you would need to be updating a Contact column on every row in every sheet that needs these notifications. There is a hard limit of 20 Contacts in a single contact cell, so plan accordingly. This can get pretty complicated, but in a nutshell:

On your Notification List sheet, you'd need a matrix to define NotificationGroup with unique NotificationID, and Description of the criteria. NotificationGroup would be a multi-select Contact column. This is the column that people would add/remove themselves from for various scenarios as defined in the Description field.

In your data sheet, add NotificationID and NotificationGroup columns (NotificationGroup being a multi-select Contact column.) Add automation rules to change cell value, so that when certain fields change to certain values, the corresponding NotificationID is set (which matches the defined criteria on your Notification List sheet.)

In the data sheet's NotificationGroup field, use an index/match to populate the contacts:

=INDEX({Notification List NotificationGroup}, MATCH(NotificationID@row, {Notification List NotificationID}, 0))

(The values in { brackets are references to the column in the Notification List sheet; follow Smartsheet's prompts to "Reference Another Sheet" when building you INDEX/MATCH formula.)

Lastly, create your Alert automation rules, which at that point should be easy, because the trigger can be when the NotificationID field changes (meaning the criteria fields controlling that changed,) and the Action is simply Alert the Contacts in the NotificationGroup column.

Easy as pie, right? 😀

(If you need more than 20 contacts in a cell, you may have to use multiple NotificationGroup columns and NotificationID columns in both sheets to accommodate.)

Jeff Reisman

Smartsheet has Automation Workflows for exactly this purpose, you don't need an extra sheet to define notification rules, unless you just want to keep track of them (which isn't a bad idea, I would love a reporting feature that could output this!)

If you map out your users who should be alerted with the same combinations of conditions, you can create single automation rules with different condition paths resulting in different actions.

KatHenley

I am able to use Workflows for this, but I have 300 people changing their notifications constantly, so I do not want to have to change the workflows every time. I am trying to get to where the separate sheet can be updated (by people without licenses) and then pull the current group that needs notification. Does that make sense?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317582#Comment_317582

I see what you're saying. To do this, you would need to be updating a Contact column on every row in every sheet that needs these notifications. There is a hard limit of 20 Contacts in a single contact cell, so plan accordingly. This can get pretty complicated, but in a nutshell:

On your Notification List sheet, you'd need a matrix to define NotificationGroup with unique NotificationID, and Description of the criteria. NotificationGroup would be a multi-select Contact column. This is the column that people would add/remove themselves from for various scenarios as defined in the Description field.

In your data sheet, add NotificationID and NotificationGroup columns (NotificationGroup being a multi-select Contact column.) Add automation rules to change cell value, so that when certain fields change to certain values, the corresponding NotificationID is set (which matches the defined criteria on your Notification List sheet.)

In the data sheet's NotificationGroup field, use an index/match to populate the contacts:

=INDEX({Notification List NotificationGroup}, MATCH(NotificationID@row, {Notification List NotificationID}, 0))

(The values in { brackets are references to the column in the Notification List sheet; follow Smartsheet's prompts to "Reference Another Sheet" when building you INDEX/MATCH formula.)

Lastly, create your Alert automation rules, which at that point should be easy, because the trigger can be when the NotificationID field changes (meaning the criteria fields controlling that changed,) and the Action is simply Alert the Contacts in the NotificationGroup column.

Easy as pie, right? 😀

(If you need more than 20 contacts in a cell, you may have to use multiple NotificationGroup columns and NotificationID columns in both sheets to accommodate.)

KatHenley

This is awesome. Thank you!!!!! A little extra work upfront, but, a lot less during the process.