Monday, June 22, 2009

RFC822 Import to MS CRM email activity utility

When migrating from Goldmine and some other CRM systems to MS CRM there is sometimes a need to import historical email.  This is frequently stored in a SQL database in rfc822 format. RFC822 is an email format standard.

What I am providing is a single class along with a file from the MS CRM SDK that this class currently uses. This single class is consolidated from a number of other classes in my import framework and is intended to give you a nearly complete rfc822 import solution that you can modify for your needs.  Every place that you need to make a modification according to your particular system has a //TODO: comment explaining what you need to adjust particular to your configuration. In addition assume that there will be some tweaking required, and make sure to do thorough debugging and testing to verify that your email records are being properly generated.

You can download the source code here.

There are 2 steps to using this class.

Part 1: Initialize a new RFC822ToEmailActivity object.

var emailConversion = new RFC822ToEmailActivity("Contoso"); // Your Orgname

Part 2: Calling the CreateEmailFromRfc822Record() method

CreateEmailFromRfc822Record() should be called in your loop that is reading the rfc822 records

var emailEntity = emailConversion.CreateEmailFromRfc822Record(     rfc822Record, // This is the string holding the whole 822 record     localEmailDomainName, // This is the local email domain name ex.      emailOwnerId, // systemUserId of email entity owner     relatedEntityName, // contact or account or....     relatedEntityId // id of the entity to regard the email to
  1. Calls parsing method
  2. Creates email attachments from pathnames returned from parsing method.
  3. Writes to an error log file with all attachments that are not found and other errors.
  4. Makes any other changes to the email entity before saving it.
  5. Sets Email State as completed (sent or received) based on local domain name.

Called Internally: ParseRFC822() parsing and email activity creation.

  1. Parse an rfc822 record
  2. Create an email activity based on that record.
  3. Compare email addresses, both To: and From: with a local domain name to decide if the email is incoming or outgoing.
  4. Create ActivityParties for To:, CC:, BCC:, From: related to CRM users, Contacts, Accounts and Leads in that match order precedence.
  5. Return a list of pathnames to any attachments.

RFC882 Email Attachments

Products like Gold Mine are storing email bodies in the database but they only have pathnames to the attachments. These attachment paths are frequently on the end user’s local computer which can be unreliable. These attachments may have been renamed, moved or deleted.

Hint1: Create a single shared folder on a file server with a separate tree branch for each computer that has attached files in the email history and modify the pathnames programmatically to match the structure you create on the file server.

S:\importattachments\C\Documents and Settings\mkovalcson\My Documents

Hint2: Comment out or create a test mode where nothing is written into the CRM database, but missing attachments are still written to a log file where you can see what isn’t found and decide how important it is to hunt attachments down before the final import.

You may want to look at this before you get started.
MS CRM Data Import (Cleaning up the Mess)

Saturday, June 13, 2009

MS CRM 4.0 Entity Mapping and Hidden Mappings

Microsoft has a powerful way to manage automatic attribute mappings from entities created from other entities. Typically we see this in the QOI process where an Opportunity is created, and then a Quote, Order or Invoice can be created from the Opportunity and much of the information is automatically copied from the Opportunity to that Quote, Order or Invoice.

Entity Mapping

If you open up your Opportunity from Customize Entities and look at 1:N Relationships, you can see the opportunity_quotes relationship.


If you double click on that you will see mappings. This shows you the names of each source opportunity attribute that is “Mapped” to a target quote attribute when a Quote is created from an opportunity.


This is very useful if you want to add custom fields and have those automatically propagated too or if you want to disable any default mappings.

So what about mapping the opportunityproduct? or quotedetail, salesorderdetail, and invoicedetail for that matter?

Hidden Entity Mapping

Step 1. You need the EntityMapId for the relationship that you want to map.

You can manually scan the EntityMapBase table matching against the SourceEntityName and TargetEntityName.


Or you can write a select like the one below to get a nice list of Guids to copy and paste into a temporary form.

Select TargetEntityName, EntityMapId from dbo.EntityMapBase
where SourceEntityName = 'opportunityproduct'


Step 2. Take the following URL and replace your server name and the EntityMapId of your choice to gain access to the rest of the mappingList editors.