At times we get requirement to query and get option set items from CRM and show them in a report or in some custom application. Then we query to SQL MSCRM Organization database to get the pick list values but we get values as 0, 1, 2 and 1000001 etc.. but where we need to show the text values.
To retrieve the associated descriptive information, we need ajoin to the StringMapBase table.
LANGID = 1033 AND ATTRIBUTENAME = ‘statecode’
This works absolutely fine for the unique optionsets but not for StateCode. For statecode, we need to have a condition on objecttypecode. Here is the query to get the objecttypecode of the entity.
NAME = ‘Opportunity’ AND OVERWRITETIME = ‘1900-01-01’
The reason for date filter is CRM maintains a history of changes to the entities. If a customization solution has been applied to your CRM instance, more than one record will exist for some entities.
Here is the query after joining.
METADATASCHEMA.ENTITY ent ON strMap.OBJECTTYPECODE = ent.OBJECTTYPECODE
ent.NAME = ‘Opportunity’
AND strMap.ATTRIBUTENAME = ‘statecode’
AND ent.OVERWRITETIME = ‘1900-01-01’
The LangId filter in all the queries is simply filtering for the English language – this is needed for multi-lingual systems.
Hope this helps.