I run regularly into situations where I generate list and where the rows have to be unique.
When using a sequence().formulamap() combination I run short of the number of unique rows I need, because the iterations stop at the predefined point as set in sequence(), which is often to early if I am filtering for unique rows.
Rather than using sequence() it would be nice if we could get a While() function, preferably with an EndWhile() option to escape from endless loops.
If anyone knows a workaround I would love to find out about that.
The best approach to this would be to implement an iterator table (see Demo 2):
By making a pair of buttons that click each other you can implement a while loop. I discovered that trick first here. It then became the foundation of many further tricks.
A while loop implementation with a column formula (not an action) is also possible, although the implementation relies on forcing a recalc on a cell that might break whenever Coda changes their under-the-hood algorithms for dependency graph resolution and circular calculation prevention.
Thank you for your reply and sharing. I guess we would use WithName() for some of this now.
I have to build something with a helper table to see if I can get it to work.
The thing is, I have some formulas that need to keep on running until a certain condition is met, and I donāt know up front how many repeats it is going to take to meet the condition.
For example, I have a list of people donating, each of them funding a project with different amounts of money.
I want to process this list and total the money they want to donate, but as soon as the target is met I want to break out of the iteration (the real life case is much more complicated, but I do want to break out of the iteration) and continue with other actions.
I canāt seem to find a way to get out of a formulamap() until the entire list is processed. If I have 10K respondents and I hit my target after processing the first 20, that is going to make a big difference in processing time.
Thatās why I would like to use a while() instead of (or in combination with) formulamap().
If I succeed I will post here, if I find it in your video I will also report back - I looked at the first part but got interrupted (the story of my life ).
@joost_mineur ā please donāt mind the āCurrentValue hackā sentiment about that trick. Itās indeed not so relevant for that purpose anymore. Please watch the second demo, it showcases exactly that āwhileā logic. As long as there are hours to allocate to contracts, keep finding the next contract for the person in question.
Not sure if this provides a solution or not. But my clients use WHILE loops all over the place in their spreadsheet workflows and the WHILE CONDITIONS can be quite complex. Not at all easy to map that into FormulaMap() functions and Iteration Tables (pun intended). So I have revived an ancient SPIN-LOCK technique from the early days of multi-CPU UNIX systems. The trick is to minimise the length of the āspin loopā (the iterations that dont do anything).
).