Keeping Track of Public Queues with a Vista Gadget

Way back when CRM 1.0 came out, we embraced email enabled queues as a key responding to our clients support requests. By replacing an email distribution list with a queue we eliminated the problems we often had where two or more people would start responding to an issue at the same time. Now the first person that can work on the issue takes it off of the queue.


However queues introduced a new problem for us. When we used a distribution group everyone knew about the problem immediately. With queues we had to remember to go check the queue regularly to see if anything was in it. To get around the problem we created a small Windows application that installed on each workstation and then popped up a message indicating how many items there were in each monitored queue. This worked pretty well for us until we started upgrading to Vista. We had purchased a specific VB control to pop up the message and that control would not work with Vista.


We decided to start over and embrace the new world of Vista Gadgets. In keeping with our original design, we opted for a two tiered solution with a dedicated webservice on the CRM server and code in the gadget that consumed data from the webservice. The gadget let someone see how many items are in a particular queue without having to open CRM or jump to the Queue area under workspace.


The VS 2005 solution for the webservice and all of the files to recreate the gadget are attached to this post. We consciously hard coded URLs in the javascript for the gadget because it is easier to edit the file and distribute the gadget with the URL set than it is to have each user set the URL when they install the gadget. Therefore you will have to follow the provided instructions to recreate the gadget file.


Richard Riddle, our lead CRM developer did all of the coding on this one. We hope you find it useful.

Outlook Hanging with CRM

One of the reasons I have not been posting in a while is that we have been very busy trying to resolve some lingering issues at one of our CRM installations. We have been working with Microsoft over the past 6 months trying to clear up some Outlook performance issues as well as some related email issues when using the CRM Address Book provider.


It has taken a long time to isolate the issues and frankly my commitment to the Dynamics CRM platform was shaken for quite some time. I think Microsoft if is paying attention and being dillegent is addressing the problems and we are starting to see some hotfixes that address the specific issues we have been trying to resolve.


MS KB 938547 should be public within the next few days at http://support.microsoft.com/kb/93847. This KB provides a hotfix that you will have to call in for. Don’t bother calling for the hotfix until the KB link above is working. This hotfix requires that CRM Update Rollup 2 be installed on the system.


I’ll let you know how our testing of this hotfix goes.


I hope to have news of other hotfixes soon. One of our major issues is sending email when using the CRM Address Book Provider. We have received some preliminary hotfixes but they have not fixed the underlying problem. As best as we can determine, the problem seems to only occur when using Outlook 2003. We cannot reproduce the problem with Outlook 2007. When the final fix comes out I will let you know.


 

DST Changes for CRM

This just in. Microsoft has released its Daylight Savings Time update strategy for Microsoft CRM. If you thought you were all ready for the DST change that is happening in the US on Sunday March 11, 2007 think again!


 Give yourself time to digest the 27 page document explaining how to make the changes to the system and data. You can dowlnoad the necessary files here.


 

Using Group Policy to control preferences on the CRM Outlook Client

I’ve been struggling with how to standardize synchronization settings for the CRM Outlook clients at one of my customer sites. After playing around with scripts I decided to take a look at Group Policy. I am not expert on Group Policy but I was able to find some great guidance in Group Policy, Profiles, and Intellimirror for Windows 2003, Windows XP, and Windows 2000 by Jeremy Moskowitz (Sybex Press). After reading up a bit on creating custom Group Policy templates I decided to give it a try.


The result is a basic template that allows an administrator to standardize the registry settings that affect how the CRM client for Outlook behaves on a client computer. If you are interested in using this template, it is attached at to this post. You can also download it at http://www.mscrmfaq.com/files/mscrmclient30.zip. As always this file is released as is.


It turns out the the CRM client for Outlook is not really Group Policy compliant because the registry settings it uses do not exist in the proper area of the registry. The template I created is actually applying “old style” NT preferences as opposed to policy. The major difference is that when a true group policy is removed, the registry settings on the affected machines are also removed. When an “old style” policy like this one is removed the registry settings on the target machines are not changed. So a word of caution here, any changes you make using this template will permanently overwrite registry settings on the target computer. Take a moment to understand what the current settings are on your systems before implementing this template.


To use this template unzip the file and then copy the MSCRMClient30.adm file to the C:\Windows\inf folder on the machine you are using to manage group policy. Open GPMC and create a new policy linked to an OU. Edit the new policy and then add the new template as follows:


  • right click the Administrative Templates folder under the User Configuration and select Add/Remove Templates.
  • Click the Add button and then locate the MSCRMClient30.adm policy file and click OK to save.
  • Click Close on the Add/Remove template to finish adding the template to the policy.
  • Make sure the Administrative Template folder is still selected and then select the View -> Filtering option from the MMC menu bar.
  • Uncheck the “Only show policy settings that can be fully managed” check box and click OK. (You will need to do this each time you want to edit the policy.)

