Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Unique Autonumber across all table

Topic Labels: Formulas
Solved
Jump to Solution
14147 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Guillermo_Monge
4 - Data Explorer
4 - Data Explorer

Is possible to have a unique id number from autonumber function across all tables?,
For example:
If I create a new record on table A, that record must be 1; but if I create another new record on table B must be 2

1 Solution

Accepted Solutions
Zollie
10 - Mercury
10 - Mercury

Not as you’re describing. Autonumber’s functionality is frustratingly limited. But if you’d like unique identifiers associated with each record across any number of tables, you could do one of the following:

Use 'Record ID’
Each record already has a unique identifier. It’s a hash generated by Airtable’s backend tools. You can see it in a field by creating a formula field and entering RECORD_ID() in it. I’m not sure if it’s possible for a hash to be reused across multiple tables, but I’d imagine it’s unlikely. If you’re worried see the next section.

Create a Custom ID
If you’d like more control over the ID name, you could add words onto an autonumber. For example, the name of each table. That way even if multiple tables have the same number, their respective names will ensure a unique value. It might look like this:

First Table

Autonumber (autonumber)
1
2
3

Custom Record ID (formula)
FirstTable1
FirstTable2
FirstTable3

Formula:
"FirstTable"&{Autonumber}

See Solution in Thread

2 Replies 2
Zollie
10 - Mercury
10 - Mercury

Not as you’re describing. Autonumber’s functionality is frustratingly limited. But if you’d like unique identifiers associated with each record across any number of tables, you could do one of the following:

Use 'Record ID’
Each record already has a unique identifier. It’s a hash generated by Airtable’s backend tools. You can see it in a field by creating a formula field and entering RECORD_ID() in it. I’m not sure if it’s possible for a hash to be reused across multiple tables, but I’d imagine it’s unlikely. If you’re worried see the next section.

Create a Custom ID
If you’d like more control over the ID name, you could add words onto an autonumber. For example, the name of each table. That way even if multiple tables have the same number, their respective names will ensure a unique value. It might look like this:

First Table

Autonumber (autonumber)
1
2
3

Custom Record ID (formula)
FirstTable1
FirstTable2
FirstTable3

Formula:
"FirstTable"&{Autonumber}

Michael_Crinni1
5 - Automation Enthusiast
5 - Automation Enthusiast

I thought I'd add my solution to this. It has the same issue in terms of the possibility of cross table 'non-uniqueness' but has some control which I think makes it nice.

UPPER(RIGHT(REGEX_REPLACE(RECORD_ID(),"[0-9]+",""),1) & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(RIGHT("00000000000"&ID,3),""),"0","a"),"1","b"),"2","c"),"3","d"),"4","e"),"5","f"),"6","g"),"7","h"),"8","j"),"9","k"))

ID is your autonumbered column as we know this will always be unique.

The number at the end of this section is used to add extra letters from the Record ID - increase it to reveal more of the Record ID

UPPER(RIGHT(REGEX_REPLACE(RECORD_ID(),"[0-9]+",""),1)

The number at the end of this section adds more padded zeros to your ID - increase it to something suitable for the maximum number of codes you think you'll ever need (E.g. 3 would give you a max of 999 guaranteed unique)

RIGHT("0000000000000"&ID,3)

The beauty of this this it produces a fairly well obfuscated code from unique values that your database automatically generates.

For example a Record ID equal to rectEF3ZpIiu7o802 with an autonumber value of 30 would produce the code "OADA"

I hope it's helpful to somebody.