Email Alert Workflow

SML

I have automated email alerts set up when final dates on my plan changes. How do I highlight the cells that changed in the automated email alert so that they can quickly see what changed

jmyzk_cloudsmart_jp

@SML

[Project Namet]# references the value of a Sheet Summary field, "Project Name."

Have you set the field value?

If you do not use a Sheet Summary filed, you could use formula like;

="Your Project Namet" + ">" + JOIN(ANCESTORS([Task Name]@row), ">") + ">" + [Task Name]@row

😁

jmyzk_cloudsmart_jp

@SML

Highlight changes in an alert is a built-in function.😅

Please set the "Alert Someone" action in the workflow to include the fields. (all or specific).

https://help.smartsheet.com/articles/2479256-customize-the-content-of-your-alerts-an

d-requests

If you want more elaborate highlighting, you could create a workflow to change the format in Sheet by changing the value of a "Conditional Formatting" condition field. In this case, changes are highlighted in the Sheet, as well.

For example, you could set a "Change Cell Values" workflow to change the value of a field ("Changed" Check Box, for example) when there are changes in specific columns (Final Date, for example) so that the values change triggers changes in target columns in "Conditional Formatting."

To show the elaborate highlighting in the alert, you need to trigger the "Alart Someone" workflow after you change the field with the above workflow ("Changed" Check Box, for example) to reflect the change of format in the Sheet.

SML

Thank you.

I have another question. I am trying to create a summary report to be sent to stakeholders on a weekly basis. The report would show all the updates that have been made to multiple project timelines along with text in from a notes column on the project plans.

What would be the best way to do this and make it clear to the audience what has changed and the reasons why. I'd also like to see if I can include in these weekly notifications, a column that they can mark as approve for these updates.

Thank you

jmyzk_cloudsmart_jp

@SML

Let's consider a case where you need to report the changes in the date of individual tasks.

First, use workflow automation triggered by a date change to change the value of the "change" column to "checked" when the task date is changed. (Note to identify changes affecting the entire project, we need to use the "ISCRITICAL" function to determine if the data change is such a change.)

For clarity, use conditional formatting to change the format of rows and bars whose dates changed. (It is a good idea to make it easy to tell at a glance whether a change is a date change with no other impact or a critical change affecting a subsequent task.)

With the change check, filter reports that summarize multiple projects. (Note that only some of the tasks in a project will be filtered, making it difficult to understand their position in the project as a whole, so create with a formula a breadcrumb like "Project Name > Parent Row Name > Task Name."

Add a "Reason for Change" column in the report as well.

The Approve Update column should be a drop-down or checkbox. If you use a drop-down, it is a good idea to change the column to "Approve?" with automation. 

For details, please look at the demo dashboard at the following link.

https://app.smartsheet.com/b/publish?EQBCT=7a6d2d8a42024f0ea3fea567ef2df5ce

The above is an overview, but since this is an interesting case, we plan to write an article explaining it to Japanese readers. When it is ready, I will send you the URL so you can read it with a translation application if you are interested.🙂

SML

Hello @jmiyazaki@cloudsmart.jp

Could you share the file below?

Thank you!

jmyzk_cloudsmart_jp

@SML

I have fixed the sheet's access control setting.😅

SML

Thank you @jmyzk_cloudsmart_jp

The formula for the breadcrumb is showing as unparseable for me. Is there something I should fix in this formula?

=[Project Namet]# + ">" + JOIN(ANCESTORS([Task Name]@row), ">") + ">" + [Task Name]@row

jmyzk_cloudsmart_jp

@SML

[Project Namet]# references the value of a Sheet Summary field, "Project Name."

Have you set the field value?

If you do not use a Sheet Summary filed, you could use formula like;

="Your Project Namet" + ">" + JOIN(ANCESTORS([Task Name]@row), ">") + ">" + [Task Name]@row

😁

SML

Hi @jmyzk_cloudsmart_jp

One last question. Do I set up the smartsheet summary with all the fields that I want to pull into the report? I'm not sure how it would pull in the distinct information.

jmyzk_cloudsmart_jp

Hi @SML

Is this a general question about summary sheets separate from the first question?

If true, you use a formula or cell reference to pull distinct information from the base sheet.

For example, if you want to pull the number of date change information, you can use a formula like this.

[# of date change] field in Sheet Summary

=COUNTIF([Finish Change]:[Finish Change], 1)

😅

https://help.smartsheet.com/learning-track/level-2-intermediate-users/sheet-summary