Monthly Archives: July 2013

Migrating from ACT! to Dynamics CRM

Today let’s discuss migrating data from ACT! to Microsoft Dynamics CRM 2011. The last migration we undertook from Act to Microsoft Dynamics CRM also involved a brand new tool for us called Data Synchronisation Studio from the team at Simego. At xRM Consultancy we have dabbled with a lot of data migration tools, but Simego and it’s Data Synchronisation Studio stood out from the pack this time due to some powerful features it offers. So we decided to give this a trial to see if it really was worth investing our time and money in. For this post let’s pick one part of a migration that is often a pain point: attachments. This will also allow us to explore some of the features of Data Synchronisation Studio and how it stands up to the task.

Preparing the data

First question you may ask – what exactly is ACT! underneath the hood? More importantly, how do we extract that data and get it into Microsoft Dynamics CRM? ACT! sits on top of a SQL Server database so this makes querying and pulling the data out a lot easier than having to deal with APIs or flat files. Most of the data in here is quite straight forward and you’ll immediately identify some entity tables such as TBL_CONTACT and TBL_COMPANY. Linking the data up to related tables and retrieving the relevant columns is a little more challenging.  Our strategy to ease this pain is using a set of views and functions we’ve built up over time which allows us to easily extract the relevant and linked data.

For this example let’s deal more specifically with how attachments are stored and referenced. People already up to speed with Microsoft Dynamics CRM will know that attachments are base 64 encoded and stored directly in the database. In ACT! these files are stored externally and the file name is referenced within the relevant table – TBL_ATTACHMENT. So we need to combine queries with external files. Let’s start off with an example query:

SELECT * FROM TBL_ATTACHMENT

Running will get you some results like this:

SS1

As we can see from the query ACT! gives us a list of files, which includes the file name, but no path. This isn’t a problem, because there will be a folder on the server where all these files are stored. Once you find them it’s pretty much a 1 to 1 mapping from each record to a file. All we need to do is map these files to the relevant entity records we will have already imported. Let’s take a look at a basic query to link these attachments to a company record. Take the following query:

SELECT [a].[ATTACHMENTID]
      ,[t].[NAME]
      ,[a].[FILENAME]
      ,[a].[DISPLAYNAME]
      ,[c].[COMPANYID] as [OBJECTID]
      ,'account' as [OBJECTTYPE]
from TBL_ATTACHMENT [a]
inner join TBL_HISTORY [h] on [h].[HISTORYID] = [A].[HISTORYID]
inner join TBL_HISTORYTYPE [t] on [t].[historytypeid] = [h].[historytypeid]
inner join TBL_COMPANY_HISTORY [ch] on [ch].[HISTORYID] = [h].[HISTORYID]
inner join TBL_COMPANY c on [c].[COMPANYID] = [ch].[COMPANYID]

Running this will get you some results like this:

SS2

The key thing to note here is linking to the company record via a table called TBL_HISTORY. This table is a multipurpose table, but it’s main purpose could be described as keeping a record of all “history” of actions that occur against records. I’ve included the TBL_HISTORYTYPE in the query to show how to pull out a description of the type of history activity that was recorded, but this isn’t something we need to migrate. Finally, and most importantly, we can use the link table TBL_COMPANYHISTORY to pull back records that are associated with companies.

Importing the data

Now that we have these company attachment records we need to import them. Remember, there is a mixture here between table records and files stored externally on the hard drive. This is the perfect opportunity to see what Data Synchronisation studio can offer in order to fulfil this task. At a first glance it looks just like your standard data import tool:

ss3

On the left you can link up your source data, i.e. the ACT! database. As I mentioned previously we have a set of views we create in the ACT! database so let’s hook into one of those. For all intense purposes let’s say the view is called xrmc_CompanyAttachments. On the right you link to your target table, which is the annotation entity in your Microsoft Dynamics CRM organisation. You can map across most of the columns straight from the query I prepared above.

ss4

So everything apart from the file contents has been mapped. Getting Data Synchronisation Studio to read this file and import it opens up a really interesting and powerful feature of this tool, namely Dynamic Columns. Every Dynamics CRM developer who has used a migration tool will love this – Dynamic Columns are simply properties exposed using C# classes! So let’s write our property:

// Convert the pysical file to a Base64 string
public string FileContents
{
  get
  {
    string fullFilePath = "C:\\Path To Act Attachments\\" + FILENAME;
    if (System.IO.File.Exists(fullFilePath)) {
      return Convert.ToBase64String(System.IO.File.ReadAllBytes(fullFilePath));
    }
    return string.Empty;
  }
}

And you end up with the following in Data Synchronisation Studio:

ss5

Imagine the power of this. Suddenly, reading in a file becomes such a simple task. Simply build the code using the provided build button and this provides you with a new list to map to your data. Map this new Dynamic Column across to your DocumentBody and you’re pretty much done:

