Integrating QuickBooks Desktop With Rails Application

January 12, 2017
admin

QuickBooks Desktop is a software of managing accounting related stuff, which is more powerful and easy to handle for accounting person or non-accounting person. In this article I have presented how to integrate QuickBooks desktop with a rails application. The purpose of integrating QuickBooks desktop with rails application is to send accounting related data over QuickBooks desktop from our rails application.

Install QBWC (QuickBooks Web Connector) application

If we want our rails application talk to QuickBooks desktop then we need an extra software installed in the system where QuickBooks desktop is already installed (Obviously windows machine).

https://developer.intuit.com/docs/0200_quickbooks_desktop/0100_essentials/quickbooks_web_connector  from this site you can download the appropriate QBWC file for your QuickBooks desktop based on your version. After downloading that software, just install it, and you can open QBWC application from your QuickBooks desktop application menu, File > Update Web Services. Now we need to add another application to this QBWC application by clicking on the menu, File > Add Application. Actually we need to add a communicator file which will handle sending requests to rails server and get responses. This file will be downloaded from our rails application.

Install QBWC Gem to Rails App
$ gem install qbwc
Or you can add to your Gemfile
gem ‘qbwc’
Then run
$ Bundle install

N.B. QBWC gem needs rails version >= 4.1, o/w it will not work.

Configuring QBWC settings for the application

Run the generator

$ rails g qbwc:install

Then the migrations

$ rake db:migrate

Generator will create some files and migration will create some tables into your rails application. Among these config/initializers/qbwc.rb file needs to be configured according to your requirement.
I am gonna show you the basic integration, that’s why assume that QuickBooks desktop will be used by single user/account and single company file for QuickBooks desktop. If we want multiple users and multiple company files then the above config file needs to be configured properly. You can checkout the page https://github.com/qbwc/qbwc to know more about multi user and multi company configuration.

Quickbooks requires HTTPS connections when connecting to remote machines. ngrok may be useful to fulfill this requirement.

Generate QWC file

Previously we said that we need to add a communicator file which will be added to the QBWC application to communicate to our rails application. To get this file we need to add a link into our application page which is ‘application_domain/qbwc/qwc’. This path is already entered into the routes.rb file when we execute command ‘$ rails g qbwc:install’ . Now after downloading the file we need to add that file to the QBWC application.

Now our integration is ready to exchange data between QuickBooks desktop and our rails application.

Creating Worker File

We need to create specific worker file for specific task. Like, if we want to export/import chart of account, then we need to create two specific worker file, one for importing and one for exporting. Also we need to create separate worker file for updating the QuickBooks list/transaction data. I am gonna show you very basic operation for importing and exporting of COA (chart of account).
To perform this action we need to create two worker files into rails app under application_root/app/workers directory.

$ cd application_root/app
$ mkdir workers
$ touch import_coa_worker.rb
$ touch export_coa_worker.rb

Modify import_coa_wroker.rb File

require 'qbwc'

class ImportCoaWorker < QBWC::Worker
 def requests(_job, _session, _data)
   { account_query_rq: {} }
 end

 def should_run?(job, session, _data)
   user = User.find_by_email(session.user).present?
 end

 def handle_response(response, session, job, _request, _data)
   user = User.find_by_email session.user
   account = user.account
   accounts = response['account_ret'].present? ? response['account_ret'] : []
   accounts.each do |account|
     QbCoa.find_or_create_by(
     account_id: user.account.id,
     qb_list_id: account['list_id'],
     edit_sequence: account['edit_sequence'],
     name: account['name'],
     full_name: account['full_name'],
     account_type: account['account_type'],
     is_active: account['is_active'],,
   )
   QBWC.delete_job(job)
 end

end
Understanding A Worker File

Every worker file extends QBWC::Worker class and has 3 primary methods.

  1. requests: process the request data, and returns hash or array of hashes which QuickBooks desktop will process
  2. should_run? : returns a boolean whether the jobs should run. Job can be stopped based on some conditions here.
  3. handle_response: returns the processed data from QuickBooks desktop
