Customer Effective Blog

Microsoft CRM 4.0 Email Router Service Will Not Start

Scenario: The Microsoft CRM 4.0 Email Router Service won't start and throws an error message stating "The Microsoft CRM Email Router service on Local Computer started and then stopped.  Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service." 

 

The Application Event log also logs the following entry:

______________________________

Event Type:     Error

Event Source:  MSCRMEmail

Event Category:          None

Event ID:        0

Date:               

Time:               
User:               

Computer:      

Description:

#16192 - The E-mail Router service could not run the service main background thread. The E-mail Router service cannot continue and will now shut down. System.Configuration.ConfigurationErrorsException: The E-mail router service cannot access system state file Microsoft.Crm.Tools.EmailAgent.SystemState.xml. The file may be missing or may not be accessible. The E-mail Router service cannot continue and will now shut down. ---> System.Xml.XmlException: Root element is missing.

   at System.Xml.XmlTextReaderImpl.Throw(Exception e)

   at System.Xml.XmlTextReaderImpl.ThrowWithoutLineInfo(String res)

   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()

   at System.Xml.XmlTextReaderImpl.Read()

   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)

   at System.Xml.XmlDocument.Load(XmlReader reader)

   at System.Xml.XmlDocument.Load(String filename)

   at Microsoft.Crm.Tools.Email.Providers.ConfigFileReader..ctor(String filePath, ServiceLogger serviceLogger)

   at Microsoft.Crm.Tools.Email.Providers.SystemState.Initialize(ServiceLogger serviceLogger)

   at Microsoft.Crm.Tools.Email.Providers.SystemState..ctor(ServiceLogger serviceLogger)

   at Microsoft.Crm.Tools.Email.Agent.ServiceCore.InitializeSystemConfiguration()

   --- End of inner exception stack trace ---

   at Microsoft.Crm.Tools.Email.Agent.ServiceCore.InitializeSystemConfiguration()

   at Microsoft.Crm.Tools.Email.Agent.ServiceCore.ExecuteService()

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
_____________________________

Solution: See KB954522 from Microsoft and perform the steps to resolution, which are:

1.	Delete the Microsoft.Crm.Tools.EmailAgent.SystemState.xml file. Notes • By default, the Microsoft.Crm.Tools.EmailAgent.SystemState.xml file is in the following folder: C:\Program Files\Microsoft CRM Email\Service • By default, the Service folder is a hidden folder. To see this folder, you must change the Windows Explorer settings to display hidden files and hidden folders.
2.	Restart the Microsoft Dynamics CRM E-mail Router Service. The Microsoft.Crm.Tools.EmailAgent.SystemState.xml file is re-created.

Details can be found at the link below.

http://support.microsoft.com/kb/954522

Change of a User’s Domain Login Disables Their CRM Access

Situation: We've had several occasions where a user's domain login changes in Active Directory and disables their CRM access. For example, Jane Smith's old domain login was "MyDomain\Jane.Smith". Last weekend she married John Doe and rightfully requested that her login be changed to reflect her new name. So, her network administrator changed her login to "MyDomain\Jane.Doe". But, when she tries to get to CRM, she is unable to.

Resolution: Fortunately, there's a pretty easy solution. While you'd think you could just go into CRM and modify the user's login, you can't. Also, disabling the existing account and attempting to recreate it will not work either.

You can easily correct the problem by running a simple update query against the SYSTEMUSERBASE table. Connect to the _MSCRM database using either SQL Management Studio or Query Analyzer. Run the following query against the _MSCRM database.

    UPDATE SYSTEMUSERBASE
        SET DOMAINNAME='MYDOMAIN\JANE.DOE'
        WHERE DOMAINNAME='MYDOMAIN\JANE.SMITH'

Obviously in this example above, I'm using my example names. You'd substitute your old domain login and new domain login names.

This should be performed by someone that is familiar with and comfortable funning SQL queries. And, a word of caution: be absolutely certain that you do have a backup of your _MSCRM database prior to running the update.

Note: This is only applicable for version 3.0.  In version 4.0, you can edit the user's domain login from their user record through the front end of CRM.

CRM Ad Hoc Reporting With Report Builder

Recently, I had a situation come up with a client where they needed Ad Hoc reporting in CRM.  Normally, I would point them to Advanced Find or the Report Wizard in 4.0.  The problem here was that they really wanted to get at data in the salesorderdetails entity and that just isn't visible from a normal Advanced Find.  They also wanted to do some particularly complex queries where they wanted to know customers that bought a particular product during a particular time period but did not buy a particular product during a different time period and that really could not be accomplished through Advanced Find.  They had so much data that exporting to Excel would have been too cumbersome to accomplish.  So, we decided to implement Report Builder. 

The benefits of using Report Builder are:

1. Can access any table or view in the CRM database in pretty much any way that you want to.  You can combine certain tables and views together to make it easier for the end user to access data that is related.

2. Report Builder does not currently require any installation on the client.  It will install itself the first time it is executed.

3. Allows end users to build reports

The purpose of this blog is to discuss the steps involved in making an ad hoc reporting environment using a report model and Report Builder.  I do assume that you have some knowledge of BIDS/Visual Studio 2005 so some steps will be summarized.

Step 1. Building The Report Model

Report Builder requires that data be available through a report model.  A report model is essentially a Data Catalog that is built using Business Intelligence Development Studio (BIDS) and allows developers to do all the heavy lifting of publishing data to end users in a meaningful way.  Tables or Views can be joined together to produce some great views of data with "English" field names that end users will understand.

