Friday, August 28, 2009

CRM Video Resources for Training Users

A variety of short videos for helping new users get comfortable with Microsoft Dynamics CRM are available in this Resource Center. The most recent, Create a personal view, walks through defining criteria and columns, and saving the view for reuse.

Many to Many Relationship

Many to Many relationships is not something new to CRM 4.0, they existed in CRM 3.0 as well,
however there was no ability to create new many to many relationships in the previous version, which is available in the current version.
When a Many to Many relationship is created between 2 entities, a custom table is created to maintain the relationship, which is called as the relationship table.This table stores the primary key values of both the entities.

Associating Records with a Many to Many Relationship:Assuming there are 2 entities named 'Student' and 'Book' which have a Many to Many relationship.Here's a sample code which associates multiple 'Book' entity records to the 'Student' Entity record.

Moniker Moniker1 = new Moniker();
Moniker1.Id = studentid;
Moniker1.Name = EntityName.student.ToString();
//BookList is a list which contains the guid of the 'Book' entity records.
for (int x = 0; x < BookList.Count; x++)
// Create a request.
AssociateEntitiesRequest request = new AssociateEntitiesRequest();
// Assign the request a moniker for both entities that need to be associated.
Moniker Moniker2 = new Moniker();

Moniker2.Id = new Guid(BookList[x][0].ToString());
Moniker2.Name =;

request.Moniker1 = Moniker1;
request.Moniker2 = Moniker2;
// Set the relationship name that associates the two entities.
//Refer relationship properties in CRMrequest.
RelationshipName = [Enter the name of the Relationship here];
// Execute the request.
AssociateEntitiesResponse response = (AssociateEntitiesResponse)myCrm.Execute(request);

Retrieving Associated Many to Many Relationships records:Note : RetriveMultiple method cannot be used to retrieve the records associated with a Many to Many relationship.
The sample code below shows how to retrieve records having a Many to Many Relationships using the LinkEntity class.Here we retrive the Books associated with a student record.

// Create a query expression.
QueryExpression qe = new QueryExpression();
qe.EntityName =;
qe.ColumnSet = colSet;
// Create the link entity from book to student
LinkEntity leToRetrieve = new LinkEntity();
leToRetrieve.LinkFromEntityName =;
leToRetrieve.LinkFromAttributeName = "bookid";
leToRetrieve.LinkToEntityName = [Enter the name of the Relationship table here];
leToRetrieve.LinkToAttributeName = "bookid";
LinkEntity leASFilter = new LinkEntity();
leASFilter.LinkFromEntityName = [Enter the name of the Relationship table here];
leASFilter.LinkFromAttributeName = "studentid";
leASFilter.LinkToEntityName = EntityName.student.toString();
leASFilter.LinkToAttributeName = "studentid";
// Create the condition to test the user ID.
ConditionExpression cExpression = new ConditionExpression();
cExpression.AttributeName = "studentid";
cExpression.Operator = ConditionOperator.Equal;
cExpression.Values = new object[] { [STUDENT GUID] };
// Add the condition to the link entity.
leASFilter.LinkCriteria = new FilterExpression();
leASFilter.LinkCriteria.Conditions = new ConditionExpression[] { cExpression };
// Add the from and to links to the query.
leToRetrieve.LinkEntities = new LinkEntity[] { leASFilter };
qe.LinkEntities = new LinkEntity[] { leToRetrieve };
RetrieveMultipleRequest request = new RetrieveMultipleRequest();
request.Query = qe;
request.ReturnDynamicEntities = true;
RetrieveMultipleResponse response = (RetrieveMultipleResponse)crmService.Execute(request);
bec = response.BusinessEntityCollection.BusinessEntities;

Refer the below link for an fetchXml solution by Ranjit Raghuwanshi.

Relationship Mapping (CRM 4.0)

You might have noticed, how certain fields are automatically populated sometimes, whenever a new form is opened from the CRM UI.
Try creating a new Case from the Contract form. The customer and contract fields are automatically populated on the new Case form. Similar for various other entities too.
CRM provides an excellent functionality but often quite ignored of automatically mapping attribues from one entity to another within a 1:many relationship.

Open the CRM window and goto  Settings  -> Customization  ->  Contract  -> 1:N Relationships  -> contract_cases  -> Mappings

There are 2 fields already mapped between the contract and case. It is this mapping which defines the fields to be populated when a new Case form is opened from its Parent Form (Contract)
You can use the ‘New’ button to define additional mappings. Mapping are not only related to system attributes, you can define them for custom attributes as well.

Note : Mappings can be defined only for entities having a 1:many relationship

Mapping can be defined for most Entities : Account to Contact, Opportunity to Quote, Quote to Order, Order to Invoice, Contract to Case etc.
So if you try converting a Quote to an Order, you will notice that all fields of the Quote and QuoteDetail are automatically pushed into the SalesOrder and SalesOrderDetail

However consider a scenario, where you need custom fields on QuoteDetail to be transferred to the custom Fields available on the OrderDetail. If you have a look at the QuoteDetail relationships, you will find that no relationship exists between the QuoteDetail and OrderDetail. So how do you go about mapping the custom fields.

Well, CRM definitely has a relationship between the QuoteDetail and OrderDetail Entities , however for some reason unknown, its hidden from the UI.

Here’s an UNSUPPORTED technique to unearth these relationships. So for the above example we need to transfer QuoteDetail custom attribute values to the  OrderDetail Entity.

