Migrating GoldMine to Microsoft CRM 3.0
Relationships


Overview

In most cases at least some of your Goldmine contacts will be related to each other (Goldmine calles these Referals). These relationships can get quite messy if the they are not entered consistently. For instance one relationship may be called "Business" the other "Business Contact" and then another " Business C.", all basically meaning the same thing. When migrating to Microsoft Dynamics CRM these relationship names need to be grouped into predefined relationship roles and then have the relationships linked to these roles.
In the standard project/script the actions that do this (higlighted in image on right) are deactivated as they will need adjusting to your own data. Following is a description on these actions and instructions on how to activate and adapt them for your own use.


The "GetRelationshipType" Function

This function takes the Goldmine relationship name and returns our Microsoft Dynamics CRM equivalent of it. You will have to come up with your own conversion list/map that suits your data. In order to find the different names, run this query on your Goldmine database: 
SELECT
	DISTINCT CONTSUPREF,
	COUNT(CONTSUPREF)
FROM
	Goldmine.dbo.CONTSUPP
WHERE
	RECTYPE = 'R' AND
	NOT CONTSUPREF IS NULL
	AND NOT CONTSUPREF = ''
GROUP BY
	CONTSUPREF

Then take the resulting list and decide how you want the individual items grouped. Mach each of them as a "WHEN - THEN" clause to your desired role. If a role in the Microsoft Dynamics CRM system already exists, it will be used - otherwise newly created.

The Contact Relationships Folder

All you need to do here is make sure this folder action is active. No other adjustments should be needed.

What it does is as follows:

  1. Create RelationshipRoleBase & CustomerRelationshipBase Table

    The tables RelationshipRoleBase and CustomerRelationshipBase are created as copies of the target database (without the data). This is where the data will be assembled into.

  2. Create RelationshipMap Table

    Creates an intermediate table where each two reciprocal relationships are represented as one record.

  3. Populate RelationshipMap

    Fills the RelationshipMap table with records of RECTYPE R from the Goldmine CONTSUPP table. Relationship names are filtered and transformed using the previously created GetRelationshipType function.

  4. Populate RelationshipRoleBase

    First all originally existing relationships are copied from the target Microsoft Dynamics CRM database. Then a DISTINCT over all entries in our new RelationshipMap table extracts the unique values for the new Relationship roles.

  5. Map RelationshipRoleBase Values

    Here the CustomerRoleId and PartnerRoleId fields are mapped to the newly created relationship roles.

Delete Relationships

Deletes all previously migrated relationships so that one can re-run the migration without importing everything yet another time. Make sure it is active, or you may get errors on other delete actions.

Copy Relationships

Finally our assembled data get copied into the target database. Make sure this is active or you will see no results in your Microsoft Dynamics CRM system.

Copyright © Jonathan Gill 2007