How can I send automated updates to submitters?

Rachel Fogleman

I am setting up an automation for my client and am having trouble sending out automated messages and wondered if someone could help me with a solution.

Here's the structure:

Sheet 1: Reports are submitted monthly to sheet 1. (The submitters are not allowed to see the data in the workspace.) Approvers review the information and click approve. Approved rows move to sheet 2.

Sheet 2: All approved rows live here to develop summary data.

Sheet 3: Summary of all information is housed here to view at a global level and to make reports and dashboards.

The client wants an automation when a submitter's report is approved, Smartsheet automatically sends an update of their balance. I tried making formulas to pull in dates and checkboxes, but the automations aren't recognizing the dates and checkboxes properly (I think because there is a formula in those cells). I'm a little bit stumped - does anyone have ideas on how to achieve this?

David Tutwiler

I think I understand what you're trying to do, but want to confirm.

The client wants an update on some information once it has been approved, correct? If that is the case, then the information would live on Sheet 2, right?

If that's correct, then you should be able to set up an automation "When Rows are Added" to be able to send to the client. If you're worried about their access to that sheet, you could always send the notification as "Message Only" and use the {{ }} to bring in specific fields from the sheet to give the customer the information in the message they need.

Does that work for what you're looking for?

Rachel Fogleman

Almost... the Summary information lives in Sheet 3 and I need to give the cumulative information to the submitter when approved in Sheet 2. Sheet 3 is where the "balances" live which is what needs to be communicated.

David Tutwiler

Will the balances automatically update when information is submitted onto sheet 2, or are there other times those balances might change?

If you trusted that the only time the balance would change is when approved rows hit Sheet 2, then you could set your automation on Sheet 3 to be "When rows are added or changed" and the condition would be when the field containing balances changes to Any Value.

That would trigger an email any time the balance changed.

If there are other things affecting that balance and you only want it to occur when a new row was added to Sheet 2, then there would have to be some handshaking between the sheets to determine if you had updated information or if the customer had been notified yet. The formulas and automations for those checkboxes could get complicated.

Rachel Fogleman

There are several different "buckets" of funds that could (or not) change when a report is approved, depending on what the submitter reports. I tried bringing in the date from Sheet 2 and have the trigger recognize the date in Sheet 3 to send the automation, but the formula is messing it up. (It looks like it doesn't recognize the cell as "blank" since there is a formula there.)

I may have to resort to telling them to send it out to all receipients on X date once a month.

David Tutwiler

That does make sense. Sorry for getting to the final answer, but that is a difficult timing-related notification problem.