Get Entity/Attribute's Display Name from CRM database

The Display Name for CRM Entity/Attribute is always a special case. In CRM 3.0, the Display Name is saved in the table: OrganizationUIBase, column: FieldXml. To get the Display Name for each attributes isn't an easy job. My approach was transfer the FieldXml column(NVARCHAR) into XML type, then get data from there. Here's the code I'd like to show about how to get the Display Name from CRM 3.0 (I suppose that you only want to see entity Account and Contact):

-- Get the display name from xml field
USE [Contoso_MSCRM]
GO
SELECT CONVERT(XML, REPLACE(CONVERT(NVARCHAR(MAX), O.FieldXml),'' ,'')) AS XmlField
INTO #temp1 FROM OrganizationUIBase O
WHERE NOT EXISTS(SELECT 1 FROM OrganizationUIBase WHERE Version>O.Version AND ObjectTypeCode=O.ObjectTypeCode)
SELECT DISTINCT
   t2.x.value('(../../@objecttypecode)[1]','int') AS ObjectTypeCode,
   t2.x.value('(../../@name)[1]','nvarchar(100)') AS EntityName,
   t2.x.value('@name', 'nvarchar(50)') AS AttributeName,
   t2.x.value('(displaynames/displayname/@description)[1]','nvarchar(100)') AS DisplayName
INTO #temp2
FROM #temp1 AS t1 CROSS APPLY t1.XmlField.nodes('/entity/fields/field') AS t2(x)

-- Join the metadata database
USE [Contoso_METABASE]
GO
SELECT
   Entity.Name AS EntityName,
   Attribute.Name AS AttributeName,
   #temp2.DisplayName AS AttributeDisplayName,
FROM Attribute
   INNER JOIN Entity ON Attribute.EntityId = Entity.EntityId
   INNER JOIN #temp2 ON #temp2.AttributeName = Attribute.Name AND #temp2.ObjectTypeCode = Entity.ObjectTypeCode
WHERE EntityName IN ('Account', 'Contact')
ORDER BY  EntityName, AttributeName

DROP TABLE #temp1
DROP TABLE #temp2

In CRM 4.0, because it supports multi languages, so the database has been re-designed: the FieldXml field has been abandoned. Instead, Microsoft uses a new table: LocalizedLabelView to save the Entity/Attribute's Display Name, it's much easy to get the Display Name, same example here (English version, the LanguageId is 1033):

USE Contoso_MSCRM
GO

SELECT  EntityView.Name AS EntityName, LocalizedLabelView_1.Label AS EntityDisplayName,
       AttributeView.Name AS AttributeName, LocalizedLabelView_2.Label AS AttributeDisplayName
FROM    LocalizedLabelView AS LocalizedLabelView_2 INNER JOIN
       AttributeView ON LocalizedLabelView_2.ObjectId = AttributeView.AttributeId RIGHT OUTER JOIN
       EntityView INNER JOIN
       LocalizedLabelView AS LocalizedLabelView_1 ON EntityView.EntityId = LocalizedLabelView_1.ObjectId ON
       AttributeView.EntityId = EntityView.EntityId
WHERE   LocalizedLabelView_1.ObjectColumnName = 'LocalizedName'
 AND LocalizedLabelView_2.ObjectColumnName = 'DisplayName'
 AND LocalizedLabelView_1.LanguageId = '1033'
 AND LocalizedLabelView_2.LanguageId = '1033'
 AND EntityView.Name IN ('Account','Contact')
ORDER BY EntityName, AttributeName

FilteredView and CrmImpersonator?!

I was curious about how to use FilteredView in the CrmImpersnator class for a IFD(Internet-Facing Deployment) solution? Thanks to David Jennaway who gave me a clue.

Ok, the reason for that is because I'm building a CRM dashboard for an On-Premise/IFD CRM deployment. In order to use the Web services from an APSX page, I have to use the Microsoft.Crm.Sdk.CrmImpersonator class, however by doing that it's not possible to get relevant data from the FilteredView, because the CrmImpersnator() will authenticate as [NT AUTHORITY\SYSTEM] account(which is a system account in CRM).

The solution is to use SQL Execute As statement, see the sample code below:

using (new CrmImpersonator())
{
........
    string username = "domain\\Guest";
    string sqlQuery = "SELECT name FROM FilteredAccount";
    string queryString = "GRANT IMPERSONATE ON USER::[NT AUTHORITY\\SYSTEM] TO [" + username + "] EXECUTE AS USER='" + username + "' " + sqlQuery + " REVERT";

........
}

In this example, you have to give user the Impersonate permission first, then use Execute As statement to impersonate the user.
After running the code, if you have a look the CRM database, under the Security\Users folder, the impersonated user account is added in the list, and it grant the 'Impersonate' permission to user [NT AUTHORITY\SYSTEM], see picture below:

By using this technic, you also need to map the user [NT AUTHORITY\SYSTEM] to the CRM database(e.g: Contoso_MSCRM).

Use Shared Data Source for custom CRM report

Recently I have been asked to modify some reports. In my current project we have 4 environments (Development, Training, UAT, and Production) and we have custom reports for each environment. The reports for each environment are different because the data source is different. So the deploy administrator complain about numbers of report. They come and ask me if there is a way to have one report for all environments.

