27 March 2009

Get Entity/Attribute's Display Name from CRM database


The Display Name for CRM Entity/Attribute is always a special case. In CRM 3.0, the Display Name is saved in the table: OrganizationUIBase, column: FieldXml. To get the Display Name for each attributes isn't an easy job. My approach was transfer the FieldXml column(NVARCHAR) into XML type, then get data from there. Here's the code I'd like to show about how to get the Display Name from CRM 3.0 (I suppose that you only want to see entity Account and Contact):



-- Get the display name from xml field
USE [Contoso_MSCRM]
GO
SELECT CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), O.FieldXml),'' ,'')) AS XmlField
INTO #temp1 FROM OrganizationUIBase O
WHERE NOT EXISTS(SELECT 1 FROM OrganizationUIBase WHERE Version>O.Version AND ObjectTypeCode=O.ObjectTypeCode)
SELECT DISTINCT
t2.x.value('(../../@objecttypecode)[1]','int') AS ObjectTypeCode,
t2.x.value('(../../@name)[1]','nvarchar(100)') AS EntityName,
t2.x.value('@name', 'nvarchar(50)') AS AttributeName,
t2.x.value('(displaynames/displayname/@description)[1]','nvarchar(100)') AS DisplayName
INTO #temp2
FROM #temp1 AS t1 CROSS APPLY t1.XmlField.nodes('/entity/fields/field') AS t2(x)

-- Join the metadata database
USE [Contoso_METABASE]
GO
SELECT
Entity.Name AS EntityName,
Attribute.Name AS AttributeName,
#temp2.DisplayName AS AttributeDisplayName,
FROM Attribute
INNER JOIN Entity ON Attribute.EntityId = Entity.EntityId
INNER JOIN #temp2 ON #temp2.AttributeName = Attribute.Name AND #temp2.ObjectTypeCode = Entity.ObjectTypeCode
WHERE EntityName IN ('Account', 'Contact')
ORDER BY EntityName, AttributeName

DROP TABLE #temp1
DROP TABLE #temp2



In CRM 4.0, because it supports multi languages, so the database has been re-designed: the FieldXml field has been abandoned. Instead, Microsoft uses a new table: LocalizedLabelView to save the Entity/Attribute's Display Name, it's much easy to get the Display Name, same example here (English version, the LanguageId is 1033):



USE Contoso_MSCRM
GO

SELECT EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName
FROM LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN
AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN
EntityView INNER JOIN
LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON
AttributeView.EntityId = EntityView.EntityId
WHERE LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'
AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
AND LocalizedLabelView_1.LanguageId = '1033'
AND LocalizedLabelView_2.LanguageId = '1033'
AND EntityView.Name IN ('Account','Contact')
ORDER BY EntityName, AttributeName

21 March 2009

Introduce a Data Audit solution for Microsoft Dynamics CRM

I'm pleased to introduce a Data Audit solution for Microsoft Dynamics CRM:

What it does?
Data Audit add-on can record the fact: Who did What at When. For example: you want to audit changes for the field: Account.EmailAddress1, all you need to do is just 3 clicks! The add-on will record the entity name, audit field, record id, original data, modified data, modified time, modified by information. Those audit histories for this record will associate with the record(for applicable entities), furthermore, you can also see all audit histories in one place.

What it is?
It is an ISV solution to integrate to Microsoft Dynamics CRM seamlessly, with same interface and user experience.

What does it support?
Data Audit 1.0 supports both On-Premise and IFD deployment, Stand-Along and Web-Cluster server structure, and Multi-Tenants. It supports both system entity/attribute and custom entity/attribute.
* The 1.0 version of Data Audit supports 32bit English Version Microsoft Dynamics CRM 4.0.

How it works?
See this 2 minutes demo video:




Please email us to get an evaluation license(30 days full function).

MVP Summit 2009 @ Seattle - Meet the CRM MVPs

It's a great summit and nice to meet CRM team and MVPs.



Meet the famous CRM authors: Jim Steger and Mike Snyder



MVP Darren Liu and me eatting the Red King Crab...



Jim Wang with the coffee which made by the world's first Starbucks @ Seattle, US



Jim Wang with his baby Niu