Generating a unique primary key

You can use LEFT() function:

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

LEFT(Name,1) & Autonumber

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

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:

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

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.

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

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

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.

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.

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

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