An Alternative Method For Data Enrichment

I recently had a problem with a data import. The data had imported fine but a few hundred contacts were not imported with their email addresses (the client had forgotten to update the source data). The client had an Excel list of email addresses and ID numbers (not the GUIDs but unique integers which were imported and matchable) to identify the correct Contact in the database. Now all we had to do was ‘enrich’ the associated Contacts in CRM.

The Problem

The supported option for data enrichment is to export the records from CRM you wish to update and then use the import wizard to reimport them.

The problem in this case was there was no simple way to isolate the records that needed updating. I could have exported all the Contacts in the system and used a vlookup but there were more than 10,000 and the export from CRM, by default, is only good for 10,000 records.

It is possible to increase this limit but, in this case, we were on a deadline and the bureaucracy meant I was motivated to find another way, ideally codeless.

I could have used a lot of “Contact ID equals ######” linked by a Group OR but, given there were a few hundred records, I did not fancy this option either.

The unsupported option was to make direct update calls to the database but I try to avoid unsupported solutions unless absolutely necessary.

The Solution

There was no way for the import wizard to update the fields on an existing record (without exporting first) but I could add a child record to the Contact. In this case, as it was unused in the solution, I used the Opportunity entity. I used the topic field of the Opportunity to hold the email address and mapped the Customer using the ID (mapping to the correct Contact using the ID is done like this).

On top of this I created a workflow which, on the creation of an Opportunity, went to the parent record and updated the Contact email address with the value in the Opportunity’s topic field.

The result is we import our file of IDs and email addresses in as Opportunities. When the records are created, the workflow kicks in and updates the Contact linked to the Opportunity.

All we are left to do is bulk delete the created opportunities and remove the workflow.

Suggested Improvements

There was one other complication not mentioned above which was we also needed to populate an ‘Email ID’ field for integration to a third party system. This proved a bit tricky as it was an integer field and workflow will only populate an integer field from a money, float or integer field, not a text field. In the end I used the Freight Amount field but it is conceivable that, in the general case, either there is no child entity to use or, if there is, it does not have the right field types for workflow to copy the values across.

In this case I would create a new custom entity with the right fields on it, populate via import and use the workflow to flow the values up to the parent record. A little extra overhead but still only 15-30 minutes work, no need to get IT Administrators involved and no Excel formula fiddling.

Conclusions

If you need to update records and

• there is no simple way to isolate the records with an Advanced Find query
• there are too many to manually update them
• there are too many Contacts to export the entire list

This little trick will do the job. It is codeless and takes 15-30 minutes to set up and avoids the need for involving administrators, Excel formula, unsupported hacks or coded solutions.