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.
(be it triggered by a button or document edit. as long as a formula combination that does it)
any coda expert that has elegant solution for this?
i can’t figure it out.
hi @chrisquim ,
this one works, the button generates a new list every time and via a filter (in the button or in the table, up to you), you geneate the vars as in your list. The trick was to add RunActions(). Without this one, you have one very row the same value.
source.formulamap(runactions(AddRow(target,target.Name,RandomInteger(1,source.Count()))))
enjoy your Sunday.
Cheers, Christiaan
What I suggested is maybe not what you had in mind.
I tried to generate an outcome as below using this formula:
source.formulamap(RunActions(AddRow(target,
target.Name,
RandomInteger(1,source.Count()).Filter(CurrentValue.Contains(target.Name).Not()
))))
It is variation on the previous one, but this time we only take values not yet part of the list we are creating by leaving out the ones we already have.
anyway, it does not work as expected, I get many blank rows like below:
pushing again the button adds new rows with the values not yet distributed. Filtering the table on IsNotBlank() after a few hits results finally in a list you want. This is not how it should be. This might work for 6 rows, but not so well for 600 or 6000
Maybe @Ryan_Martens2 , @joost_mineur @Federico.Stefanato, @Paul_Danyliuk or somebody else knows what goes wrong in this approach that was inspired by the this post
In this approach you select manually the item of choice.
Looking forward to understand how to solve this puzzle.
it has been noted that multiple calls to Random() in rapid succession can be problematic.
see this post for a suggestion using just one call to random to generate a sequence of random digits that can be used in a single formula
tomorrow, when i have time, i will post a shuffle formula based on this approach
respect
max
There is a solution in the first part of the doc embedded in this post which I will re-embed here.
When you press the [New Sequence] button, it clears the existing order of items and then presses the [New Order] button for each row in the table to be shuffled.
Why in the table and not in a canvas formula? Because as Xyzor mentioned,
Random()on the canvas needs a delay to produce multiple random outputs (and is resistant to workarounds)… but this is not so in a table — each row gets it’s own separateRandom()output.
The [New Order] button assigns a new unique ordered number to the row via this formula:
RandomItem(
Sequence(1, [ListtoShuffle].Count()).Filter(
Not([ListtoShuffle].[Randomized Order].Contains(CurrentValue))
)
)
Check out this example doc:
Let me know if further explanation is needed 
It is very very Similar to Christian’s answer. I’m not sure, @Christiaan_Huizer, why yours is not working. Maybe the in-table buttons are helping mine?
thanks @Ryan_Martens2 ,
thanks @Agile_Dynamics ,
When I saw the question, I assumed a rather straightforward solution.
This morning before I read the input of @Ryan_Martens2 and after I noticed the feedback of @Agile_Dynamics I thought why not using ModifyRows() to fill out the blanks. I created the below solution which appears not to be functional either
RunActions(
source.formulamap(RunActions(AddRow(target,
target.Name,
RandomInteger(1,source.Count()).Filter(CurrentValue.In(target.Name).not()
)))
),
source.Filter(index.Contains(target.Name).Not()).index.RandomItem().WithName(ToDistribute,
target.Filter(Name.IsBlank()).FormulaMap(RunActions(CurrentValue.ModifyRows(target.Name,ToDistribute)))))
This surprises me quite a bit
what it does is:
However this low code solution does not work. In the image below you see that the value 1 is missing and instead we have two times 6. However sometimes it works, but more often not.
Maybe @Paul_Wilkins can shine some light on this. The button based logic of Ryan works perfect (thx!) , why? I don’t know.
Cheers,
Hi Christian,
I am also surprised that your solution doesn’t work. I didn’t scrutinize your code, but I’m trusting there are no logic errors in there. I also just tried similar code as a column formula, and it worked — it reshuffles on every doc edit.
WithName(
[thisTable].Filter([Row ID]<thisRow.[Row ID]).Column,
usedNums,
RandomItem(
Sequence(1, [thisTable].Count())
.Filter(
CurrentValue.In(usedNums).Not()
)
)
)
So my theory is that the difference is made by whether the code appears in the table, or out of the table. All of the Random formulas seem to produce unexpected results when run multiple times in one canvas formula or button — even in FormulaMap() — but behave much nicer in rows.
i have seen this kind of issue in other cloud-based programming systems, such as salesforce or shopify.
i think it has to do with the cloud side of coda using lots of multi-threaded code when running things on the server side, so it can scale up and support thousands of users.
we have also seen this issue when using RowId on rows generated rapidly by a formula. the RowID value remains blank for a while, and gets filled in later by the server.
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.
and it looks like the location of the code has an impact. if run from the canvas, it behaves different to running it within a button or within a table column.
perhaps a CODA engineer can comment on this and provide better documentation?
I have found that i need to place RunActions() in the innermost loops of my formulas to force synchronization - but that is only available in BUTTONS, which is why using buttons above seems to work better.
in the salesforce programming language, there are clearly defined rules for how parallelization of execution is done in order for the system to scale. and there are specific tools to control synchronization when needed.
so either we are seeing a BUG in coda that needs fixing OR we are seeing a FEATURE in coda that needs documenting!
The 3 ways i have overcome this Random() number issue in the past is
(1) compute a random string of digits just once using Random() and then use those digits in some clever way to cause pseudo-random behavior inside my FormulaMap() loops
(2) use the last digit of the Created() time-stamp of rows as a kind of random digit. This works when new records are created by users at random times. it fails if a bunch of rows are created by a formula all-at-once (they might all have the same time-stamp)
(3) pre-store a long string of random digits using an external random number generator, and then cycle through that string, grabbing a set of digits as needed.
i really hope a CODA engineer is watching this thread and can comment/document this better.
respect
max
very well said @Agile_Dynamics , time for @Paul_Wilkins to provide context.
For what it is worth: I think all the random functions need to generate a new random number when called - regardless of how frequently the function is called in succession. Right now it is not reliable, when calling it 20 times in a row within one button function, there are way to many duplicates.
Alright - Thought I might give this one a go. I think I went a little different route than @Ryan_Martens2 , but wasn’t able to look into his doc/approach too much
Im almost certain there is a more elegant solution (and one that does not rely on _delay(_noop(),300) - but its definitely working
Check it out - happy for some feedback of how this could be improved. Works on lists of numbers, words, or any other item. Will probably only work on small lists as each item added to the list takes that much longer to actually shuffle it
hi @Scott_Collier-Weir , thx for the suggestion. I’d rather not work with _delay() but as far as I can see, it does the job! I tested it with 1000 numbers, it took a while but all numbers got shuffled up. Great news!
To make it a bit easier to check the outcome, I put the delete at the beginning of the formula, see below.
RunActions**(DeleteRows([Shuffle Table]),**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]))
why does this work and why do the other (including my) solutions fail with a button outside the table?
Totally overlooked this thread, sorry. Thanks @steph for bringing this up 
In a nutshell, here’s what actually happens:
The RNG (random number generator) used in Coda is, of course, a pseudo-random one. This means it doesn’t generate true randomness from e.g. atmospheric noise like Random.org does, but generates a sequence of pseudo-random numbers (i.e. statistically they would appear random and equally distributed) based on the initial value called seed, and some transformation algorithm (probably not LCG but study this one to get the idea)
Yeah, given that the algorithm stays the same, RNG will produce the same sequence of numbers for the same seed.
In Coda, it seems like the RNG never produces the sequence of numbers but only takes one. So as long as the seed is the same, the output is also the same.
As a seed, Coda uses the combination of:
Finally, running actions (e.g. adding or modifying rows) does NOT guarantee that the doc edit timestamp will refresh right away, even if the action is enclosed within RunActions() to wait for recalcs. I don’t know the exact reason for this but most likely it’s either:
So that’s why the behavior of Random() is as it is. It gives different values in different canvas formulas and for different rows of a table (because the seed uses object ID and row ID), but it gives the same values within one formula (same object ID and same timestamp), and it gives repeated values when adding rows (rows are being added faster than they are saved to the doc and recorded as edits). Adding _Delay()s kinda helps, but it’s not reliable because there’s no guarantee that 300 or whatever milliseconds would be enough to save the next edit — something may lag time to time and the trick won’t work.
The seemingly easy fix would be to only keep one Random() instance and never refresh the seed but simply query the next value instead. However remember that multiple people may be working on the same Coda doc at once (incl. offline), so Random() has to predictably work across connected users. That’s probably why it’s implemented the way it is, and is not so easy to fix.
The only way to get a sequence of random items that are reliably pseudo-random and don’t have these issues is to get these from a column with enough rows. The trick with the temporary table is one way to solve it.
Thanks so much for the detailed breakdown of how this all works @Paul_Danyliuk @Christiaan_Huizer @Agile_Dynamics @Scott_Collier-Weir @joost_mineur & @Ryan_Martens2 . All this frustration with Coda’s built-in Random() function made me realize we can now make our own though 
This is a random function that takes a seed argument, so you can have fine-grained control over when the pseudorandom result changes, rather than being beholden to Coda’s choices of (1) never or (2) on any doc edits.
Let me know what you think and if you have any ideas for additional functionality (e.g. I’m thinking of a function that returns an array of random numbers based on an array length you specify? maybe an integer variation, like Coda has?)
I guess I could also provide Shuffle() formulas for strings and numbers as per the original request on this thread haha. They would have to be separate formulas - what would you call them? I never know if “string” is too technical a term for a no-code platform - would you call it ShuffleStrings() or something else?
I wish it was possible to also take in a list of Objects (like rows) but unfortunately that’s not (yet?) supported in the Packs SDK 
[Edit: I went with ShuffleText() and referred to “Text Values” in my parameter hints, in line with how Coda references strings elsewhere in the interface]
Ok those features are added!
Beast mode
Beast mode
I’ve put together a demo doc that demonstrates this stuff a bit more clearly: