Sep
28

CRM Migration - Migrant’s Notes

When your customer decides that his legacy CRM system is junk, and he has to move all business data to Microsoft Dynamics CRM with your help, he doesn’t expect “junk in - junk out” effect.

What are the critical success factors and the things which need special control during migration of business data to Dynamics CRM? See further – most of them are simple but often overlooked. And missing it may lead to stopping the migration, taking “ad hack” solutions, and making the migrated data even more junk than the original ones.

Good detailed plan

As for any activity with many details and dependencies, make detailed checklist of actions to perform and things to control on preprocessing, migration and postprocessing. Use top-to-bottom approach to plan actions: start with the most general and then bring more details to each level. Identify dependencies and put the actions in correct order.

Preparation on staging environment

Use staging Dynamics CRM environment to prepare the migration. When you (and your customer) are sure that all data are correctly migrated to the staging environment, do the migration to live. Backup & restore of the CRM organization database is not always an option: the restored CRM environment may not start at all. Backup & restore is not applicable at all if staging and live environments (and consequently Active Directory user accounts) are located in different domains.  

Keep the customizations of your staging environment identical to live as much as possible. Do not make parallel customizations in live and staging UI, even if they look identical. After such changes, further export and import of customizations between live and staging may become impossible.

No orphan records

In legacy CRM applications, records may have blank or invalid ID’s of related records. This can crash the software you use (for example, upgrade of ACT! database from an older format before migrating to MS CRM).

If possible, identify and fix or delete orphan records at early stage. In old ACT! and GoldMine databases you can do it on back end in dBase files.

Permissions

Migration tools and SDK’s may require that all users, active and historical, have permissions to create, append, append to, share,… If so, before the migration create all active and historical users in Active Directory and CRM and assign System Administrator role to them. After the migration secure CRM by removal of System Administrator role. Then you can assign all records owned by historical users to  actual  users, and only then disable historical user accounts.

Control size of fields

Control that all field values fit into size limits of CRM fields. Update size limits in CRM if needed. But keep in mind that updating sizes of standard fields (like address1_line1) may not solve the problem. Instead of size limit error, the import job can produce an unspecified server error.

Long values particularly happen in address fields. Consider splitting the address over several fields (address1_name, address1_line1, address1_line2 and address1_line3,…).

If you use standard CRM import, you can still save the refused records “on the fly”, split the long values, and repeat the import for refused records. 

Handling import errors

No special characters in field values

(like line end characters with ASCII codes 10 and 13)

Get rid of them before the import. Otherwise, sooner or later, they will make trouble. For example, if the data are imported from CSV files via standard CRM import, line breaks can break a record line into several, even if text qualifiers are used.

Notepad++ is a good tool to identify them in text files (e. g. CSV).

Control business required values

Control that all business required fields are 100% populated.

Control unexpected intelligence of your tools

If you export data from Excel , or use Excel to process the data, don’t be surprised with telephone numbers like 8.61E+12 or -166.0405405, while the original numbers are 8612345678910 and +32-9/222-99-99. The first one is a rounded “approximate” phone number in scientific format (8.61 x 1012), and the second is a “calculated” phone number (have you ever dialed an approximate negative fractional phone?). Excel doesn’t have cell format for telephone numbers and treats such input as a big numeral in the first case, or as a valid formula in the second case.

Solution: convert such values to text by Excel function TEXT.

Person’s names

Some systems don’t explicitly split contacts’ given names and family names. Foresee the rules which defines given names and family names in Dynamics CRM as adequate as possible.

Take into account that:

  • both given names and family names may be multiple; (e. g. Juan Luis Gonzalez Fernandez)
  • family names may have prepositions, sometimes multiple (e. g. Van Den Bogerd or de la Croix), which have to stay with the names;
  • names may be accompanied by salutations, titles, military ranks  in various languages, sometimes multiple, (e. g. Prof. Dr. Ing. Von Hochenbalken) which have to be separated and saved to a different field;
  • delimiting of the above parts may be inconsistent (space, dot with space, just dot);
  • even if you would have a comprehensive dictionary of given names, in some cultures it’s different to distinct family names from given names; (for example Roger Vincent, Laurence Olivier, Marcu Matei)
  • the field lastname always has to get a non-empty value, since it is business required.

