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.
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.
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:
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:
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.