Tag Archives: QuickBooks Integration

QuickBooks Integration: Retrieving Custom Field Values in qbxml

QuickBooks allows us to create custom fields. Sometimes we are required to retrieve the custom fields in the integration process.

So how do we want to get the custom field values out of an object using qbXML?
We will need to set the OwnerID to 0 in the request.

For example, we perform an ItemServiceQuery request with the following request:

<?xml version='1.0' encoding='UTF-8'?>
<ItemQueryRq>
  <ListID>80000031-1314102902</ListID>
  <OwnerID>0</OwnerID>
</ItemQueryRq>

In return, we will receive the following response including the custom fields and their values:

<ItemQueryRs requestID="2" statusCode="0" statusMessage="Status OK" statusSeverity="Info">
   <ItemServiceRet>
      <ListID>80000031-1314102902</ListID>
      ...
      ...
      ...
      ...
      <DataExtRet>
         <OwnerID>0</OwnerID>
         <DataExtName>Category</DataExtName>
         <DataExtType>STR255TYPE</DataExtType>
         <DataExtValue>Delivery</DataExtValue>
      </DataExtRet>
      <DataExtRet>
         <OwnerID>0</OwnerID>
         <DataExtName>Region</DataExtName>
         <DataExtType>STR255TYPE</DataExtType>
         <DataExtValue>Europe</DataExtValue>
      </DataExtRet>
   </ItemServiceRet>
</ItemQueryRs>

Integrating to QuickBooks Desktop

URL http://quickbooks.intuit.com/
Description On-Premise Accounting Software
API Yes, http://developer.intuit.com/qbsdk-current/Common/newOSR/index.html
Trial Version with API Access Yes.
Protocol REST and SOAP
Data Format XML
API Limits N/A
Sample Use Case Salesforce Opportunity > Creates > QuickBooks Transaction (Invoice, Sales Order, etc.)QuickBooks Item > Creates/Updates > Salesforce ProductSalesforce Account/Contact > Creates/Updates > QuickBooks Customer
F2I 3.5
Objects/Operations

QuickBooks Integration: Error 3170

Let us talk about one of the common errors returned from QuickBooks integration when the integration process incorrectly modify the data in QuickBooks.

Status code: 3170: Status message: There was an error when modifying Vendors/Customers list, element “#######-##########”. QuickBooks error message: Cannot merge list elements.

Why does this happen? This will occur when modifying a Customer/Vendor record by referencing the record’s ListID and Customer Name, however, there is already an existing record with the same Customer Name in QuickBooks. Here is an scenario to explain this error:

We have an integration process between the QuickBooks and Salesforce where we can sync the Customer/Account from/to QuickBooks to/from Salesforce.

1. There are 2 customers in QuickBooks.

  • Customer Name #1: Tony Adam;          ListID: 1234567-1234567890
  • Customer Name #2: Ronald Williams;   ListID: 2345678-2345678901

2. These 2 records have been synced to Salesforce and they are created as Account records with the ListID as external ID.

3. User modified Customer #1 in Salesforce. The customer name is changed to Ronald Williams (same as Customer #2).

4. Customer #1 is being synced into QuickBooks via API and it tries to update the customer by the ListID. However, QuickBooks blocks this operation as it found that there is already an exsting recod found (Customer #2) with the same Customer Name but different LISTID. Hence, Customer #1 failed to be updated into QuickBooks.

Note: Customer Name must be unique in QuickBooks, this is to avoid duplication in customer records.

QuickBooks Integration Part 2: Getting the Related Transactions

When integrating QuickBooks data to another application, syncing the accurate and relevant transaction information over to the destination application is very useful.

The easiest and simple way is to sync the specific transaction and its details. However, have ever you wonder if you want to get the other transaction details that are related to this transaction? And how can you get these details synced over whenever you create a transaction?

Lets have an example to explain the above situation.

  • An accountant has just created a Sales Order in QuickBooks.
  • Then, she converted this Sales Order into an Invoice.
  • After that, she wants to sync this Invoice to another application
  • So, this Invoice is being synced to the destination but the user wants to get more information about this Invoice, for example:

– Is the Invoice converted from Sales Order?

– Does this Invoice have any related invoice from the same Sales Order?

All these can be done with some tweaks in an integration process. Basically, what you have to do is to set the parameters when doing a request call for Invoice.

Here are the parameters that can be enabled to display/filter out the elements of the Invoice:

Lets say we have an Invoice that is converted from a Sales Order, so when we query out this Invoice,  we would like to know which Sales Order that this Invoice is converted from. We can set the parameter above (selected with red colour box), ‘IncludeLinkedTxns’ = true.

As we can see here, this is part of the Invoice response XML which we can get the Sales Order useful information from the Invoice.

Prevent Concurrent Data Modification

Applications such as Quickbooks or Saasu have a mechanism to prevent cocurrent edit. If you plan to integrate with Quickbooks or integrate with Saasu, this is the mandatory field when you are performing an update call:

  • Quickbooks uses a field called EditSequence 
  • Saasu uses a field called LastUpdatedUid

This field value is automatically updated by the application after an update event. If two connections come in with the same value, one will succeed and one will fail.

This type of mechanism is also useful to prevent concurrent updates from a UI perspective. This is how it works:

  • PersonA is editing a customer named WDCi. (current EditSequence=1000)
  • PersonB is also editing a customer named WDCi using different machine (current EditSequence=1000)
  • PersonA completes the modification first and click the save button. The request is then submitted to the server with EditSequence=1000. Now the new EditSequence = 1001
  • PersonB completes the modication later and click the save button. The request is then submitted to the server with EditSequence=1000 but the server rejected the request because it does not match the latest EditSequence number (1001)

Quickbooks Integration: Transactional Data Model

If you are integrating with Quickbooks (desktop version), here we have an example of the transactional data model of the objects that can be integrated.

  • A customer object can have one or many transactions (Sales Order, Invoice, Sales Receipt)
  • A transaction object can have one or many items.
  • A Sales Order can be converted into an Invoice.
  • An Invoice can have one or many payments.

This is high level data model diagram involving the transactional objects:

Other objects in Quickbooks may be integrated too. If you want to know more about the Quickbooks integration, please feel free to contact us.

QuickBooks Integration: Transaction (Sales Order, Invoice) Counter

When creating a transaction in QuickBooks, a transaction number will be generated followed by the last transaction created.
In most of the applications, transaction number has to be unique and is not allowed to be modified  to prevent duplication and confusion.
However, in QuickBooks, transaction number is allowed to be modified which means that when creating a new transaction, user can either use the transaction number that
is generated by the QuickBooks or modify this number to other value.
If the user attempts to create a transaction which the transaction number is same as the number of other transaction that has been created in QuickBooks,
a warning message will notify the user telling that a problem has occurred. User can still proceed with the modification.

There is another issue after modifying the transaction number, which is the transaction number counter will also be reset based on the last transaction number created.
For example, the original Invoice number generated is 1501, if a user change this number to 101 and created this Invoice. The number for the next newly created Invoice will
be 102.

If you want to integrate QuickBooks with another application, this issue should be taken into consideration because the transaction number may be used as an external ID
for the record. This external ID has to be unique to create a relationship between the record in QB and the other integrated application.