A possible action plan is:

  • prepare the dictionary of “normalized” prepositions and the dictionary of “normalized” titles.
  • prepare the script with the functions which identify and split the full name to its parts based on the most typical name format (e. g. [Titles ]Firstname Lastname). This is possible even in Excel with usage of VBA.

Correctly treat  “All in one” legacy contact records

Many popular contact management applications (Microsoft Outlook among them) use single record for account and contact. Other systems (like ACT! by Sage) allow limited number of multiple contact persons in one database record. The data model of Dynamics CRM is different, it uses single record per account and single record per contact. If you migrate the data from such sources to the CRM, you have to split it to two Dynamics CRM records and later to assign Parent Customer and Primary Contact relationships between them.

For the original records create unique ID’s and migrate these ID’s to both account and contact records. If the data come from multiple sources, indicate the data source in the ID. Upon importing  accounts and  contacts, match them using unique ID’s.

One account may have more than one contact. If you want to eliminate duplicate accounts, at the stage of preprocessing define primary records for obsolete accounts (if the data of accounts are identical, or you have no preferences for the primary account, you can do it automatically in Excel using formulas), and then map contacts to primary accounts. Such updates of CRM records and many other handy thongs may be done by the CRM Bulk Update and Export tool available on Codeplex.

Complete this mapping before the users start manual hunting for duplicates.

Find the best balance for duplicate detection and merge

The right moment to merge duplicates (preprocessing, migration, or postprocessing) depends on how clean the data are. The customer may have an idea that source A is cleaner than source B. If so, import source A first and non-duplicate data from source B next.

In principle, the source B can be imported first, followed by source A and update of the data with available values from the source A. Be careful with updates “per field” (like “take the value form better source if not blank, otherwise take the value from the worse source”). For example, a record from the worse source may have data in two address lines (address1_line1 and address1_line2), while the corresponding record form the better source has only address1_line1. As a result, this rule may give a mixture of lines taken from two different addresses.

If after all the customer realizes that he needs company name from one source, e-mail address from another, and VAT code from the third (like a young lady form a classical comedy who searched a groom with nose of Mr. A., ears of Mr. B, and incomes of Mr. C.). If so, either let the customer to clean the data before the migration, or migrate all data, double and triple, to the CRM. CRM allows you to identify duplicates and to merge two records into one with specific selection of sections, fields from each merged records, or selection of the whole record.

Merge Duplicates

Merge Duplicates

Out-of-the-box duplicate detection in CRM is more than basic, but you can enhance it by using calculated match names. Normalize account or contact names by:

  • converting them to lower case and trimming;
  • converting letters with diacritics to standard Latin letters (e.g. Maïté to maite);
  • removal of texts inside parentheses, like alternative spellings or comments;
  • removal of punctuation marks and special characters (at this stage better to replace punctuation marks with spaces, since they delimit single words);
  • removal of common abbreviations, like English Ltd, German GmbH, or French SPRL;
  • removal of spaces.

The order of the above actions is important.

This matching method was compared with six more sophisticated metrics used to find duplicates (Jaro-Winkler, Jaro, Levenstein, Monge-Elkan, Needleman-Wunch, Smith-Waterman-Gotoh) on the migrated business data. In 95% percent of cases it gave the same or better matches as any other method, and the results, even wrong, were much more obvious for the users. The failures of this method normally had words in different order, like “University of Antwerpen” vs. “Antwerpen University”.

A match name plugin used for duplicate detection can be found on Codeplex.

International challenges

Control that the data prepared to import comply with your language and regional settings:

  • date formats: dd-MM-yyyy vs. MM-dd-yyyy.

For specific types of values in activity records third party migration tools accept only dates in English (US) format. This can make problems if in client's system the regional settings of Windows or CRM are different. If it happens, change system and CRM data formats to English US, and convert all dates in input data to this format.

  • letters with diacritics and non-Latin letters.

