Matching Salesforce Data to Back Office Systems

Overview

Often when working with Salesforce Account and Contact records, you want to see if there’s a match with existing SQL Server data in back office systems. While there are several commercial solutions that perform data matching on the Force.com platform (just search the App Exchange for deduplication or lead management to see what’s out there), as well as SSIS fuzzy lookups on the Microsoft SQL Server platform, often you want a quick and free solution that compares data between Salesforce.com and SQL Server data.

To do this, you simply need to add a custom formula field in Salesforce.com and a similar query in your SQL Server data. You can tweak this formula to match different fields depending upon the demographic information contained in the back office system. I will demonstrate this with the Account object in Salesforce.com. In this example, I am assuming that the company name, street, city and postcode will be stored in the back office system.

Duplicate Check field

The field simply concatenates parts of the customer’s name and address together, converts it to upper case, and pads any empty spaces with a character not likely to appear in the name or address fields. I chose the asterisk “*” for this example.

Name	Street	City	Post Code	Duplicate Check Code
				***************************
FELLS WARGO HOME EQUITY				FELLS W********************
CATAMARAN EXPRESS COMPANY		SAN BERNADINO	91911	CATAMA E**********SAN B91911
GUMFORT CARE DENTURE CENTER	900 HOCKSVILLE RD	MASSAPEQUA	91758	GUMFOR C900 HOCKSVMASSA91758
ORLANDO/GREEN COUNTY CAB	1234 LAKE ROAD	ORLANDO	12319-5273	ORLAND C1234 LAKE ORLAN12319

You’ll notice that this works best when the fields you choose to populate the check code are required, or at least generally populated in both systems. If you choose fields that are often left blank, you can turn up more false positives for matches, which defeats the purpose of the duplicate check.

Salesforce.com Custom Formula Field

To implement this in Salesforce, you need to add a custom formula field to the Account object in Salesforce.com:

1. Login to Salesforce.com as an account with administrator privileges.
2. Click the ‘Setup’ link at the top of the screen:
   
3. On the left hand App Setup menu, click Customize, Accounts, Fields:
   
4. In the Account Custom Fields and Relationships section, click New.
5. Choose Formula as the field type:
   
6. Enter a label and a name for the field. Choose Text as the output type of the formula field:
   
7. Enter the matchcode formula into the Advanced Formula text area:
   
8. Setup the field level security and add the field to the appropriate page layouts, and Save the field.
9. That’s it! The field will now be available via the API, Apex code and can be exposed through data exports with the Apex Data Loader.

I’ve reproduced the code from the screenshot in a copyable format below to save you from RSI

UPPER (
              MID(TRIM( NULLVALUE(Name, '') )& '******' , 1, 6)
            & MID(TRIM(NULLVALUE(Name, '')) & '*' , FIND(' ', TRIM(NULLVALUE(Name, ''))
                         & '*'),1)
            & MID(TRIM(NULLVALUE(Name, '')) & '***' , FIND(' ', MID(TRIM(NULLVALUE(Name,
                         '')) & ' ',
                   FIND(' ',TRIM(NULLVALUE(Name,'')) & ' ') + 1,59)) +
                   FIND(' ', TRIM(NULLVALUE(Name,'')) & ' ') + 1,1)
            & MID(TRIM( NULLVALUE(BillingStreet, '')) &'**********' , 1, 10)
            & MID(TRIM( NULLVALUE(BillingCity, '')) & '*****' , 1, 5)
            & MID(TRIM( NULLVALUE(BillingPostalCode, '')) & '*****' , 1, 5)
        )

SQL Custom Query

Although adding the custom field to Salesforce alone has some value (for example, you could use it in custom code to detect duplicates), the real value is when you can match up the values to your back office system. I’m assuming your back office server is running SQL Server 2005 or higher for this T-SQL example, but most forms of SQL have equivalent functions that can manipulate strings, so check your documentation if you are using a different flavour of SQL, and please post your custom formula in the comments section to help out others.

This query could be added to a view, stored procedure or custom field in SQL.

For this example I’m assuming that the fields are called ‘Name’, ‘StreetName’, ‘City’, and ‘ZipCode’, and are stored in a table called CompanyInfo.

SELECT UPPER (
        SUBSTRING(LTRIM(RTRIM(ISNULL(Name, '')))+ '******' , 1, 6) +
        SUBSTRING(LTRIM(RTRIM(ISNULL(Name, '')))+ '*' , CHARINDEX(' ', LTRIM(RTRIM(ISNULL
                         (Name, ''))) + '*'),1)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(Name, ''))) + '***' , CHARINDEX(' ', CHARINDEX(LTRIM
                         (RTRIM(ISNULL(Name,''))) + ' ',
             CHARINDEX(' ', LTRIM(RTRIM(ISNULL(Name,''))) + ' ') + 1,59)) +
             CHARINDEX(' ', LTRIM(RTRIM(ISNULL(Name,''))) + ' ') + 1,1)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(StreetName, ''))) + '**********' , 1, 10)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(City, ''))) + '*****' , 1, 5)
      + SUBSTRING(LTRIM(RTRIM(ISNULL(ZipCode, ''))) + '*****' , 1, 5)
     ) AS 'DuplicateCheck'
FROM CompanyInfo

Using your duplicate check code

Once the check is in place in both systems, you can use it in several ways. You can match the fields directly, you could create a matching “confidence” score by using character by character comparisons or you could use more complex fuzzy lookup functions to further match the data.

Summary

Now that you have a single field to compare Salesforce and SQL data, you can tweak the values to find better matching algorithms, and use these fields as part of any duplicate checking process for data integration.