Migrating GoldMine to Microsoft CRM 3.0
Phase 2: Accounts & Contacts


Overview

This section will cover assembly of account and contact data ready for transfer into the Microsoft Dynamics CRM 3.0 database.

Accounts

Accounts are fairly straightforward. First the table structure is cloned (after deleting any previous instance) from the target database using a SELECT INTO statement with a TOP 0 clause to avoid copying any data from the target database. This is the way nearly all other tables in the staging database are created.
The data is copied from the CONTACT1 Goldmine table, joined with the GMCRMMap linkage table in a very straight forward way, excluding any records that leave the COMPANY field empty. The AccountId is taken from the linkage table. Here are the column mappings:

The other fields are filled from global variables or given constant values.

CustomerTypeCode

The CustomerTypeCode is mapped from the CONTACT1.KEY1 table using the previously created GetCustomerType function in Phase 0. In order for this to work you will have to define how the Goldmine values (text) map to the Microsoft Dynamics CRM CustomerTypeCode enumeration (numeric).
You can find out what values are in your Goldmine database by running this query:
SELECT
    KEY1,
    COUNT(KEY1) AS Expr1
FROM
    CONTACT1
GROUP BY
    KEY1
ORDER BY
    Expr1 DESC
Take these values and decide which ones you want to have in your Microsoft Dynamics CRM database. Then go and create the enumeration values in the web interface, noting down which numbers correspond to which labels. Then go and edit the action that creates the SQL Server function in the SQLTree editor (Initialize\Conversion Functions\Create Function GetCustomerType).

Contacts

Documentation in progress...

Copyright © Jonathan Gill 2007