Help

Re: Random number functions

Solved
Jump to Solution
2007 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_Robbins
6 - Interface Innovator
6 - Interface Innovator

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.

38 Replies 38
bonagres_sialla
4 - Data Explorer
4 - Data Explorer

You can Try using function like this : RIGHT(RECORD_ID(),5)

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.

Adam_Lockwood
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

  • Episode 123 - Show ABC - Sponsor Y, Sponsor X, Sponsor Z
  • Episode 124 - Show ABC - Sponsor Z, Sponsor Y, Sponsor X
  • Episode 125 - Show ABC - Sponsor X, Sponsor Z, Sponsor Y

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!

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.

Jonathan_Southg
4 - Data Explorer
4 - Data Explorer

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!

Doesn’t work for me as most of my items are essentially created at the same time :frowning:

image.png

@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.

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!

Screen Shot 2020-05-19 at 11.04.19 AM

Mahmoud_Habib
5 - Automation Enthusiast
5 - Automation Enthusiast

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:
image
(VALUE(DATETIME_FORMAT(CREATED_TIME(), ‘0.smh’))*(MAX-MIN))+MIN
this generates the following:
image

Moe
10 - Mercury
10 - Mercury

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.

generate a random number

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.

Benjamin_James_
4 - Data Explorer
4 - Data Explorer

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”.

random from recordid

Very nicely done @Mahmoud_Habib!

Joachim_Brindea
6 - Interface Innovator
6 - Interface Innovator

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))

 

Herege
5 - Automation Enthusiast
5 - Automation Enthusiast

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:

ROUND((VALUE(DATETIME_FORMAT(CREATED_TIME(), '0.smh'))*(156-1)),0)
but I had a very few "hits" for numbers higher than 100 not sure why.
 
Can you elaborate a little more on how this randomness is reached?
 
Much thanks 🙂

I have checked and rechecked this it is saying it is an invalid formula

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.

  1. 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.

  2. 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.

  3. 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. 

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
epicmike2835
4 - Data Explorer
4 - Data Explorer

Worked thanks!