So this is how I managed to do it:

If you have a look the CRM reports in report manager, there is a Data Source call: MSCRM_DataSource
I have noticed that actually all CRM reports use this data source, so why can’t I use it for our custom reports? I think it shouldn’t be a problem.



In Visual Studio, open the report project, Add a Shared Data Sources call: MSCRM_DataSource, it is important to keep it as same as CRM’s.
The connection string just uses the development environment, e.g.: Data Source=crm;Initial Catalog=org_MSCRM



Once finish it, upload the report rdl file by Report Manager, e.g http://crm/reports
Notice that you shouldn't upload it from CRM directly because CRM doesn’t support this technique. (You may get an error if you do it from CRM: Object reference not set to an instance of an object.)

So do it from Report Manager. After I upload the report, it appears in CRM Report area. That's good, however I can't edit the report! The error message is: String was not recognized as a valid DateTime.

Microsoft actually has a KB (920125) for this error: http://support.microsoft.com/kb/920125. The KB says: "This problem occurs because the Microsoft SQL Server Reporting Services report contains functionalities that are specific to Microsoft Dynamics CRM. For example, the Microsoft Dynamics CRM Pre-filtering functionality is specific to Microsoft Dynamics CRM."

Ok, I see. I remember the default CRM pre-filtering function is 'modified on' which is a datetime data type. If you upload the report from CRM, the report property may different with what you do from Report Manager.

So where is the pre-filtering values saved? If you open SQL Server Management Studio, expend ReportServer database, take a look the Catalog table. All reports information is saved in this table. I noticed there is a property element for all CRM reports: <ModifiedOnDateTime>2008-03-29T17:59:15</ModifiedOnDateTime>

It is a datetime data type and it doesn’t exist in the custom report! Also I noticed that the <OriginalName> element needs to be added as well to make the CRM Download Report function working properly.

So, this is how I add these elements into the custom report, the SQL query needs to run against the ReportServer database:

DECLARE @ReportName AS varchar(50)
SET @ReportName = 'My CRM Report' -- Change to the report name

DECLARE @ModifiedDate AS datetime
SET @ModifiedDate =(SELECT ModifiedDate FROM Catalog WHERE Name = @ReportName)

UPDATE Catalog
SET Property = REPLACE(CONVERT(varchar(MAX), Property), '</Properties>',
   '<ModifiedOnDateTime>'+ CONVERT(char(19), @ModifiedDate, 126) +'</ModifiedOnDateTime><OriginalName>'+ @ReportName +'.rdl</OriginalName></Properties>')
WHERE (Name = @ReportName)

It works like a dream after run a iisreset on the CRM server. Now I can edit the report without any problem!

By using this technique the advantage is: all environments will share the default CRM data source, developers don’t have to create different copies of report for each environment.

Automatically resolve e-mail sender to selected contact emailaddress1. Part I

We faced two problems in CRM 3.0 Email, the first problem was:
When an email(senderA@example.com) sent from outside world into CRM system, if sender's email address isn't in the system, then CRM will ask you to associate it with an existing record(only associate with a Contact record in our case). But once you select a contact, it looks associated, but it doesn't update the Contact's email field. You have to open the Contact record and manually copy the sender's email address into Contact's email field.

Regarding Microsoft KB: 922116, it is by design! Our requirement is make it automatically copy the sender's email address into the assocaited Contact.emailaddress1 field.

To achieve that, I made an unsupported change(again? :))

/*
Automatically resolve e-mail sender to selected contact emailaddress1
Microsoft KB: 922116, http://support.microsoft.com/kb/922116
\_controls\PartyList\resolve.aspx
*/

function applychanges()
{
    //update cotnact emailaddress1
    if((document.getElementById("crmExistingLookup").DataValue != null)
    &&(document.getElementById("crmExistingLookup").DataValue[0] != null)
    &&(document.getElementById("crmExistingLookup").DataValue[0].type == 2))
    {
        var eml = document.getElementById("txtName").value;
        var cid = document.getElementById("crmExistingLookup").DataValue[0].id;
        var connection = new ActiveXObject("ADODB.Connection");
        var connectionString = "Provider=SQLOLEDB; Server=_db; Database=_mscrm; Integrated Security=SSPI";

        connection.Open(connectionString);
        var sql = "UPDATE FilteredContact SET emailaddress1='" + eml +"'WHERE contactid = '" + cid + "'" ;
        rs = new ActiveXObject("ADODB.Recordset");
        rs.Open(sql, connection, 1, 2);
        
        connection.Close();

    }

    ....

}

Accessing CRM database from JScript

Here's the code how to accesss CRM database via JScript!

//initial connection
var connection = new ActiveXObject("ADODB.Connection");
var connectionString = "Provider=SQLOLEDB;Server=_db;Database=_mscrm;Integrated Security=sspi";
//open connection
connection.Open(connectionString);
//query string
var query = "SELECT name FROM FilteredAccount";
//create a ADO object
var rs = new ActiveXObject("ADODB.Recordset");
//open connection
rs.Open(query, connection, 1, 2);
rs.moveFirst();
var values = "";
//read data
while (!rs.eof)
{
    values += rs.Fields(0).Value.toString() + " ";
    rs.moveNext();
}
//close connection
connection.Close();
//alert value
alert(values);