Randomize / shuffle a List

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

1 Like

There is a solution in the first part of the doc embedded in this post which I will re-embed here.

Brief Explanation

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 separate Random() 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 :slightly_smiling_face:

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 @Xyzor_Max ,

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 @Xyzor_Max 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:

  • it creates the new rows in the target tables
  • it fills out some of these rows
  • it filters out the non distributed values and randmises it.
  • it filters the blank rows in the target table
  • it fills out the blank rows with the non distributed values

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.

1 Like

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

2 Likes

very well said @Xyzor_Max , time for @Paul_Wilkins to provide context.

2 Likes

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.

3 Likes

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?