Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Automate Tedious QuickBooks Tasks with COM-based API : Page 2

Learn how to programmatically control QuickBooks using the COM-based QuickBooks SDK 5.0. You'll be able to free yourself from those time-consuming, repetitive QuickBooks tasks.


Moving Many Bill Line Items from One Account to Another

The nice thing about using qbXML with the QuickBooks SDK is that the SDK comes with a tool for testing the XML for validity and also for submitting the XML to QuickBooks so it can process and retrieve the XML that QuickBooks returns. You can test a qbXML request without writing any code apart from the XML request message. Using its qbTest+ program, the QuickBooks SDK allows you to submit XML requests directly and receive XML responses directly without having to write code.

Unpack the example code file. Start up QuickBooks. Open "ABC Widgets LLC.QBW" in QuickBooks. If you use a version of QuickBooks later than 2004, you will be asked to upgrade the QuickBooks company file format. In that case, you should instruct QuickBooks to perform the upgrade. After opening the "ABC Widgets LLC.QBW" company file, start the qbTest+ program (located in the QuickBooks SDK start menu group under the Tools subgroup). Leave the "Company File" input blank. In the "Request File" input, click on Browse and specify all_bills_query.xml as the qbXML request document. Click on the "Open Connection" button. Click on the "Begin Session" button. The focus should return to QuickBooks.

QuickBooks alerts you that an unknown program is trying to access the data file. You must approve the access in order to continue. The permission-based access control method is how QuickBooks protects your data against unauthorized and possibly malicious programs. You should see a dialog asking whether to allow access to the company file by an application calling itself "SDKTest Plus 3." Click "Yes, always." QuickBooks will ask you to confirm because the external application is not signed with a digital certificate. In a production environment, such a warning should be heeded. Since we are in development, we expect to get a warning about a missing digital signature, so we will proceed regardless. Click "Yes." The focus will return to qbTest+. Click "Send XML to Request Processor," and qbTest+ will send the request to QuickBooks and then retrieve the response from QuickBooks. Click "View Output".

You should see the XML response created by QuickBooks. It contains information from the company file. qbTest+ will also save the response to a file called QBResponse.xml. The file should be in the same directory as the request XML file. Examine the request by opening all_bills_query.xml in your browser or text editor. Compare the request to the response. From the request and response, you should be able to deduce that the request asked QuickBooks for all bills, including the line items describing the expenses comprising the line items on all of the bills. You should see multiple expenses in the "Printing" account from "Big Apple Events," a fictitious company that puts on trade shows. Big Apple Events is one of the vendors that ABC Widgets uses.

In the sample QuickBooks company file for ABC Widgets, you will find expenses for a big trade show that ABC Widgets attended. Some of the expenses are categorized in the "Printing" account whereas others are categorized in the "Marketing" account. It is easy to understand how this might happen. Two or more bookkeepers may have different opinions about how things should be categorized. Even if the same person always does the books in QuickBooks, he or she may have different opinions at different times. I can think of numerous times when I have classified the same item in different QuickBooks accounts at various times. Ideally, all transactions involving item X would always be in the same account and classifications would be consistent. Even if you do not have this particular problem, you will likely run across situations where you need to make batch updates that QuickBooks does not handle efficiently via GUI, so this example should still prove useful.

Now that you have seen the request and response for a simple query by item and account, you can build the next piece of the solution. Before doing so, however, you should clean up by closing the qbXML Test+ application. Click "End Session," followed by "Close Connection," then "Exit." Now you will write some code that reads the XML response provided by QuickBooks and iterates through the bills and bill line items. With each bill line item that satisfies your criteria of being linked to the vendor "Big Apple Events" and the account "Printing", the code will generate a chunk of XML that requests that QuickBooks modify the bill line item so that it is instead linked to the account "Marketing." All of the trade show expenses will then be consistently classified into the right accounts.

The qbXML request to update a bill line item looks like so:

<?xml version="1.0"?> <?qbxml version="3.0"?> <QBXML> <QBXMLMsgsRq onError="continueOnError"> <BillModRq> <BillMod> <TxnID>1-1137620540</TxnID> <EditSequence>1137620540</EditSequence> <ExpenseLineMod> <TxnLineID>3-1137620540</TxnLineID> <AccountRef> <FullName>Bank Service Charges</FullName> </AccountRef> </ExpenseLineMod> </BillMod> </BillModRq> </QBXMLMsgsRq> </QBXML>

The edit sequence is used to confirm that you have the latest version of the record. Every time a change is made to an object, the edit sequence changes to a new number. When you run a query, QuickBooks sends back a response that includes edit sequences for all of the objects that can be modified. Your program should specify the same edit sequence when going back to QuickBooks and when asking QuickBooks to update a record. If QuickBooks detects that the edit sequence on the object you wish to modify is different from what you specified, it will know that your program made its request to modify the object based on possibly faulty information, so it will report a failure. Your program can then try to query the data again and perform the update if necessary, but using the new edit sequence.

Look at the code in update_bill_line_item_account.pl in your text editor. This code creates a qbXML request to retrieve all bills, including their bill line items. It then uses an XSLT stylesheet to iterate through the bills and their line items, searching for ones that are linked to "Big Apple Events" as the vendor and "Printing" as the account and generating a new XML document that requests QuickBooks to update the appropriate bill line items.

Three files comprise the small bill update application:

  • The update_bill_line_item_account.pl file.
  • The all_bills_query.xml file, which is the input to QuickBooks during the first pass, causing QuickBooks to return a list of all bills along with their associated line items.
  • The update_bill_line_item_account.xslt file, which is the XSLT stylesheet that generates the XML request for the update operations that cause QuickBooks to modify the bill line items so they are linked to the new account of "Marketing" rather than "Printing." If you are not familiar with XSLT, I recommend the tutorial and the reference at www.zvon.org.

