Aug 02, 2016 02:01 PM
Perhaps I’m missing it, but how do I generate random numbers? I am missing “randbetween” and “rnd” from Excel.
Thanks for any and all help.
Solved! Go to Solution.
May 26, 2019 11:07 PM
You can Try using function like this : RIGHT(RECORD_ID(),5)
Jul 15, 2019 07:31 PM
Since this thread is still seeing traffic, I should add a link to this recent post, which outlines some ways to generate unique, random[ish] values.
Dec 11, 2019 09:16 AM
I’ve got a similar issue I’m trying to resolve. I would like to be able to pull text entries from another table by LOOKUP or ROLLUP and then randomize the order of those entries, i.e. not alphabetical. Here is the use-case:
We have an episode table, a show table, and a sponsor table. Each show record is first pulling sponsor data from the sponsor table, each episode record is then pulling the aggregated sponsor data from the show table.
Episode 123 - of - Show ABC - has - Sponsor X, Sponsor Y, and Sponsor Z
I would like each new episode record to randomly order the sponsors as it is created. For example:
I’m also utilizing Zapier for some other automation tasks, but haven’t found a great solution there either. Any feedback/help would be greatly appreciated. Thank you!
Dec 11, 2019 04:15 PM
You actually may have a useful randomizing tool there in the Code by Zapier app. You (or someone you know/hire) could write a quick bit of code that randomizes the sponsor assignment for a later step to apply in Airtable.
Mar 11, 2020 01:36 PM
This is my first post, so forgive me if this isn’t helpful. But a workaround I use for generating random numbers is to set up the key column with this formula:
DATETIME_FORMAT(CREATED_TIME(),‘mmsss’)
It pulls the current minute and second the entry was created and displays a set of random digits. This has worked pretty well for my purposes!
Mar 11, 2020 02:09 PM
Doesn’t work for me as most of my items are essentially created at the same time :frowning:
May 18, 2020 07:52 AM
@Daniel_Robbins @Jonathan_Southgate Instead of trying to build the number manually by combining minutes and seconds, you could try using the “x” format specifier, which is a UNIX timestamp that includes milliseconds. Depending on how quickly the records are made (mainly in your case, @Daniel_Robbins), that might do the trick.
May 19, 2020 11:04 AM
You can use the Batch Update Block to generate random numbers for your records. See screenshot below.
Interestingly, the same block can also be used to generate random names & email addresses!
Jun 25, 2020 02:00 PM
Here’s a way to generate random number using formula field in airtable
first choose a MIN and MAX range for your random numbers then use the following formula:
(VALUE(DATETIME_FORMAT(CREATED_TIME(), ‘0.smh’))*(MAX-MIN))+MIN
this generates the following:
Aug 12, 2020 09:45 AM
We’ve built an extension that allows you to generate a random number or ID. You can choose how many characters it should have and whether to include letters or not.
Aug 23, 2021 12:58 PM
I would love to get a work around for this. I am trying to use a setup to generate automated SMS message for Zapier and need to use some random numbers to alternate the messages that are being sent.
Sep 18, 2021 12:16 PM
Here is my brute force solution! This formula takes a direct approach by converting the letters in RECORD_ID() to numbers. It’s just 52 nested SUBSTITUTE() calls :rofl:
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(RIGHT(RECORD_ID(), 14)
,"a","0")
,"b","1")
,"c","2")
,"d","3")
,"e","4")
,"f","5")
,"g","6")
,"h","7")
,"i","8")
,"j","9")
,"k","0")
,"l","1")
,"m","2")
,"n","3")
,"o","4")
,"p","5")
,"q","6")
,"r","7")
,"s","8")
,"t","9")
,"u","0")
,"v","1")
,"w","2")
,"x","3")
,"y","4")
,"z","5")
,"A","6")
,"B","7")
,"C","8")
,"D","9")
,"E","0")
,"F","1")
,"G","2")
,"H","3")
,"I","4")
,"J","5")
,"K","6")
,"L","7")
,"M","8")
,"N","9")
,"O","0")
,"P","1")
,"Q","2")
,"R","3")
,"S","4")
,"T","5")
,"U","6")
,"V","7")
,"W","8")
,"X","9")
,"Y","0")
,"Z","1")
The digit distribution should be close to uniform, but because 26 * 2 = 56 letters is not divisible by 10, there will be a slight bias toward “0” and “1” corresponding to those last two characters, “Y” and “Z”.
Dec 09, 2021 05:21 PM
Very nicely done @Mahmoud_Habib!
Mar 11, 2023 07:25 AM
In case anyone needs it, for example this is how to get a random number between 1 and 30:
IF(VALUE(RECORD_ID()) <= 0, 1, IF(VALUE(RECORD_ID()) >= 1000000, 30, MOD(VALUE(RECORD_ID()), 30) + 1))
Jul 13, 2023 02:33 AM
Thanks Joachim. That works very nice and without much complications.
I need to generate a random number between 1 and 156 and I just replaced the 30 for 156 and Voila :D.
I'm not exactly sure how that works but looks good, Although I got a lot of 1's and 2's (I have around 280 records)
I was using this formula:
Feb 03, 2024 07:53 AM
Feb 03, 2024 08:00 AM
The VALUE(RECORD_ID()) attempts to convert the unique record id (which is alphanumeric) to a numeric value. In theory, since each record has a unique id, the numeric value derived from it would also be unique.
This part IF(VALUE(RECORD_ID()) <= 0, 1,... checks if the numeric conversion resulted in a number less than or equal to zero. If yes, it returns 1.
The second condition IF(VALUE(RECORD_ID()) >= 1000000, 30,... checks if the numeric conversion resulted in a number greater than or equal to 1 million. If yes, it returns 30.
If neither of the conditions is met, it uses MOD(VALUE(RECORD_ID()), 30) + 1, which calculates the remainder when the numeric value of Record_ID is divided by 30 and adds 1 to it.
The randomness in this formula does not come from a random function but rather from the unique numeric id converted from the unique alphanumeric id of each record, distributed across a range of 1 to 30.
Feb 03, 2024 08:11 AM
Use this :
MOD(
VALUE(
LEFT(
DATETIME_FORMAT(
CREATED_TIME(),
'X'
),
5
)
&
RIGHT(
DATETIME_FORMAT(
CREATED_TIME(),
'X'
),
5
)
&
MID(
RECORD_ID(),
LEN(
RECORD_ID()
),
1
)
,
3
),
500
)
+ 500
Feb 03, 2024 01:37 PM
Worked thanks!