MVP Award

I have been awarded Microsoft MVP - Dynamics CRM.
Thanks for Microsoft and my friends, colleagues and family. :)

See my MVP profile here: https://mvp.support.microsoft.com/profile/Jim.Wang

Customize the crmForm

There are some useful crmForm customization skills which I want to share:

1. Change a filed label style

/* change new_button field label style */
if (crmForm.all.new_button != null)
{
  var field = crmForm.all.new_button_c;
  field.style.fontWeight = 'bold'; // change font to bold
  field.style.fontSize = '12px'; // change font size
  field.style.color = '#ff0000';  //change font color
}

2. Replace a field to a button, and attach the onclick() event

/* replace new_button_d to a button */
if (crmForm.all.new_button != null)
{
  var field = crmForm.all.new_button_d;
  var html = "<table border='0' cellspacing='0' cellpadding='0'><tr><img width='32' height='32' style='cursor:hand' src='/_imgs/ico_32_134.gif' alt='Click button' onclick='Button_OnClick()' /></tr></table>";
  field.innerHTML = html;
}
Button_OnClick = function()
{
  alert("button clicked!"); 
}

3. Replace a field to a lable (use replaceNode())

/* replace new_button_d to a label */
if (crmForm.all.new_button != null)
{
  var html = document.createElement( "<TD id='new_button_d'>");
  html.innerText = "this is a lable";
  crmForm.all.new_button_d.replaceNode(buttonText);
}

4. Append text under a field (you don't need to create an attribute for that)

/* append text under new_button */
if(crmForm.all.new_button != null)
{
  var html= document.createElement( "<LABEL>");
  html.innerText = "this is a text field";
  crmForm.all.new_button.parentNode.appendChild(html);
}

Double Click EmailAddress to Open in Outlook

There is a question on Microsoft CRM Forum asking how to: Double Click EmailAddress to Open in Outlook

I think it's worth to bring it here because it's also demonstrate how to send parameters in attachEvent method, so here is the answer:

/* Double Click EmailAddress to Open in Outlook, put into entity.OnLoad */
function CreateEmail(emailAddress) 
{
    return function()
    {
        if (emailAddress != null && emailAddress.value.length > 0) 
        {
            window.navigate("mailto:" + emailAddress.value);
        }
    }
}

crmForm.all.emailaddress1.attachEvent('ondblclick', CreateEmail(crmForm.all.emailaddress1));
crmForm.all.emailaddress2.attachEvent('ondblclick', CreateEmail(crmForm.all.emailaddress2));
crmForm.all.emailaddress3.attachEvent('ondblclick', CreateEmail(crmForm.all.emailaddress3));

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).

Change CRM 4.0 website port number after installation

Microsoft has a KB to describe how to update the Microsoft Dynamics CRM Web site port after you install Microsoft Dynamics CRM 4.0

Today I had to change the port number from 80 to 5555, however after following those steps, I can't run CRM reports. In the event log, it says: Error 19968 Web service request ListExtensions to Report Server http://crm/ReportServer/ReportService2005.asmx failed. Error: Unable to connect to the remote server

I have noticed that the virtual directory: ReportServer and Reports are located under the CRM website, so these config information needs change too.

There are extra two steps if your SRS is running under the CRM website:

Regedit:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\SQLRSServerURL

Deployment Manager:
Disable Organization >> Edit Organization >> change the SRS Url >> Enable Organization.

CRM 4.0 upgrade: There is already an object named 'AttributeTypes' in the database.

This week I was helping a customer to upgrade their CRM 3.0 system to CRM 4.0.
The upgrade pre-check looks fine with all passed, no errors, no warnings.
But during the upgrade process, it first alert an error regarding C360, then I fix the problem, click the "Retry" button, it then generates another error:

" Microsoft.Crm.Setup.Server.InstallDatabaseAction failed.
System.Data.SqlClient.SqlException: There is already an object named 'AttributeTypes' in the database. "