Control how your migration tool treats these characters. If you use bulk update tools, verify that their input data have Unicode format.

  • use of languages and geographical names.

In principle each record has to be in the language you want to communicate to the customer. But this makes use names of multilingual countries (like Switzerland or Belgium) inconsistent. As a result, validation scripts for country specific formats (like ones for telephone numbers) become more difficult. Instead, you can normalize country names to standard English names. Keep in mind that even if you use ISO standard, it’s not always a perfect reference. Say, the name “Taiwan, Province of China” advised by ISO 3166-1 is an insult for Taiwanese people.

Dynamics CRM Import.

If speech synthesis is comparable with getting toothpaste out of a tube, speech recognition is like trying to get the toothpaste back in. (Dr. J. Holmes, expert in speech processing)

 

The same is absolutely valid for Dynamics CRM export  and import.

 

Nevertheless, out-of-the-box import can be used to upload contacts from Outlook contact lists exported to Excel. Users can import the data to the CRM by building a data map between the data table in CSV format and the CRM entity attributes, and then by running an import job. Of course, you have to control that

  • all records being imported have all business required values;
  • there are no duplicate records within the import list and between the import list and the CRM (active and inactive);
  • the CSV files have to be really COMMA-separated, since other characters (tab or semicolon) are not accepted in sample files used in manual mapping. 

 

And even after it the import will not help with lookup and picklist attributes, because the export has their values, while the import requires their ID's (GUID's for lookup values, and numeric ID's for picklist values).

 

There is still a way to import the entities with lookup and picklist attributes. (thanks to CRM guru Jim Steiger) Be careful when following it, since any deviation will stop the process.

 

Good log

If you run scripts which change users’ data (for example, normalizes telephone numbers), foresee that the script informs your customers about:

  • all changes done in the system (so that the customer can handle all information he may miss after the cleanup, like the six more phone numbers, or a comment in the same field)

o   what type of record

o   what record

o   old value

o   new value

  • all suspicious data in fields which are left unchanged,
  • all errors happened during processing, with specific error messages,
  • nothing else (like changing assignment of the old value back to the field).

Since log may get long, foresee easy conversion of the log to spreadsheet or database, which will help to filter by specific type of issue, group records by owner, etc.

Good luck!

Posted by Viktor Bardadym | Leave your feedback

Sep
25

Creating custom commands for the Composite Application library(Prism v2)

The Composite Application Library helps architects and developers create composite Windows Presentation Foundation (WPF) and Silverlight applications. Composite applications are composed of discrete, functionally complete, pieces that work together to create a single, integrated user interface. The Composite Application Library accelerates the development of composite applications using proven design patterns to help you build these types of applications.

Commands are a way to handle user interface (UI) actions. They are a loosely coupled way to bind the UI to the logic that performs the action.

When building composite applications, presentation design patterns such as Model-View-Presenter (MVP) and Model-View-Controller (MVC) are often used to separate the UI logic from the UI layout and presentation. When implementing these patterns with Windows Presentation Foundation (WPF), the presenter or controller handles commands but lives outside the logical tree. WPF-routed commands deliver command messages through UI elements in the tree, but the elements outside the tree will not receive these messages because they only propagate events up or down from the focused element or an explicitly stated target element. Additionally, the WPF-routed commands require a command handler in the code behind.

The Composite Application Library introduces two new commands that can be routed outside the boundaries of the logical tree and that do not require handling logic in the code behind. The commands are custom implementations of the ICommand interface defined by WPF, and they implement their own routing mechanism to get the command messages delivered to objects outside of the logical tree. The commands in the Composite Application Library include DelegateCommand and CompositeCommand.

The command support in WPF is at this moment rather limited. Very few of the default controls support binding to a command. One of the controls that has a Command dependency property is the Button.


If we want to execute some code on button press and we want to leverage the commanding support, we need a few things in the view model and in the XAML view:

1. a property for the command

public ICommand ClickCommand {get; set;}

2. Initialization code for the command (eventually in the view model’s constructor)

