How to join dbo.LocalizedLabelView to get form labels in Dynamics CRM?

Question

In Dynamics CRM, I often get requirements from business users for reporting. Business users know and talk about object names and attribute labels. To write a query, I need to map them to entity names and attribute names. I would like to use a query to view this.

What will I join the dbo.LocalizedLabelView view to get the AttributeLabel column in the following query? I can't figure out what ObjectId should reference. (And if you can tell me how you understood the answer, I would be especially grateful!)

select [EntityName] = entityNames.Name, [EntityDisplayName] = entityDisplayNames.Label, [AttributeName] = attributeNames.PhysicalName, [AttributeDisplayName] = attributeDisplayNames.Label --[AttributeLabel] = attributeLabels.Label from dbo.EntityView entityNames inner join dbo.LocalizedLabelView entityDisplayNames on entityDisplayNames.ObjectId = entityNames.EntityId and entityDisplayNames.ObjectColumnName = 'LocalizedName' left outer join dbo.AttributeView attributeNames on attributeNames.EntityID = entityNames.EntityID inner join dbo.LocalizedLabelView attributeDisplayNames on attributeDisplayNames.ObjectId = attributeNames.AttributeID and attributeDisplayNames.ObjectColumnName = 'DisplayName' and attributeDisplayNames.LanguageID = entityDisplayNames.LanguageID --inner join dbo.LocalizedLabelView attributeLabels -- on attributeLabels.ObjectId = ????? -- and attributeLabels.LanguageID = entityDisplayNames.LanguageID where entityDisplayNames.LanguageID = 1033 order by entityDisplayNames.Label, attributeDisplayNames.Label 

Answer 1

ObjectId is a reference to the internal identifier of a thing in the CRM database. It could be an attribute, entity, label, or something else.

Since you need a shortcut for your attribute, use this id attribute as an ObjectId here. I think you want your join condition to look like this:

 inner join dbo.LocalizedLabelView attributeLabels on attributeLabels.ObjectId = attributeNames.AttributeID and attributeLabels.LanguageID = entityDisplayNames.LanguageID and attributeLabels.ObjectColumnName = 'DisplayName' 

If you want to describe an attribute, you can change the ObjectColumnName to Description.