Wednesday, November 19, 2008

A Couple Data import issues

I thought these items might be of help to other people populating data in MS CRM systems. The first is documented, but for the second I found no information on web.

Issue 1: When importing email activities, like all other activities in the vast majority of instances you need to complete them so they show up in history and not in the current Activities of a user's Workplace or against the items to which they are regarded.

If you import an email message with a From email address that is not a CRM system user, you can create the activity without a problem, but will get a Soap Exception "The specified sender type is not supported." When you try to set the state of the email to "Received". The following hot fix will allow you to Complete those incoming emails and set them to "Received".

Issue 2:  While importing account information after a customer of mine added additional customertypecode picklist items I ran into a Soap Exception.

<description>A validation error occurred.  The value of 'customertypecode' on record of type 'account' is outside the valid range.</description>

The customertypecode picklist starts at 1 and progresses linearly with the default values out of the box, and you can always rename the existing items, but any new picklist items start with values of 200001 and progress linearly from there.

Workaround: Edit an existing account or add a dummy account and select a new pick list item with a higher ranged value and save that account record. Now the problem magically disappears.

Hypothesis: I'm thinking that the CRM webservices are using reflection like Excel does on a spreadsheet column but against the database and it initially ranges the pick list field as a short, but only ranges it up when a larger value exists.

If anyone has a better understanding of why this behavior occurs, I would love to hear about it.


Jim said...

FYI, we get the pick list errors when we have pick list values that have a null display value. Still trying to chase down how those can happen since those pick list values don't show up in either the pick list itself nor in the valid attribute values under the entity.

Mark Kovalcson said...

That's an interesting issue that I haven't seen. Could you post what you find the problem to be?

Jim said...

Yes, I think I've zeroed in on it a bit more. Let me see if I can make sense of it here.

I found the issue using the following SQL (for Contacts):

FROM yourcrmdb.dbo.FilteredContact
GROUP BY somepicklist, somepicklistname
ORDER BY somepicklist

If there are any rows that have a non-null value in the first column (i.e., a pick list integer value) and null in the second (the pick list display value), that's the issue. To find the specific entities with that problem, I then used the following SQL (for Contacts):

'http://yourcrmurl/sfa/conts/edit.aspx?id=' + CAST(contactid AS VARCHAR(50)) AS URL,
fullname AS Name,
FROM yourcrmdb.dbo.FilteredContact
WHERE somepicklist IS NOT NULL AND somepicklistname IS NULL
ORDER BY fullname

We've been on CRM since 1.2, and we're now at 4.0 and encountered the problem due to the new API throwing the validation errors, per your post (which was helpful for researching our issue - thanks!). My supposition is that a prior version of CRM allowed for the deleting of pick list values from a pick list attribute while still having those values populated in existing entity instances, and that when we were deleting those pick list values we didn't do a good job of doing an advanced find and reassigning the pick list to valid values before deleting the obsolete values. Does that make sense?

Mark Kovalcson said...

You are probably correct. Before CRM 3.0 there were issues deleting entities and fields that caused problems after the upgrade. As of CRM 3.0 this should no longer be an issue.