Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 10, 2018 08:49 AM
I would like to generate a unique primary key for each record of a table/database based on the following criteria:
Example:
If I was to create a new record for myself, my key is S0001.
If the next entry has the first name of “Howard,” the key would be H0001.
Then if I create a record for someone with the first name of Susan, the key would be S0002 because S0001 already exists.
Similarly, Helen would be H0002.
etc.
Can this be done using Airtable?
Thank you.
Scott
Oct 10, 2018 09:28 AM
You can use LEFT()
function:
You need an Autonumber field and concatenate it with the letter. Would be something like:
LEFT(Name,1) & Autonumber
Oct 10, 2018 12:25 PM
The above works well but to my knowledge there isn’t (currently) a way to make the auto number start with a 4 digit placeholder. If you’re looking for a truly unique primary key for each record, I would suggest Elias’s solution but instead of auto number, using Record_ID().
LEFT({First_Name},1) & Record_ID() ) which will return something like this: SXXXXXXXXX
or
LEFT({First_Name},1) & “-” & Record_ID() ) which will return something like this: S-XXXXXXXX
Oct 10, 2018 02:10 PM
Oh, I also thought about the RECORD_ID()
function, it’s a good solution. Also, I missed the 4-digit part :man_facepalming: , so it would be:
LEFT(Name,1) & RIGHT('0000' & Autonumber,4)
(or you could use Record ID instead of the RIGHT
thing).
This is very similar to this thread:
Oct 12, 2018 12:06 PM
First, thank you for answering. This has enlightened me a bit on Airtable.
I think I figured out the Autonumber requires a column to be numbered. I created an autonumbered column and this works as described.
If I was starting over, this is not a problem. However, my current Customer ID numbers are the first initial followed by a sequence for that letter. In the above scenario, I get the following:
S0001 Scott…
H0002 Howard
S0003 Seth
H0004 Helen
C0005 Caren
The way I had been assigning the IDs manually is that each letter has its own sequence. Using the names from above, I have the following: Howard is code H0001, Seth is S0002, Helen is H0002, and Caren is C0001.
Is there any way to carry forward my current convention?
Thank you!!
Scott
Oct 13, 2018 03:40 AM
Hi Scott
I don’t think there is within Airtable alone - but you could it with Integromat or Zapier I think…
Create another table (Letters, say) with a record for each letter of the alphabet (A-Z) - use the letter as the Name Column and have a field for Next ID to be used.
In the Zap / Integromat process, trigger based on a new record.
In the next step search for a record in your Letters table for the record based on the first letter of the name.
Update the Key field in your original table with the value {Initial Letter} & {Next ID}.
Update the Letters record to have Next ID = Next ID + 1.
The only downside of using these tools is that they aren’t instant at the moment - and the delay before your ID is populated would depend on your Plan level. Note that with Zapier you would need to use a paid plan as there are multiple steps involved.
Oct 19, 2018 03:11 PM
Sorry I didn’t get back to you sooner… Real Life [TM] got in the way!
I understand what you are saying but I am thinking about something more simple.
What I decided to do was create a table with the letters A-Z and a column starting at 1. When I go to create a Key, I will flip over the table, take the number next to it and that will be the sequence number. Before I switch back to the main table I’ll increment the number so the next time I use that as the sequence number. Someday, I will be able to automate this. Just not today!
Thank you.
Scott
Nov 27, 2018 07:58 PM
Hi
I have a similar situation, except I would need to create four digits as a unique number, preferrably in the primary field. I would have to create unique ID numbers for a number of bases, so, all bases would have to have unique record ID’s (I setup my bases this way because I have more than 2,000 records and I don’t want to pay for a plan for personal use). Can I create unique ID’s for more than 2,000 records, in different bases? I would have to keep the ID’s in sequence (just within the bases), but they would have to be all different in all four or five bases, even when adding or deleting records.
Thank you,
MK
Mar 19, 2019 10:18 PM
A couple ideas on how to solve this… but just a note at the top, your ID system probably just needs to be rebuilt. The initial system already breaks legibility in your example… so the usefulness is pretty erm… questionable. Seth / Scott = S. Howard / Helen = H. :clown_face:
So before I get to your first question, let me just recommend that you create a legacy Customer ID lookup table and reboot your ID schema with something more durable… then you have the old system as a lookup reference where legacy records can be associated with their old ID #s along side a newer model.
OK, but if you don’t want to do that, you could…
Have a:
{FirstName} Field in your Customer ID Table
A secondary Table for Rolling up FirstName and evaluating / incrementing,
Then grab the first character LEFT({FirstName},1)
And in a sequence of columns count the number of items with the same first letter… in each alphabetical column
In the Customer ID Table… that gives you your number and initial, so then you evaluate the firstname, look up the proper letter, use a switch statement for that you’ll be happy you did, and add the letter to the count of that letter.
If it sounds confusing, it probably is. But it can be automated.
Jun 07, 2020 01:59 AM
Thanks Scott and Greg, your solution was what I had already thought of on my own before I came here. But I actually want to automate this. I have tried automating this myself but failed because it would not let me update my primary key field. Do you have any script code that would do this?
UPDATE: I posted the code i used to automate this here