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!

8 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.

My clients use WHILE loops in their Visual Basic and Python logic and its not easy to migrate this into purely FormulaMap() loops, especially when the WHILE CONDITION is complex.

So I had to find a way. And I recalled an ancient SPIN-LOCK technique we used in UNIX back in the 80s

1 Like

Perhaps you meant to reply to this thread?

Because both that thread and your solution require a button click. The one in this thread was an attempt to find a way to while-loop without a button but with a column formula.

Nice effort btw. Your approach avoids doc recalculations. But if one has to use RunActions()within a loop, then recalculations (and thus delays between iterations) are unavoidable and your method isn’t any faster than mine.

oops! silly me, i replied to the wrong thread

what i am looking to achieve is a formulation that can be used for nested while loops inside a single action - without the need to rely on multiple actions to tik-tok each other along. if i had 3 nested while loops, i dont want to have to create 6 separate actions.

i suspect my approach is actually slower than the one you built, but it allows me to nest while loops, for loops and until loops to an arbitrary depth in a single action.

remember, my goal is to translate legacy routines from VBA or JS into CFL in an automated way.

so far i have not come across a need for a while loop inside a canvas or column formula.
but it would be great if you were able to overcome the problem you encountered with your while loop in a live formula - but i suspect the undefined sequence in which evaluations occur in CFL may make that impossible. the reason the RunActions() is needed in my while loops is to overcome that; without it coda tries to run everything in parallel so the checking of the while condition is indeterminate.

max