WHILE loop with a live formula

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

thisRow.SomeColumn

with

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!

4 Likes

Thank you so very much for sharing :grin: !!!

I’ve bookmarked it for my maybe-one-day-advance knowledge :yum: !
(If one day, I finally get past some puzzling formulas :smirk: )

Why are you adding the list.count()?

Just to demonstrate that the end condition can be dynamic (i.e. the loop won’t just calculate till the initial number, but can be affected by calculation result as it happens).

See how in the gif the last number is not 1024 (because the previous one, 512, is the last one < 1014) but 2048.

P.S. The trick looks unreliable. E.g. trying to decrease the input worked yesterday but doesn’t work anymore now. I also tried incorporating intermediate column to do calculations there, but they recalculated in non-deterministic order.

I need to examine this behavior more before anyone can rely on it.