Migrate GoldMine Email and Web Addresses

This post examines some detailed issues around migrating email and web addresses from GoldMine, and provides examples of create SQL queries to address them. For free consulting to advise on your requirements please email here.

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.

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 addresses is they may be in more than one field. They are stored in the CONTACT field in the CONTSUPP table and 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 in the 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

Conclusion

Pivot statements are an excellent way of overcoming the problems arising from different data conventions involving one-to-many data formats across various CRM systems. The above example shows how to manage email addresses when migrating from GoldMine. The same procedure would be used for web addresses.

To request a call or webinar with InaPlex to review your migration requirements, please email here. For more detail on migrating from GoldMine, ACT!, and many other CRM systems, please have a look at our posts under the “CRM Migration” category.

Inaport, from InaPlex, delivers CRM integration and data migration solutions for leading CRM systems including Salesforce, Microsoft Dynamics CRM, Sage CRM, Infor CRM (previously Saleslogix), Goldmine and ACT. It has a range of connectors and maps for quick results, and its straightforward approach provides a wealth of powerful functions. To learn more please book a free webinar, download a free 30 day evaluation license, or visit the InaPlex website.