CRM 4.0 Upgrade error. An item with the same key has already been added

Recently when testing a CRM 4.0 upgrade for a customer, I got the error 'An item with the same key has already been added'. I'm not the first to have met this problem (see the MS newsgroup), and probably won't be the last).

From what I've found so far there are 2 possible causes, both to do with CRM believing there are duplicate field names. Either a duplication has occured between a custom field, and a virtual field, or you have somehow got a duplicate entry in FieldXml.

Duplication between a custom field, and a virtual field
For certain field types (boolean, picklist and lookup), CRM creates an additional attribute in the metadata for the text of the field. This attrbiute is not physically stored, and is considered to be a 'virtual' field. The virtaul attribute has the name of the base field + a suffix of 'name'. For example, the prioritycode picklist field results in a corresponding prioritycodename virtual field, and a custom lookup field new_accountid would result in a virtual field new_accountidname (there is also a new_accountiddsc virtual).

This can cause a problem if you created an additional, physical field with the same name as a virtual field. CRM will let you do this, but the upgrade could fail.

To determine if this is a potential problem, run the following SQL script in the METABASE SQL database. This will return a list of any duplicates (you'll need dbo rights to run the script)

select e.name as entity, a.name as attribute, count(*)
from attribute a join entity e on a.entityid = e.entityid
group by e.name, a.name
having count(*) > 1

If you've found duplicates, your only solution is to remove the duplicate field from CRM.

Duplicate entry in FieldXml
CRM stores metadata in several places in CRM. One of them is the OrganizationUIBase table in the MSCRM database. Within this table, some data is stored in the FieldXml field. This is an XML document that looks something like this:

<entity name="prism_utilisationperiod" objecttypecode="10003">
<fields>
<field name="prism_utilisationperiodid" requiredlevel="na">
<displaynames>
<displayname description="UtilisationPeriod" languagecode="1033" />
</displaynames>
</field>
<field name="createdon" requiredlevel="na" format="date">
<displaynames>
<displayname description="Created On" languagecode="1033" />
</displaynames>
</field>
</fields>
</entity>

There should only be one field element for each CRM attribute, but the problem I had was that a field element was duplicated.

To determine any duplicate fields, use the following SQL in the MSCRM database:

declare @fieldXml nvarchar(max), @otc int, @idoc int
declare curEntity cursor fast_forward for
select ObjectTypeCode, FieldXml from OrganizationUIBase where InProduction = 1
open curEntity
fetch next from curEntity into @otc, @fieldXml
while @@fetch_status = 0
begin
EXEC sp_xml_preparedocument @idoc OUTPUT, @fieldXml
if exists ( select name as field from OpenXML(@idoc, '/entity/fields/field') WITH (name nvarchar(64)) group by name having count(*) > 1 )
select @otc as otc, name as field, count(*) as occurences
from OpenXML(@idoc, '/entity/fields/field') WITH (name nvarchar(64))
group by name
having count(*) > 1
EXEC sp_xml_removedocument @idoc
fetch next from curEntity into @otc, @fieldXml
end
close curEntity
deallocate curEntity

This will list the objecttypecode and attribute name of any duplicates. If you have duplicates, the resolution is to export the customisations for the relevant entities, delete any duplicate field elements from the customisation xml, then reimport the xml and publish the entities.

Credit is due to Michael Hohne and Kesh Patel who've contributed to finding the problems and solutions.

Email Templates with Custom Entities

I've recently been doing some development work creating emails based on templates. In most cases this is straightforward, once you've applied CRM 3.0 Update Rollup 1, 2 or 3, and also updated any web references. However, there are a couple of quirks I found when trying to use custom entities.

First; you can only use Global templates with custom entities. CRM 3.0 does not allow you to create an entity-specific template. This led to the first quirk; I was using a QueryExpression to find a specific template by name, and also templatetypecode (as CRM allows you to create templates with the same name but on different entities). When searching for a Global template, the templatetypecode is systemuser, rather than null as I was expecting.

Next, when using InstantiateTemplateRequest, it failed if the ObjectType were set to any custom entity. The error message is the unhelpful 'An unexpected error occurred'. Again, the solution is to treat a Global template as if it were actually a template for the systemuser entity. Therefore to make it work you have to specify an ObjectId and ObjectType for a systemuser - it doesn't seem to matter which systemuserid you use.

Putting this together, the following code shows how to create an email based on a template for a custom entity:

public email CreateEmailFromGlobalTemplate(string TemplateName, string EntityTypeName, Guid ObjectId, Guid SomeUserId)
{
QueryByAttribute qa = new QueryByAttribute();
qa.EntityName = EntityName.template.ToString();
qa.Attributes = new string[] {"title", "templatetypecode"};
qa.Values = new object[] {TemplateName, EntityName.systemuser.ToString()}; // Use systemuser for global template
ColumnSet cs = new ColumnSet();
cs.Attributes = new string[]{"templateid"};
qa.ColumnSet = cs;
BusinessEntityCollection bec = svc.RetrieveMultiple(qa);
if (bec == null && bec.BusinessEntities.Length == 0)
throw new Exception("Cannot find template");
Guid templateId = ((template) bec.BusinessEntities[0]).templateid.Value;
InstantiateTemplateRequest req = new InstantiateTemplateRequest();
req.ObjectId = SomeUserId; // Any valid systemuserid
req.ObjectType = EntityName.systemuser.ToString(); // Use systemuser for global template
req.TemplateId = templateId;
InstantiateTemplateResponse resp = (InstantiateTemplateResponse) svc.Execute(req);
if (resp == null && resp.BusinessEntityCollection.BusinessEntities.Length == 0)
throw new Exception("Cannot create email");
email newMail = (email) resp.BusinessEntityCollection.BusinessEntities[0];
newMail.regardingobjectid = new Lookup();
newMail.regardingobjectid.type = EntityTypeName;
newMail.regardingobjectid.Value = ObjectId;
return newMail;
}

One more thing to bear in mind: If you use InstantiateTemplateRequest then the code has to run under a CRM context of a live user, rather than the SYSTEM user, otherwise you will get the error 'The specified users settings have not yet been created' - I guess because CRM also looks at user templates