public MyConstructor()
{
  ClickCommand = new DelegateCommand<object>(OnClickCommand);
}

3. Command handler:

private void OnClickCommand(object argument)
{  /**/ }

4. and some XAML code in the view that guarantees the command will be executed on button click:

<Button Height="24" x:Name="MyButton"  
        Command=”{Binding ClickCommand}”/>

This is straightforward if you are familiar with the M-V-VM pattern.

Let’s consider another scenario where we need to execute a command for the lost focus event of a TextBox. To be able to solve this in the same manner as above we would need to use the Command dependency property of the TextBox and bind a DelegateCommand to it.

Unfortunately, the TextBox does not accept commands by default for the lost focus event.

A solution to the problem is to use the attached properties feature of WPF.

The goal is to create an attached property for the TextBox. The attached property’s name will also be Command and the command bound to it will handle the PreviewLostKeyboardFocus event. After we have this, we will be able to write the following code in the XAML view:

<UserControl xmlns:Commands= "clr-namespace:{namespace containing the attached command class}">
   <TextBox Commands:PreviewLostKeyboardFocus.Command="{Binding MyTextBoxPreviewLostKeyboardFocus}"/>
</UserControl>

The code in the view model is identical as for the Button scenario (but with suggestive variable and method names of course):

public ICommand LostFocusCommand {get; set;}
public MyConstructor() 
{
   ClickCommand = new DelegateCommand<object>(OnLostFocusCommand); 
} 
private void OnLostFocusCommand(object argument)
{  …  } 

Now let’s dive into the implementation of the attached CommandProperty.
public class PreviewLostKeyboardFocus
    {
        /*Dependency property for the Command attached property. 
         For the Command property to be bindable it has to be dependency property.*/
        public static readonly DependencyProperty CommandProperty = DependencyProperty.RegisterAttached(
                "Command",
                typeof (ICommand),
                typeof (PreviewLostKeyboardFocus),
                new PropertyMetadata(OnSetCommandCallback));

        public static readonly DependencyProperty LostFocusCommandBehaviorProperty =
                DependencyProperty.RegisterAttached("LostFocusCommandBehavior",
                                                    typeof (TextBoxLostFocusCommandBehavior),
                                                    typeof (PreviewLostKeyboardFocus),
                                                    null);

        /*Acessor methods for the Command attached property*/
        public static void SetCommand(TextBox textBox, ICommand command)
        {
            textBox.SetValue(CommandProperty, command);
        }

        public static ICommand GetCommand(TextBox textBox)
        {
            return textBox.GetValue(CommandProperty) as ICommand;
        }
       
        /*Callback executed when the Command property  is databound*/
        private static void OnSetCommandCallback(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            var textBox = d as TextBox;
            if (textBox != null)
            {
                TextBoxLostFocusCommandBehavior behavior = GetOrCreateBehavior(textBox,  e.NewValue as ICommand);
            }
        }

        /*Creates the command behavior if it does not already exist*/
        private static TextBoxLostFocusCommandBehavior GetOrCreateBehavior(TextBox textBox, ICommand command)
        {
            var behavior = textBox.GetValue(LostFocusCommandBehaviorProperty) as TextBoxLostFocusCommandBehavior;
            if (behavior == null)
            {
                behavior = new TextBoxLostFocusCommandBehavior(textBox, command);
                textBox.SetValue(LostFocusCommandBehaviorProperty, behavior);
            }
            return behavior;
        }

        #region Nested type: TextBoxLostFocusCommandBehavior

        /*Wrapper for the PreviewLostKeyboardFocus  event*/
        public class TextBoxLostFocusCommandBehavior : CommandBehaviorBase<TextBox>
        {
            public TextBoxLostFocusCommandBehavior(TextBox textBox, ICommand command) : base(textBox)
            {
                Command = command;
                textBox.PreviewLostKeyboardFocus += (sender, args) => Command.Execute(new PreviewLostKeyboardFocusCommandArgs
                                                                                          {
                                                                                                  Sender = (TextBox) sender,
                                                                                                  KeyboardFocusChangedEventArgs = args
                                                                                          }); 
            }
        }

        #endregion
    }