The scenario I will use to demonstrate the building of the report model is the following:

A marketing person wants to monitor the activities occurring for a particular campaign to verify that people are making phone calls to actively drive the campaign.  In order to do that, I want to be able to show a matrix of activities associated with a Campaign so that I can see the number of phone calls made by sales rep.

To create the report model, open BIDS (Visual Studio 2005 with the SQL Server Reporting Services AddIn), create a new report model project and do the following steps:

Step 1a. Connect to The Data Source

Create a new data source using the wizard.  You should use windows authentication and connect to the MS CRM database.

Step 1b. Create a Data Source View

Create a new data source view using the wizard.  However, I usually do not simply add tables or views from that list.  To me, I like to build named queries after the Data Source View is created.  That way, I can get all the joins that really add value.  So, for this particular scenario, to show Activities for campaigns, I use FilteredActivityPointer as the main table and then if I need to pickup custom attributes for a specific type of activity, I need to join with the filteredview for that type of activity (e.g., FilteredPhoneCall, FilteredTask).  Although I did not use these other filteredviews in the actual select, I have included them here so you can see how the join works.  Note that I am using Filtered Views.  Filtered Views allow you to utilize the security features of CRM to only show the data that is appropriate for the user running the report.  You should always hit the FilteredViews where possible unless you have performance concerns.  The other thing to note in the query below is that I am looking at the regardingobjecttypecode to get the account associated with the activity.  If the regarding field has a contact in it then I get the parent account of the contact.  If the regarding field has the account then I simply get the account.  I also use the regardingobjecttype field to get the campaign id and campaign name.  Finally, in the where clause I filter out activity types like service activities as I am only interested in tasks, appointments, phone calls, faxes, and emails.

SELECT        apo.activitytypecodename AS 'Activity Type', apo.activityid AS 'Activity ID', apo.createdbyname AS 'Created By', apo.createdon AS 'Created On',
                         apo.description AS 'Description', apo.modifiedbyname AS 'Modified By', apo.owneridname AS 'SalesPerson', apo.ownerid AS 'SalesPerson ID',
                         apo.prioritycodename AS 'Priority', apo.regardingobjectidname AS 'Regarding', apo.regardingobjecttypecode AS 'Regarding Type',
                         apo.scheduledend AS 'Scheduled End', apo.scheduledstart AS 'Scheduled Start', apo.statuscodename AS 'Status Reason', apo.subject AS 'Subject',
                         apo.actualend AS 'Actual End', apo.actualstart AS 'Actual Start',
                         CASE WHEN apo.regardingobjecttypecode = 1 THEN apo.regardingobjectid WHEN apo.regardingobjecttypecode = 2 THEN
                             (SELECT        c.parentcustomerid
                               FROM            contact c
                               WHERE        c.contactid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o.accountid
                               FROM            opportunity o
                               WHERE        o.opportunityid = apo.regardingobjectid) END AS 'Account ID',
                         CASE WHEN apo.regardingobjecttypecode = 1 THEN apo.regardingobjectidname WHEN apo.regardingobjecttypecode = 2 THEN
                             (SELECT        c.parentcustomeridname
                               FROM            contact c
                               WHERE        c.contactid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o.accountidname
                               FROM            opportunity o
                               WHERE        o.opportunityid = apo.regardingobjectid) END AS 'Account Name',
                         CASE WHEN apo.regardingobjecttypecode = 3 THEN apo.regardingobjectid END AS 'Opportunity ID',
                         CASE WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o2.campaignid
                               FROM            filteredopportunity o2
                               WHERE        o2.opportunityid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 4402 THEN
                             (SELECT        ca.regardingobjectid
                               FROM            filteredcampaignactivity ca
                               WHERE        ca.activityid = apo.regardingobjectid) END AS 'Campaign ID', CASE WHEN apo.regardingobjecttypecode = 3 THEN
                             (SELECT        o2.campaignidname
                               FROM            filteredopportunity o2
                               WHERE        o2.opportunityid = apo.regardingobjectid) WHEN apo.regardingobjecttypecode = 4402 THEN
                             (SELECT        ca.regardingobjectidname
                               FROM            filteredcampaignactivity ca
                               WHERE        ca.activityid = apo.regardingobjectid) END AS 'Campaign Name'
FROM            FilteredActivityPointer AS apo LEFT OUTER JOIN
                         FilteredPhoneCall AS pc ON apo.activityid = pc.activityid LEFT OUTER JOIN
                         FilteredEmail AS em ON apo.activityid = em.activityid LEFT OUTER JOIN
                         FilteredAppointment AS app ON apo.activityid = app.activityid LEFT OUTER JOIN
                         FilteredFax AS fax ON apo.activityid = fax.activityid LEFT OUTER JOIN
                         FilteredTask AS tsk ON apo.activityid = tsk.activityid
WHERE        (apo.activitytypecode IN (4201, 4202, 4204, 4210, 4212))

To create the named query, you can right click on the datasourceview and select New Named Query option.  Once you have created the named query, a table will display on the datasource view with the name of the named query and it will show a list of fields returned from the query.

One thing to remember when creating your data source view is that you should Alias your resulting field names to user friendly names that end users will understand.  So, for example, in the query above, I have the following in the select statement "apo.owneridname AS 'SalesPerson'".  This allows end users to select a field called SalesPerson which makes sense instead of owneridname which doesn't make sense to most end users.

