When you are migrating data from a soon-to-be defunct system to your new shiny CRM system (or perhaps from one operational system to another), you may want to take the opportunity to clean, update or translate the data in transition.
This blog describes the challenges of migrating Pick-list fields (also known as drop-down fields or Option Set fields) to ensure all permutations are addressed. This topic is just a small part of the overall migration or integration process but Collier Pickard can help you through the whole process.
What’s the difficulty in mapping Pick-lists and Option Set Fields?
Oftentimes, different values for Pick-lists and Option Set Fields are needed in your new CRM system. This can be due to differences in the setup and/or business process, or you may find that the move provides an opportunity to tidy things up. This means there’s often data mapping as well as data transformation involved.
As an example, let’s say you’re migrating to Infor CRM and you’ve got the following information in the Account Status field of your current CRM system:
|2||No value assigned|
In your new Infor CRM system, you may want different values for your account status, such as those in the Target column below.
|3||NULL or No Value||Inactive|
To do this, you would need to find the source values and map them on to the new values. How do you do this?
To begin with, a helpful SQL query should contain the following:
- SELECT status, count(status) FROM account
- GROUP BY status
- ORDER BY count(status) DESC
This query will return a list of all the values of the status fields in the account table; it will also list how many times each value is used, enabling you to review its usefulness. Here is an example of the results from a sample data:
It’s worth noting that conducting this kind of search can produce unexpected results – using the example above:
- a ‘blank’ status has occurred 882 times; this should prompt you to investigate the reason for this e.g. to see if there is another field that can provide more information on the account status.
- a “NULL” status doesn’t currently occur, which means you needn’t worry about this in mapping unless you want to future proof (i.e. repeat the exercise at a later date) this data migration/integration.
- Duplicate values. Sometimes your data may contain leading/trailing spaces which would result in what appears to be a duplicate value (one with space(s), one without). You can use the expression ‘trim(#STATUS)’ to trim any spaces off the status value.
What is NULL?
According to Wikipedia, Null is a special marker used in Structured Query Language (SQL) to show that a data value does not exist in the database. So what’s the purpose of Null? Well, it fulfills the requirement that all true relational database management systems (RDBMS) support a representation of “missing information and inapplicable information”. Therefore, in SQL, Null is used to identify this marker.
This should not be confused with “0” as a value. A null value indicates a lack of a value, which is not the same as a value of zero; think of it like this – a missing answer is not the same as “no” or “none” or “zero” as an answer. For example, if we were to ask the question, “How many cars does Simon own?” the answer may be:
- “zero” (we know for sure that he doesn’t own any cars)
- “null” (we don’t actually know if he owns any cars)
In a database table, the column that retrieves this information would start with no value (marked by Null), and it would only get updated with the value “zero” if it’s been proved that Simon doesn’t own any cars.
So in SQL, “null” is a state, not a value. This is important to remember as its usage is quite different from most programming languages, where “null” means it’s not assigned to a particular instance.
How to map using Excel
If you decide to use Excel to map data values, at a basic level you should set up a worksheet that contains the values from the Source database plus the required mapped values for the Target. For example:
The Source column in your Excel worksheet can be populated by conducting the query in SQL. Next, right-click and Copy the results, then paste it into the Excel column. Alternatively, you can save the results to a CSV file and then load them into Excel.
It’s also worth bearing in mind that there are specialist data migration/integration solutions that can move data to wherever it’s needed for a truly consolidated CRM solution.
Data migration and integration are areas riddled with pitfalls if you don’t develop them regularly or properly. We have resident gurus who can take the strain and pain out of these high risk project areas – just give us a call for more advice!