How to create a unique id Using a formula in the primary field

@faiberts: Welcome to the Airtable community. I think I understand what you’re trying to do, but I’m not sure. Forgive me if I got it wrong. But if I’m understanding you correctly, this ought to do the trick for you.

.

Getting unique value for each record/row

If you’re just trying to come up with a unique ID value for every record in a table, that’s not too hard. Try using the RECORD_ID() function as the formula for a Formula field. That should do the trick. If you would like different type of unique value, let me know and I can suggest other formulas that should do the trick but will require a little more work.

Now one of Airtable’s special features is that it makes it unnecessary for users to worry about primary and foreign keys. In Airtable, if you want to link line items for the Acme account to the Acme record in the Accounts table, you just click on the linked record field in the LineItems table and select “Acme”. Airtable takes care of everything else. Of course the key values are there but you don’t have to see them.

.

How to get and use Airtable’s hidden key values in related tables

But sometimes, some of us do need to see those values, because we need to take our relational data outside of Airtable and for that, we need those primary and foreign key values, so we can relate records properly in another system. Once again this is easy. Remember, under the hood in Airtable, the key values are there. So all you need to do is reveal them, using the function I mentioned above. So, say you have two tables: Projects and Tasks. Relationship between them is one-to-many (one Project can have many tasks, but a task can be linked to only one project). Do this:

1. In Tasks, create a field/column named Record_ID. Make it a formula field and use the formula I mentioned above: RECORD_ID(). Be sure to leave the space between the parentheses blank.


2. Go over to Projects and do the very same thing: Create a formula-type field named Record_ID using the RECORD_ID() formula.


3. Finally, go back to Tasks and create a new column that could be called “Project_Record_ID”. This should be a lookup column that gets the value of the Record_ID column over in the Projects table. Like this:

When you save that, you’ll see that every row in the Tasks table has a distinct value in the table’s own Record_ID column, but if you filter the tasks on a single project, you’ll see they all have the same value in Project_Record_ID:

.

Using these values outside Airtable

Finally, if you need to take your data outside Airtable, into another relational database management system (like, say, FileMaker or AppSheet), just export each table to CSV. Make sure when you export Projects (sticking with example above) you export the Record_ID column; and when you export Tasks, be sure to export both Record_ID and Project_Record_ID.

And Bob’s your uncle!

William

@faiberts: Welcome to the Airtable community. I think I understand what you’re trying to do, but I’m not sure. Forgive me if I got it wrong. But if I’m understanding you correctly, this ought to do the trick for you.

.

Getting unique value for each record/row

If you’re just trying to come up with a unique ID value for every record in a table, that’s not too hard. Try using the RECORD_ID() function as the formula for a Formula field. That should do the trick. If you would like different type of unique value, let me know and I can suggest other formulas that should do the trick but will require a little more work.

Now one of Airtable’s special features is that it makes it unnecessary for users to worry about primary and foreign keys. In Airtable, if you want to link line items for the Acme account to the Acme record in the Accounts table, you just click on the linked record field in the LineItems table and select “Acme”. Airtable takes care of everything else. Of course the key values are there but you don’t have to see them.

.

How to get and use Airtable’s hidden key values in related tables

But sometimes, some of us do need to see those values, because we need to take our relational data outside of Airtable and for that, we need those primary and foreign key values, so we can relate records properly in another system. Once again this is easy. Remember, under the hood in Airtable, the key values are there. So all you need to do is reveal them, using the function I mentioned above. So, say you have two tables: Projects and Tasks. Relationship between them is one-to-many (one Project can have many tasks, but a task can be linked to only one project). Do this:

1. In Tasks, create a field/column named Record_ID. Make it a formula field and use the formula I mentioned above: RECORD_ID(). Be sure to leave the space between the parentheses blank.


2. Go over to Projects and do the very same thing: Create a formula-type field named Record_ID using the RECORD_ID() formula.


3. Finally, go back to Tasks and create a new column that could be called “Project_Record_ID”. This should be a lookup column that gets the value of the Record_ID column over in the Projects table. Like this:

When you save that, you’ll see that every row in the Tasks table has a distinct value in the table’s own Record_ID column, but if you filter the tasks on a single project, you’ll see they all have the same value in Project_Record_ID:

.

Using these values outside Airtable

Finally, if you need to take your data outside Airtable, into another relational database management system (like, say, FileMaker or AppSheet), just export each table to CSV. Make sure when you export Projects (sticking with example above) you export the Record_ID column; and when you export Tasks, be sure to export both Record_ID and Project_Record_ID.

And Bob’s your uncle!

William

Okay, I can’t help myself. One last comment.

I strongly urge you not to use email addresses as key values. If you absolutely have to, then you have to. But you probably–almost certainly–don’t have to, even if you think you do, so don’t.

A key value in a relational database should always be

• unique within its table


• immutable

That second requirement–immutability–is as important as the first. To make sure that ID values are immutable they need to be persistent and meaningless.

• 
  Persistent means that the value originally assigned to a row (record) stays with it no matter where it goes, when it goes in the database, out of the database, back into the database.


• 
  Immutable means that a key value must have absolutely no meaning other than its use as key value.

If you use email addresses or government ID values as keys, and you discover that you entered somebody’s government ID wrong or you misspelled their email, or the person changes their email address, well, you’ve now got an ugly problem on your hands.

William

@WilliamPorterTech thanks for you help.

Thanks for your well documented help, it works perfectly but then I saw what you said about updating the variant registry (tasks following the example you shared with me).