After you create a named view, you need to set a logical primary key so I usually include the GUID for the table that makes sense and make that the logical primary key.  To set the logical primary key, right click on the field in the named query table that you want to be the logical primary key and select set logical primary key from the menu.

After setting the logical primary key, you need to set any relationships to other named queries that you may have.  This option is available by right clicking on a named query table and selecting New Relationship... from the menu.  This is critical to allow users to join information together in Report Builder.

My example data source view is shown in the picture below.

Step 1c. Create a Report Model

Once you have your data source view, you are really close to being done.  Creating the actual report model can be very easy depending on your needs.  I always try to use the wizard as it greatly simplifies things.  The wizard will look through the Data Source View and create the appropriate model entities and source fields based on the fields and relationships defined in the Data Source View.  After the wizard has created the report model, you can edit the results.  The main things I tend to edit are the format of the fields, the names of the relationships, and the default detail attributes.  All of these are available in the properties for a source field or entity.  You simply select the property and update the value.  The format for the fields are useful for money and decimal fields.  Use "c2" for money fields and "f2" for things like quantities.

Step 1d. Publish the Model

Publishing the model is very easy.  First, you have to specify the report server url in the solution properties.  After that is done, simply build the solution and deploy.

Step 2. Making Report Builder Easily Accessible From CRM

Report Builder is typically an icon on the report manager website.  This is very inconvenient for the CRM user who wants to access it.  So, to give CRM users easy access, I put a button on the CRM main application toolbar right next to Advanced Find using isv.config.  The entry in isv.config looks something like this:

<ImportExportXml version="4.0.0.0" languagecode="1033" generatedBy="OnPremise">
  <Entities>
  </Entities>
  <Roles>
  </Roles>
  <Workflows>
  </Workflows>
  <IsvConfig>
    <configuration version="3.0.0000.0">
      <Root>
       <MenuBar>
          <CustomMenus>
           ...
          </CustomMenus>
        </MenuBar>
          <ToolBar ValidForCreate="0" ValidForUpdate="1">
          <Button Icon="/_imgs/bar_bottom_ico_reports.gif" Url="http://cesql01/reportserver/reportbuilder/reportbuilder.application" PassParams="0" WinMode="0" WinParams="directories=0,height=150,width=300,location=no,menubar=no,resizable=no,scrollbars=no,status=no,titlebar=no,toolbar=no" Client="Web, Outlook" AvailableOffline="false">
            <Titles>
              <Title LCID="1033" Text="Report Builder" />
            </Titles>
            <ToolTips>
              <ToolTip LCID="1033" Text="Report Builder" />
            </ToolTips>
          </Button>
        </ToolBar>
     </Root>

Note: the url of course needs to be updated with the url of your SSRS server.  For IFD deployments, there may be some changes necessary to make the server accessible through the internet.

Javascript not enforced in CRM clients when Symantec Client Firewall is installed

Scenario: CRM client machines - both web and Outlook - that have the Symantec Client Firewall installed and turned-on do not enforce onload javascript and throw script errors on load. 

Solution: Disable the Browser Privacy option in the Privacy Control settings of Symantec Client Firewall. When this option is unchecked and saved, the CRM javascript is enforced.  For those concerned about the security ramifications of this - and you know who you are - note that Browser Privacy settings can be enabled for specific sites.  Refer to the link at the bottom for further information.

 

Link: http://service1.symantec.com/SUPPORT/sunset-c2002kb.nsf/46883463d5efbecc85256ee600556cc2/8f21de63ac54b87185256ede00518e41?OpenDocument&ExpandSection=2

Pushing updates and patches to Microsoft Dynamics CRM 4.0 Outlook clients

After my recent post regarding the patches for Microsoft Dynamics CRM 4.0 for Outlook, a reader inquired about how to push updates and patches to clients without the users having to manually download and install them.

Microsoft has the update process covered in 4.0 with the automatic update process.  System administrators can download the client and extract the patches to the CRM server, create a config file, and the patches will be automatically downloaded and installed to the clients.

This link from the CRM team blog contains complete instructions for configuring the auto update process.

Book Review: Microsoft Office SharePoint Services 2007 Best Practices

When deploying SharePoint, it is critical to plan for not only what you need now, but for how it will grow and scale down the road, especially when Document Management is involved.  If you don't plan adequately for searchability, scalability, or disaster recovery, you can easily run into problems.

One criticism I have had of many SharePoint books is that they are written from a technical perspective and explain how to install and configure SharePoint, but they gloss over the critical planning stages or the best practices for scalability.

I've recently read a new book from the Microsoft Office SharePoint team called Microsoft Office SharePoint Server 2007 Best Practices.  As the name indicates, this book is a very thorough guide covering the best practices for planning, deploying, optimizing, and organizing SharePoint.  This book is not necessarily intended to give you step-by-step instructions for installing SharePoint, but rather cover the full lifecycle of a SharePoint deployment and give you best practice recommendations.  The layout and organization of the book is very logical, making it easy to quickly find what you are looking for.  It covers both Windows SharePoint Services 3.0 and MOSS 2007.    It is written to be accessible by anyone on a team evaluating or implementing SharePoint, or anyone who just wants to learn more about it.

The book is broken into the following sections:

