How to get Microsoft CRM 2011 field attributes

How to get CRM field attributes

Say you need to know what all the Required Fields are or list of other field attributes like MaxLength or MinValue, for a set of entities in Microsoft Dynamics CRM 2011, you could open each entity one at a time and then open each field attribute to determine its requirement level but if you have a large solution that process is time consuming and tedious.

A quick simple solution is to query the MetadataSchema.Attribute and the MetadataSchema.Entity found  in the Microsoft Dynamics CRM 2011 Organization database.  Using the SQL I have provided below you can get a listing of just about anything you might want to know about your solutions entity attributes and field attributes.

 

SELECT MetadataSchema.Attribute.Name,   MetadataSchema.Attribute.AttributeRequiredLevelId,   MetadataSchema.Attribute.PhysicalName, MetadataSchema.Attribute.Length,

MetadataSchema.Attribute.MaxLength,   MetadataSchema.Attribute.DefaultValue,   MetadataSchema.Attribute.VisibleToPlatform,   MetadataSchema.Attribute.IsPKAttribute,

MetadataSchema.Attribute.PrecisionValue,   MetadataSchema.Attribute.MinValue, MetadataSchema.Attribute.MaxValue,   MetadataSchema.Attribute.AttributeId,

MetadataSchema.Entity.Name,   MetadataSchema.Entity.OriginalLocalizedName

FROM    MetadataSchema.Entity INNER JOIN

MetadataSchema.Attribute ON   MetadataSchema.Entity.EntityId = MetadataSchema.Attribute.EntityId

 

 

Display a CRM 4.0 PickList Display-Value in Reports and Queries

Microsoft CRM 4.0 stores picklist display values within the MetadataSchema in the StringMap table.  When creating custom reports or quieries a custom Scaler-Function is needed to show the picklist display vale.  To do this I wrote a simple function shown below called MapPickList that can be called in a SQL query to return a picklist displayvalue.

This example shows the CRM Contact table with a custom attribute called ShoeSize

SELECT     LastName, FirstName, dbo.MapPickList(‘contact’, ‘ShoeSize’, ShoeSize) AS DisplayValue

FROM  Contact

The output would look like this:

LastName            FirstName           DisplayValue
—————————————————————-
Smith                    Mark                     12.5
Estes                     Lowell                   10
Sue                        Jones                    7

And here is the SQL function.

CREATE FUNCTION MapPickList

(

/******Name of the CRM Entity we are querying******/

@entity varchar(50),

/******Name of the CRM Attribute we are querying******/

@attribute varchar(50),

/******The picklist int value stored by CRM******/

@value int

)

RETURNS varchar(500)

BEGIN

DECLARE  @return varchar(500)

SET @return = (SELECT StringMap.Value AS DisplayValue

FROM StringMap INNER JOIN MetadataSchema.Entity ON StringMap.ObjectTypeCode = MetadataSchema.Entity.ObjectTypeCode

WHERE (StringMap.AttributeName Like @attribute) AND (StringMap.AttributeValue = @value) AND (MetadataSchema.Entity.Name Like @entity))

return @return

END