Understanding Common Data Integration Challenges Can Improve BPM: A Salesforce-Quickbooks Integration Example

Untitled Document According to Intuit research, 45 percent of small business retailers manually enter sales data into their QuickBooks accounting software on a daily basis. With many such data entry issues being relatively small in scope, companies can't justify investing in expensive middleware to integrate systems to solve small problems. The result is an error-prone process with increasing costs to maintain the same records in two or more systems.

This article considers the example of integrating QuickBooks with salesforce.com. But, there is more to improved QuickBooks and Salesforce integration than just saving time and effort; quality data integration makes for better business processes which helps accelerate sales in various lines of business. On the one hand, you have the sales department that uses Salesforce, which is always updated with customers’ credit and invoice information obtained from the back office’s QuickBooks. On the other hand, your accounting department is guaranteed to keep abreast of the current sales by having their QuickBooks synchronized with Salesforce.



This is what caused Intuit to develop a native Web connector for integrating QuickBooks with third-party Web applications such as Salesforce CRM. But even with the Web connector, building a bridge between Salesforce and QuickBooks still remains difficult for the smaller and mid-size enterprise that would typically use these applications to manage their business processes.

Common Challenges

When trying to synchronize applications such as QuickBooks and Salesforce, users typically face five common challenges.

1. Data mapping

One of the most evident problems with integrating QuickBooks and Salesforce is the mismatching between data fields and types. The names and locations of data fields are often different in the two applications. Moreover, there can be mismatching even in the number of the fields. For instance, there is a total of five Address fields in QuickBooks while Salesforce allows only one, Billing Street. That is why even manual mapping does not relieve you of the problem. And custom coding typically proves to be overly resource and time consuming.

Figure 1. Compare address fields in Salesforce CRM and QuickBooks

2. Duplicated entries

The problem with this lies in Salesforce allowing repeated names, an option that QuickBooks does not have. What do you do with accounts that have the same name in Salesforce when importing them to QuickBooks? Obviously, overwriting is not the best option, as doing so creates a threat of data loss. Another problem is that when updating Salesforce tables with QuickBooks data, several entries with the same name may be encountered. If that happens, it is unclear which of them is to be modified.

3. String length conflict

In many cases, either Salesforce or QuickBooks allows more characters in a data field than the other application does in a related data field. Hence, every time when trying to import a data field from Salesforce to QuickBooks whose length exceeds QuickBooks’ limit, and vice versa, a field size conflict is inevitable. Without data integration tools, a user is most likely to encounter either an error message, or a forced string truncation. The latter is especially dangerous when it comes to truncating fields used as primary keys for data updates. Additionally, this constitutes a threat of duplicated fields emerging, if two or more fields beginning with the same characters are cut down to the same length. And while in Salesforce, it is a problem of losing the differentiating parts of the fields, QuickBooks does not support duplicated fields at all.

4. Preserving referential integrity

Not only do Salesforce and QuickBooks tables significantly differ in content, merely transferring the entries accurately, which itself is a hard enough task, does not ensure the preservation of their integrity. The links existing between data fields are not preserved this way, and need to be traced before importing and created over afterwards.
In QuickBooks, for example, a product (Service Item) is linked to an Account, while Salesforce relates Products to Pricebooks, and the correlation between Accounts and Pricebooks is not at all an obvious thing. In fact, this is probably the trickiest of the challenges, making manual QuickBooks and Salesforce integration next to unfeasible.


Figure 2. Salesforce’s Product and QuickBooks’ Service Item

5. Automatic ID generation in Salesforce

What is more to the issue described above, upon creating a data record (in particular, when importing it), Salesforce automatically generates its own ID for it, no matter if it already has one, which is likely to be true when transferring tables from third-party applications, such as QuickBooks. This makes it impossible to preserve links between the records imported from QuickBooks that are connected via their IDs. On the other hand, while QuickBooks generally accepts imported IDs, the format of IDs in QuickBooks and Salesforce may differ.

Possible Solutions

Facing these and other challenges, QuickBooks and Salesforce users have to choose between building their own or buying software (on-premises or hosted) to get the job done.

The benefit of building their own software is the flexibility to customize synchronization according to any requirements, but the disadvantages (the cost, access to engineers with knowledge of Salesforce API and QuickBooks SDK, and time) make this option unreasonable in most cases.
Hosted or on-premises data integration tools, on the other hand, are cost-effective and quick to deploy. The cost to deploy on-demand service to synchronize Salesforce to QuickBooks data may range from a few hundred to under a thousand of dollars, per month.

Since QuickBooks and Salesforce have been designed specifically for small and midsize business users, data integration software suppliers are willing to make their products as accessible to these users as possible. For instance, Apatar On-Demand, with its pre-built Salesforce and QuickBooks synchronization, allow running one- or two-way, recurring or one-time synchronization of account, contact, order, and opportunity data, while preserving links between tables in both Salesforce.com and QuickBooks. Implementation of this hosted service takes 30 minutes or less using a browser.

Figure 3. Pre-built synchronization: just fill in your Salesforce/QuickBooks details


A number of similar solutions are also available in the market.

Apatar’s software as a service (SaaS) solution and the other options provide more examples of how users can improve business process management quickly without adding more technology that requires more administration and onsite equipment.

About the Authors

Alex Khizhnyak is Chief Apatar Evangelist and Chairman of Belarus Java User Group. Since 1998, he has gained experience as an author, editor, conference speaker, media specialist, and blogger. So far, his education background combines IT, programming, economics, and journalism. Alex works at Apatar, Inc., the leading provider of open source tools for the data integration market, and writes a blog on Open Source and Data Integration (http://apatar.com/blogs/alex/).

More by Alex Khizhnyak

Renat Khasanshyn is founder and CEO of Apatar, Inc. Mr. Khasanshyn is a subject matter expert on data mashups and open source business models, and speaks frequently at a wide range of events. Most recently, Mr. Khasanshyn was selected as a finalist for the 2007 Emerging Executive of the Year award by the Massachusetts Technology Leadership Council. In 2006, he founded Apatar, the world's first on-demand, open source data mashup software company. Prior to founding Altoros Systems in 2001, Renat was VP of Engineering for Tampa-based insurance company, PriMed, Inc. Renat has a passion for emerging technologies and won the 2007 IBM Business Mashup Challenge. Mr. Khasanshyn is a co-founder of the Belarusian Java User Group and studied Engineering at the Belarusian State Technical University. Renat writes a blog, Naked Open Source, found at http://www.nakedopensource.com

More by Renat Khasanshyn

Michael Fedotov is Apatar Evangelist and has been working as a freelance journalist for a number of IT-related periodicals since 2003, covering next to all aspects of IT, and specifically software developments, in an abundance of articles. He has taken part in several scientific conferences and provided service for many more participants creating and holding their presentations. He also has experience working as an interpreter and presently is studying Japanese.

More by Michael Fedotov