Importing Related Data

Sometimes your data is supplied in one flat file - eg a spreadsheet or csv file - but it needs to be stored in CatBase in two related tables because there is repeating data. How do you import the data from one flat file into two related tables?

For example, let's look at a common scenario: Products and Parts. You have a number of Products and each Product has a number of different options,, such as different sizes, colours, box sizes etc. The number of options varies from product to product, and when you publish the data you want to show each product with its related parts shown in a table - something like this perhaps:

This product (Star Delta) has four Parts to choose from.

In order to achieve this, you will need to import the product details (product name, picture name, description) into the Product table and each different part details into a related record in the Parts table: the Part Number, Description, Price, etc.

The most important thing you need to know is: What is it that links the Parts to their Products? In your import data, you will need to find a unique key for each Product. This could be a product code, ID number, or perhaps the product name. It must be UNIQUE to each product - and that means unique not only in the import file but also in your CatBase database. for example, if your import file contains a product with the record number 1234, you must be sure that you don't already have a product with that record number in your CatBase database - unless you want to add the imported data to the existing record. You will import this unique key into either the RelatedRecordName or RelatedRecordNumber field in the Parts table. So, your key field must be either the product name or the product record number.

In this Tutorial we assume that you are already familiar with the CatBase Import Wizard. If not, please review the Importing tutorial first.

Overview of the Process

These are the steps you'll need to take to import the related data :

Identify the key field for the products (product code, ID number, product name, etc.)
Import the file into the Product table, ignoring the fields that belong to the Parts
Import the file into the Parts table, ignoring the fields that belong to the Products, except for the key field

Let's take a look at some sample data in a spreadsheet:

Here we have details on two products (HB Pencils and Gel Pens), which each have a different number of options (or Parts).

1. Identify the key field for the products

In our example, we could use either the product ID or the product name field as the key field. However, product ID would be the best choice, as it is sure to be unique; product name could possibly be duplicated (especially if you're including products from different suppliers). So we'll go with product ID for this example.

If your data is in a spreadsheet, you'll need to save it as a delimited text file first (see the tutorial on importing spreadsheet data for more details).

2. Import the file into the Product table

Create a new Import Style
Select the Product table in the Import into Table popup (your tables might be named differently, of course)
Click on the Select button
Locate your import file and double-click on it
Match up the import fields to your CatBase fields. Important points to keep in mind:
1. Make sure that you match your key field to the correct field in CatBase: if it's a unique NUMBER, import it into the RecordNumber field. If it's a unique NAME, import it into the table's key field (Product Name, or whatever you have named it)
2. To ignore the fields that belong in the Parts table, select Ignore this field in the Import Field dialogue:

The Sample Data area would look like this in our example:

The columns headed Extra Field will be ignored when you import the file.

We need to tell CatBase that we don't want duplicated Product records.

Go to the Updates tab.
Select the Update Existing Records radio button
Choose your key field in the in the Primary Key Field popup menu (Record Number, in our example)
Go back to the Main tab
Click on Import Now to import the product records
When the data has been imported, you should have one Product record for each unique product

3. Import the data into the Parts table

Now we're ready to import the Parts and link them to their products.

Save your Products import style and create a new Import Style
Choose Parts from the Import into table popup
Click on the Select button and choose your import file
This time you ignore the fields that belong to the Product - except for the Key field which you identified in Step One. If the Key Field is a record number, import it into the RelatedRecordNumber field; if it's a record name (eg a product name), import it into the RelatedRecordName field.
This is how our example data would look after it had been mapped to the CatBase fields:
Click Import Now to import the parts
When it's finished, you should see a list of the Parts.

Check the result!

On the CatBase main window, make a list of all the Product records and open one of them. If you have the Parts related records displayed on your data entry form, it should list the related Parts for each Product - for example:

When the Parts records were imported, CatBase recognised the fact that the data being imported into the RelatedRecordNumber or RelatedRecordName field linked each Part to its Product, and automatically created the required link between the two tables.

Options