Customer Effective Blog

Microsoft Dynamics CRM Automatic Account Assignment by State

This post was inspired by a recent question in the  Microsoft Dynamics CRM Forums.

Someone asked

"We have multiple sales people each of which is responsible for a number of the 50 US states. I want to create a workflow that automatically sets the owner depending on the State/Province field of the lead."

There are several approaches that you could take to automatically assign the owner of a lead or account based on the state/province.  The following is a fairly simple "no code" approach that works pretty well.

1.  Create a custom State entity.  This entity can be very simple--just create a new User Owned entity called State. You don't have to add any additional attributes--just go with the default "Name" attribute for the primary attribute.

2.  Create a 1:N relationship between the Custom State entity and Account (or Lead or Contact, depending on where you want this to work from).  Name the relationship attribute something like State--you will probably want to either rename the default State or Province attribute or choose a different name for your relationship attribute, so you don't confuse them in advanced finds.

3. Edit the Account form--remove the standard State/Province attribute from the form and add the new custom state lookup attribute in its place.  Save and publish the form.  When accounts are added, users will now select the State or Province from the lookup, rather than typing in the name.  The added benefit of this approach is you will get standardized state names on your Accounts, instead of some users entering MS, some typing Miss, and some typing Mississippi.

4.  In your custom State entity, create one record for each State or Province.  Use the owner field on each record to connect the state to the sales representative that manages that state.

5.  Create a workflow based on the Account entity.  Call it something like "Assign Accounts."  Set it to run upon create of Account and On Demand.

6.  Add an "Update Record" step and click "set properties."

7.  When the "Update Account" form launches, go to the Administration tab and select the Owner field.  Using the Form Assistant on the right side of the form, select "State" in the "Look for:" entity picklist and "Owner" in the attribute picklist.  Then click Add and then OK.

8.  Then go to the "Additional Fields" tab and click on the "Address 1:State/Province" field and with the Form Assistant select "State" in the "Look for:" entity picklist and "Name" in the attribute picklist, then click "Add" and "OK."  The reason for taking this extra step is because we still want to populate the standard State/Province field, as it is integral to the Outlook integration.  If we created Accounts without a value in this field, then we created Contacts on that Account, no State value would be mapped to the Contact, and then any users who synchronized those contacts with Outlook would not have State on those records.  This approach insures that the standard State field will still be populated, so the chain won't be broken.

Considerations for reassignment

The process that I have outlined will automatically assign your Accounts to the sales representatives responsible for the State when the Account is created.  You could easily make it update the Owner when the Company moves by modifying step 5 to also run upon update of the Account State lookup entity.  But what can you do if you have a sales representative leave the company and you reassign the State to another user?

One important thing to understand about workflows created via the CRM workflow wizard is that in the case of a 1:N relationship, such as our relationship between State (1) and Accounts (N), it is possible to update the N with a value from the 1 when the N record is created and also update the 1 with a value from the N when the N is created or updated, but you cannot update the N when the 1 is updated.  To clarify, with our workflow, we are updating the Account with the Owner of the State when the Account is created or updated; however, we can't have a change to the owner of the State record change the Owner of all of the Accounts associated with the State using the out of the box workflow wizard.  We could with a custom plug-in, but we're looking at "no code" options.

Here are two approaches that you could take.  This is not an exhaustive list, and what is best for you will depend on the volume of accounts that you have in CRM.

1.  Use the Reassign Accounts feature in CRM.  Before deactivating the old sales rep's user record in CRM, open the user record, select actions, then click reassign records.  Click "Assign to another user" and use the lookup to select the user that you want to assign the records to.  As the warning tells you, this will assign ALL records owned by the selected user to another user.  This is a good option if you are keeping the state/territory makeup the same, just changing the user, and if you don't want to keep any historical records, such as activities assigned to the old user.  You will still want to go into the State record and change the sales rep associated with the State.

2. Update the sales rep assigned to the State on the custom State record, then do an advanced find to find any accounts in that state.  Then select all records and click the "Run Workflow" button   and select your "Assign Accounts" workflow.  This will reassign the selected Accounts to the new sales rep for the state.  One consideration is that if you have more than 250 accounts in the selected State, you will need to apply the workflow to every page in the view.

 

Technorati Tags: CRM,Microsoft CRM,workflow,Dynamics,territory management,states,accounts