You will now see a folder under Administrative Templates named Microsoft Dynamics CRM 3.0 Client for Microsoft Outlook with various policies in sub folders.


 

What drives activity visibility in MS CRM?

I've been doing a deep dive into the effects of the security model. I recently noticed that the UI does not support sharing of activities to users or teams. This came as an unfortunate surprise because I was planning on "sharing" to be my way out of a tricky situation concerning visibility of activities between business units.

Imagine a scenario with three business units: Executive, Finance, and Staff where Executive is the parent business unit to both Finance and Staff. The security roles in use all allow organizational read for accounts and contacts but only deep read access for activities. The organization President and VP are members of the Executive BU, the CFO and accounting pros are members of the Finance BU, and the marketing, sales, and service folks belong to the Staff BU.

With this basic setup everyone in the company can see any account or contact due to the organizational read privilege. The President and VP can see any activity because they are at the top and have deep read privilege on all activities. The people in the Finance BU can see each others activities due to the same deep read privilege. The same is true for people in the Staff BU. By design people in the Staff BU cannot see activities owned by members of the Executive and Finance BU and members of the Finance BU cannot see activities owned by members of the Executive or Staff BU.

Now the problem is that sometimes the President is communicating with a CRM contact and may make a deal with the contact that others in the company need to know about. However the security roles don't allow this. My first thought was all you need to do is share the activity out to other users and they could see it, but as I stated earlier that is not an option.

In turns out that CRM is smarter than I thought about activity visibility. When an activity is linked to a record using the Regarding field, the owner of linked record inherits visibility to the activity. Therefore they can see the activity even if their native security role would prevent it. It is important that this only applies to the owner of the linked record not everyone that has visibility of the linked record. It also turns out that you can take this one step further. If you share access to the linked record than anyone that you granted shared read access, will also be able to view any activities linked to the share record.

One other side note – this inherited visibility does not apply unless the activity is linked via the Regarding field. Linking through the activity parties as sender, recipient, organizer, etc does not make the activity visible to the owner of the contact or account record. Therefore if the President emails a contact owned by a Staff member the Staff member cannot see the email unless the President explicitly links the email to the contact in the Regarding field.

Recurring Service Cases

We recently updated our CRM solution for automatically creating recurring service cases. We use recurring service cases to automatically schedule items for our customers that have signed up for managed services or that are under some form of retainer.


 


I originally implemented this in CRM 1.2 using CRM using special products in our CRM product catalog to represent each service offering. For example we had a product item the corresponded to Server Patch Management and another that corresponded to UPS system check, etc. Each product was related to a maintenance item that was defined in an XML configuration file. The maintenance item contained all the information need to create a service case as well as scheduling information (e.g. 1st and 15th of the month, 2nd Tuesday of the month, 90 days since last completion, etc.)


 


I created an ASP.NET web service that contained the code to make it all work. When the web service was called it would look through all active CRM Contracts and examine each contract line item to see if the contract line item was for one of our recurring maintenance tasks. If so the code determined if it was time to schedule a new service case and created the case if required. A simple VBScript executed by the Windows scheduler (an often overlooked tool) called the web service daily. It wasn’t elegant but it worked for almost a year and ensured we did not drop the ball on our contractual obligations.


 


Custom entities in CRM 3.0 have really allowed us to refine this solution. Richard Riddle, one of our CRM developers, took what I had started with and helped me make a much richer and more robust product. We made significant changes in the architecture of the solution by removing all of the complexity of the XML configuration files and placing all of the information on scheduling and product catalog relationships in a new custom entity that we call Maintenance Items. We also implemented the concept of a template service case that makes it much easier to control what information goes into the service cases that are scheduled automatically. Lastly we replaced the ASP.NET web service and Windows schedule with a new Windows Service.


 


The new system is much easier to configure and gives us greater flexibility. For example we can now have several recurring services associated with one product SKU. This makes it much easier to package our services in the product catalog and makes our CRM Contracts much more readable. So with a little code and a custom entity Microsoft CRM is now capable of scheduling an unlimited number of recurring service cases based on the contracts we negotiate with our customers. 

Gathering Data From Public Web Forms

We have been working on a project that requires harvesting data from a public facing web form. This is a pretty basic request but in the past we’ve only been asked to do this for lead capture and we have generally specified using the C360 web capture tool as a very effective tool for capturing lead data and either relating it to an existing contact or lead or creating a new lead.


However with this project we have to capture data that updates an existing contact or creates a new contact (C360 currently doesn’t support this). We also have to create a custom entity using some of the captured data.


