Sunday, October 14, 2012

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.

image

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.

image

image

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.

7 comments:

Unknown said...

hello! i am a student and i have to follow some steps that we were given by our informatics teacher in crm.one of the steps is to import an excel file(which contains some data about two products) to a price list. me and colleagues have the same problem:after following the import steps, when watching the imports in the workplace ,we see that we have two errors so that excel file was not imported.the error is called "the lookup reference could not be resolved"can you please give us some advice?we really do not know how to manage this problem and dead line for the project is coming very soon.Thank you!

Unknown said...
This comment has been removed by the author.
Leon Tribe said...

Hi Florina,

This error means that a column in your spreadsheet is referencing another record in CRM but it cannot find it. For example, if you are importing contacts, the parent account is a reference to an account in CRM.

By default, the reference will try to match on the primary attribute (name) or the GUID of the parent record. This can be changed in the mapping. See here for details (http://leontribe.blogspot.com.au/2011/08/changing-lookup-reference-when.html ).

If you are still struggling with it, let me know.

Leon

Unknown said...

Hello!
I tried to follow the steps given in your article but it did not work.I really think that the problem is in mapping screen where we have to link the columns in our data source to fields in CRM. But i tried all the possibilities and it still brings me errors.I do not have any experience in this programme and probably this is why i am not able to match them correct..
Unfortunatelly, without this import i can not go on with the other tasks given by our teacher.

Florina

Leon Tribe said...

Send me the spreadsheet and I'll have a look ;)

Unknown said...
This comment has been removed by the author.
Christian Spence said...

Great solution. Always worth creating hidden fields like SysAdminFlag and hidden entities SysAdminEntity to be able to drag info in and then move it around for things like this.