Thursday, 9 May 2013

How to get OptionSet values using SQL?

Obtaining a list of option values for a MS CRM 2011 OptionSet is pretty simple, especially if you plan to do this via the CRM Web service API. However a requirement may arise where you may want to do this using direct SQL against the Organization database.

Example Background

By default on the account entity there are several address fields, and one in particular is the Address Type picklist which is used to categorize the type of address stored for this account. Using this field as an example, the following SQL Query can be used to obtain a list of all options available to that field:

SQL Statement
  2. EV.LogicalName as Entity,
  3. ALV.Name as Attribute,
  4. LLV.Label,
  5. APV.Value
  6. FROM LocalizedLabelView as LLV
  7.     INNER JOIN AttributePicklistValueView as APV ON LLV.ObjectId = APV.AttributePicklistValueId
  8.     INNER JOIN OptionSetView as O ON APV.OptionSetId = O.OptionSetId
  9.     INNER JOIN AttributeLogicalView as ALV ON O.OptionSetId = ALV.OptionSetID
  10.     INNER JOIN EntityView as EV ON ALV.EntityId = EV.EntityId
  11. WHERE
  12.     ALV.Name like '%address1_addresstypecode%' AND EV.LogicalName = 'account'
  13. ORDER BY EV.LogicalName, ALV.Name, APV.Value

SQL Results

Enjoy :)

No comments:

Post a Comment

Action Microsoft.Crm.Setup.Common.Analyzer +CollectAction failed. Fatal error during installation

When installing the Srs Data Connection (Microsoft Dynamics CRM Reporting Extensions), you may have experienced the following error: ...