CRM Mobile for Windows Mobile

CRM Mobile is always an interesting topic. Some people love it, some hate it.

I built up a Windows Mobile software company about 4 years ago when I study my master course in the University College London. It's quite successful and I'm proud of it so much. And I am a Windows Mobile Specialist since year 2005, in the same year I started working on Microsoft CRM, now I am a Microsoft Certificate Business Management Solutions Professional. So I am an expert on both products, quite comfortable. I'd like to talk about my thoughts about CRM Mobile in this post.

Microsoft hasn’t release CRM 4.0 Mobile software which I don't think they will in a short time. As you may know that Microsoft has a product called: Dynamics Mobile which supports Dynamics AX and NAV, and the good news is: it will support CRM 5 too. With Dynamics Mobile framework, ISVs can build up more and better Windows Mobile software for Microsoft Dynamics.

CWR Mobility is the most popular CRM Mobile provider, even C360 uses (and sales) CWR’s product: I think they are partners. I have to say this software is one of the most complicated CRM Add-Ons. So how good is it? I can tell you my opinion.

The Environment:

I have a fresh CRM 4.0 installed on a Windows Server 2003 R2, with SQL Server 2005(it can be use Virtual PC which needs setup ActiveSync with your mobile device).
I also have a Windows Mobile device (Orange HTC Touch, OS: WM6.0) which has been recently upgraded to Windows Mobile 6.1(WM6.1, the latest version of Windows Mobile OS)


The Server Side:

First you need to download Mobile CRM software from CWR website, install it on the server (it doesn't have to be installed on the CRM server; however my environment is all-in-one box). The installation process will create a Virtual Directory under Microsoft CRM folder on IIS, and a CWR Mobile CRM Website on IIS. It also creates two shortcuts: Mobile CRM Server Configurator and Mobile CRM Server Administrator

You need to run Mobile CRM Server Configurator and select your Organisation and License file (I have an Organisation call Contoso which can use a demo license which also downloadable from CWR website). The configuration process will create two databases just like CRM's: one Config database and one Organisation database.

If everything going well, open CRM, navigate to [Settings] section, you may see a new link call [CWR Mobile CRM] (because the configuration process also modified the CRM Sitemap), then you may import a Profile (a sample Sales profile can be found on CWR website). After that, you can add Users with this Profile.


The Windows Mobile Side:

You can either install Mobile client software through your PC (CWRDesktopInstaller.exe) or through the Device (CWRPDAInstaller.exe), the installation file can be found in the CWR Mobile CRM installation folder. You may follow the steps when first time running the software, it will initialize CRM Mobile on this mobile device and creates a CF database (for syncing data) on it.


Some problems I know:
a. There is an error: "Error while updating Sitemap" when run Mobile CRM Server Configurator;
b. The alert/error message doesn't make much sense, e.g. "......, see log file for details";
c. The interface of mobile device doesn't design very well.

As I said early, this software is one of the most complicated CRM Add-Ons, which I can see there are some problems, hopefully CWR will fix it soon. Frankly, I know the .Net CF, I think Microsoft should put more money and top exports on it. Mobile device isn't the same as PC(which is almost universal), it is quite hardware specialised and it has a long way to go. So if you are going to deploy CRM Mobile for your company, you may need an expert.

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.

The mysterious CRM Lookup (III)

5. Let's have a look another common used filtered lookup example: I want the regarding field is set to open cases.

a. CRM 3.0

/* set the regarding to open case : Form.onLoad() */
crmForm.all.regardingobjectid.lookuptypes = "112";
crmForm.all.regardingobjectid.lookuptypeIcons = "/_imgs/ico_16_112.gif";
 
/* only show the active cases : Form.onLoad() */
if (crmForm.ObjectId != null)
{
  crmForm.all.regardingobjectid.lookupbrowse = 1;
  crmForm.all.regardingobjectid.additionalparams = "fetchXml="
    + "<fetch mapping='logical'><entity name='incident'><all-attributes /><filter>"
    + "<condition attribute='statecode' operator='eq' value='0' />"
    + "</filter></entity></fetch>";
}

b. CRM 4.0

In 4.0, the above feature is not supported anymore which means not working any more, but we can still use the method which mentioned in my previous post. Set 'statecode' as a find column of the Case Lookup View, then add the following code to the entity's onLoad():

/* set the regarding to open case : Form.onLoad() */
crmForm.all.regardingobjectid.lookuptypes = "112";
crmForm.all.regardingobjectid.lookuptypeIcons = "/_imgs/ico_16_112.gif";
crmForm.all.regardingobjectid.additionalparams = 'search=Active';

All good, now we are changing the requirement to: I want the regarding field is set to open cases which owned by the current user!

Ok, this simple and clean approach will not work for this a little complicated search. We need to build up a fetch query first. It's easy to use advanced find:

Look for: cases
-Status Equals Active
-Owner Equals Current User'

And then click Find, we get results.

Now, how can we get benefit from this Advanced Find and get the FetchXml statements from the query? Thanks Ronald Lemmen who first find a trick, in the result page, paste it in the IE Address Bar:

javascript:prompt("", resultRender.FetchXml.value);

Then you will get a prompt window, the value is the FetchXml statements which we need.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="incident"><attribute name="title"/><attribute name="ticketnumber"/><attribute name="createdon"/><attribute name="incidentid"/><order attribute="title" descending="false"/><filter type="and"><condition attribute="statecode" operator="eq" value="0"/><condition attribute="ownerid" operator="eq-userid"/></filter></entity></fetch>

Thanks Adi Katz who found a brilliant way to do it, and George modified it slightly. Basicly it overwrites the code-behind function, which I think it could be a risk. However so far so good after three months since it has been first released, I will post updates if it occurs any issue.(There is a supported filtered lookup product from Michael Höhne which is not free but great product.)

You need to modify lookupsingle.aspx file in the path \CRMWeb\_controls\lookup\lookupsingle.aspx
Add the following codes:

 
<script runat="server">
protected override void OnLoad(EventArgs e)
{
    base.OnLoad(e);
    crmGrid.PreRender += new EventHandler(crmGrid_PreRender);
}
void crmGrid_PreRender(object sender, EventArgs e)
{
    if (crmGrid.Parameters["search"] != null && crmGrid.Parameters["search"].StartsWith("<fetch")) 
    { 
        crmGrid.Parameters.Add("fetchxml", crmGrid.Parameters["search"]);  
        crmGrid.Parameters.Remove("searchvalue");  
        this._showNewButton = false; 
    } 
}
</script>

And then you can filter it from entity's onLoad() just like what we did in CRM 3.0. Notice that we have pasted the FetchXml statements below(fetchStr).

/* set the regarding to open case which owned by current user : Form.onLoad() */
crmForm.all.regardingobjectid.lookuptypes = "112";
crmForm.all.regardingobjectid.lookuptypeIcons = "/_imgs/ico_16_112.gif";
 
var fetchStr = "<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="incident"><attribute name="title"/><attribute name="ticketnumber"/><attribute name="createdon"/><attribute name="incidentid"/><order attribute="title" descending="false"/><filter type="and"><condition attribute="statecode" operator="eq" value="0"/><condition attribute="ownerid" operator="eq-userid"/></filter></entity></fetch>";
crmForm.all.regardingobjectid.lookupbrowse = 1;
crmForm.all.regardingobjectid.additionalparams = "search=" + fetchStr;
 

That's it, now we reach the end. :)

The mysterious CRM Lookup (II)

4. Now, how can we set/filter the lookup content? Because CRM3 and CRM4 are so different in lookup, so the solution is different. Let’s see a common example: In the Account record, only show the account owned contacts in the Primary Contact (primarycontactid) lookup.

a. CRM 3.0

/* CRM 3.0: only show account owned contacts in the primarycontactid lookup : Form.onLoad() */
if (crmForm.FormType == 2 && crmForm.ObjectId != null)
{
    crmForm.all.primarycontactid.lookupbrowse = 1;
    crmForm.all.primarycontactid.additionalparams = "fetchXml="
    + "<fetch mapping='logical'><entity name='contact'><all-attributes /><filter>"
    + "<condition attribute='accountid' operator='eq' value='" + crmForm.ObjectId     + "' />"
    + "</filter></entity></fetch>";
}

b. CRM 4.0

As far as I know, there are two unsupported ways to do that. Because we don't need a complicated fetchxml in this case, we so could use the first approach:

1. Customize Contact entity, open Contacts Lookup View, click 'Add Find Column', add the Parent Customer (parentcustomerid), save and publish it.
2. Customize Account entity, put the following code into Form.onLoad() :

/* CRM 4.0: only show account owned contacts in the primarycontactid lookup : Form.onLoad() */
if (crmForm.FormType == 2 && crmForm.ObjectId != null)
{
    var name = crmForm.all.name.DataValue;
    crmForm.all.primarycontactid.additionalparams =  'search=' + name;
}

It's a nice approach which used the 'search' feature of the CRM lookup. When this parameter is specified it defaults the search string in the lookup dialog and applies the search when the dialog is opened.


It's great, now let's change the requirement:
How about: Only show the Parent Account (parentaccountid) owned contacts in the Primary Contact (primarycontactid) lookup.

We still need to repeat step (1), and then in the step (2):

/* CRM 4.0: only show parent account owned contacts in the primarycontactid lookup: Form.onLoad() */
FilterLookup = function(source, target)
{
    if (IsNull(source)  IsNull(target)) { return; }
    var name = IsNull(source.DataValue) ? '' : source.DataValue[0].name;
    target.additionalparams = 'search=' + name;
}

Also, we need to put the following code into parentaccountid.onChange():

/* CRM 4.0: only show parent account owned contacts in the primarycontactid lookup : parentaccountid.onChange() */
FilterLookup(crmForm.all.parentaccountid, crmForm.all.primarycontactid);

It's great too, now how about we add another requirement on the above example:
The Primary Contact (primarycontactid) should be automatically selected when this contact is the primary contact of the selected Parent Account (parentaccountid).

Although we could do it through AJAX, we can also do it through CRM 4.0 lookup field automatic resolutions technique. Thanks for Adi Katz, let's start from begin:

(1) Turn off the Parent Account (parentaccountid) "automatic resolutions in field" feature by double click the field on the Form.
(2) Put the following codes in Account.onLoad():

function OnAfterAccountSelect()
{
    var contactLookup  = crmForm.all.primarycontactid;
    if( contactLookup.DataValue != null ) {return;}
  
    contactLookup.AutoResolve = 1;
    var accountLookup = crmForm.all.parentaccountid;
    primaryContact = accountLookup.items[0].keyValues.primarycontactid;

    contactLookup.SetFocus();
    contactDiv = document.all.primarycontactid_d.getElementsByTagName("DIV")[0];
    contactDiv.innerText = primaryContact.value;
    contactLookup.Lookup( true , true , primaryContact.value , true );
}

function OnCrmPageLoad()
{
    crmForm.all.parentaccountid.attachEvent( "onafterselect" , OnAfterAccountSelect );
}

OnCrmPageLoad();

The second approach which can deal with some complicated conditions will be introduced in the next post. :)