Introduction--includes an overview of the various SharePoint Technologies, how to determine which you need, and how SharePoint will help your business.

Part I--Planning--This is not just about planning for the IT infrastructure, but more about planning for the impact that SharePoint will have on your organization, such as dealing with push-back from users after moving away from shared folders to SharePoint document libraries and breaking down departmental "information kingdoms."

Part II--Building--This section gets into best practices for building your environment, including content management strategies, the role of custom development, and dev and test environments, including replication of content between environments.

Part III--Deploying--This section has a great discussion about organizing your content and search/crawling strategy to optimize searchability for your content, security of content, business intelligence, and intranet/extranet/internet deployment scenarios.

Part IV--Operating--This section has great tips on availability, disaster recovery, capacity planning, and performance monitoring. 

What I appreciated most about this book is it is very readable and does not include a lot of "fluff," just straight forward best practices.  For example, in Chapter 8, the section on should SharePoint replace file servers is very straightforward about the limitation of SharePoint and when file servers should still be used.

If you are someone new to SharePoint or a System Administrator charged with deploying SharePoint, this book may leave you wanting more--it is not designed to be a how-to book; however, it does include many good recommendations for additional resources.

 

 

 

 

Microsoft Dynamics CRM 4.0 for Outlook Cumulative Hotfix

The Microsoft CRM Team blog published a list of the top ten common issues with the CRM 4.0 Outlook client and their fixes.  This is a very useful reference that comes in very handy when you install CRM for Outlook.  The most exciting news is that The hotfix package for KB950088 is a cumulative hotfix that will fix all ten of these issues (from reading the description on the link, it would not appear so, but Microsoft confirms that it is a cumulative hotfix).

Possible Pitfall when Using Email Router for Incoming Email

Situation: The CRM 4.0 Email Router is configured so that it can check individual user's mailboxes for incoming CRM email. This configuration wasn't possible in version 3.0. The only option in 3.0 was to use what is now termed the "Forward Mailbox" method whereby a rule created on each user's Inbox forwards, as an attachment, all messages that are received to the "forward mailbox", at which point the Email Router examines the message and creates CRM Email Activities as appropriate. While the "Forward Mailbox" method is still an option in 4.0, several other options are also available. Following is a description of each:

1. Email Router:
   1. Incoming Messages – When using the Email Router for incoming email, the router must check each individual users inbox and process messages accordingly
   2. Outgoing Messages – When messages are sent directly from the CRM web-client, the Email Router sends them. When sent from Outlook, Outlook/Exchange sends them as they do any other non-CRM emails.

       

2. Forward Mailbox:
   1. Incoming Messages – A rule on the users Exchange Mailbox forwards each incoming email a an attachment to the designated "Forward" mailbox. The email router then checks this one mailbox and creates CRM email activities
   2. Outgoing Messages – N/A

       

3. Outlook Client:
   1. Incoming Messages – When the Outlook Client synchronizes with Microsoft CRM, it checks each new email message in the users Inbox and creates CRM email activities for those emails that are in response to CRM emails.
   2. Outgoing Messages – When messages are sent directly from the CRM web-client, Outlook is still used to send the email. Note that with this method, unless the users Outlook is running, the emails will NOT be sent. The next time the users starts Outlook, any pending emails will be sent. Emails sent from Outlook process as any other non-CRM emails.

The CRM implementation guide does a good job of explaining each of these and when to use each method. Additionally, there is a white-paper available that gives recommendations for which method to use. Please contact me and I can send you a copy.

One item to watch out for; however, is the way that the Router scans the inbox when it is used for Incoming messages. We had a situation where a particular user had roughly 25,000 emails in their Inbox. When we setup the Email Router to check individual mailboxes and specified on that user's CRM record that for incoming messages, the email router should be used, the router actually started checking every message in the user's inbox. The result was the creation of email activities in CRM from messages going back several years. So, if you choose to use this method, watch out for users that have very large inboxes.

Changing Service Calendar Colors in Microsoft CRM

Situation: I recently worked with a client who used an Excel spreadsheet to track all of their consultant's service appointments. Each type of appointment (e.g. "Confirmed Client Appointment", "Vacation", "Tentative", etc.) had a specific color coding within the spreadsheet. To give them better visibility into their scheduling, we implemented Service Activity tracking and Service Calendar functionality in Microsoft CRM. While CRM does automatically assign a unique color to each Status Reason on the Service Activity, the colors did not match up with what the client was accustomed to seeing. They asked me if there was a way to customize the colors. Turns out, there is.

Within the website directory for CRM (e.g. c:\inetpub\wwwroot), locate the \SM\Gantt\Style directory. Within that directory, locate the file GanttControl.css. Towards the bottom of the file, you will find an entry for each Status Reason that is added to the Service Activity entity. They will look something like this:

div.ganttBlockServiceActivityStatus8

