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
Description
StringMapId
Primary ID of the record
ObjectTypeCode
Object Type Code of the entity
AttributeName
Schema Name of the picklist attribute
AttributeValue
Integer value of the picklist option. This value is actually stored in the base tables.
LangId
Language Code
OrganizationId
Organization ID
Value
text value displayed within the picklist on the CRM UI
DisplayOrder
Order of the values in the picklist on the CRM UI
VersionNumber
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 = EntityName.contact.ToString();
//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 = EntityName.contact.ToString();
//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
                <options>
                <option value="1">
                  <labels>
                   <label description="Buyer" languagecode="1033" />
                  </labels>
                </option>
                <option value="2">
                  <labels>
                    <label description="Seller" languagecode='1033" />
                  </labels>
                </option>
              </options>
An additional option node would need to be created for every new option you want to add.
Eg :
          <option value="200000">
                  <labels>
                    <label description="Re-Seller" languagecode="1033" />
                  </labels>
                </option>

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


4 comments:

  1. CRM is now a mature market with enterprise vendors offering end-to-end integrated solutions. Hence, the main focus for our CRM offering is the customer and how to bring more value to the end-user. Pegasys CRM services comes in all shapes and sizes from extensions to Microsoft Office over a call center to high-end opportunity management and selling tools for increasing effectiveness and efficiency.Thanks. CRM Services

    ReplyDelete
  2. Hi,
    Is it possible to set the DisplayOrder field
    of the StringMap table using the supported way? (Option 1).
    Thanks in advanced
    Daniele Barbini

    ReplyDelete
  3. Thanks for the sharing of such information.This is a great stuff of reading. I will pass it on to our audience. Thanking you,CRM Services

    ReplyDelete
  4. Thanks for the sharing of such information.This is a great stuff of reading. I will pass it on to our audience. Thanking you,CRM Services

    ReplyDelete