Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 24, 2022 11:02 PM
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.
Solved! Go to Solution.
Jul 25, 2022 10:02 PM
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}
.
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.
Jul 25, 2022 03:19 PM
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}
Jul 25, 2022 08:09 PM
Hi @Vivid-Squid
I tried what you said, but " id " still differs for duplicate “School Name”.
Jul 25, 2022 10:02 PM
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}
.
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.
Jul 27, 2022 07:01 PM
Thank you for the solution. :slightly_smiling_face: