Generating a unique primary key


#1

I would like to generate a unique primary key for each record of a table/database based on the following criteria:

  1. Use the first letter of the person’s first name. For example, a record with the first name of “Scott” will have the key beginning with an “S.”
  2. Followed by a four-digit sequence number starting with 0001.

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


#2

You can use LEFT() function:

You need an Autonumber field and concatenate it with the letter. Would be something like:

LEFT(Name,1) & Autonumber


#3

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


#4

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:


#5

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


#6

Hi Scott

I don’t think there is within Airtable alone - but you could it with Integromat or Zapier I think…

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

  2. In the Zap / Integromat process, trigger based on a new record.

  3. In the next step search for a record in your Letters table for the record based on the first letter of the name.

  4. Update the Key field in your original table with the value {Initial Letter} & {Next ID}.

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


#7

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


#8

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