A long ago I showed how one could simulate a WHILE loop in Coda as an action using two buttons that press each other.
Recently I encountered a challenge to implement a similar iterative calculation using a live column formula. A client wanted to calculate estimated task end date given a start date, a duration, and a complicated set of availability rules (e.g. “only 4 hours on Mondays”, “off on Tuesdays” etc). This wouldn’t be possible without an ever-changing exit condition of an iterator: each time an away day would be encountered, we’d have to test for one more day until we fulfilled all the workdays.
This challenge really got to me, so I poked around a bit and found a trick to make a cell recalculate until a possibly changing condition is met:
The trick is to replace
thisTable.Filter(CurrentValue = thisRow).First().SomeColumn
— unlike direct same-row dereference, which would recalculate just once or twice, the
Filter()-based dereference will trigger circular recalculation until there’s no change anymore.
If you run into an endless loop, reopen your doc with
?nocalc=true. Fix the data or the formula so that there’s no endless loop anymore, then reload the doc without this parameter.
Please subscribe to codatricks.com — I’ll actually publish it soon!