Microsoft CRM: data conversion import from Act!

Best Software Act! is very popular CRM for small and mid-size organization.   This system attracts business owner by its low price, plus system is very easy to use.  However if your business is growing you should reach the moment to implement more advanced CRM solution.  Natural question is how do we convert the data from Act! to new CRM solution and the mapping of your objects for conversion.  You would probably like to avoid operator data entry with potential numerous errors and mistypes.  Assuming that you are IT specialist, well give you technical side of Act to MS CRM data migration

This system attracts business owner by its low price, plus system is very easy to use.  However if your business is growing you should reach the moment to implement more advanced CRM solution.  Natural question is how do we convert the data from Act! to new CRM solution and the mapping of your objects for conversion.  You would probably like to avoid operator data entry with potential numerous errors and mistypes.  Assuming that you are IT specialist, well give you technical side of Act to MS CRM data migration:

• First you need to download Act! SDK from Best Software website
• Install Act! SDK on the computer, where you plan to do programming
• Well use asynchronous data export/import model, this means that well design the system, containing two parts: export into XML and this XML file import into the CRM
• Lets code Act! data export application, well use C# to address Act Framework classes, well need these libraries:

using Act.Framework;
using Act.Framework.Activities;
using Act.Framework.Companies;
using Act.Framework.ComponentModel;
using Act.Framework.Contacts;
using Act.Framework.Database;
using Act.Framework.Groups;
using Act.Framework.Histories;
using Act.Framework.Lookups;
using Act.Framework.MutableEntities;
using Act.Framework.Notes;
using Act.Framework.Opportunities;
using Act.Framework.Users;
using Act.Shared.Collections;

• To connect to Act! database:

ActFramework framework = new ActFramework();
framework.LogOn("Act Username", "password", "SERVER, "Database");

• Now we need Act field names to map them with the fields in the MS CRM:

private void ShowContactsFieldsDescriptions(ActFramework framework) {

    ContactFieldDescriptor[] cFields = framework.Contacts.GetContactFieldDescriptors();

    ContactFieldDescriptor cField;

    for(int x = 0; x < cFields.Length; x++)

        {

            cField = cFields[x];
            Console.WriteLine("Table Name:tt{0}", cField.TableName);
            Console.WriteLine("Column Name:t{0}", cField.ColumnName);
            Console.WriteLine("Display Name:t{0}", cField.DisplayName);
            Console.WriteLine("ACT Field Type:t{0}", cField.ACTFieldType);
            Console.WriteLine("");

         }

}

• Lets get contact list and create the file for import instructions to MS CRM:

ContactList cList = framework.Contacts.GetContacts(null);
FileInfo t = new FileInfo("Contacts.xml");
StreamWriter stw = t.CreateText();

• Now we form export data:

for (int i = 0; i < cList.Count; i++) {

    string strContactXml = "<contact>";

    ContactFieldDescriptor cField;
    Object oValue;

    // First Name
    cField = framework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.FIRSTNAME");
    oValue = cField.GetValue(cList[i]);
    if (oValue != null && !(oValue.ToString().Trim().Equals("")))
 strContactXml += "<firstname><![CDATA[" + oValue.ToString() + "]]></firstname>";

    // Last Name
    cField = framework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.LASTNAME");
    oValue = cField.GetValue(cList[i]);

    if (oValue != null && !(oValue.ToString().Trim().Equals("")))
 strContactXml += "<lastname><![CDATA[" + oValue.ToString() + "]]></lastname>";
    else
 strContactXml += "<lastname>" + "N/A" + "</lastname>";

    // Salutation
    cField = framework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.SALUTATION");
    oValue = cField.GetValue(cList[i]);
    if (oValue != null && !(oValue.ToString().Trim().Equals("")))
 strContactXml += "<salutation><![CDATA[" + oValue.ToString() + "]]></salutation>";

    // Job Title
    cField = framework.Contacts.GetContactFieldDescriptor("TBL_CONTACT.JOBTITLE");
    oValue = cField.GetValue(cList[i]);

    if (oValue != null && !(oValue.ToString().Trim().Equals("")))
 strContactXml += "<jobtitle><![CDATA[" + Regex.Replace(oValue.ToString(), "rn", "<BR>") + "]]></jobtitle>";

• This is only portion of the data, that could be transferred into CRM, the whole list of fields is too long for small article, but your could design the whole list of desired fields.  Please, pay special attention to replace <BR> HTML tag this is required for text data transfer into CRM
• Next is import application creation.  We will not describe here connection to MS CRM details please read Microsoft CRM SDK if you need this examples.  Well concentrate on the nature of the import.

 The XML export file should look like this:

<contact><firstname><![CDATA[John]]></firstname><lastname><![CDATA[Smith]]></lastname><salutation><![CDATA[John]]></salutation><address1_line1><![CDATA[1234 W. Big River]]></address1_line1><address1_city><![CDATA[Chicago]]></address1_city><address1_stateorprovince><![CDATA[IL]]></address1_stateorprovince><address1_postalcode><![CDATA[123456]]></address1_postalcode><description><![CDATA[Toy Corporation]]></description><ownerid type="8">{4F1849C3-9184-48B5-BB09-078ED7AB2DAD}</ownerid></contact>

• Reading, parsing and MS CRM object creation look is relatively simple:

        Microsoft.Crm.Platform.Proxy.BizUser bizUser = new Microsoft.Crm.Platform.Proxy.BizUser();

        ICredentials credentials = new NetworkCredential(crmUsername, crmPassword, crmDomain);

        bizUser.Url = crmDir + "BizUser.srf";

        bizUser.Credentials = credentials;

        Microsoft.Crm.Platform.Proxy.CUserAuth userAuth = bizUser.WhoAmI();

        // CRMContact proxy object

        Microsoft.Crm.Platform.Proxy.CRMContact contact = new Microsoft.Crm.Platform.Proxy.CRMContact ();

        contact.Credentials = credentials;
        contact.Url = crmDir + "CRMContact.srf";
        CorrectXML("Contacts.xml", userAuth.UserId);
        StreamReader reader = File.OpenText("Contacts.xml");

        string input = null;                       

        while ((input = reader.ReadLine()) != null)

        {

            string strContactId = contact.Create(userAuth, input);
            Console.WriteLine("Contact {0} is created", strContactId);
            log.Debug("Contact " + strContactId + " is created");

}

• Just consider in more details CorrectXML function it places OwnerId into XML contact tree:

        private void CorrectXML(string fileName, string userId) {

           File.Move(fileName, fileName + ".old");
           StreamReader reader = File.OpenText(fileName + ".old");
           FileInfo t = new FileInfo(fileName);
           StreamWriter writer = t.CreateText();
           string input = null;

           while ((input = reader.ReadLine()) != null) {

               input = Regex.Replace(input, "{_REPLACE_ME_}", userId);
               writer.WriteLine(input);

           }

           reader.Close();
           writer.Close();
           File.Delete(fileName + ".old");

        }

• Finally, we are launching export, import, opening MS CRM and looking at the contact list, transferred from Act!
• Separate task would be Sales data from Act!, Notes etc. we plan to describe them in the future articles

Good luck with integration!  If you want us to do the job - give us a call 1-630-961-5918 or 1-866-528-0577! help@albaspectrum.com

Andrew Karasev is Chief Technology Officer at Alba Spectrum Technologies ( http://www.albaspectrum.com ), serving Microsoft Great Plains, CRM, Navision to mid-size and large clients in California, Illinois, New York, Georgia, Florida, Texas, Arizona, Washington, Minnesota, Ohio, Michigan

Microsoft CRM Implementation for Large Corporation overview

Microsoft Business Solutions CRM is now approaching the phase of being mature product and the CRM solution you may consider for large publicly traded company.  Our view point considers the fact of multiple platforms and systems coexistence and balancing: UNIX, Linux, Microsoft Windows, Java, .Net, PC, AS/400, RS6000, Microsoft Exchange, Lotus Notes Domino, etc.  This increases the cost of maintenance, but decreases the risk of being trapped to one vendor/solution.  So far weve seen successful implementations of Microsoft CRM in the following industries: Distribution, Logistics, Non-Profit, Chemicals, Pharmaceutical, Placement/Recruiting, Medicare/Hospitals, Retail, Wholesale, Education, Beverages, Services, Defense, Aerospace, Oil & Gas.

• In-house Implementation.  This is non-expected paradox.  Multiple clients, especially large companies, do it themselves.  The reason is Microsoft CRM utilizes all spectrum Microsoft Technologies: Exchange, Active Directory, .Net, Full-text search, etc.  Microsoft oriented IT people understand it and like to be involved.  So, you should consider self-implementation first.
• Integration.  Microsoft CRM has relatively simple business logic and sits in Microsoft SQL Server, which enables customization and integration with third party systems and databases.  If you have Microsoft Great Plains you can use MBS CRM-GP integration tool, based on BizTalk.  If you have UNIX-based ERP/Accounting, you can deploy MS SQL linked server to bridge MS CRM and ERP.  The same would work for Oracle ERP Oracle Financials.  Navision has third party integration tool and the one from Microsoft is on the way to be released.  Axapta is also candidate for the integration.
• Customization.  Microsoft CRM SDK describes majority of MS CRM objects and classes and enable VB.Net or C#.Net developer to build-in custom .Net application for Microsoft CRM existing screens.  And this is actually the way of the integration you realize integration in the custom .Net application.
• Messaging.  Microsoft CRM uses MS CRM-Exchange connector in its standard functionality.  However if you have IBM Lotus Domino email server you can deploy MS CRM-Lotus connector, developed and supported by Alba Spectrum Technologies.
• Data Conversion.  You should use Scribe for initial data migration.  However you need some programming work if you plan on bringing advanced data (email attachments from Siebel, for example)
• External consultants.  You should consider customization and integration initial design as candidates for outsourcing.  Then, when coding is done and implemented you may switch to your internal developers to support custom pieces.
• Nation-wide support.  Microsoft CRM is very innovative from the support side.  It means that Microsoft CRM partners tend to go to the nation-wide and international market to sell, implement and support the application.  Being web application Microsoft CRM could be accessed, customized and modified from anywhere.
• Internationalization.  Microsoft Business Solutions promotes MS CRM worldwide and has local versions of the product.

You can always have us help you, give us a call: 1-630-961-5918 or 1-866-528-0577, help@albaspectrum.com

Andrew Karasev is consultant and CTO in Alba Spectrum Technologies ( http://www.albaspectrum.com ) Microsoft Business Solutions partner, serving clients in Illinois, New York, California, Florida, Georgia, Texas, Arizona, Virginia, Minnesota, Canada, UK, Europe, Australia, New Zealand, Asia, Russia.  He is Microsoft Great Plains certified master, Great Plains Dexterity, Microsoft CRM SDK C#.Net, Crystal Repots developer.  You can reach Andrew: akarasev@albaspectrum.com

Lotus Notes Domino and Microsoft CRM Integration

Well, even if  the combination might look very unusual, we see the coexistence of these two systems, especially in large corporations, non-profit organizations. Technically Lotus Notes Domino has parallel structure, including Domino email server.  Lotus is extremely flexible and you can program your own CRM or purchase CRM for Lotus, however Microsoft CRM has Microsoft SQL Server database and very simple Sales automation module.  Lets look at the integration reasons and scenarios.  Be sure that each specific case requires custom programming, tuning and support.

• Why Lotus & MS CRM?  There are several reasons to combine the two.  First Lotus Domino has very long history and is present on the market over 15 years.  Corporations might have business system built upon the Lotus Notes Domino platform and it is not desirable or even feasible from the complexity standpoint to phase it out.  The second reason is possible IT strategy to balance multiple platforms to avoid over-dependence on one vendor (such as Microsoft).  There might be also licensing issue if corporation has several hundred Lotus licenses it doesnt want to lose them and pay for 200 Microsoft CRM users.  In this case MS CRM might be the solution for Sales department and all the other departments should stay on Lotus.
  
• Lotus Domino as Messaging for MS CRM.  There is no need to program this custom piece, just use MS CRM Lotus Domino connector, supported by Alba Spectrum Technologies.  It has advanced functionality, versus standard MS CRM Exchange connector.  Instead of using GUID in the email header, it scans Contacts, Accounts, Leads emails for matching.
  
• Lotus Workflow.  Workflow is possible in Lotus and MS CRM.  In Lotus, however workflow is the most advanced.  You can use Lotus Workflow and then upon the completion it can transfer control to Microsoft CRM.  This is typical Microsoft CRM SDK (to program MS CRM) and Lotus Java Agent programming from the side of Lotus.  Lotus workflow may work with integration to your ERP/Accounting application, such as Microsoft Great Plains, Navision, Axapta, however this is outside of the scope of this article.
  
• Lotus/CRM Activities Synchronization.  One of the popular requirements is the synchronization of Microsoft CRM Activities: Appointment, Phone Call, Fax, etc. with Lotus TOTOs.  This should allow users work on the same project sharing Lotus and MS CRM data space.

If you need help in the integration or customization, feel free to call us: 1-630-961-5918, 1-866-528-0577, help@albaspectrum.com

Andrew Karasev is Chief Technology Officer at Alba Spectrum Technologies ( http://www.albaspectrum.com ) - Microsoft Business Solutions and IBM Lotus Domino Partner, serving corporate customers in the following industries: Aerospace & Defense, Medical & Healthcare, Distribution & Logistics, Wholesale & Retail, Chemicals, Oil & Gas, Placement & Recruiting, Advertising & Publishing, Textile, Pharmaceutical, Non-Profit, Beverages, Conglomerates, Apparels, Durables, Manufacturing and having locations in multiple states and internationally.