What constitutes a "change" for triggering a workflow? Can a cross-sheet reference be used?

James Keuning

I am going to explain the problem, but the details are not important as I really just want to know what constitutes a change, and if I can use a change in a cross-sheet field as a workflow trigger.

I am trying to trigger a workflow based on a row change in a sheet where every cell is a lookup. I have workflow that copies a row to a sheet (call this sheet HOLDING), and I need to transform that data before moving it along to another sheet. Row data will get copied to HOLDING one at time. I have another sheet (TRANFORM) that looks at HOLDING and uses MAX to pull the most recent record added to HOLDING. The TRANFORM sheet only has one record on it ever. As soon as a new record is added to HOLDING, a manual refresh of TRANSFORM will change what is shown. But Smartsheet apparently does not consider the refresh of cross-sheet data to be a change.

I have a similar situation with another sheet, but in that case I just need to have a daily refresh, so I just use the Record a Date workflow to update a field every night at 10PM. That is enough to trigger the change. I have through about setting a Record a Date workflow to run every hour because that's as granular as Smartsheet can get, but I need the solution to be able to run more frequently.

I asked this question here, but since that one is not getting any responses, I thought I would break this down to just ask about triggers.

Jeff Reisman

Just a note on the timing:

From the Activity Log for SheetA you can see it took 10 seconds from saving my row to copy it to SheetB:

And then it took 12 seconds to move from SheetB to SheetC:

Jeff Reisman

@James Keuning

From Smartsheet's Help articles:

• To prevent infinite loops, cells containing cross-sheet formulas or cell links will not trigger automation which automatically changes the sheet (Move Row, Copy Row, Lock Row, Unlock Row, Approval Request, Record a Date, Assign People, Change Cell, Clear Cell). To work around this, consider using time-based automation or recurrence workflows. 

But don't despair, there may be a workaround that can help you.

I have a large sheet that needs manual data entry via a short form. Our warehouse admins hit this sheet with several hundred entries, one right after the other, every morning. This resulted in slow downs when submitting / refreshing the form for the next entry, on the order of 5-30 seconds. Unacceptable. Since the problem was trying to make that many updates to a large sheet, I thought about using small sheets on the front end. So I created 2 sheets to "leapfrog" data into the main sheet using Move a Row automation. The User enters their data into the form connected to Entry Sheet 1, which is empty or nearly so, so it submits and refreshes instantly. Move a Row is set to trigger when a row is added and our primary key equals any value. It moves the row into Entry Sheet 2, which has the same Move a row automation set up, to move the row into the main sheet. It doesn't matter how fast data goes into Entry Sheet 1, because it's two sheets removed from the main sheet. If the row takes 10 seconds or 2 minutes to get to the main sheet, not a big deal.

How can this help you?

Something many people don't know is that you can have more columns in your destination sheet than in your source sheet. So theoretically, you can set up Copy a row automation in HOLDING triggered by rows being added, so that new rows COPY to TRANSFORM automatically. Extra Columns in TRANSFORM handle your calculations/transformations based on the data from the HOLDING sheet, while an Automation rule MOVES the row to the final destination, based on the trigger of a row being added. In your final destination sheet, hide the Columns that were brought from HOLDING that you don't need.

Ex.

Here's SheetA:

Here's SheetB. I have more columns, with formulas in them.

Copy row from SheetA to SheetB:

Add Workflows to SheetA and SheetB:

SheetA, Copy Rows:

SheetB, Move Rows:

New row added to Sheet A:

Moments later, it's in SheetC, with transformed values:

And it's not in SheetB, because it just stopped there long enough to transform data and move to SheetC:

Activity Log in SheetB shows it was received and moved out:

Jeff Reisman

Just a note on the timing:

From the Activity Log for SheetA you can see it took 10 seconds from saving my row to copy it to SheetB:

And then it took 12 seconds to move from SheetB to SheetC:

James Keuning

Thanks for taking the time to write all of this out. It's helpful to know that someone else has thought through this and reached the same conclusion as I have. I am running my process the same way as you are, and there is only one thing that I don't like, which is what's motivating me to try to improve:

1. Each of my tasks has a TASK ID in the TASK LIST sheet, this is a system field.
2. Each task has a RECURRENCE INTERVAL field and a DUE DATE field in the TASK LIST sheet.
3. My TRANSFORM sheet does one thing - it adds the RECURRENCE INTERVAL to the TODAY() field to calculate a new DUE DATE. So if I complete a task today, and the interval is 2, a new due date 2 days in the future is created.

The thing I do not like: When my task copies to HOLDING, and then moves to TRANSFORM, the TASK ID travels with it, obviously. And then it writes back to the TASK LIST, the new record has the same TASK ID as the original. I want this to increment to a new TASK ID.

I cannot figure out how to drop the system field TASK ID. But in the end, I decided that this is ok.