random

[OLIVIER_NIEL]

Hello everyone,

I am currently working on a database in Airtable where I need to automatically generate a random number from a specific list of numbers for each new entry in my table named "Article". The list of possible numbers is as follows: [1448, 800, 140, 105, 106]. I want each time a new record is created, one of these numbers is randomly selected and inserted into a column named "Nombre".

I tried using built-in formulas in Airtable, but they don't seem to work as I wish. I also tried writing a script in Airtable's Scripting app, but I haven't been able to get the desired behavior.

I wonder if anyone has encountered a similar issue or if anyone knows how I could achieve this, either with formulas or with a script.

[kuovonne]

Here are some ideas. 

Use the last character of the record ID as the source of randomness. You will need to map each possible character to one of the values. 

Use the seconds of the created time of the record as your source of randomness. Map a range of seconds to different numbers using nested IF() functions. Or use MOD() to convert the second to a value from 1-5, then convert that number to one of your numbers using SWITCH(). If you create multiple records at the same time, this will not work. 

Otherwise you will need a script to get a random value. 

One way to do this in a script is to put your possible values in an array. Your array will have five elements in it. Then generate a random number from zero to four. Use this random number to access a random element from the array to get the random number. 

There are other variations when using a script. For example, if your values are select choices, you can use the select choices as your starting array and then use a random index on that array. 

If your choices are records in a view, you can get the records in that view and get a random index from this query results to pick a random record. 

[OLIVIER_NIEL]

Je n'ai rien compris

[Alexey_Gusev]

There are plenty of ways. One of them: take any char from record_id(), except first three in  'recXXXXXXX'. I took right char.
X may be letter in upper case, lower case or digit, 26+26+10=62.
Exclude '8' and '9', for example, to get 60 possible ways of output. MOD(1-60,5) will be random number from 0 to 4.  Wrap into switch and task completed.

SWITCH(MOD(FIND(
  RIGHT(SUBSTITUTE(SUBSTITUTE(RECORD_ID(),'9',''),'8','')),
    'abcdefghijklmnopqrstuvwxyz'&
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ'&
    '01234567')
,5),
0,1448,
1, 800,
2, 140,
3, 105, 
4, 106)

[OLIVIER_NIEL]

for me, it's in a series of specific numbers...for example, he has to choose between the numbers 100, 430.1, or 5433.

[Alexey_Gusev]

Use the same formula, 60 can be divided by 2,3,4,5,6 just set 3 here

and put your numbers as 
0, 100,
1, 430.1,
2, 5433 )

But I would recommend to review and understand how it's working

In short, 

FIND(
  RIGHT(SUBSTITUTE(SUBSTITUTE(RECORD_ID(),'9',''),'8','')),
    'abcdefghijklmnopqrstuvwxyz'&
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ'&
    '01234567')

outputs random number from 1 to 60
then you wrap it in  MOD( _Main_formula_ , N ) to get random number from 0 to N-1
then use SWITCH to choose from your N numbers

Note: if 60 / N is not integer, for example N=8,
you will get different probability.  Instead of 100/8=12,5% for each, you'll get 7/60=11,667% for 5,6,7,0 and 8/60=13,33% for 1,2,3,4.  If such difference is not critical, you can use it for any "number of numbers"

[Alexey_Gusev]

Hmm, I just read your message again and seems like you need to dynamically choose from a list of comma-separated values, so you can't hardcode them in formula. And it's a different number of numbers in each row.
It's doable as well.
- find number of commas (by LEN({Field})-LEN(SUBSTITUTE({Field}, ',' , ''))   , for example)   result+1 will be N
- get random number from 1 to N,   let's call it R
- substitute R-th occurence (4th parameter of SUBSTITUTE) of comma from  CONCATENATE(',' , {Field})  by any pattern . Added start comma - to give a first number an equal chance
- find this pattern and cut substring from there to next comma

I would recommend to write each step in notepad including previous as Result1, Result2 etc. And then do Replace. Final formula will be quite huge.

If you don't need to dynamically support it, you can write script or create Automation to run each time field with numbers changed. I think it's more reasonable way to do it.
Automation should include script step with just 2 lines:

const arr=input.config().numbers.split(',')
output.set('randomnumber',arr[Math.trunc(arr.length*Math.random())])

Then use output in next Update record step.

btw, I applied some school math from a long time ago,
if you add 2 different randoms from previous example, their MOD will have equal probability for any N no matter how it can or cannot be divided. This formula is good for any N (of course not more than 36)

MOD(FIND(UPPER(LEFT(RIGHT(RECORD_ID(),2))),'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')
+FIND(UPPER(RIGHT(RECORD_ID())),'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), N )