Random number, not repeated between 1000 and 9999

@jrsepul2000 

Unfortunately, Airtable is missing several formula functions that you might expect to see in spreadsheets or other database apps.

The random function is one of those missing functions. You may want to email support@airtable.com to ask them about adding RANDOM() as an official Airtable formula function.

However, there are 4 workarounds that you can use to generate random numbers in Airtable:

1. You can generate a pseudo-random number by basing your number on the date & time of record creation or the Airtable Record ID. See this post and this post.

2. You can use Airtable’s Batch Update extension to MANUALLY generate random numbers for records that have already been created in your base. Note that there is no way to automate this, and it only works for records that have already been created in your base.

3. If you know JavaScript code, you can use Airtable’s automations to automatically generate a random number for your records.

4. If you want a no-code way of automatically generating random numbers, you can Make’s advanced automations for Airtable, which offers a native random() function. If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one of the ways that you could instantly trigger a Make automation from Airtable.

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

@jrsepul2000 

Welcome to the Airtable community  😀

If you wanted a unique sequence of numbers starting from 1000, one way to do this would be to add an Autonumber field, then add a formula field that referenced it, adding the output to 1000.

The formula would be simply 1000 + {autonumber}

This is not a randomly generated number, but they are always unique. 

As far as I know, Airtable does not have a built-in formula for generating truly random numbers (like Excel’s RAND() function), but there are workarounds for creating something similar to a randomly generated number if you get creative with things like MOD(), NOW(), RECORD_ID() or CREATED_TIME() functions. This would not guarantee uniqueness though.

As for option 2. You could set up an automation that triggers when you create a new record in the table you are working with. It would find a record in your table of numbers that is not yet linked to your table and link it to your newly created record. Not sure I would recommend this process, but in theory it is possible.

I assume that your table will never have more than 8999 records. I am also assuming that you want a system that will assign numbers as new records are created.

As others have mentioned, there is no RANDOM() function in Airtable formula language. It is also possible to generate pseudo random values using record creation time or the record ID. However, there are no guarantees that you will not have repeats using either of those methods.

Here's a way to setup a table of numbers that requires no code and no third party tools. It's a bit of work to setup but should not need any manual intervention as long as you don't have too many records.

1. Create a [Random Number] table with one record for each number from 1000 to 9999.

2. Add a second number field and fill it with random values using the Batch Update extension, as Scott  suggested. This field will be used to randomize the list of numbers.

3. Sort the records according to the random number generated by the Batch Update extension.

4. Create a same-table link field for the {Next Record}. Copy/paste the primary field values into the {Next Record} linked record field, offsetting by one to link each record to the next one. Note that the {Next Record} field will be blank for the last record. 

5. Create a {Use Me Next} checkbox field. Select this checkbox for the first record.

6. Link the [Random Number] table to your original table using linked record fields.

7. Create an automation that will link a record in your original table to an unused record in the [Random Number] table. The trigger can be when the record is created, or some other trigger if that is what your workflow needs.

8. The first action in the automation should do a Find Records action to find the record with the {Use Me Next} checkbox selected.

9. The second action is an Update record action that links {Use Me Next} record to the triggering record and also clears the checkbox.

10. The third action is another Update Record action. This one selects the {Use Me Next} checkbox for the record in the {Next Record} field of the original {Use Me Next} record.

Is this a bit convoluted? Yes. Are there other ways to do this? Yes.  One of the reasons this is so convoluted is because Airtable currently does not have a way of selecting just one item in a list in an automation action without using code. If Airtable releases that feature, the process will be much simpler.

In the middle of the platform change for the community when community posts were read-only, another member messaged me a better alternative. You can do a “Find Records” action on a view that is sorted according to the random number generated by the Batch Update extension and that is filtered to only include rows that have not yet been used. The “Find Records” action can then limit the results to the first record in the view. This means you don’t need the {Use Me Next} checkbox or the {Next Record} field.

I greatly apologize for not not remembering who sent me this reminder about performing a “Find Records” on a sorted view. All personal messages were deleted in the change to the new platform, so I can not look it up. Hopefully whoever it was will speak up so that he can get proper credit.

