Non-API, Non-Pack, Client-Side, Instant RANDOM number generator Formula

Showcase Tips and Hacks
1

For a client project, I needed to use random numbers in a large table of test data (5k rows).

There are the following Random number generator functions available today in Coda;

  • Random()
  • RandomInteger()
  • RandomItem()
  • RandomSample()
  • Randomizer Pack by @Nick_HE

They all work and do exactly what it says on the tin

BUT…

They all execute on the Coda server farm - which means they involve a delay. The Randomizer Pack by @Nick_HE was created to also overcome some asynchronous issues the built-in functions above, but as a Pack, it too runs on the server ie: there is a delay.

That made them unsuitable for this particular project, where we needed to assign a random number to each NEW record in a table in real-time, with about 5 thousand rows being generated each day. This table gets generated and added to as the users process the document - so a FAST random number generator that runs in the client browser was needed.

The following formula is placed in the ‘value for new row’ option for a numeric column called RND in the table. So every time a new row is added, it instantly gets a big random number with almost no delay. This works regardless of HOW the row gets created - by a button - by an automation - by the user hitting the ‘+’ symbol on the table, by the user pressing ENTER after editing the last row of the table - etc etc etc.

It runs in the client, so its very fast, and the random number is instantly available to any other formulas that need to use it (which is not the case with the built-in Random() and RandomInteger() functions).

The formula is based on the ANSI Standard RNG used in the C Language Compiler. Its simple, fast and generates a reasonable distribution of random numbers with values between 0.0 and 1.0 exclusive (meaning you never get exactly zero or one).

It generates floating-point numbers with a random set of up to 9 decimal digits. This is the most useful form of random number as we can multiply it to get whatever range we need.

So here is the calculation that is applied to the RND column of the table in the ‘value for new rows’:
image.png

How it Works;

If this is NOT the first row being created (ie Count(thisTable) is not zero)
then take the PREVIOUS value for RND - ie Last(thisTable.RND) and use it in the calculation

Otherwise, this IS the first row being created in the table (Count(thisTable) is zero), so we use the current time-stamp as a ‘seed’ value in the calculation. Now()*10000 yields the number of seconds since the start of 1970 - a nice big number.

We multiply that value by a long special number and add another special number to that to get another large number. The ‘special’ numbers have been fine-tuned over many decades for the best distribution of pseudo random numbers.

Then we modulate the result by 0.999999999 to get a number between 0.0 and 1.0 (exclusive) with up to 9 decimal digits. The x % y operation divides x by Y but returns the REMAINDER or ‘Modulus’.

To test that this is generating an evenly distributed spread of random values, I plotted a large sample of them on a scatter chart - if there are large blobs or stripes or any big patterns - then its not evenly distributed

image.png
image.png789×361 79.7 KB

As you can see the scattering is fairly evenly spread for just 2,000 values (the largest set that Coda’s Scatter Chart can plot). This plot uses the current random number for the x-coordinate and the previous random number for the y-coordinate - so any unstable behavior shows up as distinct patterns or ‘biases’ in the distribution of the dots. Its certainly ‘random’ enough for most purposes.

We can use this basic random number in several ways.;

  • for a random number between 0 thru N, we use RND*N
  • if it needs to be an integer between 0 and N, use Truncate(RND*N)
  • if it needs to be in the range A thru B, use RND*(B-A)+A
  • and again, for an integer between A and B, use Truncate(RND*(B-A)+A)

It just goes to show that Packs are not the answer to everything. Sometimes a good old fashioned formula does the trick.

respect
Max

13 Likes
2

This is awesome!

I hope someday there’s the ability to encapsulate this kind of functionality in (first) a function you can re-use within a doc and (later) in a Pack of client-side functions you can re-use anywhere

2 Likes
3

Love the application of computer science to what they’re describing as «no need to learn to code» :grin:

(by «they» I mean no-code propagandists, of course)

3 Likes
4

Maybe I should point out that the formula editor does complain about a “Circular Reference” for the

Last(RND)

reference in the new row value for the RND column.

But it runs perfectly regardless.

Not the first time I have had to ignore well-meaning warnings from a compiler :relaxed:

Max

5

Interesting and very practical approach @Agile_Dynamics ,
what is the formula you use in the the column RND?
curious to apply this in my docs.
merci!
Christiaan

