Migrating Goldmine Premium Edition CRM to Zoho CRM

Challenges

Problem:

In Zoho CRM FirstName and LastName are separate fields. In Goldmine Firstname and Lastname are concatenated in to Contact1:Contact and there is a separate field for last name Contact1:Lastname.

Solution:

Add a column to the table Contact1 and write to it with the below statement that parses the first word in the Contact field.

Update CONTACT1 
SET CONTACT1.CONTACTFIRST = SUBSTRING(CONTACT,1,CHARINDEX(' ',CONTACT)-1)
WHERE CONTACT LIKE '% %'

Problem:

The Contact1 table, the Notes table is joined to Contact1. So if you run Select * From Contact1 you are going to gets the notes fields which will ruin your data with all of its line breaks. I used the following query to omit the Notes. Note that this query also omits results where last name is blank. In ZohoCRM, when importing to the contacts module they are assuming that every record is a contact with first and last names. For those records in Goldmine without a contact name and only a company name, I ran a separate query.

Solutiion:

Run statement to exclude notes column.

Select
ACCOUNTNO,COMPANY,CONTACT,LASTNAME,DEPARTMENT,TITLE,SECR,PHONE1,PHONE2,PHONE3,FAX,EXT1,EXT2,EXT3,EXT4,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIP,COUNTRY,DEAR,SOURCE,KEY1,KEY2,KEY3,KEY4,KEY5,STATUS,MERGECODES,CREATEBY,CREATEON,CREATEAT,OWNER,LASTUSER,LASTDATE,LASTTIME,U_COMPANY,U_CONTACT,U_LASTNAME,U_CITY,U_STATE,U_COUNTRY,U_KEY1,U_KEY2,U_KEY3,U_KEY4,U_KEY5,recid
From CONTACT1
Where LASTNAME <> ''

Problem:

The “Notes” field is problematic because of the html content contained within the notes when you can only import CSV files. As in the screenshot below, line breaks were inserted in to the CSV file where I didn’t want them.

Solution:

Query to return records without the DIV tags

SELECT GOLDMINE9.dbo.NOTES.ACCOUNTNO,GOLDMINE9.dbo.CONTACT1.CONTACT, GOLDMINE9.dbo.CONTACT1.Lastname,
CONVERT (varchar(max), convert(varbinary(max), note)) AS "ConvertedNotes" from GOLDMINE9.dbo.NOTES
Join GOLDMINE9.dbo.CONTACT1 ON notes.ACCOUNTNO = CONTACT1.ACCOUNTNO
Where CONVERT (varchar(max), convert(varbinary(max), note)) not like '%<DIV>%'

Export to Excel and do a Find and Replace of all <BR> tags (replace with blank)

In Goldmine, email addresses are stored in the CONTSUPREF table allowing for a 1-to-many relationship of the data. In Zoho CRM, there are two email address fields contained in the Contact table. I needed a query that would join the data from the Contact1 table and the Contsupref table of Goldmine to be able to import to the Contact table (remember, last name is a required field when importing in to the Contact table in Zoho CRM).

In addition to joining the tables I needed to find only records that have a 1 in the 2nd position of the contsupp.zip field. The contsupp table is a multi-use table and for those rows where contsupp.contact = ‘Email Address’ the zip code field is a binary switch field. The 1 or 0 positional placement indicates the relationship the record, in this case n1nn indicates the record is the “primary” email address. For a much more detailed explanation visit:

http://enigmaware.co.uk/goldmine/tabledefs/contsupp_p_email.php

Position 1 = TEXT (0) or HTML (1)
Position 2 = Primary address (1) or normal (0)
Position 3 = Wrap Lines(1) no wrap (0)
Position 4 = MIME(1) or not MIME(0) - if set to 0 then position 1 must also be 0
SELECT contsupp.ACCOUNTNO,
contsupp.CONTSUPREF,
contsupp.ADDRESS2,
CONTACT1.LASTNAME 
From CONTSUPP 
Join dbo.CONTACT1 on CONTSUPP.ACCOUNTNO = CONTACT1.ACCOUNTNO
where
SUBSTRING(contsupp.ZIP,2,1) = '1' and dbo.contsupp.CONTACT = 'E-mail Address'

The HIghlights

I really do prefer Zoho’s data structure over that of Goldmine. Goldmine had introduced a number of work-arounds in the database to accomodate one-to-many relationships that the original design didn’t have (contsupref table anyone?).

The Lowlights

Like virtually every other CRM product out, ZohoCRM users CSV files to import data. Goldmine uses comma’s in their accountno field and since this is a key field that uniquely identifies a record you can see the problems this would create. I had to create different, unique numbers for those records that have comma’s in the accountno field.

To address this problem I ran contact1 to a testtable and then ran an update query against the table to replace commas with exclamation points.

SELECT * 
INTO TestTable 
FROM CONTACT1 

UPDATE TestTable
SET accountno = REPLACE(accountno,',','!')

There is a disconnect in one stage of the data import process. The field “Contact Name” in the gui is a concatenation of the fields FirstName and LastName in ZohoCRM. However, when you try to import to the Notes table and use the Contact module for reference, they don’t let you key off of the account number, oh no, you have to key off the Contact Name field, which is a non-exsistent field in the database. This really complicates importing notes.

NOTE: I’ve been a Goldmine administrator since 1999. 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s