As it’s obvious from the event wrapper class, the event forwards the sender and the event arguments to the command. So the command’s handler has access to the same data as the event itself. the PreviewLostKeyboardFocusCommandArgs  class is a custom class used to transfer parameters:

public class PreviewLostKeyboardFocusCommandArgs
{
    public TextBox Sender { get; set; }
    public KeyboardFocusChangedEventArgs KeyboardFocusChangedEventArgs { get; set; }}

The PreviewLostKeyboardFocus class might seem quite verbose, but this is the standard WPF way of defining attached properties that can take part in the data binding binding process.

Most of the code is standard and can be extracted in a R# template. The template can be downloaded here.

That’s it!

With just a little coding effort we can have a clean view and be able to wrap WPF event in Commands and handle the view events in the view model or controller(depending of the design approach).

Posted by Voicu Matei | 2 Comments

Sep
21

Format and validate phonenumbers in Dynamics CRM 4.0 Plugin

When importing data in Dynamics CRM 4.0 there is no client side code triggered, so to make sure you get phonenumbers (or anything else for that matter) in a clean format, you might want to consider creating a plugin that runs server side and generates an import failure to let the customer know some data is not valid in his imports.

This way the user knows when something went wrong and the correct data will still be imported.

To make this plugin work there is the issue of the country prefixes and areacodes that exist. Therefore it is necessary to create two new entities: Country and AreaCode.

The Country entity needs to contains the telephone prefix of that country (eg: belgium = 32, Netherlands = 33 …)

The AreaCode is contains a link to the country entity and contains the phone areacode. (eg: 9 for Ghent, Belgium …)

Ones this data is in CRM we can use it in our plugin through the webservice.

In the end the phonenumbers will be formatted in an outlook compatible format.

eg: +32 (9) 265.74.20

When using the installer inside the project on CodePlex the plugin will be registered for the create and update messages for lead, account and contact.

 

You can find the plugin on CodePlex in our Dynamics CRM 4.0 Plugins project

Posted by Wim De Coninck | Leave your feedback

Sep
08

Improved Duplicate Detection in Dynamics CRM

Why do I need it?

We've noticed the CRM Duplicate detection doesn't allow for much creativity, you can only create Duplicate Detection Rules with the following condition:

  • full string match
  • begins with (number of characters)
  • ends with (number of characters)


The problem with this is that people or companies aren't always entered in the same format. For instance if you look at our company name we would create the account with the name "Orbit One". Other people might write "Orbit One BVBA" (the bvba part is a type of business entity)
There are some other examples that come to mind:
"Dell NV" vs "Dell"
"HP Belgium" vs "HP"
"Gaëtan" vs "Gaetan"
"François" vs "Francois"
These exceptions will never be caught by the default CRM Duplicate detection.

How can I fix this?

We added the following attributes and filled the with data:

  • on account => new_matchname (contains the accounts name)
  • on contact => new_matchname (contains the contacts first + lastname)
  • on lead => new_matchcontact (contains the leads first+ lastname)
  • on lead => new_matchcompany (contains the lead company name)

The fields are filled with a normalized name. The process is:

  • to lower case and trim.
  • replace special characters (except parenthesis, a-z, 0-9, special characters like éèàç ..., and spaces) with empty string.
  • remove everything between the parentheses eg: "HP (was Compaq)" becomes "HP"
  • remove suffixes and prefixes that are type of business entity
  • remove all spaces
  • replace special characters (eg: é > e, à => a, ç => c, ñ => n, ...)

Next there need to be some new duplicate detection rules.

  • full string match on new_matchname for contact
  • full string match on new_matchname for account
  • full string match on new_matchcontact for lead (add companyname if needed in your specific cases)

The current implemented type of business entity which will be stripped are:

  • nv
  • sa
  • vzw
  • bvba
  • cvba
  • sarl
  • sprl
  • scrl
  • nvsa
  • bv
  • llc
  • gmbh
  • ltd
  • ag
  • kt
  • og
  • vof
  • cvoa
  • commva
  • commv
  • ebvba
  • ez
  • belgium

