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.
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 '% %'
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.
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 <> ''
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.
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:
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'
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?).
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.