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;
- Randomizer Pack by @Nick_HE
They all work and do exactly what it says on the tin
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’:
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
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.