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

[Nathaniel_Hamme]

I have had a similar problem generating a key field that can be kept unique. My solution also had to migrate from another database so I wasn’t starting at one. On top of all this, I am not using the software that I create and I wanted to ensure data integrity and no duplicates. My problem was I needed to replicate a curtain formula field that created a unique Work Order Number, but it was based off a snap shot of another field. My solution was to create a secondary table that created my key field for me. Then I used an automation that when I created a field in the key field generation table (Table A), it populated my Key field in the table where I needed it (Table B). I also disabled the create a record on Table B so the only way to create a record was through Table A.

My record ID has a couple unique properties:
It always has “TL” in front followed by year created and a unique ID associated with it. I elected for a 5 digit number essentially allowing me up to 99,999 records. Since I’m on a Pro-Plan, I’m only allowed 50,000 anyway. If I ever get close to going over, I dupe the base and start over in a new year. Below is the code I used in my work order generator.

This Creates my Offset from the old data base(you can make these numbers whatever 1597 came from how many records I imported into Table B: Field “Next Open W/O”

CONCATENATE((Autonumber + 1597 +109)& "")

This was the key field generator that autopopulates when you hit the add button. Using the auto number also ensures you never dupe a record even if you delete a record.

If(LEN({Next Open W/O}) = 1, "TL" & (INT(YEAR(CREATED_TIME()))-2000) & "0000" & {Next Open W/O},
If(LEN({Next Open W/O}) = 2, "TL" & (INT(YEAR(CREATED_TIME()))-2000) & "000" & {Next Open W/O},
If(LEN({Next Open W/O}) = 3, "TL" & (INT(YEAR(CREATED_TIME()))-2000) & "00" & {Next Open W/O},
If(LEN({Next Open W/O}) = 4, "TL" & (INT(YEAR(CREATED_TIME()))-2000) & "0" & {Next Open W/O},
If(LEN({Next Open W/O}) = 5, "TL" & (INT(YEAR(CREATED_TIME()))-2000) & {Next Open W/O})))))

Generated Key Field looks like this:

TL2101706

Airtable could have saved me a little time by adding the functionality of a CASE statement, it would have been much cleaner than a nested IF statement.