{

border: 1px solid #A5DE63;

FILTER: progid:DXImageTransform.Microsoft.Gradient(gradientType=0,startColorStr=#E7F6D6,endColorStr=#A5DE63);

}

As Status Reasons are added, an additional section like the one above is added to the file, with the # at the end of the first line corresponding to the integer value of the status reason. All you need to do to change the color is find the corresponding HEX code of the color you want to use and replace the startColorStr and endColorStr values in the corresponding block of code. Note, leave the # and replace the six characters after. A good location to find the corresponding HEX codes is here: http://html-color-codes.com/. This is what you'll find:

Simply use the corresponding code for the color you'd like. The result is a much more visually appealing Service Calendar:

As always, be sure and create a copy of the GanttControl.css file prior to making any changes.

Retrieving All Records with Fetch XML

You may or may not be aware that when retrieving records from the CRM service using the Fetch() method, CRM will limit the results to the first 5000 records by default. I found a nice post by Ronald Lemmen on fetching all records, but I wanted to take his concept a step further by having a function do the following:

1. Accept fetch xml  in a string format
2. Add the paging functionality to fetch all records
3. Merge the results together
4. Return the merged results in a string format

I like this approach because it uses no extraneous data types, the fetch xml is passed as a parameter (no hard-coding of your fetch unless you want to!), and the returned xml is identical to what the Fetch() method would return (except more records are there, of course).

Auto close a Case-via Workflow

The traditional way to Resolve a case is for the user to click on "Actions" on the case and select the appropriate status. However, if capturing the time for case resolution is not critical, work flow can be used to eliminate this step.  For example, the user wants to simply change the status of the Case to Approved and have the Case be resolved without the extra step.

To create a work flow do the following:

1) Setup work flow on entity Case

2) Add condition for Status that is to trigger the case to Resolve.  For example, Status equals Approved

3) Add step of "Change record Status to " the Resolved status you want the case to display

4) Publish Work flow

This will Resolve the case with the status you have selected in your "Change record status to" step.  The value for the Resolution for the Case will default to the Title of the Case itself.

Again this may not be applicable to all scenarios but if a client wants to streamline the process this may be an option.

   

Cannot Track in CRM Error

This is a very quick post but I wanted to pass it on.  I had users in a certain role that were not able to track items in CRM.  The error was a Synchronizing Records error and the description was "The logged on user does not have the appropriate security permissions to view these records or perform the specific actions".  The fix was to make sure the user role has at least "Read" access on the Leads entity.  Even if leads are not being used, this level of access must exist. 

Customer Effective User Group 2008

Customer Effective is proud to announce our 2008 User Group, November 6-7, 2008.   The User group will focus on Microsoft Dynamics CRM 4.0, and will be held at our corporate headquarters and the historic Westin Pointsett in Greenville, SC.

This event will include many informative sessions including:

• Keynote address by David Smith, General Manager, US Dynamics Partners, Microsoft
• CRM Business Intelligence
• CRM/SharePoint integration
• Microsoft CRM Customization
• Getting the most out of your sales module
• Workflow automation
• much more

Visit our website to learn more and register.  There is no cost to register or attend, other than your transportation and hotel.

Default Unit of Measure-version 3.0

In many implementations, the Customer's product catalog will only contain one unit of measure.  If this is the case, most of the time the unit of measure is Each=EA.  A common question from customers with this set up is "since my unit of measure is always the same, why do I need to select a unit of measure on my Opportunity or Order products?".  This blog provides basic Java script to default the unit of measure for version 3.0 for Opportunity products. 

1. Go to Product Catalog under settings and open Unit groups
2. Open the Unit of Measure you want to use for your default
3. Click Ctrl-N to get the guid value for this unit of measure
4. In the code below copy and paste the guid in
5. // This sets the default values for Quantity and Unit of Measure upon a new
   //form
   //  UOM = Each
   var CRM_FORM_TYPE_CREATE = "1";
   var lookupItem = new Array();
   var iUnit = "Each";
   if (crmForm.FormType==CRM_FORM_TYPE_CREATE)
      {
       // Set: GUID of unit "Each", object type code and the text name "Each".
      lookupItem[0] = new LookupControlItem
   ("{C96EA008-7D70-DD11-A905-001B7803AB14}", 1055, "Each"); // Set the form control value to the lookupItem just created.
      crmForm.all.uomid.DataValue = lookupItem ;
      }
6. Place the Java script on the Onload event of the Opportunity Product form and on the Onchange event of the product attribute field
7. Enable and publish

Best Practices For Refreshing Data in a Microsoft Dynamics CRM 4.0 Test Environment

For high availability CRM deployments, it is recommended that you have additional environments for test, Dev, and QA, so you can manage changes to your configuration without impacting users.  This is also a good idea for disaster recovery, so if the production environment fails you can roll over to one of the other environments.

To have a valid test environment, you need to closely approximate your production CRM environment, with current configuration and data.  So what is the process to "refresh" the data and configuration in your test environment to match the production environment?

Step 1. Deactivate test CRM organization using CRM deployment manager.  On the test CRM server, open Deployment Manager by going to Start-->All Programs-->Microsoft Dynamics CRM-->Deployment Manager.  Open the Organizations folder, select your MSCRM test organization, and click Disable from the right side menu.  Note you will need to be a deployment admin to perform this step.

2. Delete test CRM organization in Deployment Manager.  Once you have disabled the Organization, you will be able to delete it.

3. Drop test MSCRM database in SQL Server Management Studio on the test SQL environment.

4. Restore backup of prod MSCRM database to the test SQL Environment

5. Import organization to the test environment using the CRM Deployment Manager.  In Deployment Manager, click Import Organization, and follow the wizard to point it to the the restored MSCRM database.

Error when tracking e-mails that have an attachment

Scenario:  When using Microsoft CRM 4.0 for Outlook, you receive the following error message when you attempt to track an e-mail that has an attachment:

An error occurred promoting this item to Microsoft Dynamics CRM.

