Iterative Functionality (circular reference) in smartsheet.

Chris Harrigan

I'm currently trying to find a way to do a simple iterative count using the automation tools in smartsheet. Simple iteration is one of the most basic tools in computer based problem solving, so it seems like a good feature to have built into automation tools.

The specific situation is that I want to track how long a card has been in a column in smartsheet. The card can move backwards and forwards multipe steps and any number of times, but the general flow should be from left to right and the final column is complete/done.

I am fine with the innacuracy of simply using the 11pm central time test (built in automation function) to see what column a given card is in, and I would like to add 1 to that columns day total within that card. There seems to be no way to do this, or do any sort of iteration, even by 1's in Smartsheet and the automation tools.

Does anybody know of a solution?

Chris Harrigan

In general though, a built in time in column functionality for Smartsheet Cards seems like it could be useful to alot of people.

Genevieve P.

Hi @Chris Harrigan

You could potentially add a number of helper columns in your sheet to create this type of calculation. The way I would do it is to have 2 columns per status / card lane (a Start and an End) and a Days column for that status, too.

I would then use a Record a Date workflow to record the date that the Card entered that lane/status. Then you can use a formula to check between that Date and Today's Date to return the number of days it has been in that lane.

=(TODAY() - [Start Date]@row) + 1

The tricky bit is identifying when the card moves out of that lane, and having that be a set date. If the cards always moved in one direction, you could use entering a specific lane as the trigger for a new Start date for that lane, and then have that be the "end" date for this formula:

=IF([Start Date Status 2]@row = "", ((TODAY() - [Start Date Status 1]@row) + 1), (([Start Date Status 2]@row - [Start Date Status 1]@row) + 1)

If you're on an Enterprise plan, an alternative would be to use the Work Insights panel in the sheet. You could use a Time Series snapshot to see how many of your cards were associated with a specific value at a point in time.

Please also let the Product team know of your feature request for Card View by filling in this form, here!

Thanks,

Genevieve