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:

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:

The reason for the date filter is that CRM maintains a history of changes to the entities. If a customisation solution has been…