Referencing Other Records

A classic pain point in CSV imports is when you need to reference other records.

For example:

• importing products, you need to set tax codes

• importing products, you need to set which websites they are on

• importing products, you need to set which categories they are in

• importing products, you need to set preferred supplier

• importing customers, you need to set which source website they came from

In all of these cases, it's key to understand what's going on here. When you set a reference, you are not just hard-coding a value. For example, when I set the category of a product, I am not just setting a name - I am actually pointing it to that record on the system.

Key to getting this right is understanding how to refer to other records. It's actually quite simple: you use their primary identifier. Important - that's not their internal ID of the the form 34ng8484nn3f993f3f3 - that would be ugly and hard for non-techies to manipulate. Instead, each record has a more "human" name you can reference it by that we call primary identifier.

How do you know the primary identifier of a record? Just do an export - you'll find the !pid field on every export. Here are some examples of primary identifiers for different entities:

• Product Category - name e.g. "Fresh Food"

• Customer - email e.g. [e-mail]

• Website - name e.g. "Retail Website"

It's crucial to write the primary identifier EXACTLY correctly including capitals, spaces and punctuation - close won't do! Computers are dumb and even a single mistake will mean that the import will fail because it won't be able to cross-reference the record. Typically, you won't be writing these references by hand - you'll either be copy-pasting them from the CSV exports, or better yet you'll be using VLOOKUP in excel to cross reference them from your own data sheets. If that sounds like tech babble - don't worry, it's not that hard. Either search for "Excel VLOOKUP" and watch a few tutorials or ask for help. VLOOKUP is a secret weapon in Excel, especially for every-day commerce data manipulation, so it's worth knowing.