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:
Post a Comment
Remember, if you want me to respond to your comment, then you need to use a Google/OpenID account to leave the comment.