Figuring Out the Best Way to Link Records

[Christopher_Can]

I’m working on a project in my Airtable base and a bit stuck on how the approach it.

I have a database of people. Lets say there are 100 entries with 100 different people.

What I would like to do is make a new tab for a project tied to some of those 100 people. Let’s say 25.

So I would like to be able to select 25 of the 100 people and put them in this new tab linked back to the main database of 100. What I’m not sure of is what the best way to do this is without doing too much copy/pasting.

And then beyond that, I would like to be able to easily replicate the process for new projects on new tabs.

Any insight would be appreciated.

[Per_Carlen]

Is there only one project? What information will you store about the project?

[Christopher_Can]

There would be multiple projects and an ever increasing number.

What we’re capturing is answers to questions, like from a survey.

The main tab has basic demographic and contact info, along with a unique ID number for everyone.

So if we say an entry looks like this:

Name | Email | Phone | ID
John Smith | johnsmith@whatever.com | (111) 111-1111 | 1234

I’d like to have that info copied over easily to a specific project and linked back to the main tab, so that for instance we could just paste in the ID number or something and have the link pull in the phone number, email, name, etc, automatically.

[Kamille_Parks]

Sounds like you need two tables: People and Projects

Unless there’s something especially unique about your use case, you wouldn’t have one table per project just like how you don’t have one table per person.

Your People table would have a Link to Another Record field that connects to the Projects table. All you do is type in the name of each project a person is related to. This enables your Projects table to use Lookup fields to pull in any linked People records’ phone numbers, emails, etc.

Airtable Support

How to link records in Airtable

When you’re managing a workflow, there’s a lot of information to keep track of. Knowing which project connects to which client, or which venue you’re using for which event, is crucial to staying on...

Airtable Support

Lookup field overview

A lookup field allows you to pull record contents from one linked record into another linked record. As a reminder, linked relationships can be either within the same table or across two separate t...

[Per_Carlen]

Sounds like you should create a link between a People table and a Projects table then use Lookup fields.

Airtable Support

Lookup field overview

A lookup field allows you to pull record contents from one linked record into another linked record. As a reminder, linked relationships can be either within the same table or across two separate t...

[Christopher_Can]

So the only thing unique about what we’re doing is that each project has a different set of information we collect from people. We are in part using Airtable in the data collection step.

For example, if we have John Smith in Project A, we may ask him 10 survey questions and have all of those recorded in Project A. But then if we also have John Smith in Project B, there may be 10 totally different questions that are asked and recorded.

I’m less interested in knowing John Smith did Project A and Project B and more interested in making sure John Smith’s underlying information from the main tab he is in can be pulled in via a lookup field. But my main issue is creating that link in the first place in an easy to replicate manner without copy/pasting too much.

[Kamille_Parks]

Christopher_Cantrall:

For example, if we have John Smith in Project A, we may ask him 10 survey questions and have all of those recorded in Project A. But then if we also have John Smith in Project B, there may be 10 totally different questions that are asked and recorded.

In that case, one table per Project may be a good idea.

I see a few alternatives:

1. Use 3 tables: People, Projects, and Survey Responses. Survey Responses would be linked to both People and Projects and would have as many fields as you would unique questions. Projects would use Rollup or Lookup fields to bring in only the relevant survey responses. You would simply customize a new Form view when needed to collect information about different projects.
   • This method reduces the number of fields necessary in your one Projects table to make navigating that table easier.
2. Use a lot of tables: People, and one table for each Project. You say your list of projects is ever expanding, and if you have 10 or 15 or 20 different projects and each project asks 10 unique questions, having one Projects table with 200 fields where most fields will set empty could be an issue. If each project is its own table then each Project will only have the questions (fields) relevant to that project.
3. Use a lot of bases: One for People, one for each Project. Each Projects base would include a synced version of the original People table so you still would only need to update People records in one place. (About Airtable Sync) Using multiple bases allows you to focus on each project more easily since each project is apparently so different.

Christopher_Cantrall:

But my main issue is creating that link in the first place in an easy to replicate manner without copy/pasting too much.

No matter which method you go with, there is no copying or pasting involved since all recommended methods involve linking records together in one way or another. You just select which Project(s) to link to a particular Person and your Lookup or Rollup fields pull in relevant information automatically.