the idea in my case is to have a table of only clients and for each client X amount of services (relation of one to several) in the table of services those don’t change; that’s why the idea of creating a table that calls transactions (serious relation of a service to several transactions; from one to several). This table is where updates (prices, status, payments, etc) occur.

If I share your recommendation of not using email, I imagine that you can have a column with some of them working to generate a code based on a format (I don’t know if it’s possible in airtable, through formulas).

I have reviewed crm as a basic zoho and it is too much for just what I need and I do not want to invest time developing something I am with little time to get this done.

The idea I have is to capture the data through a typeforms form and through zapier connect it to airtable to create or update the records.

Thanks for your kind help

@faiberts: The short answer to your question is Yes. I’m not entirely sure what you mean by “code”, but yes, you can create a formula column in Airtable and populate it using a formula that will reliably generate a unique value from other values.

Beyond that I’m not able to suggest the design of your system for you, in any detail. But I can address a couple of general questions, and perhaps these comments will suggest to you a way to solve your problem. I apologize in advance for the length of the response below. It’s an important topic, might be useful to somebody else using Airtable, and I had some selfish reasons for writing it.

Good luck,

William

p.s. If you’d like help with the formula, feel free to send me a direct message.

.

“Match columns” vs Key columns

In relational database systems, data in different tables is always linked when a value in a column in one table precisely matches a corresponding value in a column in the other table. In some platforms these are called “match fields” (“match columns”). Most of the time database developers like me call these “key” fields. But there may be a useful distinction between “match columns” and “key columns”.

As I said, keys should be unique (within their table) and immutable; and I went on to say that one of the ways you make a key value immutable is by making it meaningless outside the database. Properly generated keys tend to look like this: “UmNygMQcY1twgeD9r”.

The problem is, precisely because good key values are meaningless to us humans, it’s rather awkward for us to use them. We don’t like to look at values like UmNygMQcY1twgeD9r, we find it extremely hard to pick UmNygMQcY1twgeD9r out of a list that also includes rMqkwlLC5eMx77Hsp etc. Your eyes are probably getting blurry reading this paragraph.

This is why Airtable hides these values from us. Instead, in Airtable we get what I would call match fields. When a new order comes in, we switch to a view of the Orders table, click into the Accounts column (which is a Link to Another Table column) and Airtable shows us a list of known accounts, by name, so it’s easy for us to pick the right one. Behind the scenes, of course, meaningless key values are being used to link the order to the account, but we don’t see any of that.

So the match field is the meaningful token that allows us human beings to use the base; but invisible, meaningless key values are used to make the actual links between tables. And because that’s the case, if we change the name of the Acme account from Acme Inc to Acme Industries LLP, none of the links between Acme and its orders get broken. The invisible, meaningless IDs did not change.

.

Match values not always unique!

Now, here’s where it gets tricky. Primary keys (i.e. the key values in the One tables) must be unique. But the column we most naturally use as a match column sometimes will include duplicates, or more precisely, not “duplicates” but rather, values that are not unique. (This is an important distinction. The result of 2 + 5 is the same as the result of 4 + 3, but the first 7 is not a “duplicate” of the second 7. They’re just both 7s.)

I build systems in which the top level table in the hierarchy is almost always a list of people–typically, hundreds or thousands of people. I see a lot of this sort of thing:

Candace Moreno

Carlos Garcia

Carlos Garcia

Catherine Lawrence

Sometimes we can determine that the first Carlos Garcia is actually Carlos Garcia, Sr., and the second one is Carlos Garcia, Jr. That helps. But we can’t always do this. Or perhaps we have middle names–but often we don’t. Sometimes you just have two or more records with identical names.

Once we are able to figure out that it’s the first Carlos Garcia that we want, then the link between him and (say) the rows in the Orders table that belong to him will be made using the invisible key values, and everything is fine from that point on. But Which Carlos Garcia? is a question that occurs a lot, in Airtable and in other database systems as well. It’s a big deal for example when you Google a name like “John Adams”, or when you look for a name in the huge database known as Wikipedia. See for example this page which attempts to “disambiguate” the name John Adams.

So we tend to create meaningful distinguishers to help us keep things apart. In my systems I’ll often write a calc that puts the first name, middle name, last name, name suffix and birth date into a single column. So users can see something like this:

Carlos M Garcia (4/1/1980)

Carlos Garcia, Sr. (8/18/1968)

These are easy calcs to write. In Airtable, we’ll often write a calc like this and use it as the primary column for the table. If you’re dealing with a table where every record = a person, and the name and birthdate are not reliably distinct, you might have to add some other column like Zip Code or drivers license number or whatever you’ve got. Then when you link to that person’s record from a Many table, in Airtable the “Link to Another Table” will use that calculated column as the match column.

I suspect this may be exactly what you need to do in your system. Create a primary column that is a formula field containing several values that give you a unique result.

.

But the key values are always the most important thing!

Still, it’s always the hidden key values that do the real linking. So if your base includes, say, three or four or five tables (not just two), and you’re implementing the suggestions I gave you earlier about using the RECORD_ID() function to get primary key values out into the open, then you just need to do the same thing for all the tables in your base. In other words, in every table, a field named Record_ID that gets that row’s primary key value, and also in every Many or “Child” table, a field named something like Parent_Record_ID that looks up the Record_ID value from the linked parent record.

Then if you export your multi-table base’s data to another system, you make sure to export those key columns as well.

.

Too much work?

Yeah, it’s a little bit of work. But you don’t have to worry about the key columns at all if your system is never going to leave Airtable. And while we often do have to create those “disambiguated” primary column calculations, they not too hard.

The truth is, Airtable isn’t really all that easy, once you go beyond grocery lists. But the same can be said of every database system I know, and I’m familar with quite a few.