ss6

If we do a compare, using the Compare A -> B button, let’s take a quick look at what our data looks like:

ss7

And that’s pretty much it. Click on Synchronise and watch those file migrate!

Next time I’ll discuss a more advanced scenario where we had to merge customer data from ACT! and Sage 50 Accounts into a single view of the customer within Dynamics CRM.

If you’ve got ACT! and you are looking to upgrade to Microsoft Dynamics CRM why not get in touch with our team.

Using Tags for creating dynamic marketing lists

Once you have started using our Tagging solution for Microsoft Dynamics CRM  you will want to use that information to segment your data. A common scenario is to build a marketing list of all Accounts that have been tagged with one or more tags.

The scenario we will use in this post is as follows: We are organising a breakfast event and want to be able to quickly tag contacts that have registered an interest in coming along. Account managers will call or email clients and prospects and manage the interest by tagging the relevant individuals.

Step 1 – Adding the tag to the contact records

Once tagging has been enabled on the Contact form a user simply has to enter (or select if it the tag has already been used) “Breakfast Event July 2013″ in the Tags box.   This process is repeated over the course of the campaign.

Breakfast Event Tag

Step 2 – Create Marketing List

When your are ready to collate your list of interested contacts, create a dynamic marketing list which identifies all contacts which have been tagged “Breakfast Event July 2013″

a) Create the Marketing List

Breakfast Event Marketing ListMembers

b) Click Manage Members to build the query

  1. Select Connections (Connect From) from the Related section of  the drop down list
  2. From the next “Select” list choose Connected From (Tag) from the Related section
  3. Finally choose Tag from the Fields section of the next “Select” list and lookup the Breakfast Event July 2013 tag

Breakfast Event Manage Members

c) View the marketing list members – we have only one in this case but as users add (or remove) the tag to contacts the list will dynamically change.

Breakfast Event Marketing ListMembers

Easy huh! Tagging can be a very quick way to manage your marketing lists – use it for managing subscriptions to newsletters, product interests, partner emails….

You can download the Tagging for Microsoft Dynamics CRM solution for free!

Tagging for Microsoft Dynamics CRM released

Have you ever wanted to set multiple ‘Regarding’ for a Microsoft Dynamics CRM record? Would you like a very quick and easy way to categorise your CRM information? Are you familiar with the concept of “Tagging” information?

xRM Consultancy are pleased to announce the free version of our Tagging Solution for Microsoft Dynamics CRM. The free version allows 250 tag ‘connections’. A paid for version with no restriction will be available shortly which does not have the 250 tag connection restriction.

Tagging Dashboard

The tagging solution allows users to ‘tag’ any record in CRM with either their own tags or previously entered tags. Users simply enter a word or phrase they wish to tag a record with. As they start typing existing tag matches are automatically displayed for users to select, and if no match is found the user’s new tag is added to the Tag ‘catalogue’. It is very intuitive and easy to use.

Please download the Tagging solution now and see how easy it is to use – we believe your users will love it! Please give us your feedback, positive or negative so we can improve the functionality our solution provides.

Future enhancements

We are already thinking about what we can do to improve the Tagging solution and would welcome your suggestions.

Please contact us with any ideas or questions you have on our Tagging solution.

  • XRM Consultancy Timeline
    Permalink Gallery

    Timeline for Microsoft Dynamics CRM updated with support for more entities

Timeline for Microsoft Dynamics CRM updated with support for more entities

We are pleased to announce that our second release of the very well received Timeline for Microsoft Dynamics CRM (MS CRM) is now available.

The new release has the following updates:

  • Support for more entities; we now cover:
    • Appointment
    • Campaign Response
    • Case
    • E-mail
    • Fax
    • Letter
    • Opportunity
    • Order
    • Phone Call
    • Service Activity
    • Task
  • Updated the queries that collect the timeline data:
    • Account Timeline records retrieved:
      • Where the Account is set regarding
      • Where the Account is set as the customer
      • Where the Account is a participant in an activity party
      • Where any of the Account’s Contacts are a participant in an activity party
    • Contact Timeline records retrieved:
      • Where the Contact is set regarding
      • Where the Contact is set as the customer
      • Where the Contact is a participant in an activity party
    • Opportunity Timeline records retrieved:
      • Where the Opportunity is set regarding
  • Added support for positioning the timeline navigation at the top

This release is still available as a free managed solution download.

Commercial Version

We are planning a paid for version with the following functionality:

  • Filter which records are displayed on the timeline (both at the configuration level and via the user interface from the timeline itself)
  • Display custom entities in the timeline
  • Display timeline on additional entities, include custom entities

Pricing has yet to be set, but please get in touch to register your interest.