Oct
26
Written by:
Cornerstone Administrator
10/26/2009 1:47 PM
Data migration has many challenges but the most difficult is duplication of records. Using Accounts as an example, there are many different ways to input a company name. If there are more than one users entering new account information in a database, chances are you will find the same company multiple times. Example: Microsoft or Microsoft , Inc. or Microsoft Inc. or Microsoft Incorporated or Microsoft Corporation. (CRM 4.0 includes the ability to set rules which identify possible duplicates so the user can determine if a new record should actually be created.)
The best way I found to work through legacy data is to use the built in parse functions in SCRIBE.
1. STRIPCOMPANYSUFFIX: Removes all trailing punctuation and words in the following list: Inc, Incorporated, Corp, Corporation, Co, Company, Ltd, etc.
2. STRIPCOMPANY: Removes all punctuation, any trailing words in the following list: Inc, Incorporated, Corp, Corporation, Co, Company, Ltd, etc.
3. STRIP( SourceField, "A" | "N" | "P" | "W" | "O" ): Returns only the requested type of characters from text string: alpha, numeric, punctuation, whitespace, other/unknown
a. STRIP(“A”)
b. STRIP(“N”)
The source account name is parsed 4 ways and inserted into a new table. If there are existing accounts in CRM 4.0, those company names are extracted and parsed as well. Then simply compare parsed value to parsed value and create a series Success messages to log in the rejected source rows log. Always insert a unique ROW number in your source file so you can compare the results to the original file.