Duplicate Detection Jobs

In CRM you can add duplicate detection jobs by going to Settings > Data Management > Duplicate detection jobs

image

Create a new Duplicate detection job for for instance accounts. Make sure you filter on only active records. When you merge a duplicate record the record that isn’t the master record will be placed on inactive. So if you forget this filter you’ll keep getting the same duplicates ;-)

Once the job ran, you can see the duplicates entries.

image

What we think is another major pain in the a$$ is that you can only merge 2 records at a time. So not really in bulk… Maybe we will come up with a solution for this later :-)

 

You can find the Matchname plugin on CodePlex. There is an installer include so that you don’t need to worry about the plugin registration, creating the steps, …. More on this on CodePlex.

Posted by Wim De Coninck | Leave your feedback

Sep
08

Windows 7 RC Image Deployment

Windows 7 RC Image Deployment
 

The document attached to this blog provides instructions for implementing a basic image-based deployment of Microsoft Windows 7 RC0 operating system.

Migrating an operating system is a major project that requires careful planning and guidance. To ease the process, Microsoft has provided a number of tools for automating Windows 7 deployments including the Windows Automated Installation Kit (WAIK). For Windows 7 Microsoft has released the Beta version of WAIK version 2.0.

There are many guides available on the internet, but we felt they all miss some steps. Therefore we decided to share our internal documentation.

The process for our deployment scenario includes

  • Prepare an USB stick with "Windows PE"
  • Prepare a master installation
  • Capture an install image
  • Manually deploy an image on a single machine with ImageX

The document attached does not cover automated deployment of images on multiple computers using tools as ADS.

 Download documentation

 

 
 
 
 
Posted by Arne Verstraete | 2 Comments

Sep
08

Synchronizing activities from Dynamics CRM 4.0 to Outlook

The other day we did some research for a client into the synchronization behavior of Dynamics CRM for Outlook specifically when it comes to activities.

Syncing activities like: Tasks, Appointments, Phone Calls and Letters to Outlook is a powerful option in Dynamics CRM but there can be some ambiguity into what’s gets synced where and what happens when you delete items from Outlook that were previously synced to or from CRM.

These are my current activities viewed in the CRM web client.

image

Looking in my Outlook To-Do bar I get a mix of CRM activities and my own Tasks

image

As you can see from the example above not all activities get synced from CRM to Outlook. So which ones do and what is there counterpart in Outlook?

Sync activities to Outlook

  • Tasks
  • Letters
  • Phone calls
  • Fax

→ All get synced as tasks in  Outlook.

  • Email

→ Email after it’s been send gets synced as sent email but before send it doesn’t get synced to Outlook

  • Appointments

→ Appointments get synced to Outlook as meeting in your Calendar.

  • Campaign responses

→ Won’t show up in Outlook.

  • Service activities

→ Service activities show up as meetings in Outlook

Deleting items in Outlook

This is all quite straightforward but when deleting items in Outlook it is not always clear what happens to the activities in CRM. Well the sync behavior is depended on the status of the activity.

  • When an activity is completed CRM it will stay in CRM but it won’t get synced back to Outlook.
  • When deleting an activity that is not yet completed it will delete in Outlook and CRM.

Although it would be nice to have the email activity before send also synced to a task in Outlook the overall behavior is quite nice and keeps your work load from CRM and Outlook thightly together which I think is a real advantage over other CRM systems.

Deleting items in CRM

Again the behavior is depended on the status of the activity.

  • When an activity is completed CRM it will stay in Outlook.
  • When an activity is not yet completed CRM it will be deleted from Outlook as well.

 Some more information Microsoft written about this sync behavior:

http://rc.crm.dynamics.com/rc/regcont/en_us/OP/help/ug_ol_RecordsActivities.htm

Posted by Thijs ter Beek | Leave your feedback

Contact us - Raas Van Gaverestraat 83, 9000 Gent, Belgium - Tel. +32 (9) 330.15.00 - Privacy Statement - Sitemap - Sign In Developed with Microsoft Office SharePoint Server 2007