Creating Jobs

Now our worker file is ready to import COA from QuickBooks. But before that we need to create jobs.
Jobs will store data into the qbwc_jobs table as an active job. When we click on update button from QBWC application then the QBWC application will act like a client and our application will act like a server. So QBWC will send request for exchanging data over QuickBooks. Now qbwc_jobs table will hold the data which will be sent over QuickBooks, but we need some codes to store the job request.
Somewhere in the code or console we can add a job like the following

QBWC.add_job("import_coa", true, '', ImportCoaWorker)

Here, first parameter ‘import_coa’ is a unique name for the job, second parameter is for the job is enabled/disabled, third parameter is for the company file name and the last one is the worker class for handling the process.

Now above import functionality doesn’t have any data to be sent over QuickBooks. It’s just a query request that will be responded with all the COA currently listed on the QuickBooks. But if we want to export account then we need to send data/ COA information (as sixth parameter) which will be stored into the QuickBooks.
Our export worker will be like the following.

Modify the export_coa_worker.rb File

require 'qbwc'

class ExportCoaWorker < QBWC::Worker
 def requests(_job, _session, data_array)
   { account_add_rq: build_account_add_hash(data_array) }
 end

 def should_run?(job, session, _data)
   user = User.find_by_email(session.user).present?
 end

 def handle_response(response, session, job, request, _data)
   user = User.find_by_email session.user
   account = user.account
   if response.is_a? Array
     accounts = response
   else
     accounts = [response]
   end
   accounts.each do |account|
     qb_coa = QbCoa.find_or_create_by(
       account_id: user.account.id,
       qb_list_id: account['list_id'],
       edit_sequence: account['edit_sequence'],
       name: account['name'],
       full_name: account['full_name'],
       account_type: account['account_type']
    )
   end
   QBWC.delete_job(job)
 end

 def build_account_add_hash(data_array)
   temp_array = []
   data_array.each do |data|
      temp_array << {
      account_add: {
      name: data[:name],
      is_active: data[:is_active],
      account_type: data[:account_type],
     }
    }
   end
   temp_array
 end
end

Now aggregate the data for COA and add them to the job list.

coas = [{name: ‘Bank Account’, is_active: true, account_type: ‘BANK’},
        {name: ‘Income Account’, is_active: true, account_type: ‘INCOME’},
        {name: ‘Accounts Payable’, is_active: true, account_type: ‘AP’},
        {name: ‘Accounts Receivable’, is_active: true, account_type: ‘AR’}]

QBWC.add_job("export_coa", true, '', ExportCoaWorker, nil, coas)

This is how we can pass the data/information to the worker. However, if you want to update the list/txn data over QuickBooks then you will need to provide two things must, which are:
1. txn_id and
2. edit_sequence
txn_id and edit_sequence need to be stored into your database as they will need later when you will send update request for that list/txn data.

So, in this way data can be imported and exported to QuickBooks desktop.You can also import/export transaction data over QuickBooks.
Coa, Item List, Customer/Vendor these are list data for QuickBooks, and
Invoice, Bill, Payment are called transaction (txn) data for QuickBooks.

Intuit Onscreen Reference for Data Format

So, someone can have a question in his mind that what should be the data format for all the list / txn request, or where can I get those? Okay, there is an onscreen reference for data format built by QuickBooks developer INTUIT, where you can see all the data formats for list/txn requests.

https://developer-static.intuit.com/qbSDK-current/Common/newOSR/index.html

Use the qbxml format to see requests and responses to be used in your jobs.

So, this is how we can integrate QuickBooks desktop with our rails application. I have shown a very basic data exchange between an application and QuickBooks, but QuickBooks and server need to be synced properly, which means, you need to map all the list items and transaction through your application, so that, you can easily grab the exact data for future modification.

Contributor: Nahid Ebna Hasan, Nascenia

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.