Automation to change cell value not working
I have SCC setup to provision projects.
Inside the project plan there is a variety of data coming from the intake sheet that will be used to determine what duration to pull from a separate master lead time sheet via INDEX/MATCH. The final lead times populate the Duration Planner column for every task.
In addition, there is a MigrationData column that checks if any additional parameters coming from the metadata sheet should be adjusted to completed or not by adding a C to rows in this column that should be changed to "completed".
I then want (ideally only at project provisioning) to use the Change Cell Value automation to update the task status field (single select drop down) based on any fields that have 0 in the Duration Planner or C in the MigrationData column.
No matter what i try i can not get it working. What am i missing and how can i make this work?
Things that i have tried:
- Triggered by ANY changes to DurationPlanner or MigrationData columns with the condition that it is a 0 or a C. - DOES NOT WORK
- Triggered by ANY changed to a "Trigger" column that is an inbound link to intake sheet. Requires additional step of changing the text in this intake sheet after a project is provisioned. - DOES NOT WORK
- Record A Date automation to add todays date to a "datetrigger" column every day at 8am (this part works), combined with a separate change cell value automation set to trigger by ANY changes to the datetrigger column. - DOES NOT WORK
- Used a "Final Duration" formula column to simply pull the values from Duration planner, and trigger from ANY changes to the Final Duration column. - DOES NOT WORK
Best Answers
-
Since your action is a Change Cell action which modifies the sheet, it sounds like you're running into an issue because with this type of action the workflow cannot be triggered by a cell-link or cross-sheet formula (or a formula referencing a cell link or cross-sheet formula). See the note at the bottom of this article: Trigger Blocks: Define When Your Workflow is Executed
Instead of having your trigger based off of if a row is added or changed, try using the Date trigger instead (ex. every day at 8am, like your Record a Date workflow) with a Condition Block to check for the 0 or C.
Cheers,
Genevieve
-
For problem 1, yes, there is potential that it could take time until the project is then set up how you would want it.
For problem 2, you can set an additional Condition in the workflow that says that the Created Date would be in the last (days). This means it will stop any further update to your project if the Created Date of the row is in the past two days (for example). However, if anyone creates new rows that meet the criteria, then the workflow would take these into account as they are created. Would this help?
Cheers!
Genevieve
Answers
-
Since your action is a Change Cell action which modifies the sheet, it sounds like you're running into an issue because with this type of action the workflow cannot be triggered by a cell-link or cross-sheet formula (or a formula referencing a cell link or cross-sheet formula). See the note at the bottom of this article: Trigger Blocks: Define When Your Workflow is Executed
Instead of having your trigger based off of if a row is added or changed, try using the Date trigger instead (ex. every day at 8am, like your Record a Date workflow) with a Condition Block to check for the 0 or C.
Cheers,
Genevieve
-
Yes I tried that and it wasn’t working, but eventually it did start working. Problem with this solution is:
problem 1: it has to wait to update until a specific time each day. So if I provision a project a min after the automation time setup, I have to wait 24 hours until project is fully setup correctly
problem 2: it has to run every day for life of project. I just want it to run once.
-
For problem 1, yes, there is potential that it could take time until the project is then set up how you would want it.
For problem 2, you can set an additional Condition in the workflow that says that the Created Date would be in the last (days). This means it will stop any further update to your project if the Created Date of the row is in the past two days (for example). However, if anyone creates new rows that meet the criteria, then the workflow would take these into account as they are created. Would this help?
Cheers!
Genevieve
