@Katrina_Reiniers-Jac:
Yes, this is very possible to do with Airtable. While it can be performed from any level of account, if I understand your use case, I would recommend it be done using a Pro level account, as this will provide access to the CSV Import Block, which will greatly reduce your downstream effort.
The thing to keep in mind is that Airtable’s plain vanilla CSV import was designed as a base development tool, rather than a production tool for ongoing base maintenance and management. Accordingly, tables created using the ‘Import a spreadsheet’ option from the table creation menu are exceedingly simple, consisting entirely of single-line text data fields. Since your desired work flow includes the ability to perform analysis and create views of newly imported tables that assume the existence of formula and referential (that is, based on linked-records) fields, keep in mind that all such non-data fields will need to be added to the table manually following the import process.
Since your description makes it appear the various collection tables you wish to import over time all have the same or similar data layouts, I recommend you create a standard table-to-be-imported template that contains the necessary formula, lookup, and rollup field configurations. Then, immediately prior to a CSV upload, you would duplicate and rename this template table and import your data into it. (This can be done either through the CSV Import Block or by importing the CSV into a ‘scratch’ table and then cutting-and-pasting the data from it into your new duplicate of the template table. (Don’t worry: I’ll touch on all of these shortly.)
What makes all of this work is the way Airtable handles values entered into linked-record fields:¹ Namely, if a record exists in the linked table whose primary field matches the value entered, a link is established to it from the current record. If no such record exists, a new record is created with a primary field that equals the value, with the current record linked to the newly created target record.
Perhaps the best way to illustrate this is by creating a small demo base and a couple of dummy .CSV files and stepping through a few scenarios.
Begin by creating your [Photo] table. This corresponds to your own master table. In a live base each [Photo] record would contain information about the image itself, the photographer, owner, cost, and so on — but for our purposes it needs only to contain a single, primary field, {Photo #}. Define this single-field table and create 5 records in it with {Photo #}s of 100, 101, 102, 103, and 105.
Next, define an [Import Template] table. Ignore the primary field for now and proceed to the second field; configure it as a linked-record field with the target table being [Photo] and name it ‘Link2Photo’. The next field is also a linked-record field. When asked for the table to link to, select ‘Create a new table,’ and name this new table ‘Photographer’; name the field ‘Link2Photog’. Return to the primary field and configure it as a formula field with the formula
'Collection - '&{Link2Photo}&' - '&{Link2Photog}
Finally, create the following three.CSV files:
Import1.csv
Key,Link2Photo,Link2Photog
,100,Photog1
,101,Photog2
,102,Photog3
,103,Photog1
,105,Photog4
Import2.csv
Link2Photo,Link2Photog
102,Photog3
103,Photog4
104,Photog7
104,Photog8
101,Photog1
Import3.csv
Key,Link2Photo,Link2Photog
,106,Photog9
,107,Photog2
,108,Photog4
,102,Photog4
,103,Photog4
Now, explore the following scenarios:
Standard Airtable import
- Click the ‘
+’ to the right of the last-defined table in your base to create a new table; select ‘Import a spreadsheet’.
- Navigate to wherever you stored
Import1,csv, select the file, and upload it. Airtable creates a new table containing 5 records, each with 3 single-line text fields.
- Right-click on
{Key} and change it from text to autonumber.
- Right-click on
{Link2Photo} and change it from text to linked-record; set the target table to be [Photo].
- Right-click on
{Link2Photog] and change it from text to linked-record; set the target table to be [Photographer].
Now, examine the base. You will find 5 records have been added to [Photo], with a reciprocal link established between them and your new [Imported table]. You will also find 4 records have been created in [Photographer], with one record linked to two [Imported table] links. (Ordinarily, you would link [Photo] to [Photographer], with your collection tables linking only to [Photo]; I included this second link to demonstrate how multiple links can be created during an import and how multiple records can be linked to a single target record.)
Import followed by copy-and-paste
- Begin by duplicating the
[Import Template] table. Call this new table [Import2], and do not duplicate records.
- Create a new table by importing
Import2.csv as outlined in Steps 1 and 2 of the previous scenario.
- Do not reconfigure fields in your newly imported table. Instead, select the cell for
{Link2Photo} in Row 1.
- Next, while holding down the shift key, select the cell for
{Link2Photog} in Row 5. ‘10 cells selected’ should appear in the lower left of your screen.
- Press
Ctrl-C to copy the marked cells. ‘10 cells copied’ should appear in the lower left.
- Go to the
[Import2] table you recently created. Select the ‘+’ to the left of the first record area to create a new record.
- Select the cell for
{Link2Photo} in Row 1 of the table, and press Ctrl-V to paste the copied values. Airtable will ask if you want to add 4 records to support the paste; answer ‘yes’.
- ‘Pasting…’ followed by ‘Paste complete’ appears in the lower left. Five records are added to the table, with
{Link2Photo} and {Link2Photog} populated.
At first glance, this appears to be nothing more than a more labor-intensive method of accomplishing what was already handled by Scenario 1 — until you look at the primary field. While of little value in and of itself, its existence illustrates calculated fields can be preserved and replicated in duplicated tables populated with imported data. Again, while this example is trivial, you can imagine how time-consuming it could be to add a dozen or two calculated fields to a table imported according to the first scenario. While the copy-and-paste step introduces a possibility for error — pasting should be performed only in a dedicated view, where data and calculated fields are never commingled and data field order never changes — it does provide a way for Free and Premium users to add imported data to existing tables.
Import using the CSV Import Block
This third scenario is an option as long as you are on a Pro account, are still in the Pro evaluation period of your Free account, or, frankly, feel like creating a new account using an alternate email in order to make use of its Pro evaluation period.
- Begin by duplicating the
[Import Template] table. Call this new table [Import3], and do not duplicate records.
- With
[Import3] active, select the ‘Blocks’ header to the right of your screen to open the Blocks sidebar. Select ‘+ Add a Block’ and choose ‘CSV Import Block’ from the list of possible Blocks to add.
- Select ‘Or click to choose a file…’ and browse to
Import3.csv. Open it for importation.
- Airtable scans the file and opens the ‘Set up field mappings’ screen.
- Make sure ‘Table’ is set to ‘Import3’.
- Toggle on ‘First row of CSV file is headers’.
- Under ‘Field mappings’, make sure
{Link2Photo} maps to the ‘Link2Photo’ column and {Link2Photog} maps to ‘Link2Photog’.
- Airtable indicates how many records will be created. (Because the primary field is a calculated field, all records will be shown as ‘Unnamed record’.) Select ‘Save records.’
This scenario is a ;less labor-intensive, more foolproof method of importing CSV data into an existing table. Once again, calculated fields are preserved, preventing you from having to add them manually to a newly imported table.
Each of these scenarios shows how you can use link-records and imported CSV files to achieve the one-to-many master-to-collections architecture you described. However, I’d like to suggest a solution that’s something of a hybrid of your original design and @Justin_Barrett’s suggestion: Rather than a [Photo] table and multiple collection tables, define [Photo] and [Collection]. The latter table would have the same layout as your [Import Template] table, plus one additional field: A single-select field called {Collection}. Now, rather than create a new table for, say, [Wedding] photos, you would instead additively import a CSV file of wedding-related photos with the value of {Collection} set to ‘Wedding’. Such a design would allow you to query how many collections Photo # 010203 was in simply by performing a lookup or rollup on {Photo::Link2Collection} (that is, the {Link2Collection} field in the [Photo] table). Your original design, on the other hand, would require you to create an intermediate field in [Photo] that aggregated the values of {Link2Wedding}, {Link2Puppies}, {Link2Snowstorm}, and so on, as each collection table would have an independent link to [Photo].
(I didn’t really address directly how you would handle {Owner}, {Photographer}, {Cost}, and the like, but I hope it should be evident after examining the relationships among [Photo], [Photographer], and the various import tables. for instance, in [Photo] you could define a lookup field called {Imp1 Photog} that followed the link to [Import1] to retrieve the values of {Photographer}. Although admittedly bassackwards in the context of your finished base — you would want to link {Owner}, {Photographer}, and other such attributes of an individual photograph from [Photo] records, making them accessible from the import tables, and not, as I have done here, the other way around — this provides an example of how you can share such data among tables at various levels of your data hierarchy.)
I hope this has given you an idea of how to approach your base. If anything is unclear — and I must confess to having written this reply in multiple installments over a period of several hours — drop me a note here and I’ll give it another try.
- ‘Entered’ in this instance has a broad definition: It can mean literally entered, as in being copy-and-pasted into the linked-record field or inserted into it by an integration service such as Zapier or Integromat; it can mean having been imported into a linked-record field by the CSV Import Block; or it can mean having been entered as the result of a field-type conversion, as when a single-line text field resulting from a CSV import is converted to a linked-record field.