Convergence Early Registration Extended and other Convergence 2009 News

We are really looking forward to Convergence 2009, March 10-13 in New Orleans.  If you aren't familiar with Convergence,

Convergence is the premier Microsoft Dynamics event, bringing customers, partners, team members and industry experts together to discuss ways your current Microsoft Dynamics solution can help your company cut costs, improve operational efficiencies, increase employee productivity and maximize your current platform investments, which, in turn, can foster long-term, profitable growth.

Some Convergence news from Microsoft:

• Early Registration discount date has been extended to January 21, 2009.  Save $300 by registering early, secure your favorite hotels while they are still available and take advantage of better airline rates.
• The session catalog (containing 225 sessions) is now live on the Convergence marketing site.  
• The Convergence Customer Excellence Awards recognize, honor and celebrate customers who have achieved outstanding success with their Microsoft solutions.  Customer Excellence Award nominations are now being accepted through January 20, 2009.  

I will be at Convergence 2009 and will also be participating in some of the CRMUG events. 

Technorati Tags: Convergence 2009,Microsoft,CRMUG

Microsoft Dynamics CRM 4.0 Rollup 2 Released

UPDATE--users have commented about an error after installing rollup 2 where they can no longer publish workflow rules.  Here is the solution:

you need to manually open the Microsoft CRM Web site web.config file in an editor like Notepad and add the following section to it in order to continue being able to publish workflows:

 

In the section <authorizedTypes>

…multiple lines here

 

                                                <authorizedType Assembly="mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Namespace="System.Globalization" TypeName="CultureInfo" Authorized="True"/>

 

</authorizedTypes>

----------------------------------

Microsoft has released Update Rollup 2 for Microsoft Dynamics CRM 4.0.  This cumulative update includes a large number of hotfixes released after Rollup 1.

You can download the rollup here.

This is a move to more frequent rollups than there were in the past. 

According to Microsoft, "Dynamics CRM 4.0 Update Rollups are planned on an “every other month” basis going forward.   So Update Rollup 3 should be on the horizon for mid-month March 2009, Update Rollup 4 mid-month May 2009, etc.   This will provide a consistent release schedule and allow for more planned testing and deployment of hotfix packages going forward."

Technorati Tags: Microsoft CRM,updates

Configuring an IFD-only Microsoft Dynamics CRM Implementation

As you are probably aware, you can configure Microsoft Dynamics CRM 4.0 to be fully accessible from outside of your network using the CRM IFD tool.  Using this simple executable you can configure your CRM environment to work only "On Premises" (only over the network) or "IFD + On Premises" (Windows authentication while on the network, forms authentication when off the network).

But what if you want to configure CRM to work exclusively in IFD mode?  Consider the following scenario--you have some users who are in the office and some who use CRM remotely, and you want to standardize your user processes and CRM URL for all users.

You will notice when you run the CRM IFD tool that there is no option for IFD only; however, it can be done.

To configure IFD Only, you would still select IFD + On Premises, and enter in the IP address of your server.  The trick is the subnet--this determines which PC's are internal vs. external.

So if you want everything to be treated as external and sent to IFD, you would enter the IP address of your server, but enter a subnet that is different than your network.

For example, if my internal subnet was 255.255.252.0 and my CRM server ip address was 10.10.10.10.  I could put 10.10.10.10 - 255.255.255.255 in the subnet, and then all users would be considered outside of the subnet and would get the IFD sign-in page, except if they log on from the CRM server.

 

Technorati Tags: Microsoft CRM,IFD,forms authentication only

CRM New Year's Resolutions

At the start of a new year, it is a time-honored tradition to set New Year's resolutions--goals to make yourself a better you.  Besides stopping smoking, losing weight, or running a marathon, this is a great time to evaluate where you are going with your Microsoft Dynamics CRM deployment and take little steps to get more out of it.

Jim Glass asked "What are your New Year's Resolutions?" over at the CRM Team Blog

Here are 10 New Years Resolutions that would help almost anyone get more from their Microsoft Dynamics CRM deployment:

1.  Check out the Microsoft CRM Accelerators. 

The latest accelerator, the Enterprise Search Accelerator, was just released yesterday.

2.  Get your environment up to date. 

