Generating a unique primary key

[Scott_Barman]

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

[Elias_Gomez_Sai]

Scott_Barman:

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

You can use LEFT() function:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

Scott_Barman:

1. Followed by a four-digit sequence number starting with 0001.

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

LEFT(Name,1) & Autonumber

[Rosy_Baker]

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

[Elias_Gomez_Sai]

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:

Formatting an autonumber [SOLVED] Ask the community

You need to count the ID characters and fill with zeros till 6. 'LS' & REPT(0,6-LEN(CONCATENATE({ID}))) & {ID} [image]

[Scott_Barman]

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

[Julian_Kirkness]

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.

[Scott_Barman]

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

[M_k]

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

[Greg_Stein]

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.

[Hamish_Ahern]

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

Creating a custom incrementing Primary Key without using an autonumber Apps

I have a table called “Settings” and a field called “Next Number” and a field called “Format” Next Number is 101000 Format is ‘TRnnnnnn’ How do I write a script that when I press run, it updates all blank Primary Keys on my view, with the new numbers in sequence, and updates the Next Number setting, ready for the next time I run the auto sequence script. I have tried using a formula, but that doesn’t work because I need to use the next number from the other table, not from the same table. I…