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 7And 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
I find if you select the records from the filtered view it would be much easier:
SELECT LastName, FirstName, ShoeSize
FROM Contact
Hi Ibrahim, I’m glad you commented because it reminds me to mention why I didn’t use a filtered view which is the Microsoft recommendation for reporting. Filtered views in complex custom CRM application with large numbers of custom entity’s have very high overhead and are not efficient at all. This function offers a way to get a sleek very efficient resultset.
Hi
I think i’m having this issue.
I created a new entity, and when i use excel to query the table, MS Query, it shows the integer value of the picklist and not the description. how can i get around this?
thanks
mark
Thanks, this helped a lot!
A somewhat related question – when you have a duration field in CRM, CRM stores this value in minutes, whereas, from the front end it displays it as “15 minutes”, “30 minutes”…”1 day”, “2 days”, etc.
I’m using SQL Reporting Services to develop a report. Is there a way I can convert the duration field in CRM (in minutes) to “15 minutes”, “30 minutes”, “1 day”, “2 days” etc.?
GJ!
It helped my task!
…Well I’m a little late to the party here, but I just wanted to say that I agree with your method, and I came to the same conclusion; that a function like this is need – on my own. I call mine “dbo.f_GetPicklistValue” – but the parameters and method are the same. I also created the reverse function;
dbo.f_GetPicklistAttributeValue
…which takes a string as the last parm and returns the integer stored in the database for the picklist, allowing me to do something like…
SELECT LastName, FirstName
FROM Contact
WHERE ShoeSize = dbo.f_GetPicklistAttributeValue(‘contact’, ‘shoesize’, ’9.5′)
Cheers,
Chris List
What’s the point of using ‘like’ in the query instead of ‘=’?
This does not work on Account sadly
I have a custom field named new_claimsystem
SELECT
dbo.MapPickList(‘account’, ‘new_claimsystem’, New_ClaimSystem) AS ClaimSystem
FROM Account
Gives error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
FYI added:
AND (MetadataSchema.Entity.CustomizationLevel > 0)
now it works fine