6

there is no column formula for RND because its value is set to a random number when the row is created

the ‘value for new rows’ option is used to set that value

the formula there is the one i gave in the o.p. above

here it is again…

(
    If(Count(thisTable),
        Last(thisTable.RND),
        Now()*10000
    )
    *1003515245 + 12345
)

so this formula is only run when a new row is created
it takes the last random number and uses it to compute the next one

max

1 Like
7

thanks @Agile_Dynamics , only now I see your point

since you wrote the below I wrongly assumed you used a second column with some magic.

The formula is based on the ANSI Standard RNG used in the C Language Compiler. Its simple, fast and generates a reasonable distribution of random numbers with values between 0.0 and 1.0 exclusive (meaning you never get exactly zero or one).

This makes the solution even more elegant.
merci!
Christiaan

8

Thanks so much for sharing this, @Agile_Dynamics , very insightful!

I had some trouble recreating your formula, so I hope you don’t mind that I’m printing it out once more here in case others are running into the same problem.

(I had copied your formula from your second post, ie your response to @Christiaan_Huizer , to try it out, and that formula was missing the last piece of the puzzle).

So, long story short:

  • Create a table, Column A (text) contains item name, Column B (number) is blank
  • For Column B, you set the following formula as the “Value for new rows”:
if(thisTable.Count(),thisTable.[Random Number].Last(),Now()*10000)
*1003515245+12345
%.999999999

Now, mind you, there’s two parts to this formula that threw me off :wink:

  1. The formula specifies a certain calculation to be done. The If-statement merely changes the starting number that the formula stall begin with. (aka: Mind your parentheses!)
    If a row already exists, then start with the last row. If no row already exists, then start with the number Now()*10000 (see Max’s post for why he used this - mainly because it’s a “nice big number” (indeed it is, Max :wink:
  2. Then, once the beginning of the formula has been specified by the if-statement, then perform the following calculation on that number: *1003515245+12345
  3. And then, as a last step, multiply the result with %.999999999 (see Max’s post on why). I had never seen a calculation being done that way, but am happy to report that the result is the same if you use the (to me, much more familiar) way of writing it like this *0.999999999

I hope this helped someone (instead of just confusing everyone reading this far :slight_smile:

9

apologies for the confusion

thanks @Nina_Ledid for the clarification

and some further clarifications on the clarifications…

  • nina calls the calculation column ‘Column B’ in her text, but refers to it as ‘[Random Number]’ in her formula. the key point is that this formula refers to itself. it uses the PREVIOUS random number to generate the NEXT random number

  • the reason for using Now()*1000 as the “Seed” value is not just because its a big number but because it will be a DIFFERENT number each time it is used.

  • the N % 0.999999999 is not a multiplication but a MODULUS operation. ie: divide X by 0.999999999 and return the REMAINDER (which, in this case will be a float between 0 and 1 with lots of decimal digits. the other way of writing X % Y in Coda is; Mod(X,Y)

  • the fact that the multiply does a similar job is news to me, but probably not unexpected. so i would like to test that statistically before endorsing that simplification.

when i get back to my office in the morning, i will publish a document with several randomizer and stastical functions that are useful for generating large-scale test cases. thus the definitive formulas will be available.

which is what i should have done in the first place, sorry

respect
max

10

And that, dear @Agile_Dynamics, is the main difference between the two of us - I for one opt for the simplification prior to testing. Hmm, I wonder who of us has the more statistically reliable work method :wink:

Thanks for clarifying, I greatly appreciate it!

11

This is a sample document showing how the Random Number formula works.
The main random number is RND
You wont find the formula for it in the usual place.
You have to look in the ‘Value for New Rows’ option under the “Number Options” menu.
Thats because the number is computed only when a new row is created.

The second column shown uses a regular formula. It takes the RND value and computes from it a random integer between 5000 and 9500 - just to illustrate how it’s done.

The BUTTON clears the table and generates RowCount new rows (initially set to 500).

The chart shows how well the RND random numbers are distributed. Its a test of the randomness.

@Nina_Ledid, I tried using *999999999 (multiply) instead of %.999999999 (modulus), but it did not do the same at all. The modulus generates large decimal strings between 0.0 and 0.999999999, while the multiply did not.

Respect
Max

2 Likes
12

If this were a crime, I would be on death row.

1 Like