You can use Excel’s RANDBETWEEN(1000,9999) for random numbers, but to avoid repeats, a helper column with RAND() and Snapchat solar system sorting might help. A VBA script could automate unique assignments if needed.

This is can be done with a basic script triggered via an automation.

async function insertUniqueRandomNumber() {
  const table = base.getTable('Random Number');
  const query = await table.selectRecordsAsync();
  let randomNumber;
  let exists;

  do {
    randomNumber = Math.floor(Math.random() * 10000) + 1;
    exists = query.records.some(record => record.getCellValue('Random Number') === randomNumber);
  } while (exists);

  await table.createRecordAsync({
    'Random Number': randomNumber
  });

  console.log(`Inserted unique random number: ${randomNumber}`);
}

insertUniqueRandomNumber();

I forgot to add the automation trigger.  I’ve added a final “update record” that removes the checkbox.

You cannot use Excel functions in Airtable.

async function insertUniqueRandomNumber() {
  const table = base.getTable('Random Number');
  const query = await table.selectRecordsAsync();
  let randomNumber;
  let exists;

  do {
    randomNumber = Math.floor(Math.random() * 10000) + 1;
    exists = query.records.some(record => record.getCellValue('Random Number') === randomNumber);
  } while (exists);

  await table.createRecordAsync({
    'Random Number': randomNumber
  });

  console.log(`Inserted unique random number: ${randomNumber}`);
}

insertUniqueRandomNumber();

While this script will work most of the time, there are a few issues.

Some of the issues are fairly minor. For example, the original poster wants numbers from 1000-9999, but this script will produce numbers from 1-10000. This is easily solved by incorporating a bit more arithmetic in the script.

Another issue is that this scripts creates a new record, when the use case might need to apply the random number to an existing record. It isn’t clear what the original poster wants. This is also easily solved in a number of ways. The most straightforward is to have the script output the random number, and then using an update record action to put the number in the script.

Additionally, the original poster self-identified as not a programmer. Thus, I expect the original poster would prefer a non-scripting solution.

There is a bit of inefficiency in mapping the query results to get a list of existing numbers inside the loop. The extra processing probably wouldn’t be noticeable by the average user. It is also easy to change by doing the mapping before the loop.

However, the main concern that I have with this script is that it is not guaranteed to generate a new random number before the script times out. The script generates a new random number in each iteration of the loop. While it is statistically unlikely to happen when there are only a few records, it is theoretically possible that the loop would never actually generate a new unique number. This becomes more possible when there most of the numbers have already been taken. For example, when there the last record is ready to get its “random number”, there should be only one number left available. It is possible that the loop will never actually generate that particular number, and the script will timeout.

Great feedback ​@kuovonne.  I thought I would post it as an example of how it could be done providing the code so the user doesn’t have to do anything but copy and paste.  I’m interested on the performance issues you brought up and will test those.

While it can be great to provide code that users can simply copy/paste, in this particular case, I felt that the original poster could NOT simply copy/paste the code and wanted to point out why. It wouldn't take much to change the script to fit the original poster's use case, if the original poster were interested in learning coding, but I did not get that impression.

As for the performance issues, they probably aren't noticeable because code executes so fast. And Airtable doesn't charge extra for scripts that take more computing resources. However, we should still try be good stewards of computing power, even if it doesn't seem to make much of a difference because little habits add up.

Actually the idea was even easier, without extra scripting and extensions.
1st field - Number (1000-9999)  2nd - RECORD_ID() formula  3rd - Checkbox ‘Used’
View sorted by record_ID, filtered by ‘used’ is empty.
then Automation ‘Find Records’ by this view, limited to 1 record.
the rest is simple.
If you want to reshuffle, change formula to RIGHT(RECORD_ID(),  X ) where X is any number from 3 to 13  (62**3 is enough for 10k numbers)

Actually, clear random is not applicable here because of ‘birthdays paradox’ (in group of 23 or more people probability of having the same birthday date is > 50%).
On bigger numbers it’s even worse, in 112 random numbers from 1000 to 9999  repeat probability is 50%.
In 5% of range, 450 numbers, repeat is almost guaranteed, 99.9987%

You can check it:
 

const numbers=new Array(112).fill(0).map(n=>1000+Math.round(9000*Math.random()))
console.log({numbers,uniq:[...new Set(numbers)]})