Please excuse the long explanation..
I have a spreadsheet showing some factories producing a number of products. For the sake of the example, let’s assume that the factories are the primary records and the products that they produce need to be split out in to a linked table.
So the source spreadsheet looks like this:
| Factory | City | Country | Product |
|---|---|---|---|
| Acme Tools | Denver | USA | Spanners |
| Acme Tools | Denver | USA | Screwdrivers |
| Western Hardware | Paris | France | Spanners |
| China Supply | Shanghai | China | Hammers |
| China Supply | Shanghai | China | Screwdrivers |
I can create and import this spreadsheet in as a new table called Factory.
But I don’t want duplicates in the Factory table. I want to have a Products table that would look like this.
| Product | Factory (linked) |
|---|---|
| Spanners | Acme Tools Western Hardware |
| Screwdrivers | Acme Tools China Supply |
| Spanners | Western Hardware |
| Hammers | China Supply |
This obviously works fine, and the Factory table would only have 1 row per factory
| Factory | City | Country | Product (linked) |
|---|---|---|---|
| Acme Tools | Denver | USA | Spanners Screwdrivers |
| Western Hardware | Paris | France | Spanners |
| China Supply | Shanghai | China | Hammers Screwdrivers |
All of that I can do. But how do I create that structure for the first and subsequent imports? I will have several thousand lines of data and I don’t want to have to match the linked fields manually as part of the import process.