Description:
In this example we explain that how to get Option set
values from SQL Server in an application outside dynamic CRM. Or how to fetch
the text value of the option set (Dropdown list) in Dynamic CRM.
There are many question raised when we are new in Dynamic CRM like
Where does Microsoft Dynamic CRM stores Option Set
values in SQL Server?
How to retrieve the values of an option set of dynamic
CRM?
Microsoft Dynamic CRM store Option Set values in the StringMapBase table. You will query via
object code, Attribute Name, Option Set Value, Language Id and then it will
returns the attribute value.
You can also create a one function in SQL Server that
will returns the attribute value so you can easily use it multiple time like
the below example
Function:
ALTER FUNCTION [dbo].[fn_new_GetStringMapValue]
(
@EntityName nvarchar(100),
@AttributeName nvarchar(100),
@AttributeValue int
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE
@Result nvarchar(4000)
SELECT
@Result = Value
from
StringMapBase t1
inner join MetadataSchema.Entity
t2 on t1.ObjectTypeCode
= t2.ObjectTypeCode
where
t2.Name =@EntityName
and t1.AttributeName
= @AttributeName AND
AttributeValue = @AttributeValue and t2.OverwriteTime = '1900-01-01'
RETURN
@Result
END
Here in the above function EntityNmae
is the name of the CRM entity like Lead,Opportunity etc..and AttributeName
define the coloumn name of the entity for which you want to get the Attribute
value and finally attribute value define the value of the attribute name.
How to call this function to get the option set value.
For example suppose you wants to get the LeadSource
Option set value of the Lead then you will easily perform the following query.
Select LeadSourceCode,dbo.fn_new_GetStringMapValue('Lead','LeadSourceCode',LeadSourceCode) as 'Lead Source' from
Lead
In above query ‘Lead’ is the EntityName
‘LeadSourceCode’ is the AttributeName and LeadSource means its value like 1,2,3
etc.. Defines the attribute value
Finally output of the above query like
SELECT
ReplyDeleter.Name,
AVG(CONVERT(FLOAT, r.Score))
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY s.Name ORDER BY s.Score) AS RecordNumber,
COUNT(*) OVER (PARTITION BY s.Name) AS NameRecordCount,
CAST((COUNT(*) OVER (PARTITION BY s.Name)) AS DECIMAL(9, 1)) / 2.0 AS MedianPoint,
COUNT(*) OVER (PARTITION BY s.Name) % 2 AS IsOdd,
s.Name,
s.Score
FROM
Demo AS s
) AS r
WHERE
(
r.IsOdd = 1
AND r.RecordNumber = CEILING(MedianPoint)
)
OR
(
r.IsOdd = 0
AND r.RecordNumber IN (MedianPoint, MedianPoint + 1)
)
GROUP BY
r.Name
;
SELECT CONVERT(FLOAT,
ReplyDelete(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score ASC) AS BottomHalf))
+ CONVERT(FLOAT,
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Demo ORDER BY Score DESC) AS TopHalf)
) / 2) AS Median
SQL 'LIKE' query using '%' where the search criteria contains '%'
ReplyDeleteselect new_probability,* from OpportunityBase where new_Probability is not null and new_probability LIKE '%\%\%' escape '\'