Microsoft Dynamics CRM Compatibility List

Ever wanted to know if a specific product version or service pack are considered to be compatible and supported with CRM 4.0 or 2011? Microsoft have now released a new "living" KB article that describes the current support stance for most of the products you would consider using with CRM.

This is especially useful for situations where you want for example to know what latest Exchange Server major and minor versions are supported etc.

The article is here: KB2669061

Update Rollup 6 is now part of the installation files

Happy days!!!

As of the latest update (20/01/2013) to the downloadable server installation files for CRM 2011, the installation files now include CRM Update Rollup 6 (Build 05.00.9690.1992)... yay!

This will definitely help with the long winded multi-step patching process we have had to use up to now, especially when installing multiple front end servers.

You can find the installation files on the Microsoft Download Center here.

Infinite Loop Detection in CRM Workflows

Annoyingly, if you have a workflow that calls itself or a child workflow more than 7 times in an hour, you've probably runi in to CRM infinit loop detection. Basically, if CRM detects this happening it will step in and shut down the whole workflow.

However, you can chage the setting (provided of course that you know what you are doing and that your workflows are well written) to a greater number. You do this using the CRM Deployment Config Tool.

For example, if oyu need to up the number of loos from 7 to 20, you could use the following command:

microsoft.crm.deploymentconfigtool.exe workflowsettings update -maximumdepth:20

Happy testing!

Internet Explorer 8 and broken menus in CRM 4.0

** Please note, this issue is fixed un Update Rollup 9**

I noticed this one ages ago, but I've never really taken the time to sit down and work out what was going on.

Ever since the release of IE 8, I've noticed that the menus seem to overlap in very odd ways. In certain circumstances you even get pop-out menus completely covering the menus underneath them like the image on the side of the page.

Now this is obviously less than idea. Functionality it doesnt make a big difference, but aesthetically it can be very annoying to users... and annoyed users are never fun to deal with! ;)

So I noticed that this was happening predominantly when the CRM server was in the local client's Trusted Sites internet security zone. So after a bit of trial and error I managed to isolate the specific setting causing my headaches... There is a setting called 'Allow script initiated windows without size or position constraints'. This is set to Disable by default in the Trusted Sites security zone. Setting it to Enable solves the issue:

Can I use a picklist/lookup as my primary attribute?

In my experience it's safe to say that CRM's approach of always needing the primary attribute of a custom entity to be an nvarchar attribute  unfortunately doesn't fit all business scenarios.

Take the example where an organisation has a complex product management scenario where the standard product functionality has to be suppressed and replaced with something more suitable. Within the Account record you may want to see all of the products owned by the organisation. If you have a standard catalogue of products, having to name each one whenever a customer acquires said product is far from ideal.

There are a couple of ways of dealing with this, but I generally like to use JavaScript to plug the 'gap'.

So say we want to have a picklist of 'Product' for our primary attribute on our new product entity, we could do the following:

1. Create the new entity, schema name of new_product.
2. In the new entity, give the primary attribute a name of new_productname (Name this 'Product Name').
3. Create a new pick-list attribute called new_productnamepicklist (Also name this 'Product Name').
4. Place the picklist attribute front & centre on your entity
5. Place the primary attribute elsewhere and disable it... I generally use a 'System' section on an 'Admin' tab.

Now we just need some JavaScript to automatically populate the new_productname attribute with the value from new_productnamepicklist attribute. Place the following code in the onChange event of the new_productnamepicklist attribute:

var CRM_FORM_TYPE_CREATE = 1;
var CRM_FORM_TYPE_UPDATE = 2;

if ((crmForm.FormType == CRM_FORM_TYPE_CREATE) || (crmForm.FormType == CRM_FORM_TYPE_UPDATE)){
    //get our value from the picklist - make sure you use SelectedValue, not DataValue as we want the text, not the item code.
    var recName = crmForm.all.new_productnamepicklist.SelectedValue;

    //Set the attribute name
    crmForm.all.new_productname.DataValue = recName;
    //Don't forget to force submit as the attribute is disabled on the form.
    crmForm.all.new_productname.ForceSubmit = true;    
}

Please be aware that while this particular approach is very simple to implement, it has its limitations. As the work is being done on the form, the onChange event will not fire if the record is updated as part of a bulk edit, or if it is created via an import or through the web services etc. If you need a more sophisticated solution to handle those scenarios, you will want to use a plugin - I'll try and write a follow-up with more detail on that approach when I get a chance.

Lastly, if you face this challenge, please be sure to give some thought to your views. If you look at a list of products, there are few things less helpful than seeing row after row of a single product. You will need to add extra attributes to your view columns to make it easy for your user to identify the data they need.

CRM build and Update Rollup numbers

Ever needed to figure out what Update Rollup was running on a CRM server. Hopefully this table will help you.

Version	Build Number	Released on
RTM	4.0.7333.3	19/12/2007
Rollup 1	4.0.7333.1113	24/11/2008
Rollup 2	4.0.7333.1312 or 4.0.7333.1316	15/01/2009, 08/02/2009
Rollup 3	4.0.7333.1408	12/03/2009
Rollup 4	4.0.7333.1551	0705//2009
Rollup 5	4.0.7333.1644 or 4.0.7333.1645	02/07/2009
Rollup 6	4.0.7333.1750	27/09/2009
Rollup 7	4.0.7333.2138	22/10/2009
Rollup 8	4.0.7333.2542	17/12/2009
Rollup 9	4.0.7333.2644	11/02/2010
Rollup 10	4.0.7333.2741	08/04/2010
Rollup 11	4.0.7333.2861 or 4.0.7333.2862	03/06/2010
Rollup 12	4.0.7333.2935	02/08/2010

I'll try to keep it as up to date as possible.

Changing the label of a field and a section

I've previously written about changing the name of a field dynamically using client script. Well now a requirement has come up to not only change the label of an attribute, but also a section heading... easy right? No chance!

As it turns out, with an attribute we have a nice and easy element Id that we can always use to address that field in confidence that we are looking at the right thing. We don't have this with section labels. So my problem was two-fold:

1. Find a way to iterate through all the sections until I found the one that I wanted, and then change its name.
2. Then I had to come up with a way of storing that change, so that next time I needed to rename it I knew what to look for; once I've  renamed the section, the original name I used to find it obviously wont work anymore (Duh).

Blatant plagiarism note: Big thanks to Irfan Saeed who did all the heavy lifting with regards the JavaScript to actually rename the sections.

So now that I have a way to find and rename the section, all I need is a way to keep a handle on what the current name is... enter the global variable:

While generally speaking best practice says that you should always prefix your JavaScript variable declarations with the 'var' keyword, it is legal to omit said keyword. If you don't use the 'var' keyword, then the script interpreter will grant that variable global scope... meaning that we can access it and use it anywhere on our form. Sounds like a perfect way to keep a handle on the current name of our se3ction doesn't it?

So in order to do this, create a variable in the forms onLoad event, omitting the 'var' keyword as discussed:

currentSectionName = "";

Now in the onChange and onLoad events of our attribute and form respectively, paste the following code (obviously updating the field names to your own requirements:

//only do something if there is data in the attribute
if (crmForm.all.new_projectcategory.DataValue != null) {
    //get the attribute value
    var pCat = crmForm.all.new_projectcategory.DataValue;

    //define the default section name
    var defaultSectionName = "Delivery";

    //check if the current section name is different to the default name
    if (currentSectionName == "")
        currentSectionName = defaultSectionName;

    //work through the values
    switch (pCat) {
        case "1":
            crmForm.all.ownerid_c.innerText = "Project Manager";
            if (currentSectionName != "Delivery") { UpdateSectionName("tab0", currentSectionName, "Delivery"); }
            currentSectionName = "Delivery";
            break;
        case "2":
            crmForm.all.ownerid_c.innerText = "Project Administrator";
            if (currentSectionName != "Implementation") { UpdateSectionName("tab0", currentSectionName, "Implementation"); }
            currentSectionName = "Implementation";
            break;
    }
}

function UpdateSectionName(TabNumber, CurrentSectionName, NewSectionName) {
    var anchorNode = document.getElementById(TabNumber);
    var secBarCss = "ms-crm-Form-SectionBar";
    var addrSecElm = null;
    var results = getElementsByClassName(secBarCss, anchorNode);
    for (var i = 0; i < results.length; i++) {
        if (results[i].innerText == CurrentSectionName) {
            addrSecElm = results[i];
            break;
        }
    }
    if (addrSecElm != null) {
        addrSecElm.innerText = NewSectionName;
    }
}

function getElementsByClassName(className, anchorNode) {
    if (!anchorNode) anchorNode = document.body;
    var result = [];
    var regEx = new RegExp("\\b" + className + "\\b");
    var children = anchorNode.getElementsByTagName("*");
    for (var i = 0; i < children.length; i++) {
        if (regEx.test(children[i].className)) result.push(children[i]);
    }
    return result;
}

And there you have it; now when we change our field, we are dynamically updating both the attribute and section labels.

Before:

After:

Note: Please be aware, while this approach works, it is currently unsupported by Microsoft so it may stop working in a future release.

Dynamically changing the label of a field

A question came up on the CRM forums and I thought I'd so a quick article on it.

In order to change a fields label, we need to modify the fieldname_c.innerText property. Take for example the following example where we have 'Project Category' and 'Project Manager' attributes:

Now for the sake of argument, say I want to rename my 'Project Manager' attribute based upon the option selected in the 'Project Category' attribute. I would have to put the following code in to the form's onLoad and attributes onChange events:

//only do something if there is data in the attribute
if (crmForm.all.new_projectcategory.DataValue != null) {
    //get the attribute value
    var pCat = crmForm.all.new_projectcategory.DataValue;

    //work through the values
    switch (pCat) {
        case "1": crmForm.all.ownerid_c.innerText = "Project Manager";
            break;
        case "2": crmForm.all.ownerid_c.innerText = "Project Administrator";
            break;
    }
}

Eureka! Now when I set my Project Category value, I get different names on the Owner attribute label...

SyncEntry_ and SubscriptionStatistics_ tables

If you have a look at your database, you may notice several SyncEntry_ and SubscriptionStatistics_ tables.

Ever wondered what they are? These tables are for use with Outlook clients. You will have 2 of each table for each online client, and 3 of each table for each offline client. Additionally these tables are per user, per machine... so if a user changes computer then there will be more tables for that user.

On a related note, you can actually remove these tables in a supported fashion if you arent using them - the UK CRM team posted a topic on ow to do this here:

http://blogs.msdn.com/ukdynsupport/archive/2009/05/07/script-to-clean-up-syncentry-guid-and-subscriptionstatistics-guid-tables-in-the-sql-database.aspx

The code is reproduced below for your convenience...

Declare  @SyncEnt  char(60),
   @sql   nchar(100),
   @sqlSync        nchar(100),
   @DN             char(50)
                
Declare User_cursor CURSOR for
                
select DomainName from SystemUserBase
                
 OPEN User_cursor
 FETCH NEXT FROM User_cursor INTO @DN
                
WHILE @@Fetch_Status = 0
BEGIN
 DECLARE CRMSync_cursor CURSOR FOR
 select substring(SyncEntryTableName,11,42) as SyncEntryGUID from subscription where systemuserid in
    (select systemuserid from systemuserbase where domainname =@DN) 

 OPEN CRMSync_cursor 

 FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt 

 WHILE @@Fetch_Status = 0
 BEGIN
  SELECT @sql = 'DROP TABLE SubscriptionStatistics_' +(@SyncEnt)
  SELECT @sqlSync = 'DROP TABLE SyncEntry_' +(@SyncEnt) 

  EXEC sp_executesql @sql
  EXEC sp_executesql @sqlSync
  FETCH NEXT FROM CRMSync_cursor INTO @SyncEnt
 END 

 CLOSE CRMSync_cursor
 DEALLOCATE CRMSync_cursor

 delete from subscriptionclients where subscriptionid in
    (select subscriptionid from subscription where systemuserid in 
    (select systemuserid from systemuserbase where domainname = @DN)) 

 delete from Subscriptionsyncinfo where subscriptionid in
 (select subscriptionid from subscription where systemuserid in
    (select systemuserid from systemuserbase  where domainname = @DN)) 

 -- Please Uncomment The 3 lines below if you are on UR7 or Higher
 -- delete from SubscriptionManuallyTrackedObject where subscriptionid in
 -- (select subscriptionid from subscription where systemuserid in
 -- (select systemuserid from systemuserbase where domainname = @DN)) 

 delete from subscription where systemuserid in
    (select systemuserid from systemuserbase where domainname = @DN) 

     FETCH NEXT FROM User_cursor INTO @DN
END 

CLOSE User_cursor
DEALLOCATE User_cursor

New 2010 Partner CRM VPC released

As the old partner CRM VPC has expired, the kind folks at Microsoft have been working hard on putting together a new VPC for us.

The new updated 2010 VPCis set to expire in August 2011. Significant new features are:

1. Office 2010 client,
2. CRM Update 11
3. New portal accelerators

The VPC is available through PartnerSource here.

Keeping a hold on your database size

CRM databases have a tendency to grow very quickly. I've seen growth on smaller 50-ish user sites of easily more than 1GB per month. This gets bigger even quicker if you have a workflow and e-mail heavy solution.


I wont cover the e-mail issue in significant detail here other than to say some thought should always be given to the storage of e-mails in CRM and more specifically, attachments. This also applies to all other methods for saving attachments into the database. Some things to consider:

• What to set the CRM maximum file size to (this cant be bigger than 8192 kilobytes). Keeping it smaller will improve database size, but will mean that larger attachments will not be saved.
• When to promote an e-mail to CRM - do you really need every e-mail (and attachment) when a large amount of correspondence can often just be noise, with only certain elements of a conversation actually being relevant.

If you don't keep control of this, then you database can get very big very quickly.

The other area and the one I'm exploring in detail here is the asynchronous operation tables in CRM. With every system job and/or workflow, new records will get written in to these tables (specifically the Asyncoperationbase and Workflowbase tables) Over time as your system goes through the motions of your various business processes, match code updates, system expansion tasks and all the rest, these tables can get very, very big; sometimes containing millions of records, causing not only a storage problem but almost certainly giving us some performance headaches too.

Fortunately Microsoft have recognised this and given us a few ways of dealing with the issue as part of the Update Rollup process.

So first off we have 2 options, both enabled via the addition of new registry keys:

1. AsyncRemoveCompletedJobs (http://support.microsoft.com/kb/974896)
   
   This will remove all completed Async jobs in CRM of the following types:

   CollectSQMData

   PersistMatchCode

   FullTextCatalogIndex

   UpdateContractStates
2. AsyncRemoveCompletedWorkflows (http://support.microsoft.com/kb/974896)
   This will remove all completed workflow jobs from both the AsyncOperationBase and WorkflowBase tables.

And now to the downside - on of the strengths of CRM workflow is its persistent nature - meaning that because the workflow instances exist as data in the system we can report on them... so we are able to extract meaningful detail and manangmenet intelligence from our workflow data. Obviously this ability becomes a bit redundant if we dont keep any of this data.

My general answer to the above challenge is to enable the 'AsyncRemoveCompletedJobs ' registry key, but not the 'AsyncRemoveCompletedWorkflows' key. This means that we can reclaim some space from the other system jobs, while leaving the workflow history in place to support our MI requirements.

One last caveat; these registry cahnges are not restrospctive. In other words once the specific feature is enabled it will only remove jobs/workflows instanciated after the change, not historical data. To get around this the kind folks at Microsoft have provided us with some SQL swcripts that will remove historical data from the tables in question.

The article can be found here: http://support.microsoft.com/kb/968520/

A cautionary tale: The script provided by Microsoft deletes all completed jobs, workflow included. So if you intend to replicate the functionality of retaining completed workflow history as per the above registry keys, you will need to modify the script accordingly to not remove records where OperationType = '10'. If you dont do this you lose everything... and I'm talking from painful experience here :(.

Having been presented with a pick list containing 50 items that my customer wanted me to upload to CRM, I got to wondering if there is an easier way of doing this.


Of course, I can edit the XML of the record directly but that's still a lot of manual work.

Enter the Dynamics CRM Pick-list XML-Generator from Mario Raunig... an incredibly useful time saving tool.
 
Thanks Mario!!

How to get code in my blog?

I've often come across very useful information posted by other industry experts on blogs. Now that I'm doing it myself I was wondering how people managed to post good looking code on their sites - one example is Jim Wang over at http://jianwang.blogspot.com/.

Well, I've found the key - a very useful tool called SyntaxHighlighter.

And just so I can remember how to set it up next time I need it, there is a very useful wiki over at Carter Cole's Blog.

And just so you can see it working:

//Define and initialise our variables
var alertMsg;
alertMsg = "Hello World";
//Set up the alert window
if (alertMsg != "") {
    window.alert(alertMsg);
}