Is it possible to report daily % complete progress for a given task?

cvazque6

I'm newer to smartsheet and have googled this a bunch and I'm lost. I have a task called "Batch 1 Models" and below it 10 sub-tasks. I'm looking to report to my management that Batch 1 is 50% complete as of 8/21. If it's 60% complete on 8/22, I want to capture that as well to be able to say that we've increased 10% in 1 business day.

Is there any any to accomplish this or take a snap shot of % compelte on a daily basis?

jmyzk_cloudsmart_jp

Hi @cvazque6

You can use workflow automation triggered by a regular date time to report daily.

You can simply send the top row daily.

The template.

The workflow example.

You can test the workflow with the "Run/Test now" menu.

Below is the message that I got.

To keep a record of past % complete, we can copy the top row to another sheet daily.

Another sheet example

To determine which row is the latest, I added the "Latest Record" checkbox column with the following formula;

=IF(MAX(Created:Created) = Created@row, 1)

Note that I added the "Created" system column to the sheet.

When you copy a row with children, Smartsheet automatically copies children's rows. My solution copies the project's top row to the sheet's top row with a simple formula like this;

=[Task Name]2, =[% Complete]2

You can use the INDEX(COLLECT()) function to get the "Last % Complete" value from another sheet with the following formula;

=IF([Row ID]@row = 1, INDEX(COLLECT({% complete change record Range : % Complete}, {% complete change record Range : Last Record}, 1), 1))

To summarize, at 11:00 AM (or whatever time you chose), the workflow copies a row to the "% complete change record" sheet. Then, the above formulas update the "Last % Complete" and "Change Since Last" column values.

Then, at 12:00 AM, the automation sends the top row to the designated receiver like this.

The modified message customization.

jmyzk_cloudsmart_jp

Hi @cvazque6

You can use workflow automation triggered by a regular date time to report daily.

You can simply send the top row daily.

The template.

The workflow example.

You can test the workflow with the "Run/Test now" menu.

Below is the message that I got.

To keep a record of past % complete, we can copy the top row to another sheet daily.

Another sheet example

To determine which row is the latest, I added the "Latest Record" checkbox column with the following formula;

=IF(MAX(Created:Created) = Created@row, 1)

Note that I added the "Created" system column to the sheet.

When you copy a row with children, Smartsheet automatically copies children's rows. My solution copies the project's top row to the sheet's top row with a simple formula like this;

=[Task Name]2, =[% Complete]2

You can use the INDEX(COLLECT()) function to get the "Last % Complete" value from another sheet with the following formula;

=IF([Row ID]@row = 1, INDEX(COLLECT({% complete change record Range : % Complete}, {% complete change record Range : Last Record}, 1), 1))

To summarize, at 11:00 AM (or whatever time you chose), the workflow copies a row to the "% complete change record" sheet. Then, the above formulas update the "Last % Complete" and "Change Since Last" column values.

Then, at 12:00 AM, the automation sends the top row to the designated receiver like this.

The modified message customization.

cvazque6

This is an amazing write up. Thank you so much for taking the time to do this! I've got myself most of the way there following your instructions but I'm a bit lost at certain points.

1. I successfully created the automated workflow to send a daily email with % complete progress. Thank you!

2. I successfully created the automated workflow to copy the rows over to a separate sheet. I see what you mean about it bringing the top parent row and all the children. This is where I'm a bit confused on how to properly enter the formula you suggested =[Task Name]2, =[% Complete]2. Would I do that formula in this sheet or a separate one?

3. I'm also a bit confused on how you're using the "Record" column and check boxes and also the "Row ID" and how they play into the overall formulas.

Thank you again, truly appreciateyour help here and feel like I'm getting close. Your suggestions here are exactly what I'm trying to accomplish!

jmyzk_cloudsmart_jp

Hi @cvazque6

As shown in the image below, the [Task Name] column of the first row has the "=[Task Name]2" formula.

The same for the [% Complete] column, "=[% Complete]2".

https://app.smartsheet.com/b/publish?EQBCT=3fe4d61324fa4a4eb320a60c21f30ffb

[Row ID]

Row ID is used in both workflows to determine which row to copy or send. Alternatively, you can add a checkbox column [Top Row], for example, and check the top row to use as the condition.

[Record]

I am sorry about the [Record] checkbox. I used to test the workflow with the "When rows changed" trigger, which triggered the automation when the [Record] was checked. However, I switched the explanation using the "Run/Test now" menu. So, the [Record] column is not necessary. If you want to manually record the [% Complete] change by checking the column, keep the column.

Your use case is realistic and practical, addressing a common need for daily progress tracking in Smartsheet.
I'd appreciate your 'Awesome' click to help others discover and benefit from my solution!😁

cvazque6

I got this up and running now. Thanks again for all your help, I've definitely marked your responses as awesome to help anyone else. One last question, what formula are you using to capture the "change since last"? I'm having a hard time with that.

jmyzk_cloudsmart_jp

Hi @cvazque6

I used this formula. The IF statement prevents other rows from having the values. It doesn't have to be a column formula, so you can remove the IF statement and lock the first row.😂

=IF([Row ID]@row = 1, [% Complete]@row - [Last % Complete]@row)