The mysterious CRM Lookup (I)

1. As many of you already know that the CRM lookup field saves the GUID of the related entity. Let's have a deep view of the lookup field. When you create a relationship between two entities, one entity can reference the other entity through a lookup field. However it's not just create one attribute in the database, it means although you can only see one relationship created in CRM interface, there are some invisible attributes for internal/customizer use. Let's see an example, we know that we can reference some values from a lookup fields:

crmForm.all.regardingobjectid.DataValue[0].id;  // The GUID of the lookup.
crmForm.all.regardingobjectid.DataValue[0].name;  // The text value of the lookup.
crmForm.all.regardingobjectid.DataValue[0].typename;  // The entity type name.

But how CRM get those values? Actually when user open a CRM record, those attributes are downloaded from CRM database to the 'containers' which can be seen from entity's customization.xml, it's a complex structure which I don't want to explain in this post. So when you create a relationship between two entities, the CRM system will create more than 2 'containers'(attributes) in the database to keep other information about the lookup field(e.g. id, typename etc).

2. Set the default value for a lookup field, let's take a look at the special lookup field again: regardingobjectid
In many cases, the regardingobjectid is default to Account, but how can we change the default value to Contact?

In the onLoad() event, you can set the default attributes by using setAttribute method which is unsupported by Microsoft, however it's a standard XML DOM method.

Let's see some examples:

crmForm.all.regardingobjectid.setAttribute("lookuptypes", "1,2");  //only show account and contact
crmForm.all.regardingobjectid.setAttribute("lookuptypeIcons", "/_imgs/ico_16_1.gif :/_imgs/ico_16_2.gif");  //set the icons
crmForm.all.regardingobjectid.setAttribute("defaulttype", "2");  //default to contact

Instead of using setAttribute method, you can also use CRM method directly:

crmForm.all.regardingobjectid.lookuptypes = "1,2";
crmForm.all.regardingobjectid.lookuptypeIcons = "/_imgs/ico_16_1.gif:/_imgs/ico_16_2.gif";
crmForm.all.regardingobjectid.defaulttype = "2"; 

3. If you have a look of the URL when you open a lookup window, you may see how CRM calls a lookup:

/lookupsingle.aspx?class=ActivityRegarding&objecttypes=1,2,3,4&browse=0&ShowNewButton=1&ShowPropButton=1&DefaultType=0

lookupsingle.aspx has some parameters which can be referenced by our developers, those parameters are:

Objecttypes : Entity code, e.g. Objecttypes = "1, 2" //show account and contact
DefaultType : the default lookup entity, e.g. DefaultType = "2" //default to contact
Browse : bool, 0 = show the "Look for" bar; 1 = browse model, hide the "Look for " bar.
ShowNewButton : bool, 0 = hide the "New" button; 1 = show the "New" button.
ShowPropButton : bool, 0 = hide the "Properties" button; 1 = show the "Properties" button.

In an IFRAME or a ISV solution, if you don't want users to see the 'New' Button, you can just set the URL to:
/lookupsingle.aspx?class=ActivityRegarding&objecttypes=1,2,3,4&browse=0&ShowNewButton=0&ShowPropButton=1&DefaultType=0

It's cool so far! But how about if want to hide the 'New' button in CRM?
You can't just say: crmForm.all.regardingobjectid.ShowNewButton = 0; it doesn't work. But what you can do is in the onLoad() event, use attachEvent method to attach a setadditionalparams event for the regardingobjectid. Again, those methods are all unsupported customisations, however, those are widely used in the Web development.

/*
    Function: show/hide the 'New' button of lookup
    bShow = 0 : hide the New Button
    bShow = 1 : show the New Buton
*/
function NewButton(bShow)
{
    return function()
    {
        crmForm.all.regardingobjectid.AddParam("ShowNewButton", bShow);
    }
}
crmForm.all.regardingobjectid.attachEvent("setadditionalparams",NewButton(0));

Microsoft Dynamics Sure Step Methodology

I'm one of the first students in UK of the Microsoft Dynamics Sure Step Methodology, the instructor was Mr. Shaun Letley who is a brilliant guy works for Microsoft UK. So in the last year I had his Sure Step training course in Reading, United Kingdom. The course was very helpful; it does help me to have a better understanding of the project methodology.

The Microsoft Dynamics Sure Step Methodology is a comprehensive implementation methodology describing the processes and disciplines necessary to implement Microsoft Dynamics AX, Microsoft Dynamics CRM, Microsoft Dynamics GP, Microsoft Dynamics NAV and Microsoft Dynamics SL. The implementation of all these products has many similarities which is why the methodology has been structured as a general methodology with a product specific layer for each product. The general content is mostly the prescriptive phase by phase, activity by activity descriptions on how to carry out the implementation plus the Project Management discipline. The product specific content is typically represented as tools, templates and hyperlinks to product specific materials found outside the methodology.

The methodology is actually good for everyone in the project, not only for project manager/functional manager. As I'm a technical consultant, my major job is consultant/development a solution. I can still learn some helpful tips to make sure the project is going well. Also, the Sure Step provides a full process for both big and small projects so don't make misunderstanding about it. The Sure Step is actually suitable for all dynamics projects. It also provides a lot of templates which could be use in your project! Of cause you can still using your own templates with Sure Steps, it's flexible and powerful.

I strongly recommend the Sure Step methodology to all Dynamics people because it does make sure your project is well organised, delivery on time and easily maintained.

Take this link on PartnerSource to see the full details about Microsoft Dynamics Sure Step Methodology.

"You have exceeded the maximum number of 2000 characters in this field; it will be truncated."

When you create a campaign in CRM 4.0, the Office field(attribute name: objective) has a 2,000 characters limit. How to get rid of the limitation?

It's a system field which you can't change its MaxLength from CRM customisation interface, however, you could make change from the customisation.xml file.

What you need to do is: export the campaign entity as a xml file, open the field, search "objective", you may find it's property: 2000, change it as you like(In CRM 4.0 the ntext data type has the MaxLength limitation up to 100,000 which is much better than CRM 3.0). Then save the file, import into CRM, and publish it. It works as you wish.

Although it's an unsupported customisation, since CRM 4.0 does allow you extend the field length(using DMM), so I think it's safe and nearly supported. :)

By the way, this technique can be used in many places, not just for this attribute. It also works in CRM 3.0.

