CRM 2011 StringMap Table

Fields in a CRM entity record are often based on a pick list. Should you need to query the SQL database directly to extract information these will come out not as the text based descriptive values, but a code such as 0, 1, or 2. To retrieve the associated descriptive information, a join to the StringMapBase table is required. A rough SQL query might be something like so:

select AttributeName, AttributeValue, Value from StringMapBase
where LangId = 1033 and AttributeName = 'TheNameOfTheAttribute'

This works fine for most items, but some such as statecode are used in multiple entities. A join to the MetaDataSchema.Entity table to get the ObjectTypeCode will filter down the records. If you do not the following will result:



Say you wanted to retrieve the values for statecode for the Opportunity entity, you would write query such as this:

select ObjectTypeCode, EntityId, Name from MetadataSchema.Entity
where Name = 'Opportunity' and OverwriteTime = '1900-01-01'

The reason for the date filter is that CRM maintains a history of changes to the entities. If a customisation solution has been applied to your CRM instance, more than one record will exist for some entities.

Joining the two queries together:

select t1.AttributeName, t1.AttributeValue, t1.Value
from StringMapBase t1
inner join MetadataSchema.Entity t2 on t1.ObjectTypeCode = t2.ObjectTypeCode
where t2.Name = 'Opportunity' and t1.AttributeName = 'statecode' and t2.OverwriteTime = '1900-01-01'

The resulting data set looks much better:



The LangId filter in all the queries is simply filtering for the English language – this is needed for multi-lingual systems.

Advertisements

About John Winford

Based in Vancouver, Canada I’m an IT professional with a number of specialties. First and foremost I am the front-line between the business users I enable and the technical team I represent. Not content to simply push the paper I also get my ‘hands dirty’ when required. I have an extensive amount of experience with technical project management, ERP implementations, BI work, and development across SharePoint, CRM and generic Widows applications.
This entry was posted in CRM. Bookmark the permalink.

2 Responses to CRM 2011 StringMap Table

  1. In case you will use StringMap or StringMapBase instead of FilteredStringMap you will get an error during the execution of a report if user doesn’t have sysadmin rights.

  2. Warren Caulton says:

    Thanks for this post. I was trying to discover the values and associated text for industrycode. I was floundering with the MetadataSchema tables trying to get a join that would work, Then I did more web searches and found this post. Thanks

    This is my query
    select AttributeName, AttributeValue, Value, ObjectTypeCode from StringMapBase
    where LangId = 1033
    AND AttributeName in (‘industrycode’, ‘customertypecode’)
    GROUP BY AttributeName, AttributeValue, Value, ObjectTypeCode
    ORDER BY ObjectTypeCode, AttributeName, Value, AttributeValue

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s