Status Rollup Automation Becoming Invalid
I wanted to automate status rollups for parent rows. Via the forums I found this writeup (Solution to Automating Status Rollups - Quip) and followed it exactly. It worked great.
Recently for some reason recently every time the "Auto Status Rollup 1" automation kicks in it ends up giving me the "A workflow has become invalid" email saying that its unable to run with a "This workflow triggered itself directly or through another workflow. Please modify the workflow so that it doesn't cause an infinite loop".
After testing I have determined that it does run, then invalidates itself, then it will not run again because it deactivates after becoming invalid.
I know that the trigger is very generic, but the conditions are quite specific. I cannot figure out why the automation ends up in this state. Any thoughts?
Answers
-
You do not need any automation to make that happen. In fact, automation is confounding the solution, because Smartsheet can do this with formulas.
Post some of your sample data with indications of how you want the rollup to behave. Feel free to tag me so I will hopefully see it. What that solution is missing is the concept of levels. We can determine where in a hierarchy each family member is, and based on that level, we can control how the roll-up is calculated.
-
Sample data picture posted below. Status values are Not Started, In Progress, and Complete. The way I want it to behave is that the child object status will determine the parent object status.
Project will show in progress if any phases and any tasks are in progress, but will show complete when all tasks and all phases are complete.
Phase will show in progress if any tasks are in progress but will show complete when all tasks in that phase are complete.
-
I've taken a look at this and I now that I understand what is going on, I see why they want to use automation. And it's because you want to have one column with all of this information.
I understand the desire to use one column. I came from Excel, where we can have a column with formulas, but can also directly edit cells in that column. It took some getting used to.
To accomplish this in Smartsheet, I would use two columns. One column where we indicate the status of the tasks, and one column that rolls up that status to the Phase and Project levels. It requires a bunch of nested IF formulas, essentially what the example provides in the Rollup field.
I am looking at that automation, and one theory is: The result of Auto Status Rollup 1 is that the Status field changes. When the Status field changes, that triggers Add “Not Started” to all blank statuses to run. If there are any blank statuses, those records will change, and that will trigger Auto Status Rollup 1. And the cycle repeats. You and I know that this third condition will never arise because there are no more blank statuses - they got taken care of the first time Auto Status Rollup 1 ran. But I do not know whether Smartsheet knows that, or if it cares. It might look at the conditions and figure out that this CAN cause an infinite loop, not that an actual loop WAS caused, and based on the possibility, it throws the error.
-
I removed the Not Started Conditions from the Auto Status Rollup 1 automation to test whether or not that is where the repeat is causing the infinite loop. The behavior is still the same. Below is a screenshot of where it gets stuck when it runs. This is the same behavior as before in terms of where this would get stuck. However the helper column does show complete but it gets stuck taking that helper column and updating the project level.