Within SQL, run the below query:
Select * from entitymapbase where targetentityname = 'orderdetail'
This query should return 3 items, and we are concerned only  about the row with a SourceEntityName column value of  "quotedetail".
Copy the GUID value available in the EntityMapId column for the particular row
Paste the GUID at the end of the below URL

That opens up the secret mapping page ......  :-)  Happy Mapping
You can use this to map your custom fields as well.

Update Rollup 6 for Microsoft Dynamics CRM

Update Rollup 6 for Microsoft Dynamics CRM 4.0 (KB 970148)

Microsoft has released Update Rollup 6 for Microsoft Dynamics CRM 4.0. The update includes bug fixes and performance enhancements packaged together for easy deployment.

Update Rollup 6 is available for all languages that are supported by Microsoft Dynamics CRM 4.0.

For more information about this release, see Microsoft Knowledge Base article 970148: Update Rollup 6 is available for Microsoft Dynamics CRM 4.0.

Important: This update is not necessary for Microsoft Dynamics CRM Online. All updates are installed automatically.
We recommend customers install the update rollup to keep your software performing at its best. Installing previous update rollups is not a requirement to installing the current rollup.

Wednesday, August 26, 2009

CRM Picklist and the StringMap Table (CRM 4.0)

If you have a look at the CRM base tables (eg: AccountBase) in MSCRM, you would notice that all columns of Picklist datatype seem to store Integer values. So from where exactly does CRM get the actual text value to be displayed on the UI?
Well, this comes from a not so well known table called 'StringMap'. MSCRM stores all related option values for any picklist in this table.

Here's the Structure of the StringMap Table:

Column Name
Primary ID of the record
Object Type Code of the entity
Schema Name of the picklist attribute
Integer value of the picklist option. This value is actually stored in the base tables.
Language Code
Organization ID
text value displayed within the picklist on the CRM UI
Order of the values in the picklist on the CRM UI
Last updated Timstamp (used during synchronization process)

Modifying the StringMap table to add new values is not recommended and can cause undesired results. In fact CRM does not support modifying any of the CRM tables directly. All updates are to be made via the CRM UI or Web Services.

CRM seems to store picklist values in 2 tables:
1. StringMap
2. Metadataschema.AttributePicklistValue
(This seems to be used as a temporary table)

Whenever an option value is added/edited/deleted for any picklist and saved from the UI, CRM updates the METADATASCHEMA.AttributePicklistValue table. Upon publish of the Form the values are pushed into the StringMap table based on the values in the above table.
Hence directly updating the StringMap table via Sql with new values won’t suffice, since CRM will replace the values in DB with the new values, when the Entity is published via the UI.

There are 2 possible ways to edit picklist values:
1. The Metadata Service (The only SUPPORTED way to add/edit/delete picklist values)
2. Modifying the Entity xml. (This requires editing the xml file for an entity)(UNSUPPORTED)

Newly created values for any system picklist will have a value of 200000 or greater. This is to maintain backward compatibility during upgrades from CRM 3.0. This ensures that upto 199999 values for any picklist from CRM 3.0 will be successfully transferred during an upgrade to CRM 4.0
Note: Newly created values for any custom picklist would however start from 1.

Below is an example of both the options:
Option 1:

//Example for creating an option via the Metadata Service:
CrmLabel crmLabel = new CrmLabel();
LocLabel englishLabel = new LocLabel();
CrmNumber langCode = new CrmNumber();
// Set lang code as English
langCode.Value = 1033; 
englishLabel.LanguageCode = langCode;
englishLabel.Label = "New Value";
crmLabel.LocLabels = new LocLabel[] { englishLabel };
//Create the Insert Request
InsertOptionValueRequest insertRequest = new InsertOptionValueRequest();
//Set the Entity Name
insertRequest.EntityLogicalName =;
//Set the Attribute Name
insertRequest.AttributeLogicalName = "customertypecode";
insertRequest.Label = crmLabel;
insertRequest.Value = new CrmNumber();
insertRequest.Value.Value = 200000;
//Execute the Insert Request
InsertOptionValueResponse insertResponse = (InsertOptionValueResponse)metadataService.Execute(insertRequest);

//Example for deleting an option via the Metadata Service:
//Create the Delete Request
DeleteOptionValueRequest deleteRequest = new DeleteOptionValueRequest();
//Set the Entity Name
deleteRequest.EntityLogicalName =;
//Set the Attribute Name
deleteRequest.AttributeLogicalName = "customertypecode";
//Declare the Attribute Value
deleteRequest.Value = 200000;
//Execute the Delete Request
DeleteOptionValueResponse deleteResponse = (DeleteOptionValueResponse)metadataService.Execute(deleteRequest);

Option 2

Export the required entity. (MSCRM-Settings-Customization-Export Enitities) OR use the ExportXmlRequest and ExportXmlResponse if you want to perform the export via web services.
Open the file within an xml Editor.
Search for the attribute schemaname you want to update.
You should find something similiar to the text given below
                <option value="1">
                   <label description="Buyer" languagecode="1033" />
                <option value="2">
                    <label description="Seller" languagecode='1033" />
An additional option node would need to be created for every new option you want to add.
Eg :
          <option value="200000">
                    <label description="Re-Seller" languagecode="1033" />

You can use the ImportXmlRequest/ ImportXmlResponse and PublishXmlRequest/ PublishXmlResponse classes to publish the xml back into CRM.