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

8 comments:

Renata said...

Thanks so much for this! I was trying to figure this out all day.

joon said...

i tried the same thing with same logic. what is the difference between the one i use and the one provided by you
select
E.Name AS EntityName,
L1.Label AS EntityDisplayName,
A.Name AS AttributeName,
L2.Label AS AttributeDisplayName
from MetadataSchema.LocalizedLabel L1, MetadataSchema.LocalizedLabel L2, MetadataSchema.Attribute A, MetadataSchema.Entity E
where
L1.LanguageId = '1033'
and ( L1.ObjectColumnName = 'LocalizedName' and L1.ObjectId = E.EntityId )
and L1.Label = 'Company'
AND L2.LanguageId = '1033'
and ( L2.ObjectColumnName = 'DisplayName' and L2.ObjectId = A.AttributeId )
and E.Name = 'Account'
and A.EntityId = E.EntityId
and A.CustomizationLevel = 1
ORDER BY EntityName, AttributeDisplayName

Anonymous said...

Hi, I'm trying to retrieve only the display names with jscript but couldn't manage to do it. Please tell me how can this be achieved.

Unknown said...

Keep up the fantastic piece of work, I read few blog posts on this web site and I believe that your site is real interesting and has lots of great information. ERP Software in Mumbai || System Software || CRM Software in Mumbai || MLM Software

Unknown said...

I really appreciate spending some time to talk about that, I believe firmly regarding this and so really enjoy understanding more about this kind of subject.This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something like this. CRM Software || MLM Software in Mumbai || ERP Software || System Software in Mumbai

Credit Loan Solution said...

My name is Leah Brown, I'm a happy woman today? I told myself that any loan lender that could change my life and that of my family after having been scammed separately by these online loan lenders, I will refer to anyone who is looking for loan for them. It gave me and my family happiness, although at first I had a hard time trusting him because of my experiences with past loan lenders, I needed a loan of $300,000.00 to start my life everywhere as single mother with 2 children, I met this honest and God fearing online loan lender Gain Credit Loan who helped me with a $300,000.00 loan, working with a loan company Good reputation. If you are in need of a loan and you are 100% sure of paying the loan please contact (gaincreditloan1@gmail.com) You can also whatsApp them at: at +44-75967-81743 (WhatsApp Only) .. and inform them Leah Brown addressed to you ..

yosabrams0918 said...

It is best to take part in a contest for among the finest blogs on the web. I'll suggest this website! gsn casino games

raynnowui21 said...

After research a couple of of the weblog posts in your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website record and will be checking back soon. Pls try my website as properly and let me know what you think. free online casino slots