Customer Forum

Using import to bulk update / overwrite records

Workbooks Support Posted: 2011-06-10 11:04

Here are some examples of how the import facility can be used to bulk update / overwrite records:

Before you start, it is important to understand the role of the Object Reference field.  It is available in grids and is called "Reference", "Object Reference" or "[Record type] reference" e.g. "Organisation reference".

Object Reference is the field used to uniquely identify records in Workbooks.  It is totally unique to each individual record, and therefore the perfect field to use to locate a particular record.  

When performing updates we strongly advise you to use Object reference as it is a true unique identifier, which is not necessarily true for name e.g. You may have two organisations in your system called Widgets Ltd., but each of those will have a unique identifier ORG-348, ORG-456.

Wherever possible, include object reference in the list of columns you export.  You can then use this column as the unique identifier when you re-import / overwrite data.


Example 1: Clearing / Blanking values e.g. removing Customer Agreements

Scenario: You have many hundreds of organisations that are incorrectly marked as customers.  You want to remove the tick from the customer agreement checkbox.

Step 1: Create a view or report that identifies the records you want to update.  In this example the simplest thing to do is to go to Start > Organisations > Customers and export a CSV file remembering to include the Organisation reference column.

Step 2: Start > Import > Organisations > Select the CSV > Next > Next again.

Step 3: Setup two Field Mappings:

i) Click Organisation reference in the Unmapped File Fields section on the left of the screen > Select "Object Reference" as Field's value > Save & Close

ii) Click Add Mapping in the Field Mappings section.  Set Field's value to Is Customer with a Mapping Type of Blank Value > Save & Close.

(click to enlarge)

Step 4: Click Next to proceed to this screen, where you set the Import Mode and field to Match on.  Set Import Mode to "Only update existing records" > select Object Reference as the field you are going to use to uniquely identify the records.

(click to enlarge)

Step 5: Click Run.  Wait for the import to complete.  You can click the Refresh button to check progress.

Step 6: Review the information displayed in the import screen and check you're happy with the updated data e.g. Start > Organisations > Customers.  You should now have less organisations marked as customers.

Step 7: Approve or Reject the import.