How to efficiently populate records into a linked column?

[GVSP]

Hi all,

I read a number of articles on the forum regarding the linking of fields across tables, but cannot figure out how to handle the following situation efficiently.

I have an Excel file containing data collected over a long period of time, about the various components and subcomponents found in a given system.

After importing the file into Airtable, I have two tables:

• Table 1 lists all components in the system
• Table 2 lists all subcomponents in the system and shows what component they’re associated with.

Note that each component can be associated with 0, 1, or multiple subcomponents. Subcomponents are associated with only one component.

I’m trying to add a “Subcomponents” column in Table 1 where, for each component, the cell would show all the associated subcomponents (if any).

At this point, I’ve added a “Subcomponents” column in Table 1 and linked it to Table 2. However, I’d like for Airtable to automatically populate each cell with all the right subcomponents. For the time being, I’ve been adding “Subcomponent” records into Table 1 manually, but it’s very time-consuming.

Is there a way to do this quickly in Airtable? Alternatively, have any of you been able to prepare an Excel file in such a way that the linking can be done more efficiently once the data is imported?

Hopefully, my description makes sense. Don’t hesitate to ask questions if needed and I look forward to getting unstuck.

[Joe_Hewes]

Not completely sure how your data is arranged but something that helped me last time with a similar task was swapping the index field out .

If you set the index field to be the main description of your record in table 2 - say “widget a” then you can just paste that in as text to your linked field in table 1. Airtable will link the record automatically as long as it matches exactly.

That way you can paste and link whole sheets at once. Once you’re all done linking up, you can set the index field to something better.

Hope that makes some sort of sense!

[GVSP]

Hi and thanks for your response.

I’m new to Airtable, so I want to make sure I understand the method you’re describing.

My two tables are initially as follows:

Table 1
Component name|Component notes
Component A|Some notes
Component B|Some notes
Component C|Some notes

Table 2
Subcomponent name|Subcomponent notes|Component name
Subcomponent 1|Some notes|Component A
Subcomponent 2|Some notes|Component A
Subcomponent 3|Some notes|Component B
Subcomponent 4|Some notes|Component C
Subcomponent 5|Some notes|Component C

I then add a “Subcomponents” linked field in Table 1 so it looks like this:

Table 1 (updated)
Component name|Component notes|Subcomponents
Component A|Some notes|
Component B|Some notes|
Component C|Some notes|

The new “Subcomponents” column is empty for now.

Then it sounds like I should copy the values from the “Subcomponent name” column in Table 2 and paste them in the new “Subcomponents” column in Table 1, so that Table 1 gets populated and looks like this:

Table 1
Component name|Component notes|Subcomponents
Component A|Some notes|Subcomponent 1, Subcomponent 2
Component B|Some notes|Subcomponent 3
Component C|Some notes|Subcomponent 4, Subcomponent 5

Is that right? What confuses me is that there are more rows in Table 2 than in Table 1, because components can have multiple subcomponents.

Hopefully, my description can help you point out what I’m missing here.

Thanks!

[Joe_Hewes · Accepted solution]

GVSP:

Table 1
Component name|Component notes
Component A|Some notes
Component B|Some notes
Component C|Some notes

Table 2
Subcomponent name|Subcomponent notes|Component name
Subcomponent 1|Some notes|Component A
Subcomponent 2|Some notes|Component A
Subcomponent 3|Some notes|Component B
Subcomponent 4|Some notes|Component C
Subcomponent 5|Some notes|Component C

If you already have your fields setup as above (assuming your first column is the index field) simply converting the Component name field to a linked field type referencing table 1 should be enough to link everything up.

Give it a try and let us know :slightly_smiling_face:

[GVSP]

Well, that was simple enough, thanks for clarifying!