Our first thought was to build a custom web form using ASP.NET and have it connect to the target CRM system using web services. However, we were concerned about three things. First we could not always guarantee that our CRM server would be on line (even the best systems need patching etc.) , the number of hits/second at the public web form could be very high, perhaps higher than the target CRM system could process synchronously and provide a good user experience, and we had no guarantee that the public web server could support ASP.NET.


We decided to make a generic web capture system based on parsing email sent from a web form. Emailing web form data is a basic capability of most hosting platforms so this seemed to clear up compatibility issues. Email would also allow the process to be asynchronous so the users would not perceive a delay. Additionally, email would allow the process to survive a short CRM server outage.


The process is pretty simple. We created a service that monitors a specified CRM email enabled queue. When an email arrives in the queue, the service reads it, parses it, and used the data to create a web request record in CRM (a new custom entity). For auditing purposes we also attach the inbound email the the newly created web request record.


When the web request record is created a workflow rule executes that calls a custom .NET assembly to search for a matching contact record (we use email address just like C360 does) and then either relates the web request to the contact or creates a new contact and relates the web request to the contact. After relating the contact to the web request it is very easy to use the existing workflow functions for dynamic updating to update the contact fields that we want to update. We create and update the custom entity using a similar process.


 

Minor CRM Gripes

There are not many things that I don’t like about MS CRM 3.0. But one item has recently jumped to the top of the list: Email support for custom entities. I can understand the difficulty in figuring out how to use an email address field in a custom entity to actually create and send an email. What I can’t understand is why I can’t create email templates for custom entities.


I can create email templates for other record types in CRM that don’t represent people (e.g. orders, quotes, service cases.) I should be able to create email templates for my custom entities too. As long as they are related to one of the primary email record types (account, contact, lead) the system should know how to send the email.  Maybe in the next service pack???


By the way coming in at number 2 and 3 on the list are: Can’t call a workflow for one record type from another record type; and Can’t relate a custom entity to a customer (i.e. an account or contact with one lookup.


 


 


 

Migrating Act! to CRM with the new CRM Data Migration Framework

We just completed a data migration from Act! 6.0 to Microsoft CRM 3.0. Rather than use a third party migration tool like Scribe we decided to use the recently released Microsoft CRM Data Migration Framework (DMF).


The primary reason we opted to use the DMF was that the data in Act was very “dirty”. Specifically each record in Act might actually represent anywhere from 1 to 5 individuals with the names and contact information for the secondary “individuals” on each record separated by various delimiters. I don’t know that Scribe couldn’t do it but I had a feeling it would be easier to script out the migration in SQL than it would be to figure out how to make a tool deal with these irregularities. 


The overall process was exporting all the data out of Act! 6.0 and moving it to a format that was more SQL Server friendly. We decided to use a tool specifically designed for the purpose Import Pro from crmaddons.com http://www.crmaddons.com/pc-15-3-export-pro.aspx. From Access we moved the data to a new SQL Server database (ActData) using DTS. 


The DMF consists of several wizards and a common data framework (CDF) database. The wizards assist in mapping data in the CDF to data in CRM to account for various pick list option values, record ownership mapping, etc. Getting clean data into the CDF is the key to a good migration with the DMF. 


The majority of our effort was creating and testing scripts that normalized the data, split out the secondary contacts in each contact record and then migrated the data to the CDF database. Once the data was in CDF things went smoothly with a few exceptions.  


First, we had problems importing historical data as completed activities. It appears that the DMF doesn’t allow for the creation of a record with a statecode of 1 (completed.)  We worked around this issue by importing the activities in an open state (statecode=0) and then ran a script in the MSCRM database to update the statecode and statuscode to mark the activity completed.  


Second, Act stores dates and times without time zone correction and CRM stores dates and time in GMT. For our conversion this resulted in some records showing the due date as one day earlier in CRM than it was in Act. We corrected this by modifying the scripts to add 8 hours to all dates as we moved records to the CDF database.  


Third, we originally had a lot of activity records that would not import on our first test import. Analysis of the failures indicated that they had only one thing in common; a date field where the time was exactly midnight (00:00:00). This was evident when looking at the DMF logs that contain the XML used to insert the record in CRM. When the time was exactly midnight, the time part of the date information was not in the XML field and CRM is very particular about using proper UTC form on date/time information. The workaround here was to add 8 hours and 1 second to all the dates as we moved records to the CDF database. 


Once all of the scripts were done and tested we were able to complete a test migration on our development environment and then run the production data migration. The Data Migration Framework is a great tool but it requires a fair amount of SQL scripting knowledge. Moving data from Act to the CDF database was relatively quick once the scripts were developed but the migration from the CDF database to MSCRM was relatively slow (3 to 4 hours to move 65,000 records.)