Figuring Out the Best Way to Link Records

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

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.

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.

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

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.

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.

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.