Monday, July 17, 2017

Dynamics CRM–Where is Status transition data stored in the DB

I was curious as to how status transition data is stored in the database for CRM and here is what I found:

The data is stored in the AttributePicklistValue table in the field “TransitionData” as string that represents xml

The XML looks like this:

<allowedtransitions xmlns="http://schemas.microsoft.com/crm/2009/WebServices">
     <allowedtransition sourcestatusid="803000000" tostatusid="803000001" />
     <allowedtransition sourcestatusid="803000000" tostatusid="803000002" />
     <allowedtransition sourcestatusid="803000000" tostatusid="2" />
</allowedtransitions>

Here is some SQL that allows you to pull the data.

SELECT DISTINCT
        e.LogicalName,
        a.LogicalName,
        sm.Value,
        apv.State_Status_Value,
        apv.TransitionData
FROM Entity e
      JOIN Attribute a ON a.EntityId = e.EntityId
      JOIN AttributePicklistValue apv ON apv.OptionSetId = a.OptionSetId
      JOIN StringMap sm ON sm.ObjectTypeCode = e.ObjectTypeCode
                           AND sm.AttributeValue = apv.value
WHERE LEN(apv.TransitionData) > 0
ORDER BY e.LogicalName,
          a.LogicalName,
          sm.Value,
          apv.State_Status_Value,
          apv.TransitionData;

Here is a query that can pull back the OptionSets and their corresponding values

SELECT o.Name,
        v.Value,
        l.Label
FROM AttributePicklistValueAsIfPublishedView v
      INNER JOIN LocalizedLabelAsIfPublishedView l ON v.AttributePicklistValueId = l.ObjectId
                                                      AND l.ObjectColumnName = 'DisplayName'
      INNER JOIN OptionSetAsIfPublishedView o ON v.OptionSetId = o.OptionSetId
WHERE o.name LIKE 'opportunity_%'
ORDER BY o.Name;


Finally, the correct way to get at this data is via the service and here is the sample code: Retrieve valid status transitions https://msdn.microsoft.com/en-us/library/dn689060.aspx

No comments: