Microsoft CRM Multi Select Picklists, Many to Many Relationships and Advanced Find

I love using the many to many (n:n) relationship in Microsoft CRM 4.0. I most often use it to create a solution for a multi select picklist requirement. For example I may have a requirement to track which industries an account belongs to. The problem is an account can be linked to more than one industry – I need a multi select picklist. Now there are a multitude of solutions out there for multi select picklists, most are really elegant when it comes to the UI and usability but pose all sorts of problems when it comes to searching using Advanced Find. That’s where the n:n relationships can provide a great solution.

Continuing our example above I have created a new entity called industry and linked it to the account entity using an n:n relationship. The result is Industry now appears as a new menu item appears on the left hand navigation bar in the Account screen.

Many users however would rather have the Industry listing  on the form itself rather than as a menu item. To solve this I place the n:n related entity in an IFrame on the form using the great solution CRM 4.0 Many 2 Many IFrame Viewer by Adi Katz. The best thing about Adi’s solution is that the IFrame is automatically refreshed after updating your selections.

To make the solution a little cleaner I would like to remove the Industries menu item on the left hand navigation bar. Now you can achieve this by setting the Display Option in the n:n relationship settings to Do Not Display as shown below.

However this will remove the relationship in the Advanced Find and I can no longer search industries as a Related Entity to Accounts in the Advanced Find screen. Strangely enough this is only a problem with an n:n relationship.

Industries will only appear as a Related entity in Advanced Find if the display option is set to “Use Plural Name” or “ Use Custom Label” If you use the latter the entity name will be what ever you used as your Custom Label.

To solve this problem rather than removing the menu item from the left hand nav bar by setting the Display Option to Do Not Display, you can remove it by using some simple JavaScript in the form onload event.

function ShowHideLeftNav(strNavItemID, blnVisible)
{
    try
    {
    var NavItem = document.all["crmNavBar"].all[vstrNavItemID];
    NavItem.style.display = (blnVisible == true) ? "inline" : "none";
    }
    catch (e)
    {
    }
}

ShowHideLeftNav('navnew_new_industry_account', false);

Typically the strNavItemID parameter is the name of the n:n relationship prefixed with nav. So in our example it is 'navnew_new_industry_account'. You can also use the IE developer toolbar (now built in to IE 8) to retrieve this.

Now you have a great solution for a multi select picklist on the form, have not confused users with multiple locations to update the list and have maintained the Advanced Find functionality :)

Creating a Sub Lookup in Microsoft CRM 4.0

Quite often there is a requirement to have the values of one picklist or lookup be determined by the value of another picklist or lookup. As most of you will know Microsoft CRM does not have the concept of sub picklists or sub lookups. I’ve often seen this requirement solved through the use of picklists and JavaScript. Yuk! This solution means that every time a new value is added to either the parent or child picklist, the JavaScript code needs to be changed. Not a great solution for system administrators who have no knowledge of JavaScript.

Here is a simple solution using lookups rather than picklists with some simple, generic (and warning: unsupported) JavaScript.

For my example today lets presume I need to add a Primary Industry lookup to the Account form. I also need add a Sub Industry lookup where the values shown are determined by what I select as the Primary Industry. 

First I need to create two new entities, Primary Industry and Sub Industry. Next I need to create a 1:n relationship between the two new entities with Primary Industry as the Primary Entity. As you can see in the image below I have also set Primary Industry relationship attribute as Business Required so all Sub Industries must have a Primary Industry.

Next I add the Primary Industry attribute to the Sub Industry form.

In order to be able to filter the Sub Industry lookup based on the selected Primary Industry value I need to add the Primary Industry attribute as a find column in the Sub Industry Lookup View. I can now publish my two new entities.

I add some values to my Primary Industries and my Sub Industries.

Now I need to add the Primary and Sub Industry lookups to my Account form. I create  a n:1 relationship between the Account entity and my Primary Industry and Sub Industry entities. In each case the Account Entity will be the Primary Entity. I can then add the created Primary Industry and Sub Industry lookup attributes to the Account form as shown below.

Now I need to add the JavaScript to filter the value of the Sub Industry lookup based on what is selected as the Primary Industry. On the change event of the Primary Industry lookup I add the following JavaScript:

var lookupItem = new Array;

lookupItem = crmForm.all.new_primaryindustryid.DataValue;
var searchVal = lookupItem[0].name;
crmForm.all.new_subindustryid.additionalparams = ‘search=' + searchVal;

I can now publish all my changes and test the result. As you can see below because I selected Finance as the value for my Primary Industry, when I click on the lookup for the Sub Industry it is “pre-filtered” and only shows the Sub Industries that relate to Finance.

New Tool for for Comparing Customization

The one thing I promised myself when I started this blog is that I wouldn’t use it to simply re-blog about recently released service packs, tools etc. but … this release is just to exciting not to blog about so I’m going to break my rule just this once!

Microsoft in collaboration with  Sonoma Partners have just released a tool to compare customisation files! http://msdn.microsoft.com/en-us/library/dd442453.aspx

This from the Sonoma Blog (Permalink):

“Customization Comparison Utility
The Customization Comparison Utility allows you to take two exported customization files (either xml or zip) and display all the differences between the files. You can review any two valid customization files and also export the results to Excel for additional analysis or documentation needs. Similar to many source control systems, the tool displays to you the following:

• What is identical between the systems
• What has changed between the systems
• Exists in the source system, but not the target
• Exists in the target system, but not the source "

This is going to be a very very useful tool, especially on larger more custom intensive projects :) Now if we can just have a tool that allows us to consolidate configuration from multiple config files, that would be great :)

Changing the schedule for the Deletion Service

I came across this tool the other day when I was asked by a client if it was possible to force the deletion service to run. When you delete a record in Microsoft CRM, most often the record is not deleted from the database but rather the deletionstatecode is set to 2 (This is not always the case so be careful in using this for reporting etc). When the deletion service runs it will remove the record from the database. After some investigation it looks like the deletion service runs every 24 hours. This means that when writing reports you need to make sure you exclude deleted records as it is likely there will be records with a deletionstatecode  of 2 in the database.

Currently the only way I know to force the deletion service to run on Microsoft CRM 4.0 is to stop and restart the Asynchronous Service. I have since found this tool that allows you to change the schedule and frequency of when the deletion service runs: http://code.msdn.microsoft.com/ScaleGroupJobEditor. This is useful when you need to ensure deleted records are quickly removed from the database :)

What is xRM?

First it was CRM (Customer Relationship Management), then came PRM (Partner Relationship Management), ERM (Employee Relationship Management) and a host of other ?RM solutions. The problem was in most cases there were specific applications that met each type of requirement. While managing relationships was at the heart of each strategy, the nuances around each was such that no one application was flexible enough to meet all the functionality requirements. Microsoft Dynamics CRM seems to have finally met this challenge and quite often you will hear Microsoft refer to Microsoft CRM as a xRM platform.

So what exactly is meant by xRM and how is Microsoft CRM an xRM platform? The “x” in xRM is a placeholder for anything, so essentially xRM means the management of any type of relationship. This might be for example, the management of a company’s relationship with its employees. Now it can be argued  that an HR system fills this function, but most often the HR system misses the subtleties of managing a relationship and more importantly it lacks  the flexibility to be configured to meet the company’s ever changing strategies around this relationship. Microsoft CRM however has the core functionality to track manage the relationship aspect and the flexibility to be configured to meet changing  Employee Relationship Strategies. Now I can feel the wrath of the HR Managers bearing down on me as they say “Karl are you honestly saying Microsoft CRM is an HR solution?” No absolutely not! What I am proposing is that Microsoft CRM together and integrated with (whether the integration is technical or strategic) an HR system can be used as a very effective Employee Relationship Management solution.

Before I annoy any more HR managers let me give another real world example of how I’ve seen Microsoft CRM used as an xRM platform. In this case it was a Private School. Their Student Management system very effectively allowed them to meet the day to day requirements of managing students. It managed timetables, financials, etc well but it couldn’t identify and track the inter relationships between the students. So for example I couldn’t easily tell that Jimmy’s brother was also at the school, nor that their father was a past alumni and Jimmy’s mum was a bit of a worry wart and emailed / called the school often. By integrating their existing Student Management System with Microsoft CRM, the school was able to effectively manage all aspects of their relationships with their students. Effectively a “S”RM solution :)

In both the above examples Microsoft CRM was configured to meet very different requirements. There was no mention of Accounts, Contacts and the term customer was completely removed. Sometime I do wish that Microsoft CRM was not called Microsoft CRM and rather it was renamed to be what it really is: The Microsoft RM platform. And how cool would it be if it was renamed the Microsoft Office RM platform :) One can always dream!

Simple Audit Tracking in Microsoft CRM 4.0

Oh dear, what happened to January! It seems I blinked and missed the first month of the year. One of my goals for 2009 was to ensure I had at least 5 to 8 quality blog posts a month. Hmm I’ll have to work harder at that one!

Looking back at my 2008 posts, I seem to have a few that talk about Microsoft CRM’s workflow functionality and how I’ve used it to solve some real world problems. This was never intentional however it does show the power of the workflow functionality and hopefully the simplicity as well. I thought I’d carry on that theme for my first 2009 post and show how I’ve used workflow to create some simple audit tracking. Again this is a zero code example. I’m a firm believer in avoiding writing code unless it is absolutely necessary.

For this example I would like to track each time the estimated close date changes on an opportunity. I want to track who changed it and when. First I need create a new entity to store the audit data in. For this I’ve created a simple entity called Audit History with some additional fields as shown in the image below. Next I created a n:1 relationship between my new entity and the opportunity entity and update the form.

Now I’m ready to create the workflow against the Opportunity entity. I set the workflow to start both when the record is created and on the change of an attribute; namely the Estimated Close Date attribute. I also set the scope to Organisation so it will run for all users.

For my first step I want to check if the opportunity is being created or updated. To do this I create a Check Condition step and check if the opportunity Created On Date equals the Modified On Date. If they are equal I know the opportunity has been created, otherwise its being updated :)

Inside my Check Condition I create a Create Record step and create an Audit History record. Note for this step I set the Change Description to “Opportunity is Created” and copy the various fields from the related opportunity.

For the Default Action step I know if the workflow steps in here the opportunity is being updated so I create a Create Record step and create an Audit History record as above with the only difference being the Change Description which I set to “Estimated Close Date Updated”

The final result would then look like the below image.

I would recommend you set your security for the Audit History entity to be read only so the records cannot be changed. Hopefully this gives you some further ideas for simple audit tracking. For more advanced Auditing where you need to track changes to any field you will have to look at developing a plugin or use an ISV product like the c360 Audit tool.