QuickBooks Integration

QuickBooks is now a SQL Database – So what does that mean   anyway?

By Dawn Scranton, Certified QuickBooks ProAdvisor

Have you been keeping up with all these new acronyms for technology? SQL, XML .NET, ODBC, VB all sounds like a new “foreign” language to me. Developers are trying their best to explain the differences, but what I keep hearing is “Now that QuickBooks is an SQL database, we have more choices for integration…” – so what does that mean anyway?

Well, we know that QuickBooks software allows us to collect various bits and grits of data. But what you may not know is that the data is managed and maintained by a Structured Query Language (SQL) that can be unique to a certain degree, because of its flexibility. This means that as your business grows and your staff’s activities span across multiple points of introduction (such as working with an outside sales force, selling at a trade show, or selling on the internet), it is likely that you will sooner or later need to share your QuickBooks database with other databases, software applications or networks and operating systems. So here are two examples of how QuickBooks SQL helps you meet that need.

Suppose you’re using a program such as Excel or Access for “Estimating” of client projects, job costs and calculating profit margins. After your staff is done creating an estimate and the proposal is accepted by your client, how would you notify the production and/or accounting department to move forward with the project? Would you re-enter the estimate in your production and scheduling software to set the wheels in motion? Would you re-enter the estimate in your QuickBooks database, so that your staff can proceed with invoicing, tracking of inventory, and reporting requirements?

Developers can enhance your staff’s productivity and business flow by automating the process of pulling and promoting specific data, from one database to another. The steps required might look like this:  

Existing Database: MS Excel	Identify and define the desired components of financial data	QuickBooks Database
Task: Estimate(s) have to be exported or extracted from the source data. Task: Estimate(s) have to be re-aligned and matched to specific QuickBooks fields. Task: Estimate(s) must be read and stored into the QuickBooks SQL database. Developers Toolbox: Translators like ODBC extract or export data into XML, TXT, or CSV formats. Developers Toolbox: Developers use data manipulation tools such as qbXML or QODBC combined with programming language skills such as VB .NET, C++, etc. Developers Toolbox: Results from the previous task are subjected to QuickBooks verification and the rules outlined in the Software Developer Kit (SDK).

Additional Considerations:

• If you want to extract data from QuickBooks to another database the process is the same, but simply flows in the opposite direction.
• When you upgrade one application (such as QuickBooks) it can cause a domino effect that requires other parts of the process (such as the interface routine) to also be updated.
• The cost of defining and developing interface routines has steadily declined. Four to five years ago, companies spent upwards of $ 30,000 - $ 50,000 to meet these objectives. Today, depending on the level of complexity, the average custom solution can be obtained for as little as $ 3,000 - $ 5,000.

Example 2 – Copying data from your Internet Shopping Cart to QuickBooks:

Another example that demonstrates the need for sharing database information is selling on the internet. Once you have a shopping cart or order management system, what happens after your shopper’s purchase has been approved? Normally, you would receive an email informing you that a sales transaction has occurred, been processed, confirmed and approved, and forwarded to the shipping department, but then what? Do we re-enter the sales transaction into QuickBooks to update our checkbook balance and sales records? Do we re-enter the customers name in our customer relations management (CRM) software for future marketing efforts and promotions?

Sounds complicated doesn’t it? Believe it or not, the process is essentially the same. The developer’s steps to streamline this process, would look similar to this:  

Shopping Cart (ISP) Server	Identify and define the desired components of financial data	QuickBooks Database
Task: Sales Receipt(s) have to be exported or extracted from the source data. Task: Sales Receipt(s) have to be directed and mapped to specific QuickBooks fields. Task: Sales Receipt(s) are read and stored into the QuickBooks SQL database. Developers Toolbox: Translators like ODBC extract or export data into XML, TXT, or CSV formats. Developers Toolbox: Developers use data manipulation tools such as qbXML or QODBC combined with programming language skills such as VB .NET, C++, etc. Developers Toolbox: Results from the previous task are subjected to QuickBooks verification and the rules outlined in the Software Developer Kit (SDK).

In summary, welcome to the world of technology, where I think programmers are eating too much alphabet soup! But now you know – The QuickBooks SQL database is a platform for assembling data, that offers developers a certain level of compatibility and the foundation to effectively communicate with the QuickBooks SDK, a (XML based) bridge that connects QuickBooks to other business software applications.

Dawn Scranton, CQA
Accounting Directors.com
(561) 683-3497

WORKS WITH QUICKBOOKS MERCHANT SERVICES