If you haven't already downloaded it, you should install Microsoft Dynamics CRM 4.0 Update Rollup 1.  And make sure that you stay up to date on new updates and hotfixes.

3.  Get familiar with the CRM 4.0 server diagnostic tools. 

If you don't have it, you should download the Microsoft Dynamics CRM 4.0 Diagnostic tool and use it whenever you experience unexplained CRM problems.  You run the diagnostic tool, turn on platform tracing, recreate the issue, and turn off platform tracing, it will create a detailed log file of everything going on with CRM, and you can frequently see more detail of what the problem is and find the solution.  Also, this is the #1 step you can take to get a faster solution when you open a support case with Microsoft--submit the platform trace log with your support case--this will save a lot of time, and support won't have to come back and ask you to run the diagnostic tool later.

4.  Get familiar with the CRM 4.0 for Outlook diagnostic tools. 

You may be familiar with the Outlook client diagnostic tool located at Start-->All Programs-->Microsoft Dynamics CRM 4.0-->Diagnostics.  When you run the diagnostic wizard, it will find and even fix some of the most common problems with CRM 4.0 for Outlook.  However, were you also aware of the advanced diagnostic tools available with the CRM 4.0 web client?

When you run the diagnostics tool, instead of clicking "run diagnostics," click the support mode button.

This will give you two tabs to choose from.  The Synchronization Troubleshooting allows you to enable/disable specific synchronization processes.

The Advanced Troubleshooting tab includes options similar to the CRM server diagnostics tool, but this time for the client.  You can turn on tracing, delete Outlook temporary files, and create a file that includes relevant logs and machine profile information.

As with the server diagnostics, if you ever have issues with a PC running the Outlook client, you will save significant time dealing with Microsoft support if you first create a trace file of the issue and generate the troubleshooting file for support and submit with your support case.

5.  Get involved in the Microsoft Dynamics CRM community. 

One of my favorite things about Microsoft Dynamics CRM 4.0 is the strong community of users around the world.  By tapping into this community you can find answers to questions, learn from other user's mistakes, help other users, and meet great people.  Probably the best place to start is the Microsoft Dynamics CRM forums.  There is very rarely a time where I have a question or see a CRM error message that a quick search of the forum finds someone else who has had the same question, and how they solved it.  Another great resource is the Microsoft Dynamics CRM newsgroups.

