Help

How to create unique id for a field having duplicate values

Topic Labels: Automations
Solved
Jump to Solution
251 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Actually, I have a field “School Name” that can contain duplicate values. There is a condition that the id should be the same for duplicate values.
So how can I do this without taking the help of another table? if there is some script, please share as I am new to Airtable.
Screenshot 2022-07-25 at 11.29.03 AM

1 Solution

Accepted Solutions

Welcome to the community, @Vivek! :grinning_face_with_big_eyes: You’re misinterpreting what @Vivid-Squid was saying. The RECORD_ID() function doesn’t accept any input (and if given input, it doesn’t do anything with it, as you can see). The recommendation was to make the formula only this, getting rid of the record ID reference completely:

{School Name}

The downside is that it’s not really an ID. It’s just echoing the name of the school.

Another approach to this would be to create a completely separate table for schools; I’ll refer to this as the [Schools] table going forward. In this [Schools] table, each school would have its own record, and you could add a formula field that outputs the record ID, naming this field something like {School ID}.

Screen Shot 2022-07-25 at 10.03.54 PM

In your existing table, the {School Name} field would become a link field, allowing you to pick a school from the [Schools] table. With that done, you could add a lookup field to retrieve the ID for the linked school. Now when you link a record to school ABC, it will always show the same ID.

Even with that, though, I don’t recommend using only that looked-up school ID for the primary field of your main table. Even though Airtable creates its own ID under the hood—the record ID that you’re seeing via these formulas—it’s still a wise choice to make the primary field unique to avoid confusion as you add more records to your table. You can certainly use that looked-up ID as part of a formula in the main table’s primary field, but add more things to it so that each record is still unique.

See Solution in Thread

4 Replies 4

Hi @Vivek,
Unlike some databases the ‘KeyID’ or left most column (field) in airtable can be duplicate. Airtable automatically assigns a Record_ID for each row (record) as you can see with your id formula. This will always be unique.

If you want the id field to be reflective of the School Name field and duplicate when they are the same, just change your id formula to {School Name}

Hi @Vivid-Squid
I tried what you said, but " id " still differs for duplicate “School Name”.
Screenshot 2022-07-26 at 8.35.28 AM
Screenshot 2022-07-26 at 8.34.10 AM

Welcome to the community, @Vivek! :grinning_face_with_big_eyes: You’re misinterpreting what @Vivid-Squid was saying. The RECORD_ID() function doesn’t accept any input (and if given input, it doesn’t do anything with it, as you can see). The recommendation was to make the formula only this, getting rid of the record ID reference completely:

{School Name}

The downside is that it’s not really an ID. It’s just echoing the name of the school.

Another approach to this would be to create a completely separate table for schools; I’ll refer to this as the [Schools] table going forward. In this [Schools] table, each school would have its own record, and you could add a formula field that outputs the record ID, naming this field something like {School ID}.

Screen Shot 2022-07-25 at 10.03.54 PM

In your existing table, the {School Name} field would become a link field, allowing you to pick a school from the [Schools] table. With that done, you could add a lookup field to retrieve the ID for the linked school. Now when you link a record to school ABC, it will always show the same ID.

Even with that, though, I don’t recommend using only that looked-up school ID for the primary field of your main table. Even though Airtable creates its own ID under the hood—the record ID that you’re seeing via these formulas—it’s still a wise choice to make the primary field unique to avoid confusion as you add more records to your table. You can certainly use that looked-up ID as part of a formula in the main table’s primary field, but add more things to it so that each record is still unique.

Thank you for the solution. :slightly_smiling_face: