Matching Salesforce Data to Back Office Systems


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 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 and SQL Server data.

To do this, you simply need to add a custom formula field in 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 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.

Street City Post Code Duplicate Check Code

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. Custom Formula Field

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

  1. Login to 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:
    Customize Accounts
  4. In the Account Custom Fields and Relationships section, click New.
  5. Choose Formula as the field type:
    Formula Field
  6. Enter a label and a name for the field. Choose Text as the output type of the formula field:
    Output Type
  7. Enter the matchcode formula into the Advanced Formula text area:
    Matchcode Formula
  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

              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.

        SUBSTRING(LTRIM(RTRIM(ISNULL(Name, '')))+ '******' , 1, 6) +
                         (Name, ''))) + '*'),1)
                         (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.


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.

It hurts when I do this – an open letter to Software Consultants

NOTE: I originally posted this at my old company's website in 2010,
but as that blog has been taken down,
I thought I'd resurrect this post here.

There’s an old joke that goes something like this:

A young woman went to her doctor complaining of pain. “Where are you hurting?” asked the doctor.
“You have to help me, I hurt all over”, said the woman.
“What do you mean, all over?” asked the doctor, “be a little more specific.”
“The woman touched her right knee with her index finger and yelled, “Ow, that hurts.” Then she touched her left cheek and again yelled, “Ouch! That hurts, too.” Then she touched her right earlobe, “Ow, even THAT hurts”, she cried.
The doctor checked her thoughtfully for a moment and told her his diagnosis: “You have a broken finger.”

So what does this have to do with software development?

Too many times consulting companies get customers to tell them what they want and then they build it. What’s wrong with that? Isn’t there an old saying that says the customer is always right?

If you build what the customer asks for without digging deeper into the underlying business problem, then I guarantee that you will not build what they need. If you are just delivering a technical solution to a customer without figuring out whether you are solving the right problem in the best way, you aren’t giving your customer value for money. In fact, no matter how low you put your rates, you are short changing your customers, because you are charging them money to not fix their problems.

Just like a doctor’s patient can describe their symptoms, but doesn’t necessarily know the correct prescription to cure their ills, a customer is typically able to describe the business problem they are facing – but that doesn’t always make them the best person to come up with the solution. You need to collaborate with the customer and dig deeper to understand what the underlying issues are.

Of course, the customer is typically the expert in their domain and you should be the expert in yours, but you have to be more than just a technical expert to deliver business value. You need to be creative and passionate, you need to establish trust with the customer and build a collective sense of ownership of the problem so that you and the customer can both contribute to the solution.

You can use techniques like the Five Whys to perform root cause analysis, but ultimately you need to be interested and involved with your customers and care about their business. Only after defining the root cause of the problem can you then relate the problem to a solution you can provide with your domain expertise.

Ultimately, it’s not about delivering a project on time and under budget if that project isn’t effective; only by helping your customers succeed have you given value for money.

Installing DBFit for SQL Server

NOTE: I originally posted this at my old company's website in 2011,
but as that blog has been taken down, I thought I'd resurrect this
post here.

I’ve been using DBFit on several projects recently, and I’m really pleased with the results. If you’re not familiar with DBFit, it’s a set of Fit fixtures that allows Fitnesse tests to execute directly against a database, without you having to build a separate connector. This post shows you how to get started with DbFit with the least amount of pain.

I practice Test Driven Development and while TDD is a pretty mature discipline in most modern programming languages, it’s still not widely practiced in the database world. We’ve had some success in the past using TSQLUnit, but I prefer DbFit because you can use it to write Unit Tests as well as Acceptance Tests. In addition, Fitnesse allows you to add additional colour in the form of documentation to truly turn your acceptance tests into an executable specification.

One thing that doesn’t (at least to me) appear to be clear from the documentation is that to install DbFit to test a Microsoft SQL Server database, you can simply install Fitnesse, and then install FitSharp. The advantage of doing this, compared to installing DbFit from sourceforge is that you will be working with the latest and greatest versions of Fitnesse and FitSharp. The current instance of DbFit on sourceforge uses a Fitnesse build from 2008.

Here is a high level overview of the installation steps (more detailed information including troubleshooting steps is available at each of the links below):

  1. If necessary, install Java 6 from here:
  2. Install Fitnesse from here: As it mentions on the downloads page, just download fitnesse.jar, into the folder in which you’d like to install it  and type java -jar fitnesse.jar -p 8080 (where 8080 is the port number you’d like to use to run fitnesse from – usually port 80 is taken by other stuff on your machine) at the command line. Fitnesse will unpack and install itself.
  3. If necessary, install .NET framework 3.5 or 4.0 from here:
  4. Install FitSharp from here (choose the correct version for your version of the .NET framework): To install FitSharp, create a folder under the folder into which you installed Fitnesse (I name mine FitSharp) and unpack the installation files into this folder.
  5. Once you’re installed and up and running, follow the examples in the DbFit Reference to see how the whole thing works.
  6. One additional tip is that if you’re following the Hello World example in the DbFit reference, use the following text instead of the one they document in Step 2: Setting Up the Environment. This will correctly point you to the location of your FitSharp installation files and use the Microsoft SQL Server flavour of DbFit:
!define COMMAND_PATTERN {%m -r fitnesse.fitserver.FitServer,"FitSharp\fit.dll" %p}
!define TEST_RUNNER {FitSharp\Runner.exe}
!define PATH_SEPARATOR {;}
!path FitSharp\dbfit.sqlserver.dll

I’ll be posting some further information on using DbFit as an executable specification that includes acceptance tests, for Unit Testing and performance testing, as well as showing you some of the ways we’ve organized our test suites to make them more maintainable and easier to understand. Happy Database Testing!


Heilmeier’s Catechism – a checklist for software projects

NOTE: I originally posted this at my old company's website in 2010,
but as that blog has been taken down, I thought I'd resurrect this
post here.

Until recently, I am ashamed to say that I had never heard of George Harry Heilmeier. A recent retweet by Roy Osherove on Twitter soon had me digging for more information.

It turns out that not only was Mr. Heilmeier  a pioneering contributor to liquid crystal displays, he was a Vice President (and later CTO) of Texas Instruments during the time they produced the mighty Speak and Spell.

Mr Heilmeier’s Wikipedia page lists an amazing amount of awards, including the National Medal of Science  and the IEEE Medal of Honor, but that’s not what sparked my curiousity.

What was interesting to me about Mr. Heilmeier was a series of questions anyone should be able to answer when proposing a research project or product development effort. These questions are known as Heilmeier’s Catechism.

Here is Heilmeier’s original list of questions:

Heilmeier’s Catechism

  • What are you trying to do? Articulate your objectives using absolutely no jargon.
  • How is it done today, and what are the limits of current practice?
  • What’s new in your approach and why do you think it will be successful?
  • Who cares?
  • If you’re successful, what difference will it make?
  • What are the risks and the payoffs?
  • How much will it cost?
  • How long will it take?
  • What are the midterm and final “exams” to check for success?

When I read this list, it struck me that these questions could easily be adapted as a software project checklist.

With some small tweaks in language, this list becomes a standard project checklist that any consulting organization should work on with their customers to answer when deciding whether or not to go ahead with a project:

Project Checklist

  • What is the underlying business problem we are trying to solve with this project?
  • What happens today? Is this problem worked around with manual processes?
  • What’s new in this approach and why do we think it will be successful?
  • Who are the project stakeholders?
  • If we’re successful, what difference will it make?
  • What are the risks and the payoffs? How can the risks be mitigated?
  • How much will it cost?
  • How long will it take?
  • How will we measure progress on the project? How do we know we’ve been successful?

What about your organization’s project approval process? Does your company use Heilmeier’s Catechism to decide whether to give a project a green light? What other questions should be asked before starting a project?

Is your consulting code an asset or a liability?

NOTE: I originally posted this at my old company's website in 2010,
but as that blog has been taken down, I thought I'd resurrect this
post here.


When you hire consultants to build software for you, how do you know if the code is worth the money you pay them?

Consulting code (indeed, any custom code) should be an asset to your business, but unfortunately, many times it’s quite the opposite.

Consulting code can become a liability. Lack of tests and poor quality, buggy code can leave you, your code and your pocket book in worse shape than before the consultants arrived (and usually with little to no recourse).

Untested code has no business value

Years ago at the Agile 2006 conference, one of the sessions was “Delivering flawless tested software every iteration”, delivered by Alex Pukinskis. The catchy name attracted a big audience and it was standing room only.

During this presentation, Alex made the following statement: ((I’m not sure whether Alex was quoting someone, but googling that precise phrase returned zero results, so I’m attributing it to him))

Untested code has no business value

This struck a chord with me, because at the time I was working for a company that was trying to transition to agile, but many of our practices still hadn’t changed. We were (okay I was) still breaking the build and still expecting the QA team to find our bugs for us. We weren’t consistently doing TDD, we had no automation of the build acceptance and we were not practicing continuous integration. I could go on, but I’m sure you get the idea.

The “aha” moment for me was the concept that the quality of the code, and a lack of defects was my responsibility as a professional developer. It was my responsibility to ensure that no defects were passed to QA. Of course, I know that software cannot be perfect, and there will be defects, but my attitude towards defects changed after that talk. Defects should be unexpected. Defects should be unusual. Defects should be prevented, not found.

No Bugs

The attitude that defects should be prevented, not found, can be summarized in the “No Bugs” philosophy.

James Shore recently published the full text of the No Bugs section from his excellent Art of Agile book. He summarizes the text in 99 words:

Rather than fixing bugs, agile methods strive to prevent them.

Test-driven development structures work into easily-verifiable steps. Pair programming provides instant peer review, enhances brainpower, and maintains self-discipline. Energized work reduces silly mistakes. Coding standards and a “done done” checklist catch common errors.

On-site customers clarify requirements and discover misunderstandings. Customer tests communicate complicated domain rules. Iteration demos allow stakeholders to correct the team’s course.

Simple design, refactoring, slack, collective code ownership, and fixing bugs early eliminates bug breeding grounds. Exploratory testing discovers teams’ blind spots, and root-cause analysis allows teams to eliminate them.

Asking the right questions

Now you are probably expecting some sales pitch from me at this point to say that you should hire us because we’re great. Well that’s not the point of this post (although you should, and we are). The point of this post is that the next time you are talking to a consulting firm or hiring a developer (( For more information on hiring developer team members, read, ask them about their definition of code quality and how they ensure it. By simply asking a couple of questions you should be able to determine whether they are all “smoke and mirrors” or if they will add value to your business. For example, you might ask:

  1. What is your definition of quality code?
  2. How do you ensure your code is bug free?

The answer to the first question should mention practices like the Law of Demeter, coding standards, refactoring and adherence to the SOLID principles.

Although unsettling, it is okay if the answer to the second question is “I‘m never 100% sure my code is bug free” (particularly if they mention Gödel’s proofs). However, they should quickly follow up with, “I make bugs less likely by practicing test driven development, peer reviews (or pair programming), automated acceptance tests and adherence to coding standards and good design principles.”

If they can answer those two questions to your satisfaction (and follow through by demonstrating these practices when building the code) then they might know what they are talking about, and actually give you value for your money.