Today's post is written by Rob Jasinski, Development Principal at Sonoma Partners.
We recently needed to integrate QuickBooks Desktop with our Microsoft Dynamics CRM solution, specifically for invoices.
Since we have invoices that are generated from data that originates in CRM, our current process had us generate a report in CRM, then manually create the invoice in QuickBooks and CRM. We wanted to automate this process.
For the integration, we wanted to use Microsoft Integration Services and create an SSIS package that we can schedule to run on a nightly basis to create invoices in QuickBooks from data generated in CRM.
The first thing we needed to do was to choose a tool that would allow us to connect to, and access the data, stored in QuickBooks. We looked at many tools, but the one thing we found in common was that every tool required a proxy to be running on the QuickBooks server (if someone is aware of a way to interface with QuickBooks directly, without the use of a proxy, please feel to leave a comment in the comments section below).
Then the SSIS package communicated with QuickBooks via this proxy, so it wasn’t a direct connection from SSIS to QuickBooks. So if the proxy wasn’t running, a connection to QuickBooks couldn’t be established. Finally we chose to use the QuickBooks Desktop connector from CData as it seemed to meet all of our needs.
In the following example, we’ll give a brief demo of setting up an SSIS package to create an invoice in QuickBooks.
The first thing was to create a connection to the QuickBooks server (don’t forget the proxy application must be running on the QuickBooks server). The only required fields are the URL (of the QuickBooks server), user name, and password.
Then I setup a simple data flow task that queries invoice data from our CRM system and passes it to the CData QuickBooks destination component, which then will create the Invoice and Invoice detail records in QuickBooks.
When creating an invoice in QuickBooks there are a couple of things to note. First, there are some required fields that need to be passed in, and the invoice must have at least one detail record. At first this posed a problem for me, in that I was hoping to first create the invoice then add detail lines later. Then I discovered there is a field on invoice called ItemAggregate, which allows you to pass in one or more invoice detail records in an XML format, essentially creating the invoice and all detail records in one call. Below is an example of ItemAggregate data:
<Row><ItemName>Professional Fees - Consultant</ItemName><ItemDescription>Consultant</ItemDescription><ItemQuantity>210.7500</ItemQuantity><ItemRate>10.00</ItemRate><ItemAmount>2100.75</ItemAmount></Row>
<Row><ItemName>Professional Fees - Sr. Consultant</ItemName><ItemDescription>Sr. Consultant</ItemDescription><ItemQuantity>84.0000</ItemQuantity><ItemRate>15.00</ItemRate><ItemAmount>1230.00</ItemAmount></Row>
Once all the detail records and all required fields were passed in, the invoice was successfully created in QuickBooks. Please note that during the last step, I was logging all errors returned by QuickBooks into an error log table. This allowed me to do some trial and error runs of creating invoices that helped me determine which fields were required as those were returned as errors.
I hope this small introduction to integrating Dynamics CRM with QuickBooks can help kick start any similar projects you’ve been thinking about. Have a question about integrating QuickBooks with Microsoft Dynamics CRM? We're here to help.