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
6 responses so far ↓
Ibrahim // March 26, 2009 at 12:21 am |
I find if you select the records from the filtered view it would be much easier:
SELECT LastName, FirstName, ShoeSize
FROM Contact
osubrenden // March 26, 2009 at 9:03 pm |
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.
mark allen // April 2, 2009 at 11:01 pm |
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
Matthew // September 4, 2009 at 6:53 pm |
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.?
Masahiro // December 15, 2009 at 9:38 am |
GJ!
It helped my task!
C. List // January 24, 2010 at 1:41 pm |
…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