Understanding QuickBooks Integration

This article discusses important concepts and best practices to use when integrating with QuickBooks. It is outside the scope of this article to fully document every aspect of the application's interfaces including record types and field definitions. Please consult the application's official documentation for full reference information. As always, familiarity with the data of the systems to be integrated is strongly advised.

General Concepts

Every QuickBooks Connector call consists of a request message and a response message. When getting data from QuickBooks, the request message contains selection criteria and the response message contains the resulting data records. When sending data to QuickBooks, the request message contains the data records to insert/update and the response contains the transaction status with any error messages.

The format of the request and response messages are represented by the Request and Response XML Profiles selected in the QuickBooks Operation Component. You use these Profiles in Maps to transform to/from other formats.

Items, Customers, and other list records can be hierarchical. A record's Full Name contains its complete hierarchy. Hierarchy levels are delimited by colons (":"). You should use the Full Name when interacting with other systems. For example, if the item number "ABC-1234" was grouped under the parent "Retail", its Full Name would be "Retail:ABC-1234".

There are two broad categories of records in QuickBooks: transaction records and list records. Transaction records contain the transactional data such as Sales Orders and Invoices and use a unique ID called "TxnID". List records are the master data records such as Customers and Items and use a unique ID called "ListID". It is often useful to send these IDs to external systems to use as a cross reference in future corresponding messages.

Getting Data from QuickBooks

When querying transactions, you can usually narrow the results by setting filters. The Request Profiles of most Query operations contain fields to specify criteria. To set criteria, add Parameters in the Start Shape and enter a static or dynamic values. For example, to perform synchronizations you typically only want to extract records that have been recently added or changed (since the last time the synchronization ran). To do this, add a Date/Time Parameter for the FromModifiedDate field and set the value to the Last Run Date. Review the individual Request Profiles to determine what kinds of filters are available.

Also, when selecting transactions, you typically want the associated line items. To return the line items, you must add a Static Parameter for the includeLineItems field and set the value to "true". Similarly, if you want the transactions associated with a given transaction, set the includeLinkedTrxns to "true".

If your transaction or list items have custom fields or "Data Extensions", the custom fields typically will not be extracted by default. To get these fields you should set the Owner ID field to "0" (zero) in the request. To accommodate multiple custom fields you will need to modify the response XML Profile and create additional DataExtRet elements (with child elements) for each custom field. Next, add a constraint to each DataExtRet element, setting Element = DataExtName, Type = "Value", and Value = the name of a specific custom field.

Lookups from QuickBooks

As mentioned in the previous section, you may want to check if a list record exists before importing a transaction. You can do this with a Connector call in a Decision shape. Use a Query Operation that only return metadata. If the record exists, the retCount attribute in the response will equal 1. (Note: The retCount attribute only applies to QuickBooks Desktop editions; it does not work for QuickBooks Online.)

Sending Data to QuickBooks

When sending data, especially transactions, any dependent records must already exist or the import will fail. For example, to add a Sales Order, the Customer and Item records referenced in the transaction must already exist in QuickBooks. If this is a possibility in your business scenario, in the same Process you should query QuickBooks using a Decision shape to make sure the necessary dependent records exist and either create the dependent record "on the fly" or reject the record.

Sending data to QuickBooks always produces a response message with the result of the import. If you configured the Operation to handle errors automatically, the Connector will interrogate the response for you and mark Documents as success/fail as necessary. You can end the Process path immediately after the QuickBooks Connector step. However, if you choose to handle errors manually, you should interrogate the response in the Process to determine whether the action completed successfully. After the QuickBooks Connector step, use a Decision to check that the statusCode attribute in the response equals 0. If it does, the action was successful and you can end the Process. If greater than 0, the action failed. You can get the error message from the statusMessage attribute to use in a dynamic alert message or exception.

Updating Existing Records

When attempting to update existing records, you must provide an EditSequence value in the Modify request. The EditSequence field is used by QuickBooks to enforce data integrity in multi-user environments and ensure users are working with the latest version of records. When synchronizing with external systems, you will need to do a lookup to QuickBooks to get the current EditSequence value. You can do this with a Map Function in the Map where you are mapping to the Modify request. Do a Connector Call query against the appropriate record type using the ListID/TxnID or FullName. Return the EditSequence and map it to the destination Profile.

Troubleshooting

For Desktop editions, if you experience errors when getting or sending data, first consult the Execution logs in the Manage Tab. For additional information you can look at the QuickBooks qbsdklog.txt file.

• QuickBooks 2007 and later: C:\Documents and Settings\All Users\Application Data\Intuit\QuickBooks\qbsdklog.txt
• Earlier versions: C:\Program Files\Common Files\Intuit\QuickBooks\qbsdklog.txt
  

One particular error you may run into when running on certain versions of Windows and QuickBooks:

20070924.155247	I	584	RequestProcessor	========= Started Connection =========
20070924.155247	I	584	RequestProcessor	Request Processor, QBXMLRP2 v6.0
20070924.155247	I	584	RequestProcessor	Connection opened by app named 'Boomi QuickBooks Connection'
20070924.155252	E	584	RequestProcessor	An internal error occured while looking for a running instance of QuickBooks. Cannot continue. hr = 800401f3
20070924.155252	I	584	RequestProcessor	Connection closed by app named 'Boomi QuickBooks Connection'
20070924.155252	I	584	RequestProcessor	========== Ended Connection ==========

Solution
1a. For Windows XP, go to Control Panel -> Administrative Tools -> Component Services -> Console Root -> Component Services -> Computers -> My Computer -> DCOM Config. Find the QBXMLRP2 object, right click and choose Properties. On the Identity tab, select "Interactive User".
1b. For Windows 2000/NT, go to Start -> Run, type "dcomcnfg". In the Applications tab, select QBXMLRP2, right click and choose Properties. On the Identity tab, select "Interactive User".
2. Restart the computer.

Date Format

QuickBooks Date/Time elements should be configured with the following format:

yyyy-MM-dd'T'HH:mm:ss

Additional Resources:
QuickBooks Interface Documentation
QBXML Error Codes