That's very strange. But finally I found it's a design lack of upgrade process: The 'Retry' doesn't actually do 'Retry'!
So I had to:
1. Stop the upgrade process and uninstall CRM 4.0;
2. Restore CRM 3.0 databases, then install CRM 3.0 with existing databases;
3. Fix the problem which generated the first error;
4. Re-run the CRM 4.0 upgrade.

I did know there are some complains about C360 add-ons stop the CRM upgrade process, these could be the reasons:
1. The C360 uninstall doesn't do a clean uninstall all the time, so you may still see C360 icons on the CRM form, but no function;
2. Some C360 products create tables inside the CRM database which is not a recommended method, and again once you uninstall the product, the tables are still there.
3. C360 has a cleanup tool which does cleanup the remained infomation in the onLoad, onSave, and isv.cofig.xml etc. However, it doesn't cleanup the database. And the tool is hard to find on C360 website.

C360 provides some great add-ons for MSCRM, but we wish it could be better!

CRM 4.0 E-mail access type bulk setting tool

(Update: You can use CRM 4.0 Workflow to do it as well, it's up to you to choose which one you feel comfortable.)
Microsoft Dynamics CRM 4.0 doesn't provide a tool for CRM User's email access type bulk setting. So I write this application to helping CRM Administrators for this purpose. It takes me 2 hours work so don't expect too much, however it does work. ;-)


Usage:
1. Launch the application, type in the following information:
CRM Server(the CRM discover server address): e.g http://localhost:5555
Organisation(the Organisation's name, not Friendly name): e.g AdventureWorksCycle
SQL Server: e.g localhost
Database: e.g Adventure_Works_Cycle_MSCRM
CRM User: e.g CRMAdmin
CRM User's Password

2. Click the 'Load' button, it will list all enabled CRM users whom belonging to this organisation with their email settings.

3. Select users your want to modify, then select Incoming E-mail and Outgoing E-mail setting from the picklist. If you don't want to change both of those, then just select blank in the picklist.

4. Click the 'Update' button, it will update user settings you selected.

Download it here: http://code.msdn.microsoft.com/crm


Enjoy it!

Cheers,
Jim Wang
http://jianwang.blogspot.com
http://mscrm.cnblogs.com

CRM 4.0 E-Mail Router for hosted Exchange

I have a client who uses a hosted Exchange for their email system (also hosted AD) and my client uses CRM 4.0 On-Premise edition. So the CRM E-Mail Router solution is:

• Outgoing: local SMTP service, use local system account

• Incoming: hosted Exchange, use a service account which can access the remote Exchange mailbox

• Both Outgoing and Incoming are configured to use Email Router

When test the connection, the outgoing STMP works fine, however, the incoming Exchange get the following error:
"Incoming Status: Failure - The remote Microsoft Exchange e-mail server returned the error "(404) Not Found". This user or queue does not have a mailbox. Create a mailbox and try again. The remote server returned an error: (404) Not Found. "

It is strange because I'm sure the user has mailbox! Thanks for Tony Iadarola who is a senior engineer works for the hosting company , after analyzing the Exchange server logs, he pointed out that the CRM E-Mail router service try to query the mailbox by using the 'half' SMTP address!( which is not recommend, MS recommend either SamAccount or Legacy DN, or mailbox GUID is used)

e.g.: In our case, we have a test user in CRM, the user's SamAccount name is: TU341-NBG125, SMTP mailbox: TUser@domain.com which is the user's primary email address in CRM. The CRM E-Mail Router service should be making a request to Exchange mail server for the Test users mailbox using http://ExchangeMailServer/Exchange/TU341-NBG125 instead it is using http://ExchangeMailServer/Exchange/TUser

If I change the user's primary email address to TU341-NBG125@domain.com, the problem will disappear! However the email address doesn't mean anything to customers, so I can't change the email address.

Finally I found a workaround: edit ..\Microsoft CRM Email\Service\Microsoft.Crm.Tools.EmailAgent.xml
Search the test user section(within the tag: <ProviderConfiguration>), edit it's <EmailAddress> tag, change the email address from TUser@domain.com to TU341-NBG125@domain.com, then save the xml file, modify it's property to Read-Only, restart the CRM E-mail Router Service.(You have to make the file read-only first, otherwise the service will re-write the <EmailAddress> tag by using the user's primary email address)

The problem is solved, all users still use their normal email address, and the CRM E-Mail Router is able to create an Email activity for incoming emails with correct email address. However I think the CRM E-mail Router should be improved by query mailbox via SAM, instead of using SMTP address which may not unique for a hosted environment.



16/07/2008, add to my post: the service account needs to have permission to access 'Root' and 'Inbox' folder of user's mailbox. e.g. if the service account has such permission, it can find user's mailbox by using the 'half' SMTP address (http://ExchangeMailServer/Exchange/TUser ). However it's might be a problem for a hosted company who doesn't want to grand the permission for the service account.

CRM 4.0: Use JavaScript execute/call/launch CRM Workflow

I have a question from my colleague: How to use JavaScript execute workflow in CRM 4.0? The question also repeats very often in CRM Forums, no answer so far.

In CRM 3.0, Mitch Milam has described how to Launching a Workflow Rule from JavaScript, it works great. However, in CRM 4.0, the class: ExecuteWFProcessRequest has been deprecated, so it won’t work in CRM 4.0. Although there are many ways to launch a workflow, if you want to run it through JavaScript, here’s the trick:

/* the function */
ExecuteWorkflow = function(entityId, workflowId)
{
    var xml = "" + 
    "<?xml version=\"1.0\" encoding=\"utf-8\"?>" + 
    "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" + 
    GenerateAuthenticationHeader() +
    "  <soap:Body>" + 
    "    <Execute xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 
    "      <Request xsi:type=\"ExecuteWorkflowRequest\">" + 
    "        <EntityId>" + entityId + "</EntityId>" + 
    "        <WorkflowId>" + workflowId + "</WorkflowId>" + 
    "      </Request>" + 
    "    </Execute>" + 
    "  </soap:Body>" + 
    "</soap:Envelope>" + 
    "";

    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Execute");
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);
    var resultXml = xmlHttpRequest.responseXML;
    return(resultXml.xml);
}

/* call */
var theWorkflowId = "3FD2DD58-4708-43D7-A21B-F0F90A0AA9F2"; //change to your workflow Id
ExecuteWorkflow(crmForm.ObjectId, theWorkflowId);

Enjoy coding! :)

Failure: The full-text indexes on the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes

Last week I had a chance to help a London company to upgrade CRM 3.0 to 4.0. As you can see there are some warnings and error, I can easily fix all warnings instead of ignore them(in fact, most warnings are safe to ignored). The error is an interesting one, regarding Microsoft KB(927226), it’s a custom indexes error, when I check the installation log, it tells me the table name: DocumentIndex



C:\Documents and Settings\user\Application Data\Microsoft\Mscrm\Logs\crm40svrsetup.log
13:51:00| Info| The following full-text indexes are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes:
13:51:00| Info| tableowner: dbo
13:51:00| Info| tablename: documentindex
13:51:00| Info| fulltextkeyindexname: cndx_primarykey_documentindex
13:51:00| Error| remark: modified full-text index
13:51:00| Error|
13:51:00| Error| The full-text indexes on the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes.
13:51:00| Error| Check SqlFullTextIndexValidator : Failure: The full-text indexes on the Microsoft Dynamics CRM database are not consistent with Microsoft Dynamics CRM 3.0 full-text indexes.

I saw another post here which has the same error message. However I found that my case is slightly different, in fact I can’t see any full-text catalog against this table. So I just create a new ftcat_documentindex against DocumentIndex table(after compare with my VPC), then the error gone. :)
 
The full-text catalog is call: ftcat_documentindex, see below: