Need Trigger to notify / email a person in a specific field

Bob Acerrano

I am trying to have a field where when the status changes to completed that an email is sent to a person in a specific field or fields. So when the status in my line 2 changes to Completed I would like an email to go to the person on line 3 field (Assigned To).

Brent Wilson

@Bob Acerrano Look at the "Alert someone when specified criteria are met" workflow under automation

You would

1. Trigger when the column "Status" changes
2. Filter to it being "Completed"
3. Notify the user
   1. There is the ability to pick a column rather then a single name so the person in the "Assigned To" will get the notification
4. You can customize the text in the email saying stuff like "Subject + {Action} + has been reported Completed" and in the body "Today() + " the status of "+{Action} +" that was assigned to you has been reported completed related to "+{Repair Reason"
5. Sometimes I directly mailed myself as well just until I knew it was working then took myself out later

Bob Acerrano

Thank you for your help. I am not sure I can do what I am trying to do. I don't want to pick an entire column. I am looking to update one specific field and have that trigger an email to a person in one specific field on the next line. Below I want to change the current status on line 2 to completed and send an email to notify one person, the person on the next line. So below, I want to change to completed and then an email to Kevin letting him know that the status on line 2 was changed to completed. I am not sure if smartsheet is capable to be this specific.

Thank you.

Ipshita

@Bob Acerrano In the Smartsheet Automation, you are only picking up a particular field "Completed" from the column "Status" so you are not really picking up the entire column, the column is only your range here and the value for that is your criterion for the trigger.

However, for your specific case, you'll probably need to add another column next to your Current Status, call it - "Completed-Responsible Person" or something and add cell values as "Completed-Kevin" or "Completed-Steve" in that column (this will work if you have a fixed number of resources in the team to be notified). Then, when you update a status for a piece of work as Completed, also update this new column with the name of the person you want to notify, for example, "Completed-Kevin" and now if you go into Smartsheet Automation,. you can use the trigger "When a row is changed", pick up the new column name as your trigger and value "Completed-Kevin", use the "Send to specific people" and pick up "Kevin's email" from the list. This is using the logic that if the status for your column is "Completed-Kevin" then Smartsheet sends an email notification only to Kevin.

You can also lock and hide this column in the sheet so no one would see it except yourself but the automation would work for your purpose. Hope this helps. Let me know if this works.

Cheers!!

Jeff Reisman

@Bob Acerrano Smartsheet automation wasn't designed to do what you want it to do, so you have to build a workaround. There are myriad ways to work around this, here's how I would do it.

Add a numeric only (no leading zeros or letters, start at 1) Auto-Number system column, called RowID. Save/refresh, and the column will populate.

Add an "SendAlertTo" contact list column. In that column, use the following column formula:

=IFERROR(IF(Status@row = "Completed", INDEX([Assigned To]:[AssignedTo], MATCH((RowID@row + 1), RowID:RowID, 0))), "")

In English: If the Status is Completed, set the SendAlertTo column to be the contact in the Assigned To column from the row where the RowID is one greater than this row. If the end result is an error (because of a no match situation, for instance,) leave the SendAlertTo field blank.

Then set your Automation to send the alert email to the contact in the SendAlertTo field when SendAlertTo changes to any value.

Brent Wilson

@Bob Acerrano sorry misunderstood

There are a few workarounds..

The easiest might be to use one @Leibel S described

You can create a column called LINE-ID and make it an AUTONUMBER

Then you can create a helper column and call it something like AssignNext and make it a contact field also

Put this in the Column

=INDEX([LINE-ID]:[Assigned To], MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0) + 1, 7)

This will find the rowID of the line and then look through it again and find the Assigned to from the row below (7th column in the MATCH)

You can hide all the columns after you are complete

Then you can have the workflow trigger and send it to AssignNext Column

Bob Acerrano

Thank you everyone for the suggestions. I will work on them and see if I can get this to work.

Thank you!