How to Coda Random Results?
RandomItem & RandomInteger in (Run)Action()
It all started for me with a simple question I noticed in the community on a quiet and sunny Sunday afternoon.
Lets say i have a list which is
List("alpha","beta","delta","charlie","echo","foxtrot")now what i want to achieve is a formula that outputs a re-shuffled randomized order of that list.
I assumed it was an easy to solve puzzle and I proposed the below solution using a source and a target table and a button outside these tables. In the source table there was a list of items, in the target table I wanted to have the re-shuffled and randomized variation after pushing the button.
Source.FormulaMap(RunActions(AddRow(Target, Target.Name, RandomInteger(1,source.Count()).Filter(CurrentValue.Contains(Target.Name).Not())
)))The result was not good as you can see. I ended up with many blanks.
My next idea after a good night of sleep was to fill out the blanks with the not yet distributed values. This approach failed as well. I had all the rows filled-out, but sometimes one or more values missed and got replaced by an already used number. Below #3 appears two times.
Recap
Before we dive into possible solutions first a recap of the functions used. To generate a list of random numbers there is RandomInteger(). It is part of the Math family and generates a random number between two values. These two you define as you wish. In the above shown formula I used the count of a table: RandomInteger(1,Source.Count())
The RandomItem reshuffles the values in an already existing list. It is a function that is part of the family Arrays. When I wrote my function to only distribute the not yet distributed values, I used this function.
An attentive reader might have noticed that in the first code snippet I used a filter to check for already distributed values on each row in the list (or as you wish column) Target.Name.
RandomInteger(1,source.Count()).Filter(CurrentValue.Contains(Target.Name).Not())The first row has not yet any value, so all values can be used, the second row can only use the remaining (5 in this example) values, the third only 4 and so on. The filter does the job.
This filter is key in any function that intents to randomize numbers or other values (like names). However there is one other function you need as well.
Why a simple formula failed
I quote Max, because he stated the issue so well:
we expect our code to run SEQUENTIALLY, with each loop in a FormulaMap() waiting for the previous iteration to be finished before the next one begins. and we expect each call to Random() or RandomItem() to happen in-sequence and generate a different result instantly. but it seems that is not what happens.
so we are seeing that sometimes these things are run on the coda servers in parallel and sometimes not returning a new value each time they are called.
The observation made is that via buttons in a table (as proposed by Ryan Martens) it nevertheless works well, but not with a button that lives outside a table and generates new rows.
Why does my simple formula outside the table not work while a comparable formula inside the table gives steady results?
Time matters — RunActions()
It is all about the time required to execute the tasks. We have the task ‘create a new row’ and the task ‘add to each new row a random number’. Coda can faster generate a new row than getting a random value (number or name) and so some rows remain empty. In an existing table the rows are already present.
In the first part of the simple formula I have to use RunActions() to force synchronisation. Without this function, every row would receive the same value.
Source.FormulaMap(RunActions(AddRow(Target, Target.NameHowever RunActions() is not strong enough to enforce a timely delivery of the randomly generated numbers which explains the blanks (empty rows). We need to ‘buy more time’.
How to buy time?
Scott Collier-Weir proposed a solution that makes use of a hidden and not yet official Coda function _Delay() and blends it with AddRow() and ModifyRows().
RunActions(FormulaMap(thisRow.List,AddRow([Shuffle Table],[Shuffle Table].Number,CurrentValue) ),_delay(_noop(),500),[Shuffle Table].Button,_delay(_noop(),500),ModifyRows(thisRow,thisRow.[Shuffled List],[Random Shuffle]))
First of all the buttons are in a table en generate two related lists: one in the table and one in the second table which he elegantly named: Shuffle Table.
In Row 4 we have 1000 numbers (1–1000) I used to test the function. I got them there via a little trick:
- I created an empty table
- I made a button on the canvas with an
AddRow()function based on the formula below that gave the first new row number 1, the 2nd #2, the 3th #3 and so on and so got a table with 1000 rows.
runactions(deleterows([DB Test]),
Sequence(1,1000).FormulaMap(
AddRow([DB Test],[DB Test].testlist,CurrentValue)))- In the first table I added a button to ModifyRows() and here I referenced the [DB Test].testlist.
- I pushed the button in row4 and the cell got filled out with 1000 numbers. Afterwards I deleted the button. I only needed it for this action.
I tested the solution with 1000 numbers and they all got properly shuffled. In an other table I checked if I got all the unique values and no doubles and the formula passed the test. So we are good to go!
What is wonderful about the community is that it accumulates knowledge based on the interaction between its members. The proposed solution is a hybrid of what already was testend with a new element, the delay function.
A more elegant approach
After this puzzle was solved by Scott via helper tables I came across an alternative approach that does not rely on additional tables. It is based on an insight shared by Paul on how to keep a calculation going until a certain threshold is reached. It was Joostmineur who showed me an implementation related to random. You define the min & max and the amount of random numbers you want to have. You press the button et voila!
It is easy to have one item. The challenge is to have more than one, like for example 10 numbers? Well then you have to push a button 10 times and each time you push that button, a random number in the defined range is added to the list. By using the described filter, you keep the numbers unique. But how does the button know that it only should hit the other button 10 times and thus stop the moment we have the results? Obviously you need to keep track of the output and once the threshold is reached, the button stops adding new random numbers. In short you need two buttons that talk with each other and only operate under a certain condition (did not yet reach x random numbers).
Do we need a _Delay() here? Yes we do. Without this unsupported function the formula won’t hold. Calculating random numbers is a heavy job. In this contribution of Paul you can read how Random operates.
Just before I wanted to publish this blog, I noticed this contribution on this subject. I am curious how it plays out.
Conclusion
It was not an easy task to generate a randomized list nor to generate a list with random numbers. Both are possible and that is the good news. To make it work you need to implement a _Delay() and you have to break up the process into pieces while applying Filters, FormulaMap() and runActions(). However, even the last solution, though elegant remains a work around.
Issues with Random are not typical for Coda, almost any other web based software deals with it one way or the other. The main thing is: we solved it!
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan Huizer and I am the owner of Huizer Automation. A company specialized in serving SME in harvesting data and keeping it aligned. I am a Coda Expert and rely mainly on Coda, Mailjet, Zapier & Paperform to get the job done.
