Migrate GoldMine to Microsoft CRM 2011 – Part 2

Part 1 of this series outlined some of the basic issues involved in migrating GoldMine to another CRM system.

This post examines some more detailed issues around email and web addresses in GoldMine, and also shows how some create SQL queries can be used to address the problems.

Email and Web addresses in GoldMine

Unlike most other CRM systems (including Microsoft CRM), GoldMine stores contact email and web addresses in a separate table (CONTSUPP), in a 1 to many relationship – one contact can have many email and web addresses.

Microsoft CRM and most other CRM systems store each email and web address in a field in the account or contact entity. Microsoft CRM has three email addresses and one web address for each contact, for example.

This makes it difficult to migrate email and web; as each row is encountered in the GoldMine data, a decision has to be made to map it to email1, email2, or email 3 in Microsoft CRM.

A second challenge with GoldMine email and web is they may be in more than one field. They are stored in the CONTACT field in the CONTSUPP table; however, if they are too long to fit into that field (40 characters) then:

• email address are split between the CONTACT field and the ADDRESS1 field
• web addresses are instead stored in the NOTES field.

So when moving email and web addresses to another CRM system, there are two challenges: making sure the correct data is moved, and mapping from multiple rows of data to a single row with multiple fields.

Using a Pivot in SQL

The solution to this conundrum is using a ‘pivot’ in SQL. Pivoting is the process of translating rows of data into a single row with multiple columns:

email1  }

email2  }                  ====>          email1   email2   email3

email3  }

Pivoting data is a multi-step process:

1. work out how to group the rows that will be turned into a single row
2. find a unique identifier for each row in the group that can be used for the pivot
3. build  a select statement based on a group that collects the rows into a single row.

In GoldMine, the rows with the individual email or web addresses all belong to the same contact,  and have the same ACCOUNTNO field – we can use that as the group.

select accountno, ...

group by accountno

The unique identifier is a bit more difficult – there is nothing int he GoldMine data that identifies email 1, email 2 etc. The solution is to use the row_number() function in SQL, which generates a row number for each row:

row_number() over(partition by accountno order by contsupref) as rowno

The row_numner() function generates a new row number for each row in the group of rows defined by the partition().

The third problem faced is the email being potentially split across two fields. This is solved using a CASE, like this:

case
	when ADDRESS1 is null then contsupref
	when ADDRESS1 is not null  then contsupref + address1
end as email

The case statement selects the CONTSUPREF field when ADDRESS1 is null, or combines CONTSUPREF and ADDRESS1 if necessary.

A Pivot Query for email addresses

Putting it all together gives us a SELECT query that produces a single row for each distinct accountno vale, along with up to three email addresses that belong to that accountno:

select accountno,
	MAX(case when rowno = 1 then email end) as email1,
	MAX(case when rowno = 2 then email end) as email2,
	MAX(case when rowno = 3 then email end) as email3
from
	(select accountno, row_number() over(partition by accountno order by contsupref) as rowno,
		case
			when ADDRESS1 is null then CONTSUPREF
			when ADDRESS1 is not null  then contsupref + address1
		end as email
	  from CONTSUPP where CONTACT = 'E-mail Address'
	) as e
group by accountno

Using the Pivot Query to get real data

Finally, that query can be used in a left outer join with a SELECT on CONTACT1 to get the main contact data, and all the associated email addresses:

select c1.accountno, company, CONTACT, e.email1, e.email2, e.email3
  from CONTACT1 c1 left outer join
    (
      select accountno,
        MAX(case when rowno = 1 then email end) as email1,
        MAX(case when rowno = 2 then email end) as email2,
        MAX(case when rowno = 3 then email end) as email3
	from
	  (select accountno,
                  row_number() over(partition by accountno order by contsupref) as rowno,
		  case
		    when ADDRESS1 is null then CONTSUPREF
		    when ADDRESS1 is not null  then contsupref + address1
		  end as email
	     from CONTSUPP where CONTACT = 'E-mail Address'
	  ) as e2
	group by accountno
    ) as e
    on c1.ACCOUNTNO = e.ACCOUNTNO

To summarize:

1. The innermost select (as e2) gives use a row for each email address, with a row number and the email data correctly formatted
2. The second select (as e) gives us an intermediate table with the email addresses for each accountno in a single row
3. This is then left outer joined to the main select, which is being used to get the main contact data from CONTACT1.