How to format a number field(integer) without showing commas?

There is an interesting topic on the CRM Forum about how to format an integer field without commas. For example, if you type: 123456 in an integer field, you may see 123,456 once it loses focus. CRM automatically add a ',' between 3 numbers. There's a global setting to get rid of it, however, it will get rid of all integer field format. How about if you just want to remove the format for just one field?

As you may know, MSCRM uses htc files to format the different type of input fields as same as email address etc, see my another post.

If you want to get the value of an attribute, in CRM we use: DataValue, e.g: crmForm.all.new_number.DataValue;
Notice that the DataValue is the real data saved in the database.

However, if you want to get the formatted value, you may use this:
crmForm.all.new_number.value;

So the trick is give the DataValue overwrites the value property.

Put the following code into the entity's onLoad() event, and the same code puts into the field's onChange() event.

if(crmForm.all.new_number != null && crmForm.all.new_number.DataValue != null)
{
 crmForm.all.new_number.value = crmForm.all.new_number.DataValue;
}

Enjoy it!

Add client-side Microsoft Office Word Spell Checker in CRM Email entity

There are many CRM spell checker add-ons on the Internet, most of those are free, but they are all server-side spell checkers.

Today let's introduce the new client-side Word Spell Checker which uses Microsoft Office Word spell check function. It does give users a familiar feeling and larger database. It also uses Word's spell check settings. So I think users will love it. It has been used for one of our clients live environment for half year, and it has great feedbacks.

As you can see, the client machine must have Microsoft Word installed for using this technique.(My clients are using Microsoft Office Word 2003, I'm not sure if it's compatible with Word 2007, please feel free to test this solution)

The file needs to be modified is: CRMWeb\Activities\email\edit.aspx

You may already recognized that it's an unsupported customization, so please make backups just in case your customization could damage the system and also could be overwrite by Hotfixs/Rollups.

Just simply add a function:

/*  Microsoft Office Word Spelling Check*/
 
function SpellCheck(field)
{
  window.frames[field].document.execCommand("Copy");
  textRange = window.frames[field].document.body.createTextRange();
  textRange.execCommand("Copy");
 
  try
  {
    var oWord = new ActiveXObject("Word.Application");
    oWord.Visible = false;
    oWord.Documents.Add();
    oWord.Top = -2000;
    oWord.Selection.Paste();
    oWord.ActiveDocument.CheckSpelling();
    oWord.Selection.WholeStory();
    oWord.Selection.Copy();
    oWord.ActiveDocument.Close(0);
    window.frames[field].focus();
    window.frames[field].document.execCommand("SelectAll");
    window.frames[field].document.execCommand("Paste");
  }
  catch(err)
  {
    alert("Error loading Microsoft Word Spelling Check: " + err);
  }
  finally
  {
    oWord.Quit(0);
  }
 
  alert("Spelling Check Finished!");
 
}

You also need to modify the isv.config.xml fie to call this function:

    <Entity name="email">
      <ToolBar ValidForCreate="1" ValidForUpdate="1">
        <Button Title="Spell Check" ToolTip="Spell Check" Icon="/_imgs/ico_18_home.gif" JavaScript="SpellCheck('descriptionIFrame');" />
      </ToolBar>
    </Entity>

What the function does is copy the text user typed in the Email body(descriptionIFrame), and paste it to a Word document, the document is unseenable because it has been moved out of the screen(oWord.Top = -2000) , then the function call Word.CheckSpelling() method to check the text just pasted. After correct all words, it will paste the whole text back to the Email body, and close the Word process.

Mail Merge function for custom entities upgraded from CRM 3.0

CRM 4.0 has a great function which provides the Mail Merge function for custom entities.

However, it only seems to work for those entities you created in CRM 4.0, it doesn't work for entities which upgraded from CRM 3.0.

There's a new flag in CRM 4.0 customizations.xml, calls: IsMailMergeEnabled which wasn't exist on CRM 3.0 customizations.xml. When we upgrade CRM from 3.0 to 4.0, it's supposed to be upgraded as well, but it doesn't.

I write a small command tool to do so, how to use the tool?

1. Export all entities from CRM 4.0, we may just call it: customizations.xml

2. Run the command line tool:

CrmTool.exe customizations.xml

3. What it does just loop all custom entities, and add the flag onto it. It saves time if you had lots of custom entities just as I do.

Enjoy it!

Download CrmTool.exe from MSDN