Microsoft has identified that this is an issue with Microsoft CRM 4.0, especially when the file name contains special characters like ampersands (&).

A hotfix has been released that fixes this issue.  You can request it here.

This is a fix for the client, and needs to be installed on any workstations that have the issue

Error when Importing Entities: Invalid Name Prefix

Recently, I came across this error when trying to import some entities from one environment to another:

Failure: Equipment_New_Project: Invalid Name Prefix

Basically, CRM did not like the name given to the relationship between the Equipment and New_project entities. A workaround is to open the configuration xml and find the "Equipment_New_Project" string (it'll be in the Relationships node) and change the name to "New_Equipment_New_Project". That seems to do the trick.

As far as I can tell, this may be happening when moving configurations from one server which was upgraded from 3.0 to another CRM 4.0 server (not sure if having been upgraded matters).

Currency column must be included if showing money field from related entity

 

 

When displaying a money column from a related entity in a CRM view the Currency field from that related entity must be included in the view as well.

For example we have a Lead that is related to a Loan. We add the Loan Amount column to the Lead view and we get an unexpected error. We must also include the Currency column from the loan entity to avoid the error.

With DevErrors "On" the text of the error is:

transactionCurrencyId needs to be supplied to format a transaction money field.

Screenshot of error with DevErrors "On":

Hide Opportunity Menu Options

Now you can easily control the opportunity close process with an attribute and workflow instead of using the Actions - Opportunity Close... feature, and no longer have to ask your users to ignore the Recalculate menu options. Use the following Opportunity entity onload code to hide both of the recalculate menu options, as well as the opportunity close... menu option.

var RecalcOppy = document.getElementById("_MBcrmFormSubmitCrmForm1truetruefalse");

RecalcOppy.style.display="none";

var RecalcOppy2 = document.getElementById("_MIcrmFormSubmitCrmForm1truetruefalse");

RecalcOppy2.style.display="none";

var CloseOppy = document.getElementById("_MIcomplete");

CloseOppy.style.display="none";

SQL Server Reporting Services 2008, part 3

In previous posts we discussed some of the exciting new reporting capabilities in Microsoft SQL Server 2008, including new text field  and output options and new reports and gauges.  In this post, I'm going to discuss what may have some of the biggest impact on reporting for Microsoft Dynamics CRM: the tablix.

A little background--prior versions of SQL Server Reporting Services included two table options.

Table: A table is what most people think of when they think of a report--a table is a grid that has a defined number of columns and one row per record returned by the report query.  For example, this would be a table displaying your company sales representatives with their phone numbers.

Matrix: A matrix is a grid that functions similarly to an Excel pivot table.  It groups data based on the row and column headers, and the number of columns is dynamic based on the number of values in the group.  A matrix is very useful for summarizing a large data set.  For example, this would be a matrix displaying pipeline value by sales stage grouped by sales representative.

 

These two options work great; however, there are frequently times when you want to have a grid that has both characteristics of a table and a matrix.  For example, you want a summary of sales pipeline by sales stage and sales rep, but you also want some additional data columns.  With SQL Server 2005 matrixes, the only additional columns that you can have is a subtotal column.

SQL 2008 solves this issue with a new capability called Tablix.  As it sounds, it is a hybrid between Tables and Matrixes (and I guess Tablix sounded better that Mable).

But don't expect to see it as a third option when you design a report in Business Intelligence Development Studio--you will still see just Table or Matrix; however, when you are in layout mode in report designer on either a table or matrix and right click on a row or column, you will find an option to define a tablix area.  Once you insert a tablix area, you define row and column groupings just like you do with a matrix.  If you right click to add a row, you have the option to add a column inside the tablix area (matrix column), or outside the tablix area (table column).

The result is that you can basically add table characteristics to a matrix, or matrix characteristics to a table.  For example, say I wanted to combine my two examples above, and have a report showing sales reps with their phone numbers and a summary of their pipeline by sales stage, tablix makes it possible.

 

When I design reports, I frequently see cases where a client wants a report with the characteristics of a tablix.  When users manually build reports in Excel, they frequently summarize data in a mixed format.  With tablixes and the enhancements to charts and text formatting, SSRS Reports can now reproduce these formats without limitation.

CRM 4.0: Updating ISV.Config Programmatically Through the API with C#

This took me a little while to figure out, but I have to give credit to Michael at Stunnware for being about the only person who's displayed some example of how to utilize the CRM Service to update the ISV.Config customizations. What I would like to do is demonstrate how to merge customizations from an XML file into the existing ISV.Config.

I think this is a pretty neat set up, considering when you import ISV.Config customizations, the existing ISV.Config data is overwritten. So it's super-important to take existing customizations into account when you import your own.

So, let's get down to business....

More fixes for waiting system jobs in Microsoft CRM

In a prior post, Proper Care and Feeding of Microsoft Dynamics CRM System Jobs,  I detailed some of the things that you can do if your system jobs in CRM do not run successfully.  The solutions that I described are applicable in the case where the Asynchronous service is running, but the system jobs are not processing. 

So what should you do if the Asynchronous Service stops running, or if it is running, but you see the following error in the Windows event log:

Host Hostname: Failed while monitoring asynchronous operations queue. Exception: System.InvalidOperationException: There was an error generating the XML document. ---> System.Net.WebException: The request was aborted: The request was canceled.

This situation is addressed in Microsoft KB 952755.  The solution is to install .Net 3.0 service pack 1, available here.

Building Automatic Configuration and Multi-Tenancy into Your Custom ISV Web Pages

I've been doing a lot more work on some custom web pages for CRM lately. The SDK recommends that any custom code should be placed in the <CrmWeb>\ISV\ folder. While there are several reasons this is a good idea, the one that sticks out in my mind is that this means your code will always from the the CRM server, and the url to access your pages will largely be set in stone.

For me, that is pretty nice when you are writing functionality that needs to take multi-tenancy into consideration. When deploying a custom page in the ISV folder, you can access the page much like many other CRM pages:

http://crmserver/organization/isv/mycustompage.aspx

There are a couple of funny things that can go on if you try accessing pages without specifying the organization in the url. I will skip those, because that discussion could open a nice can of worms. For the purposes of this post, I will assume you've read this very informative post on CRM urls and you plan on adhering to the guidelines within.

So, let's say you have a custom web page located in the ISV folder, and you'd like to be able to set the CRM web services references without the need for hard-coding the server name or specifying which organization you should be accessing. How to do it? Well, probably the simplest way is to just parse the url. Here's some code that I've developed to make the task easier:

CRM 4.0 Global Javascript Variables

I've been doing quite a bit of scripting from the global menu bar lately and I've found a great post by Huib Aarts on some global variables that you can leverage in your client-side javascript. I've found a few more of these variables that can provide some use. Here's a full list of the variables I reference:

• SERVER_URL: This variable stores a string that specifies the base server url - the server name along with the organization name. Example: http://servername:port/organization
• USER_LANGUAGE_CODE: This variable specifies the LCID value for the language pack used by the user. For English, this would be 1033.
• ORG_LANGUAGE_CODE: This variable specifies the LCID value for the organization's base language. For English, this would be 1033.
• ORG_UNIQUE_NAME: This variable stores a string that specifies the current organization's name.
• ORG_DATE_START_DAY: Represents the first day of the week specified for the organization (0 = Sunday, 1 = Monday, etc).
• IS_LIVE: Boolean value to specify whether the deployment is a CRM Live deployment.

Also, if you ever find yourself needing to utilize arrays to store days and months, why not just use the ones already created by CRM? Note that these arrays are available on the entity form, so if you have javascript on the global menu bar, you may not be able to access them:

• LOCID_ARRAY_SHORT_MONTHS: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,
• LOCID_ARRAY_LONG_MONTHS: January,February,March,April,May,June,July,August,September,October,November,December
• LOCID_ARRAY_SHORT_DAYS: Sun,Mon,Tue,Wed,Thu,Fri,Sat
• LOCID_ARRAY_SHORTEST_DAYS: Su,Mo,Tu,We,Th,Fr,Sa
• LOCID_ARRAY_LONG_DAYS: Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday

Duplicate Detection vs. Disk Space and Performance

The built-in Duplicate Detection tool is a powerful feature in CRM 4.0. With great power comes great responsibility – and the improper application of Duplicate Detection Rules can be responsible for huge growth in your database.

Let’s look at a few factors that could create significant impact on your server.

1) Turn off Duplicate Detection during a Data Migration – If duplicate detection is ‘on’ during a large import, prepare for a long day (or night). Best practice is to disable all rules before attempting any significant data migration.

