QXchange Integration with QuickBooks® Online
Overview
QXchange is a powerful data integration tool to access, integrate and manage large volumes of data from various systems. Extraction, transformation and loading of data are powered by QXchange with source and target database support.
QXchange helps organizations to bond together all enterprise data and ensures accuracy of data in a timely manner. QXchange is a simple stand-alone application that can be used by novice users at ease.
QXchange is flexible, easy to use, data integration software. It can quickly and easily transfer data between many standard data formats and applications, including MS Access, Excel, Comma delimited flat file, MS SQL Server, QuickBooks, Salesforce.com and others. It allows simple and clear setup of extraction, transformation and loading instructions to exchange data between different formats. The instructions are stored and managed as profiles.
QuickBooks® Online is a cloud based financial management software. Its designed to slash the time you spend managing your business finances, by helping you with tasks such as: Creating estimates and invoices. You can use it to invoice customers, pay bills, generate reports for planning, tax filing, and more.
Technology
Intuit® supplies the QuickBooks® Online REST API (Application Programming Interface) to allow third party products to work with QuickBooks® Online. The API makes sure that the integration works well with all the editions of QuickBooks® Online. The API also ensures that the third-party product is properly authenticated prior to getting access to the QuickBooks® Online data. QXchange is strictly following the Intuit® API standardsto communicate with QuickBooks® Online to pull/push the data.
Authentication and Authorization
QuickBooks Online API supports the OAuth 2.0 protocol. Intuit has documented the steps for accessing the QuickBooks Online API. QXchange is following the guidelines of Intuit QuickBooks Online API and pull/push the data. Every user has to authenticate and authorize QXchange to access their QuickBooks Online company. Below are the steps involved for the authorization process.
1.User will open QXchange application
2.User needs to choose QuickBooks plugin and open the connection form.
3.Choose QuickBooks Online from the list.
Below is the screenshot for reference;
To get access to data of a QuickBooks company, a QBO user must authorize QXchange through an authorization flow. QuickBooks Online API will provide an Access Token at the end of the process which is used to make QBO API requests from QXchange. To initiate the authorization flow, user need to click on “Connect QuickBooks Online” button in the connection form. This will open up the browser and show the QuickBooks connection page (this is known as QXchange QuickBooks Thin Client) which is part of QXchange – QBO authorization process.
Here is the screenshot;
Click on “Connect to QuickBooks” button to proceed with authorization. It will ask for QuickBooks Online credentials and show the list of QuickBooks Online companies associated with the account.
Once provided the credentials need to click on “Sign In” button. This will authenticate the user credentials and navigate to companies list page as shown below.
Choose the company that you want to pull/push the data. To cancel the authorization process, click on “No, thanks” button which will redirect to home page. Clicking on “Next” button will get the confirmation on establishing the connection with QuickBooks Online company.
Once authorization is success, QXchange will show the “Disconnect” button as shown in the below screenshot.
Clicking on “Disconnect” button will disconnect QXchange from the authorized apps of QuickBooks Online. Once this is connected, QXchange will show the connected QuickBooks company and “Disconnect QuickBooks Online” button.
Data Access Plug-in
QXchange integration engine uses Data Access Plug-ins (DAPs) for individual application and data format integration. All the DAPs are called using a standard Application Program Interface (API). The QuickBooks® Online API is wrapped inside one of these DAPs. This allows for a smooth and standard operation across all the various DAPs.
Architecture
Communication between QXchange and QuickBooks® Online is based on a client/server model. All communication messages are in a special form XML called QBXML as required by the SDK. QXchange sends a message request to QuickBooks® Online and QuickBooks® Online sends back a response message; the communication managed synchronously.
Supported objects in QXchange.
QuickBooks Online API is exposing various objects available in QuickBooks Online and it is supported in QXchange. Here are they;
| Lists | Objects |
| Account List | Accounts |
| Entity Lists | Customer, Customer Job, Vendor |
| Item Lists | Item Inventory, Item Non-inventory, Item Inventory Assembly (Premier Edition and above), Item Service, Item Discount, Item Other Charge |
| Simple Lists | Company, Department, Exchange Rate, Class, TaxCode, Transfer, Tax Agency |
| Payroll Lists | Employee, Time Activity |
Transaction objects correspond to the basic accounting entities in a business. Adding a transaction object (except a non-posting transaction object) affects the account balance of the business. The following types of transactions are supported:
| Transactions | Objects |
| Accounts Receivable Transactions | Invoice, Credit Memo, Receive Payment |
|
Accounts Payable Transactions |
Vendor Credit, Bill, Bill Payment Check |
| Non-Posting Transactions | Purchase Order, Estimate, Sales Order (US Premier Edition and above) |
| Bank Transactions and Sales Receipts | Deposit, Sales Receipt, Refund Receipt |
| Other Transactions | Journal Entry, Budget |
| Reports | Transaction List, ProfitandLoss Report, ProfitandLoss Detail Report, InventoryValuation Report, GeneralLedger Report |
Using QXchange
The QXchange engine works the same for all different DAPs. Every data transfer is set up as a profile, with a source and target data file, which can be of different data formats. Data transfer between specific objects and the mapping of source to target fields are also set up in the profile. The profile can be executed directly or can be scheduled to be executed later. The process can be run on a local desktop or on a server.
Source Data
QuickBooks® Online can be defined as source data and the various objects can be accessed. Field level data from the objects can be extracted and mapped to destination objects and fields in different data formats. Data from multiple objects can be extracted. Multiple profiles can be set up to extract data from various objects.
Source data can also be filtered using various expressions. Prior to executing extract, the source data can be viewed to make sure the right records are getting selected.
Target Data
QuickBooks® Online can be defined as target data and the various objects can be updated. Data can be updated to multiple objects. In some cases, update may have to affect multiple data objects at the same time, for example: Invoice Header and Line.
Data can be updated in various modes: Insert, Update and Upsert. Insert expects the record being updated to be new and unique. Update expects the record to already exist. Upsert, tries to insert first and if that fails tries to update.
Scenarios
The QXchange ability to integrate QuickBooks® Online allows many uses. Certain limitations in QuickBooks® Online reporting can be exceeded with this tool. Fields such as “Paid Date” on invoice are normally not available to reporting tools. Fields such as internal keys and last modified date/time are also not available directly. Using QXchange, you can access to those fields. This allows many different scenarios:
- Extract paid invoices in the last month into Excel
- Extract customer information with company names starting with “A”
- Make a copy of all available QuickBooks® Online data in an Access Databases, for use in reporting
- Insert spreadsheet invoice data sent from branch offices into QuickBooks® Online
- Extract Opportunities from Salesforce.com and create Customer Jobs and Sales orders in QuickBooks® Online.
- Extract Accounts from Salesforce.com and create Customers in QuickBooks® Online.
- Extract Invoices from QuickBooks® Online and update opportunities in Salesforce.com
- Get orders entered from an online e-commerce system and load them into QuickBooks® Online as Sales Orders and Invoices.Get customer data entered online and create Customers in QuickBooks® Online.
Get vendor data entered online and create Vendors in QuickBooks® Online.
Conclusion
QXchange does a tremendous job, understanding the intricacies of the QuickBooks® Online data structure as well as the complications inherent in the data integration process. With this ability and its ease of use, it allows users to make more effective use of their QuickBooks® Online Data.
