Hello dear Airtable community! :wave: Is it possible to add a fixed prefix with a randomly generated 4-digit number to a new entry? – So, a new entry is created with name and first name of a person in two columns. And in a previously created 3rd column called “CODE”, a 4-digit, randomly generated and unique number code is now automatically added (1234) to a previously defined prefix (#AB😞 for example #AB1234 or #AB9876. Who knows advice? Thank you very much in advance!!! :pray:
Unfortunately, Airtable doesn’t have a random function, and there’s no way to automatically search for uniqueness without writing your own Javascript script or using an external automation tool to do this.
You could MANUALLY run the Batch Update App to put a random number into each record, but you would need to run it manually (because apps can’t be automated) and there is no guarantee that it would be a unique value:
If you really want a truly random & truly unique value, you would probably be better off using the internal Record ID of each record, which you can put into a formula by using the RECORD_ID() function. However, that will give you a string of 17 alphanumeric characters instead of 4 numbers.
So if you want to generate random 4-digit numbers automatically and ensure their uniqueness, you would need to write your own Javascript script as part of an automation to handle that.
You could also write your own automation scenario by using an external automation tool like my personal favorite Integromat, which has a random function:
Um, this is not actually possible because the odds of creating a second “random” 4-digit number is far higher than the universe of 4-digit number sequences (which is 10,000). If you truly mean “number” as in integer, there are only 9000. The addition of two prefix letter characters increases the universe of possibilities to about 6 million combinations (I think it’s 26 x 26 x 9,000).
These are very small sets in the context of uniqueness. Perhaps you really want a GUID if you need reasonable assurance of uniqueness. And you probably need to define “uniqueness”. Do you mean immutable within a table? A base? The entire world of data records?
To clarify what I meant, you can ensure the uniqueness of a random value if you do a search for the newly-generated random value amongst the existing values before inserting the new value.
If the newly-generated value already exists amongst the existing values, then you would continue to generate values until you created a unique value.
And yes, this would depend on the number of records in the base being less than the number of unique combinations that exist.
However, all of this would take time & energy to program, so it seems like it would just be easier to go with the RECORD_ID or just go with an autonumber value instead of a random value.
I’m also not 100% sure why @Dominic_Mueller is looking for a random number instead of an autonumber?
This may or may not be relevant, but I recall many years ago when I was getting my first business checking account, it was recommended that I start the check numbering with 1001. The reason for this was that it created the impression of the business being around for a while, thereby possibly impacting the perceived trustworthiness of the business.
Maybe something similar is at play here. If the assigned number does not actually represent how many entries are in the system, it’s harder to draw accurate assumptions based on that number (for those who may be inclined to do such things). In other words, it might be an obfuscation technique.
Yeah, this is possible but there are some issues when multiple users are trying to get new random IDs. It’s a lot of moving parts and added complexities like this that tend to create more headaches over the horizon. Under this approach, as the data set grows, the ability to randomly generate IDs is continually shrinking resulting in a growing number of retries for every new record added. Imagine reaching 9999 and then trying to get a random process to hit on the one remaining ID that has not yet been used; it might take a day of retries to hit it - indeed, it will require at least 4999 attempts and more likely > 8999 attempts.
We can’t know until someone reveals the business requirements, right? Perhaps the prefix letters are meaningful and make the IDs more human-friendly and say something about the record that a GUID or record ID or auto-number could never reveal. The customer says he needs this format - we can only assume he has his reasons.
In my view, there’s nothing inherently wrong with reference IDs and unique IDs living under the same roof. The only axe I have is assuming that four-digits will provide immutable uniqueness; they can’t not assure uniqueness internally to the app, nor externally for sure.
If you need a unique ID, your best approach is a GUID.
Hi @ScottWorld ! Thanks also to @Bill.French and Justin_Barrett for the quick responses! Great community here! – Unfortunately, the RECORD ID is too long for what I’m trying to do. In fact, I want the prefix “#AB” to remain the same for each new entry, and then just add a four digit random and unique (not yet used) code of letters and numbers. For this, I have actually already been able to achieve part of my goal with the following function => CONCATENATE("#AB",{Random Code}) – however, I still don’t know where the random code should come from. Jotform offers something like that, but the integration of Jotform to Airtable doesn’t allow importing randomly created code. And I don’t know why. How could I possibly use a formula to create this desired code? After all, there would actually be 1,679,616.00 possibilities for the number and letter combinations (26+10)^4. That should be enough for the beginning. I just want to create a code that can be remembered. Is there another way you can imagine accept the use of integromat? Thank you!
This is not true. All random numbers that Jotform creates on its form are seen by Integromat. 100% of the values on a JotForm form — hidden, visible, random, calculated, whatever — are all sent to Integromat.
It sounds like all of your new entries are coming into Airtable via form submission? If so, then you have already figured out the solution to your own problem! :slightly_smiling_face:
JotForm’s random number generator also has the ability to check for uniqueness, so you will never get the same number generated twice! So you can just use JotForm’s random number generator to generate your 4-digit code there. (I’m not sure what it does when you get to your 10,000th entry, though. I’m curious to know.)
Is this true when you anchor the alpha character set to just “AB”?
Okay - this is good to know; I thought there was a reason for the leading prefix.
We recently tackled this exact challenge hoping to help our customers with a memorable reservation code for their invisible truck camper CyberLandr. The codes are like #OHEO3 and it gives us 435,897 possible alpha-numeric combinations and it’s worked pretty well so far. This code is also their personal referral code so they can earn bonuses against the final invoice price.
While the original post only mentioned numbers for the code after the “AB” prefix, it sounds like @Dominic_Mueller actually wants to use any alphanumeric characters for those latter four, not just numbers.