2) Duplicate rules that include fields with ‘blank’ Data dramatically increases the number of duplicates – Every “Blank” field matches every other ‘blank’ field.  e.g. all empty email addresses are considered duplicates of every other empty email address. –I can see some logic in that, but it makes it impossible to use duplicate detection when you want to ensure unique email addresses, but you do not want to make the email address a required field. (Personally, I’d like the option to ignore empty fields when checking for duplicates.)

3) Avoid running System-wide duplicate detection jobs. If you aren’t sure of how many duplicates you have, do not run a system-wide duplicate detection job on all records without pre-testing with some simple SQL. – In a recent dev environment, I checked a freshly imported batch of 300,000 contacts for duplicates with LastName+EmailAddress1 as the rule. Because the duplicate detection engine cross-joins the table on itself and creates at least one and often more than one record for every matched pair of duplicate records, the DuplicateRecordBase table grew to over 9 million rows and consumed all remaining disk space on this test server. – After looking into it further, only about 1/3 of the contacts in the system had an actual email address, thus creating the high number of duplicates. – As a best practice only run your duplicate detection rule during record creation/updates or on small groups of records at a time.

4) Prevention is the best policy. Configure your lookup fields to ensure that anything you are checking for dupes on could be searched. – Make it EASY for users to find the records they need and they will be less likely to create duplicates of existing records.

--Scott Sewell

Additional ways to fix the “Do you want to save this file? Blank.ASPX” problem

In a recent post, Matt Putnam described a workaround to fix the "Do you want to save this file? Blank.aspx" issue.  The workaround that he described typically solves the problem, but we have found that in some cases it does not, especially environments with restrictions against downloading files.

The issue is caused in Internet Explorer 6 by KB953838 – Cumulative Update for Internet Explorer.  After doing the workaround, IE will still download blank.aspx to your computer rather than opening it from the server, it just won’t prompt you to ask if you want to do it.  The issue we have seen is that in environments with download restrictions, this workaround may cause error messages.

Here are some additional fixes for this problem (thanks to Corey @ Microsoft).

1.  Uninstall hotfix KB953838

2.  Upgrade to IE7—it doesn’t have this problem.