Before running the update_bill_line_item_account.pl program, make sure QuickBooks is running and that it is open to the ABC Widgets company file. Once you have done that, run the update_bill_line_item_account.pl program (either by double-clicking or typing the filename at the command line). QuickBooks will pop up a dialog similar to the one that appeared when you ran qbTest+ the first time. Choose "Yes, always" to allow access to the update_bill_line_item_account.pl application (it calls itself "UpdateBillApp" in identifying itself to QuickBooks). After running the code, switch back to QuickBooks and look through the bills. You will see that all the bill line items that were previously associated with the trade show and the "Printing" account are now in the "Marketing" account. The number of changes this time around is small. With an older QuickBooks company file containing a thousand times the number of entries, automation would definitely be a real time saver.

The following is a line by line explanation of the important lines of the update_bill_line_item_account.pl program:

my $qbxmlrp_const = Win32::OLE::Const->Load("QBXMLRP2 1.0 Type Library");

Loads the QuickBooks constants used as parameters in calls to QuickBooks (The program uses the localQBD and qbFileOpenDoNotCare constants later in the code.)

my $request_processor = Win32::OLE->new("QBXMLRP2.RequestProcessor", sub {$_[0]->CloseConnection();}) or die "oops\n";

Fetches a reference to a qbXML request processor, the object that will handle your qbXML requests

$request_processor->OpenConnection2("", $name_reported_to_quickbooks, $qbxmlrp_const->{"localQBD"});

Asks the request processor to open a connection to QuickBooks

The first parameter is the appId, an identifier assigned by Intuit for use in its IDN Solutions Marketplace. The appId is only relevant if you develop software for QuickBooks that you intend to sell to other parties. Generally you will leave it as the empty string. The second parameter is the name displayed within QuickBooks when it asks the user to confirm that the client application should be permitted to access the data in the company file. The third parameter is the connection type. Possible options are localQBD, localQBDLaunchUI, remoteQBD, and remoteQBOE. The localQBD option tells the request processor to connect to QuickBooks running locally (i.e. on the same machine as the request processor and the client application). A value of localQBDLaunchUI tells the request processor to connect to a local QuickBooks and then request that the local copy of QuickBooks start up its user interface and prompt for the user to log in. A value of remoteQBD forces the request processor to open a connection to a remote instance of QuickBooks via RDS even when a local copy of QuickBooks is present. Normally the request processor tries first to connect to a local instance of QuickBooks. A value of remoteQBOE causes the request processor to connect to QuickBooks Online Edition. You must subscribe to the Online Edition in order to use this connection type.

my $ticket = $request_processor->BeginSession($qb_company_file, $qbxmlrp_const->{"qbFileOpenDoNotCare"});

Begins a new session

You must begin a session before issuing one or more qbXML requests. The first parameter specifies a company file. A blank value tells QuickBooks to use whatever company file is currently open at the time of the call to BeginSession. Once you are ready to deploy the application to a production environment, you should explicitly specify a company file. The company file will usually look like C:\Documents and Settings\wchao\My Documents\ABC Widgets.QBW. Some languages use the backslash as an escape character. In particular, Perl uses the backslash as an escape character. Thus, the company file just specified should actually appear as C:\\Documents and Settings\\wchao\\My Documents\\ABC Widgets.QBW in Perl code. The second parameter is the file mode. The possible values are qbFileOpenDoNotCare, qbFileOpenSingleUser, and qbFileOpen. The qbFileOpen option tells the request processor that the file must be opened in multiuser access mode. If that is not possible, the BeginSession call will fail. The return value is a ticket that must be passed to every subsequent call to the request processor. The ticket is used to identify the session and distinguish it from other simultaneous sessions.

my $response_xml_string = $request_processor->ProcessRequest($ticket, $request_xml_string);

Asks the request processor to act on the XML request in the $request_xml_string variable


Tells the request processor to end the session linked to the ticket in $ticket

You should get in the habit of ending sessions when you are done with them in order to free up resources. Even if you do not end your session explicitly, the request processor will end your session if your program shuts down cleanly. This is because you specified a destructor in the call to the new method of Win32::OLE. The destructor calls CloseConnection on the request processor object. If your program gets stuck before its sessions are closed, QuickBooks will refuse to close the company file until all sessions have ended. Explicitly ending the session minimizes the chance that QuickBooks will see a hung process as a lingering connection.

Most of the XSLT file (update_bill_line_item_account.xslt) should be obvious because it is passed through character for character to the resulting HTML, so I will cover only the important lines – the ones that implement our criteria of the vendor name matching "Big Apple Events" and the account matching "Printing."

<xsl:apply-templates select="QBXMLMsgsRs/BillQueryRs/BillRet[VendorRef/FullName = 'Big Apple Events']"/>

Selects only the bills that are linked to the vendor named "Big Apple Events"

<xsl:if test="count(ExpenseLineRet[AccountRef/FullName = 'Printing']) > 0">

Selects only the bills that have one or more expense lines linked to the account "Printing"

<xsl:apply-templates select="ExpenseLineRet[AccountRef/FullName = 'Printing']"/>

Selects only the expense lines that are linked to the account "Printing"

Free from Repetitive Tasks

Now that you have learned the basics of how to programmatically control QuickBooks, you will no longer be limited by time-consuming repetitive tasks. You should be able to do anything that can be described in code.

Wellie Chao has been active in the business of technology for many years, has been involved with software and hardware since 1984, and has been writing Web-based software in a variety of languages and on different platforms since 1994.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.