Next, update your RSS reader with the best CRM Blogs on the web.  Other than the CEI Blog (hey, I'm biased), a good starting point is the Microsoft Dynamics CRM Team Blog.  Any big news regarding Dynamics CRM will be covered there, since that is straight from the Dynamics CRM team.  For other blogs, check out the "Blogs We Read" list on the right side of the CEI Blog.

If you are into the social networking thing, join the Microsoft Dynamics CRM group on Facebook and the group on Linkedin.

6.  Get Certified. 

Just the process of studying to prepare for CRM 4.0 certification will force you to learn about all parts of Microsoft Dynamics CRM, and as a result, you will fully understand how it all fits together and probably find some new ways to use the application.

7.  Clean up your sitemap. 

Take a look at your CRM wunderbar--are there any icons that users don't use?  Or maybe there is a section that certain groups use and other users don't.  One of the easiest things that you can do to make CRM more user friendly is to hide the unused links or make them only available for the groups that use them.

• Sitemap surgery tips for manual sitemap editing
• Use the CRM demonstration tools to automatically edit the sitemap
• Use the privilege element in the sitemap to hide or reveal sub-areas for certain users

8.  Clean up your data

The beginning of the year is a great time to do some cleaning, and if you have used your CRM system for more than a year or two, chances are that you have some level of disorganized data in your system.  Like with your car or house, you want to periodically check for junk and clean it out.  Otherwise, it has the potential to build up and cause problems or affect user experience

• Clean out old leads.  If you use the Lead entity in CRM, you will want to make sure that you don't persist data for long periods of time in the lead entity.  The reason is that if you have a lead that you use for long periods of time, there is a potential that someone may set up a contact record in the system for the same person, and users will record activities and other data against both.  Also, there are certain pieces of data, such as notes, that do not convert when you convert a lead.  So the longer that you keep a record as a lead, the more likely that you are that notes will be added to that lead, which will be lost when you finally convert the lead.  Leads can also be a major source of database crud, especially if users are not extremely disciplined about qualifying/disqualifying leads.  Take a look at your lead entity--do you see a bunch of leads that were created in 2002?  If so, clean them out. We recommend using the lead entity only as a quick starting entry.  It is convenient to be able to enter data one time and have CRM create an Account, contact and opportunity at the same time when you convert it.  We recommend converting leads to accounts or contacts as soon as possible.  If they are not yet qualified prospects, give the Account or Contact records a relationship status of "Lead" or "Suspect." 
• De-duplicate your data.  Nothing frustrates user's experience with CRM more than the appearance of bad data--if I don't think that the data in CRM is correct, or if there are multiple records for the same company, I will be less likely to trust the application, and in turn, less likely to use it.  One of the most powerful features of Microsoft Dynamics 4.0 is Duplicate Detection.  Even if you have duplicate detection running on create of records, it is still a good idea to periodically run a duplicate detection job of your main entities, such as accounts, contacts, and opportunities and clean out any duplicates. 
• Get rid of duplicate e-mail addresses.  I mention this separately from the de-dupe point because I wanted to highlight it.  One of the worst things that you can do in CRM is have duplicate e-mail addresses.  For example, you never want to have the same e-mail address on a user and a contact record, or on a contact and an account record.   The reason is that CRM for Outlook and the e-mail router use the e-mail address of in-coming and out-going e-mails to resolve which records to track the e-mail against.  If, for example, you have the same e-mail address on a contact record as well as that contact's account record, when an e-mail comes in from that user, CRM will not now which record to set that e-mail regarding.  The e-mail will track, but it will not be associated with any CRM records (what good will that do).  I recommend using the SQL Query found here to quickly identify if you have duplicate e-mail addresses in your system, then editing those records in CRM to correct the problem.
• Monitor the health of system jobs in Microsoft Dynamics CRM.  Periodically you want to check that the Asynchronous Processing Service is running and take a look at the system jobs in Settings to make sure that system jobs are running.  And if you have a lot of workflows, plugins, imports, or duplicate detection jobs, you really want to monitor the size of the asynchronousbase table in your MSCRM database.  If you have a large number of completed system jobs, consider deleting them so your database doesn't grow exponentially.

9.  Set up a test environment. 

It amazes me how many CRM deployments do not include a dev/test/sandbox environment.  Microsoft has made it very easy to make configuration changes, so some users feel like it is not necessary to have a test environment; however, it is still recommended best practice to have at least one other environment.  While it is very easy to make configuration changes to Microsoft Dynamics CRM, it is also extremely easy to make a big mess if you don't know what you are doing.  You should always try any development, workflow, or configuration changes in a test environment before implementing these changes in your live production environment.  And you will thank me when your CRM server fails and you have another environment to keep going.

And especially with 4.0, there is no excuse to not have a test environment.  It is very easy to take a copy of an existing production environment and import to a different CRM environment.  If you have the Enterprise version, you can even set up a sandbox organization on your existing CRM environment.  Not quite as good as having a totally separate test environment, but better than nothing.

10.  Find out what's coming.

Microsoft Dynamics CRM is never standing still.  Microsoft is very open about where they are going, and there are many good resources on the web highlighting what's coming for CRM 4 and the next version of Microsoft Dynamics CRM, due in 2010.

• Microsoft Dynamics CRM Statement of Direction white paper--this is the roadmap for where Dynamics CRM is going.  You should check this document periodically, as it is a "living document" that will be updated.
• Video chat with the CRM 5 platform team from Channel 9
• Secure code on the server video from Channel 9--this is pretty interesting and talks about some of the hurdles to running ISV custom code on CRM Online, and how they are going make it available in CRM Online 5.
• CRM Team Blog an early look at CRM 5

Technorati Tags: resolutions,Microsoft CRM,asyncronous processing service,Facebook,Linkedin,forums,accelerators

Sales Pipeline Trend Analysis

In today's volatile business environment, it's more important than ever that you understand what's happening in your sales pipeline.  Your sales pipeline affects everything from resource planning, and operations, to cash flow and headcount.  Using Microsoft SQL Server Reporting Services and Microsoft Dynamics CRM you can produce some valuable reports that will give you a great picture of how the pipeline looks today.  The problem is today's data doesn't tell you very much about the long term health of your business if you can't produce a trend report or a comparative analysis.  You need to put today's numbers into context so you can compare apples to apples.  For example, let's say you wanted to compare the estimated value of your pipeline in Q1 2008 to Q1 2007.  You not going to get there until you start capturing the pipeline data in a slightly different way.

 

Most sales pipelines are moving targets with estimated values and close dates that change daily.   Today's pipeline value can look very different from tomorrow's value.  So how do you analyze changes in the estimated value over time?  Is the value increasing or decreasing compared to last month or last quarter?  One way to get answers to those questions is to begin taking daily snapshots of your opportunities.  Keep in mind the longer you wait to begin collecting the snapshot data, the more time it will take to get to the analysis.  It will take some time to build that history in order to produce meaningful reports.  Companies just beginning their Microsoft Dynamics CRM journey are in a great position to begin capturing this data from day one.  The bottom line is the sooner you begin taking snapshots of your opportunity data, the sooner you'll be able to produce trend reports and perform a comparative analysis.

 

The way we address this issue at Custom Effective is with an ETL layer that extracts data from our CRM database on a daily basis and store it in a relational database with conformed dimensions (ROLAP) .  Once the data is in the ROLAP database we have a number of reporting options.  We can build reports directly from the ROLAP database or we can quickly process cubes in Microsoft SQL Server 2005 Analysis Services for consumption via Excel or SSRS.  Also, as we expand our own BI initiative we now have the flexibility of easily adding more facts and dimensions to the ROLAP in a controlled way.  Additionally, reporting against a separate ROLAP database (as opposed to keeping snapshot tables in the CRM database) reduces the load on the CRM solution which helps with performance.

 

Another area where the value of a daily pipeline snapshot can be demonstrated is in salesperson management.  Let's say you're a salesperson manager and you perform a quick check of the 6 month pipeline on your team's dashboard and find that….."SURPRISE!"…..the estimated value is 10% less than it was the last week.  But who changed what this week?  Assuming you don't have customized audit trail functionality, there's no way good way to go through all opportunities and identify which one(s) fell out this week or which estimated values were changed.  However, if your were able to go through the pipeline starting with the last day you checked it and proceed one day a time you would know exactly who changed what….making follow up with the opportunity's owner much easier.

 

Note:

As of this blog post, there's no other solution for a sales pipeline trend report or a period over period comparative analysis with an out of the box installation of Microsoft Dynamics CRM.  I am, however, anxiously awaiting the "R2" release of the Analytics CRM Accelerator for Microsoft Dynamics CRM 4.0 release which is promising to deliver trending for the sales pipeline.  As a part of that release, I do hope the authors will give some guidance as to how to apply the Analytics Accelerator to any CRM environment. 

Microsoft Dynamics CRM Notifications Accelerator Part III – Metadata Cache

This article is the third in a series I am writing on the inner-workings of the Notifications Accelerator for Microsoft Dynamics CRM. The first two parts of the series focused on the Installer and ISV.config and the Subscription Chooser Page. If you have not yet checked those articles out, it would be a good idea to do so before diving in and reading this one. Today, I’m going to discuss the Metadata Cache object in the accelerator.

First off, I’m not going to try and claim credit for creating the CrmMetadataCache class in the solution. It is largely untouched from the previous version originally written by Michaeljon Miller. He did a great job with this and pretty much the only tweaks I added were the use of the CrmHelper class in a couple of areas and also updating some lines to allow the cache to show user localized labels. Read his post on the 3.0 version of the code (heck, download it and take a peek, even!) and you’ll likely find out about some of the quirks in the 4.0 version.

Microsoft Dynamics CRM Notifications Accelerator Part II – Subscription Chooser Page

This long-overdue post will continue with the unofficial story and documentation of the Notifications Accelerator for Microsoft Dynamics CRM. Previously, I covered some basic information on the accelerator and got into some of the inner workings of the installation program and the ISV.config customizations that are utilized. This article will explore the classes in the application and the workings of the Subscription Chooser page (source file rss.aspx) and also some of the helpers that I set up to, well, help.

If you have not yet grabbed the files for the accelerator, you can find it right here on CodePlex. In reviewing the code for this article, I did find a few loose items in the code that are useless. I admittedly didn’t clean up absolutely everything, so you may find a few unused and/or redundant objects floating around.