3.  Put some dummy texts into Blank.aspx (websiteroot\_root\blank.aspx)which triggers ASP.NET 2.0 to generate Content-Type in HTTP headers explicitly. IE would then pick up the explicit Content-Type HTTP header and use it to persist a correct copy of Blank.aspx into its Cache.  <meta http-equiv='Content-Type' content='text/html;' />

4.  Register ASPX file with IE as per http://support.microsoft.com/kb/905703/
Note: IE implements a 2-phase handling of MIME files: first attempting to resolve by IE itself, then routing to the Windows Explorer to see if any file extension handlers registered there.

5.      Force IE to read blank.aspx from IIS server each time rather than from its local cache (Tools ->> Internet Options ->> General ->> Settings ->> Check for newer versions of stored pages ->> Every time I visit the web page).  This could cause performance issues, so it is not recommended.

Categorizing Tracked E-Mails in Microsoft Dynamics CRM 4.0

OK, so say that you receive a lot of e-mail messages.  Fortunately, you have Microsoft Dynamics CRM, so you track these messages from Outlook and associate them with the Account, Contact, or Opportunity that they pertain to.

But say that you want to further categorize these E-mail messages, so that when you look through a mile-long list of E-mails on a contact record, you can see those tagged as "Product Question."

On the E-mail activity form, there is a Category text field, which can be used for this purpose; however, you will notice that when you open a email that you sent or received in CRM, the form is read only--you cannot edit the category field.

This is by design.  When an e-mail has been sent or received, the record in CRM is deactivated.  You would not want users going in after the fact and changing the contents of an e-mail record--this would call into question the integrity of the activity history in CRM.

Also, the category field does not map from Outlook to CRM.

So how can you categorize tracked e-mails in CRM?

It actually is quite easy to do using a simple one-step workflow.

1.  Create a workflow associated with the E-mail entity.  In this example, call it something like "Set Category: Product Question."

2.  Set this workflow to run on-demand

3.  Insert a step to update the E-Mail record.  Click the properties button to open the E-Mail form.  Click on the Category Field on the form.  Type "Product Question."

4.  Save and Publish the workflow. 

Now you can run this workflow rule from individual E-mail records, or select multiple E-mails from the history view and classify them as Product Questions.  Repeat these steps for additional categories.

Use Advanced Find to Evaluate Marketing List Members

In Microsoft Dynamics CRM 4.0, if you want to add or update the contacts on a marketing list, open a marketing list record, click the "Marketing List Members" navigation bar link, and click the Manage Members button  on the top of the Members list.

This brings up the manage members dialogue, which gives you several choices you can use to update the members of the marketing list.

Most of these options are pretty straightforward, but I get questions from time to time about the final option, Use Advanced Find to evaluate members.

This is a very useful option for existing marketing list members.  Say you have an existing Marketing List, and you want to update the marketing list to only include those contacts on the marketing list who are in Texas.  The Evaluate Members option allows you to perform an advanced find and update the marketing list to only include the members that meet the search criteria.

The problem is that when CRM 4.0 was released, the Evaluate Members option was not working correctly.  You could select it and perform the advanced find; however, it didn't do anything.

The good news is that this issue has been fixed.  See Microsoft KB 954498.  You can request the download here.

URL to View/Edit Associated Notes

In case you ever want to view the Notes related to any particular entity record, here's the URL you can use:

http://<crmserver>/<organization>/_controls/notes/notesdata.aspx?id={<recordid>}&ParentEntity=<entitytypecode>&EnableInlineEdit=true&EnableInsert=false

Replace <recordid> with the Guid of the record and <entitytypecode> with the object type code of the entity (If you're not sure what the object type code is, use the SDK browser: http://<crmserver>/<organization>/sdk/list.aspx and pull up the entity definition.).

Notice I have EnableInsert set to false, so you cannot add notes. This is because you will receive errors when trying to insert a note from a location other than the entity form. I have, however, been able to edit existing notes with no problem. If you want to disable editing, simply change EnableInlineEdit=true to EnableInlineEdit=false.

Plugins: Getting an Entity's Record Id in the Pre-Create Stage

If you ever have a need to work with the record id of a particular record during a pre-create stage with your plugin, then this post is for you. The title is a little misleading, though - you cannot actually get the Guid of a CRM record before that record is created. What I've found is that instead of trying to get the record id, you can create it. Here's how:

Let's say we have our plugin and we have grabbed the data being saved. What we'll do is create a new KeyProperty and add that to the entity's properties collection:

//  Grab the entity record
entity = (DynamicEntity)context.InputParameters.Properties["Target"];

//  Create the record id
string sKeyAttribute = entity.Name + "id";
Guid oGuid = System.Guid.NewGuid();
KeyProperty oKey = new KeyProperty(sKeyAttribute, new Key(oGuid));

//  Add the record id to the properties
entity.Properties.Add(oKey);

That's it. You've created your own record id. Of course, now you can subsequently use the value of oGuid in your code. I've used this in the 4.0 environment and it's worked well so far. What I imagine CRM is doing when the record is created is checking for the existence of the key property and only creating it when it is not present. I'm not really sure if it's documented or an intended behavior, but it's a nice piece of functionality.

So why go this route instead of waiting and doing a post-create plugin? Well, I like this method because it involves no additional web service calls (getting the service, retrieving the entity, updating, etc) as long as you aren't touching other entities.