Q: When I'm importing data into CatBase, how do I link each record to its category or categories?

The short answer ...

In each of the data tables in CatBase there is a field called MainCategoryName and another called MainCategoryNumber. If you import a category name or number into one of those fields, CatBase will automatically create the necessary link between the record and the category.

The more detailed answer ...

Category Links

The relationship between data and Categories is a "many-to-many" relationship, meaning that each record can belong to any number of Categories, and each Category can be linked to any number of records. In order to efficiently manage this relationship, there is an intermediate table, called Category Links, which contains one record for each individual relationship. So, for example, if you have a product called "Widget" and that product is in three different Categories, there will be three records in the Category Link table. Normally you don't need to be concerned with this, as it is all managed transparently by CatBase when you select Categories for your records.

When each record is just going into one Category

When you are importing data, if each record is going into just one category, then you simply map the category name or number in your import file to either the MaiinCategoryName or MainCategoryNumber field, and CatBase will automatically set up the required links.

One thing to watch out for, though, is duplicate sub-category names. Suppose you have main (top-level) Categories called "Buying" and "Renting" and under each of those you have sub-categories called "Region One", "Region Two", etc. If your import file contains category names "Region One" etc., CatBase won't know which main heading it goes under - Renting or Buying. In this case you should use Category numbers instead of names. (To see what your category numbers are, click on the Setup button on the main window and choose Categories).

When each record may be in more than one Category

It's a bit more complex when you want to put the same record into more than one category. There are two ways to do this.

Multiple Rows

You can create a duplicate row in your import file (spreadsheet, or whatever you are using to create the import file); one additional row for each additional category for that item. The data will be exactly the same, with the exception of the category name or number. Click on the Updates tab in your Import Style and select Update Existing Records option, and make sure that the appropriate Primary Key Field is selected (this is the unique identifier for each record, for example the Company name, Product code, Ref number, etc.)

Then, as each record is imported, CatBase will update any existing records, changing just the Main Category Name or Number, and creating the necessary links between the data and the Categories.

Import a linking file into the Category Links table

This is a slightly more complex, but more efficient, option. You'll import TWO files: one containing the basic details for each record, and one containing just the information to create the category links. Let's look at a typical example for a Product data import.

1. The first file contains all the details for each product except the Category. There's one row for each Product. Each Product must have a unique identifier which is the Key Field: this might be the Product Name; a Product Code or whatever, or it can be a unique record number (in which case it MUST be mapped to the RecordNumber field in CatBase).
2. The second file contains just two columns: the unique identifier for each Product (either the Key Field or the Record Number), and either the Category Name or Number. This is the Category Link file.
3. Import the Product details file first
4. Then import the Category Link file. In your Import Style, select Category Entries in the Import into Table popup and map the fields as follows:
1. If your file contains the Key Field, map it to the RecordName field in the Category Entries, or
2. If your file contains the record number, map it to the RecordNumber field in CatBase
3. If your file contains the category name, map it to the CategoryName field in the Category Entries table, or
4. If your file contains the Category Name, map it to the CategoryName field in the Category Entries table
5. Import the file

This will create all the required links between the Products and the Categories.

When you want to specify the complete Category hierarchy

This option was added in CatBase Version 6.74r18.

Suppose you have your Categories set up in a two- or three-level hierarchy. The top level is known in CatBase as Level 1 Category; the second level (sub-category) is known as Level 2 Category, and the third level (sub-sub-category) is known as Level 3 Category.

1. In your database, create two or three String or Text fields (depending on how many levels of Category you are using) and name them "Level 1 Category Name", "Level 2 category name" and "Level 3 category name". Note that these field names are not case-sensitive but they must be spelled exactly as shown.
2. In your import file (eg your spreadsheet), create two or three columns and name them something like "Level 1 category name", "Level 2 category name", and :Level 3 Category name". NThe names are not important - as long as YOU know what they mean and you know how to map them to the fields you created in Step One.
3. Create an Import Style and map the two or three Level x Category Name columns to the matching fields in CatBase
4. Import the data. CatBase will try to match the category names to existing Categories, and will create new ones where needed.

To update to the latest version of CatBase, please go to the Downloads page on our website

Did this article answer your question?

If it didn't, please use the Contact form to ask us! Or simply send an email to support@catbase.com.