Moving from MS Access to Airtable: Relationships

[Carey_H]

Hello,

I recently discovered Airtable and have been trying to figure out how to move our company’s Access database to Airtable. (I built the database, but I’m not IT: honestly I’m kind of a newbie. it’s a very simple database, just three tables: Survey Customers, Contact Dates, Honorariums)

I read that primary keys are not needed for Airtable, the “name” column has a hidden individual identifier, keeping records unique. However, when I import my tables, (“Customers”) and then add the table “Contact Dates” (to the same Base) I see that the Customers are not linked to their contact records. I know I can link them one at at time, but we have hundreds of customers and thousands of contact dates. (I will also have to link “Honorariums” with their customers as well.)

I tried importing a couple different ways. I just tried hiding the Access primary key before exporting as a CSV file, and let the customer’s emails act as Airtable’s “Name” field for both the “Customers” and “Contact Date” tables. The files imported fine, but the tables are not linked.

I know this may be a non-issue, because I can filter the Contact Dates table by email addresses. But what I would like to do is to somewhat recreate what I was building in Access: by clicking on the record of a specific customer, we could see everything related to them: their Contact Dates & Honorariums right there in their “profile.” But in Airtable, it seems like I have to go back-and-forth: to find the last time I contacted “Mrs.B,” I have to go to the Contact Dates table and search her by her email address. (I could add names as well and import that) I cannot click on Mrs.B in the Customer’s table and see her related contact dates unless I go and link them. (Which would be a huge time investment if I did this for every customer.)

Any advice? How can I import my tables from Access so that the relationships are maintained? Or perhaps there is an alternative set-up?

Thank you, any help is greatly appreciated!

[Mark_Morgan]

Hello @Carey_H

There are a couple of ways that I believe you can accomplish this that automatically populate these links and re-establish your relationships. I did something similar by following the instructions contained in two recent Airtable blog articles outlining a similar situation as yours, just a different software:

Part I explains how to import the data to create the first table and “fix” the column fields to the correct formats that use the power of Airtable.

Part 2 (which is where your solution is contained) explains how to add subsequent tables and re-link everything. By simply changing the format type of the fields to link to the other table, Airtable is clever enough that it automatically will do most of the work for you (with certain conditions that are outlined).

You might also find this article relevant and helpful as well if you need to add additional tables that immediately are linked to certain fields from which you can build out from there by pasting data in or entering additional column data manually.

Airtable is so thoughtfully designed! Hope this information helps.

[Chester_McLaugh]

@Mark_Morgan had some great advice which is more thorough than what I have to add.

The way I’ve done it in the past is this:

1. Create a new base by importing a CSV
2. Create a new “first column” in that table and set it to “Formula” that simply references whatever the field is in that table that has the unique identifier… usually, I’ve used email address, but if you actually have a unique ID field that’s way better.
3. Create a second table in that base and create the fields you need.
4. Make sure one of those fields is a related field back to the first table.
5. Copy/paste the data for table 2 into Airtable and as the text in your related field matches a record from table1 it will make it a link.
6. Repeat for other tables.
7. You can now go back into Table1 and change the “first column” to reference any field (or combination of fields) you would like. I find that using a Formula Field for the “first column” allows me to change the exact text of the relationship field frequently depending on my needs. For example, since Airtable auto-completes when you’re doing text entry based on the linked field, sometimes it’s more convenient for a set of tasks to use the email address, while other times I want the full name… and then I can always do both with the Formula, "{Name} & “: " & {Email}” … it’s a very flexible way to manage linked tables through a variety of fields rather than just one.

Here’s a brief example using one of the default templates. I created a new “second column” named “Proper Name” and copy/pasted column one into column two. I then changed column one into a formula that concatenates the Proper Name and the Link into one field.

Then when I’m in a related table, I can use the auto complete to lookup the related record by a fragment of the URL, not just the name.

[Carey_H]

Thank you very much! I finally had some time to try it out, and it worked! It was a little hard because I’m not using FileMaker and I had to export Excel spreadsheets from Access, then change their file type, etc. I also exported query views for Step 2, so I could be sure to include the emails column. (Which is what I used to link the two tables in Airtable)

Thanks again!

[Damon_Barnes]

Hey Mark. When I attempt to do this it just clears out the data from the existing column. Any thoughts?

[Mark_Morgan]

Hello @Damon_Barnes: Can you walk me through what steps you are following? Are you creating a new base or trying to add tables to an existing base? Thanks.

[Damon_Barnes]

I am creating a new base. Taking information from Excel and putting it into a table. Then I use Zapier to push the data into two different tables. Both tables have a column named “LocName”. I am trying to get Airtable to link the two based on that name.

Though, I think I may have gotten to the root of the problem on my own. I didn’t have LocName in the table I’m trying to look too as the “primary key”.

Now it seems to have worked once I changed that.

[Mark_Morgan]

@Damon_Barnes: Yes, that was your issue. Both tables need to be linked by a common column. Make sure in your first table that the format of the field is set to “Link to…” and then choose the second table name. Airtable does the rest.