QuickBooks Note

The previous post (which is the “real” post) was an article/tutorial/note set that I wrote after doing a QuickBooks integration project. Hopefully, it will make life easier for some poor schnook out there.

QuickBooks Integration

The QuickBooks API (“the API” or “the QB API” hereafter) is anything but quick. The API itself has an unintuitive, somewhat convoluted structure. Worse is out there and, truth be told, it wouldn’t be all that big a deal except that the documentation is terrible to boot. What little exists is mostly a patchwork of poorly written and largely incomplete examples. The most primitive reference is bur‪ied. This article is a collection of my notes through QuickBooks divided into two sections: the basic structure and idea of the API and some hints for making the way through the documentation.
It is important to realize that the QB API is really a family of APIs that work off of a common infrastructure. You can do the same things with any of them but tasks will be easier/harder, longer/more concise, etc. depending on which version you use. The two I know of are qbXML and qbFC. qbXML is an XML schema for the requests that get sent to the server and the responses that get returned. On the other hand, qbFC is a hierarchy of classes that model qbXML, but allow you to build the requests programmatically with greater ease as you do not have to work through DOM. When all is said and done, qbFC is nothing more than a pretty face for qbXML. Once you run the requests, the XML is built automagically and the responses parsed out automagically into objects. Below, I will be discussing the qbFC API.
The main reason for this article is that I saw many QuickBooks example snippets sprinkled across the web and a handful of pages that Intuit generously refers to as documentation, but no real tutorials or explanation as to the how or why of things. While I cannot explain the why, I can, at least a little, shed some light on the how.

A Hitchiker’s Guide to the API

It all begins with a session. That is the QuickBooks motto. We begin by creating a session, then create a message set. In the QB API, a message is a generic term for interacting with QuickBooks. It can be a query for information, an a bill addition, an invoice addition, pretty much anything. After putting together a set of these messages into a message set, the session is told to run all messages after which it returns a response set. The user can then cycle through these, testing the type to determine what to do with it (i.e. is it a vendor result? information on an invoice that was added?). That is the executive summary. So, what does that mean in code? It means, first of all, that all interactions with QB follow pretty much the same template and that getting it all together is pretty much a matter of dotting your i’s and crossing your t’s.

A Stereotypical QB Session

Here is a simple example of this, written in C# using the QuickBooks object-oriented API, that adds a new vendor to QuickBooks:

  1. QBSessionManager sessionManager = new QBSessionManager();
  2. sessionManager.OpenConnection("appID", "Create Vendor");
  3. sessionManager.BeginSession(quickbooksFile, ENOpenMode.omDontCare);
  4. IMsgSetRequest messageSet = sessionManager.CreateMsgSetRequest("US", 7, 0);
  5.  
  6. IVendorAdd vendorAddRequest = messageSet.AppendVendorAddRq();
  7. vendorAddRequest.Name.SetValue("ACME, Inc");
  8. vendorAddRequest.CompanyName.SetValue("ACME, Inc");
  9. vendorAddRequest.VendorAddress.Addr1.SetValue("123 Some St");
  10. vendorAddRequest.VendorAddress.City.SetValue("Somecity");
  11. vendorAddRequest.VendorAddress.State.SetValue("ST");
  12. vendorAddRequest.VendorAddress.PostalCode.SetValue("12345");
  13.  
  14. IMsgSetResponse responseSet = sessionManager.DoRequests(messageSet);
  15. sessionManager.EndSession();
  16. sessionManager.CloseConnection();
  17.  
  18. for (int i = 0; i< responseSet.ResponseList.Count; i++)
  19. {
  20.     IResponse response = responseSet.ResponseList.GetAt(i);
  21.  
  22.     if (response.StatusCode > 0)
  23.          MessageBox.Show("There was a problem");
  24.  
  25. }

Initially, we set up our session manager, which could have multiple message sets. When opening a connection, you need to specify an application identification ID and name. This will be shown to the user in QuickBooks to allow/disallow the access. These are strings and, so far as I can tell, no checks are run, allowing the user to put anything here that they care to.

  1.     sessionManager.BeginSession(quickbooksFile, ENOpenMode.omDontCare);

This line actually “opens” the file. The first parameter is the location to the physical QuickBooks file. The second is the mode, whether the application should attempt to lock the file, or what. In this case, I use omDontCare which, as its name indicates, means the app doesn’t care what the mode is. In my experience, this has worked fine, though your app may have *special needs* (menacing tone).
The next thing we need to do, then, is create at least one request message set. This message set will have a series of requests associated with it. The type of the request will vary with the operating being performed. In this example, we want to add a new vendor, so we add a vendor request.
At this point, we have started a QuickBooks session, associated a message set, and created a blank vendor creation request. The next set of lines:

  1.     vendorAddRequest.Name.SetValue("ACME, Inc");
  2.     vendorAddRequest.CompanyName.SetValue("ACME, Inc");
  3.     vendorAddRequest.VendorAddress.Addr1.SetValue("123 Some St");
  4.     vendorAddRequest.VendorAddress.City.SetValue("Somecity");
  5.     vendorAddRequest.VendorAddress.State.SetValue("ST");
  6.     vendorAddRequest.VendorAddress.PostalCode.SetValue("12345);

populate the vendor addition request with the data we want added. In these lines, we set the value, the company name, and the address. A similar method will be used for each of the other requests, though it can take some trial and error to discover which element in the API corresponds with which clickable thingamajig in the GUI app. Once we have finished populating our request(s), we need to “run” them to have any effect:

  1.     IMsgSetResponse responseSet = sessionManager.DoRequests(messageSet);
  2.     sessionManager.EndSession();
  3.     sessionManager.CloseConnection();

Here, we fetch a response set by running all requests associated with the given session manager, then clean up after ourselves. Next, we need to make sure that everything ran all right:

  1.     for (int i = 0; i< responseSet.ResponseList.Count; i++)
  2.     {
  3.          IResponse response = responseSet.ResponseList.GetAt(i);
  4.          if (response.StatusCode > 0)
  5.               MessageBox.Show("There was a problem");
  6.     }

This code checks each item in the response list of the response set (can you say “redundant”?), then check the status code. QuickBooks uses a status code of 0 to indicate success. Basically, this code will show an error to the user if one of the response codes is incorrect.

Querying QB and Making Sense of the Result

So now we have seen a basic example in which we created something new in QuickBooks, but how do we query it? After all, we didn’t do anything with the data after we ran the vendor add request, not really. Well, as before, we will jump in with an example:

  1.     QBSessionManager sessionManager = new QBSessionManager();
  2.     sessionManager.OpenConnection("appID", "Create Vendor");
  3.     sessionManager.BeginSession(quickbooksFile, ENOpenMode.omDontCare);
  4.     IMsgSetRequest messageSet = sessionManager.CreateMsgSetRequest("US", 7, 0);
  5.     IVendorQuery vendorQuery = messageSet.AppendVendorQueryRq();
  6.     vendorQuery.ORVendorListQuery.VendorListFilter.ActiveStatus.SetValue(ENActiveStatus.asActiveOnly);
  7.  
  8.     try
  9.     {
  10.          IMsgSetResponse responseSet = sessionManager.DoRequests(messageSet);
  11.          sessionManager.EndSession();
  12.          sessionManager.CloseConnection();
  13.  
  14.         IResponse response;
  15.         ENResponseType responseType;
  16.  
  17.        for (int i = 0; i< responseSet.ResponseList.Count; i++)
  18.        {
  19.               response = responseSet.ResponseList.GetAt(i);
  20.  
  21.               if (response.Detail == null)
  22.                   continue;
  23.  
  24.               responseType = (ENResponseType)response.Type.GetValue();
  25.               if (responseType == ENResponseType.rtVendorQueryRs)
  26.               {
  27.                     IVendorRetList vendorList = (IVendorRetList)response.Detail;
  28.                     for (int vendorIndex = 0; vendorIndex < vendorList.Count; vendorIndex++)
  29.                     {
  30.                           IVendorRet vendor = (IVendorRet)vendorList.GetAt(vendorIndex);
  31.  
  32.                           if (vendor != null && vendor.CompanyName != null)
  33.                                  qbVendors.Add(vendor.CompanyName.GetValue());
  34.                     }
  35.                }
  36.         }
  37. }
  38. catch(System.Runtime.InteropServices.COMException comEx)
  39. {
  40.            // something bad happened; tell the user, smash his computer, whatever; its your choice
  41. }

This code, does the mirror opposite of the example in the previous section: this code queries the list of QuickBooks vendors, in this case viewing all active vendors. We will skip over the boilerplate to start a new session and such as it is more or less the same as above. The interesting part is what we do with the response set.

  1.     responseType = (ENResponseType)response.Type.GetValue();

With this line, we grab the response type to figure out what to do with any data it contains.

  1. if (responseType == ENResponseType.rtVendorQueryRs)
  2. {
  3.     IVendorRetList vendorList = (IVendorRetList)response.Detail;
  4.  
  5.     for (int vendorIndex = 0; vendorIndex < vendorList.Count; vendorIndex++)
  6.     {
  7.          IVendorRet vendor = (IVendorRet)vendorList.GetAt(vendorIndex);
  8.  
  9.          if (vendor != null && vendor.CompanyName != null)
  10.              qbVendors.Add(vendor.CompanyName.GetValue());
  11.    }
  12. }

If the type is the one we are looking for, we process the data. In this case, we iterate over the vendor list and add it to some collection.

Debugging

The QuickBooks API is one that aims to fail silently. This is not necessarily a bad thing, as you don’t want dozens and dozens of error messages popping up when they’re really not necessary. However, it makes life a great deal harder when something really is wrong, particularly when you are first working out a process and the steps or reasons for failure are not at all clear. The first and most obvious way to debug is to look at the messages returned with any exceptions that were thrown. For the most superficial errors this was sometimes helpful, though sometimes I got exceptions that contained a message of, literally, ‘1’. Mighty helpful, that. It turns out that QuickBooks has certain minimum requirements and constraints that must be met for some requests to succeed. The problem? These were not documented anywhere that I could find. At least, not directly. It turns out that these items will be listed, with at least some information, if you examine each response’s StatusCode and StatusMessage elements. The codes had a lookup on the main site that sometimes provided a little bit of aid. Short of going to a QuickBooks class, this is about all you can do.

Conclusion

The code will be different for each and every one of the valid requests that you can make against QuickBooks, but, on the other hand, it will be basically the same. The key is gaining “enlightenment” into QuickBooks’ API. The model stays the same and, while a little underdocumented, is consistently applied across the board. This feature alone makes it a little better than a number of schizophrenic APIs that I have had the pleasure of playing with. One of the big disadvantages of their API is that it is encapsulated in a single COM object that ships with a .NET wrapper. This is a disadvantage because we cannot use the library cross platform (even on a Mac, which QuickBooks ships with). Sometime, it would be interesting to find out whether QuickBooks and/or its API can run through Wine, allowing cross-platform work. But that is an experiment for another time.

Windows is the new UNIX

I recently finished reading the “Unix Hater’s Handbook” all the way through for the first time today. For those not in the know, it is an over-the-top, semi-serious, screed against the evils of UNIX in the 70’s through early 90’s. For the curious, a full copy can be (legally) found online at http://research.microsoft.com/~daniel/unix-haters.html
It is actually quite interesting to go back a ways for those of us who are of the newer generation of computer users. To put things in perspective, my first computer wasn’t a Tandy, it wasn’t an Apple II. I didn’t travel down to the local RadioShack and plop down a thousand dollars for a pile of components, a soldering kit, and a manual that I then used to build a primitive system. My family was actually late getting into the computer scene which wasn’t really surprising given that we were behind in every way technologically. In the late nineties, we boasted a large black and white television with VCR, broadcast television (no cable or, later, satelite). The first computer in the family was an outdated 386/486 given by a friend of dad’s. My start in computers was after the advent of the still-running AoW (Age of Windows; sounds like a good game title). I am what you may call a second generation Unixer (and LISPer, Vimmer, and …). My first Unix was Linux, Fedora Core to be specific. I took it up, originally so that I could claim familiarity with it on my resume, but swiftly fell in love with it. As my main software was already Linux-compatible (Firefox and OpenOffice for desktop apps, I did Java programming during the classes I was taking, and used the MingW ports of the GCC for my tinkerings in C++), the transition was painless. This, however, is drifting away from the original point. The point is that, as someone who is a Unix-guy in a post-Unix era and never lived during the Age of Unix, the time trip is always fascinating. It is a strange fascination, I know, but I love to read about defunct hardware and software thinking about that mythical day in the future when I say I wrote in Python and the answer is “Python? That’s ancient. No one’s seriously used Python for 10 years. Try Titanium. It is the coolest language ever”. It is like hearing the battle stories of an aged warrior. So, when listening to these “battle stories”, I found it most interesting first and foremost that my battles stories will, someday, sound just the same–except with the terms Unix and Windows interchanged.

Here are, in my opinion, the highlights of their complaints, the executive summary, if you will.

  1. Unix isn’t user-friendly
  2. Unix is poorly documented
  3. Mail is a mess (delivery fails, goes to the wrong place, etc)
  4. Usenet
  5. Unix is insecure
  6. Unix is bloated and slow
  7. Unix requires a lot of hand holding, feed and care to keep up and running. This is a nightmare for sysadmins
  8. The file system
  9. C++
  10. The shell, pipes, and “Power tools”
  11. Programming environment

Even after reading the book, in its entirety, and enjoying it every step of the way (so I am not flaming someone who said something I simply didn’t like), I still don’t understand why a couple of those items count. Usenet, while being run primarily by Unix boxes, is not Unix–you don’t need Unix at all to run it, as the authors themselves acknowledge, if I recall correctly.

Moreover, #3 is questionable as it refers to userland apps. Sure sendmail is awful (as a matter of fact, it is the only program that I have used where make had to be run after changes to the configuration files–a rather frightening notion in its own right), but that makes Unix awful? Word stinks, in my opinion. Does that make Windows trash?

Or take C++. That is a programming language that is anything but unique to the Unix world. Until the Java craze, it was the thing in the Windows world as much as the Unix one. Complaining about C++ and saying that this is a Unix problem is as ridiculous as complaining about Java and calling it a Windows one.

Back on topic, though, some of the remaining complaints are valid and others are purely a matter of taste. But what stands out about this list? Is it not what Mac and Linux advocates are complaining about in Windows? That the system is slow, buggy and insecure? That Windows servers require a lot of attention to keep running and relatively secure? Why, I do believe it is! Why would that be? Do we really learn so little from the systems that were built before ours? I think that part of the problem, though, lies in being the dominant system. Unix “back in the day” and Windows now were almost ubiquitous as the OSes used to run the computers of the day. As such, the OS sees a wide range of usage scenarios that, if left unaddressed, leave unhappy customers who leave the reservation. So, the company adds something here and something else there and people are happy–for now. Until the weight of such additions starts to weigh down the system and strains it to the breaking point.

Another common thread is that, in both cases, the technology became dominant before it was mature. Windows 3.11 wasn’t what you would call a mature product when it was pushed out, but if filled a void in the market and then used that position to make sure that others couldn’t do the same. Somewhat similarly, UNIX was let loose on the big wide world shortly after its creation (to play a game on a PDP machine, no less!). Both grew, but neither grew up. Unix (non-caps) has grown up a lot since Windows took over the world, but Windows, in a lot of ways, still hasn’t grown up. The base system still shows its single-user roots and “ending a task” means pleading with runaway software to die and go away. So, why did Unix mature after it was pushed out of the spotlight? Capitalism and market forces. It had to improve enough not to die. Likewise, Windows is “borrowing” many ideas from its competitors as they grow in market share. Vista is a sad OS X impersonator in terms of desktop gadgets and eye candy. The PowerShell is an attempt to create a bash/.NET hybrid. All in all, things are getting interesting, but I think the core element here is competition. A monopoly hurts, not only whoever else attempts to start a competitor, but also the consumer, depriving them of the state-of-the-art in exchange for the inertia of the monopoly in question.

Contrary to what many a fan boy would claim, the best thing for the industry is not an Apple takeover. Nor is it a Linux takeover. It is to have at least two, preferrably three, big competitors in a dead heat. Whether that is Mac/Windows/Linux or MINIX/Haiku/Sun doesn’t really matter. When this happens, you will see the following viscious circle:

  1. Company A creates some dandy feature X
  2. Company B copies it and extends it trying to EEE (embrace, extend, & extinguish) Company

Who is A and who is B will change and cycle in its own right, but neither will remain stationary. The customers wants will be heard because if A doesn’t listen, B will. If B won’t listen, C will. If none of them will, someone will start D up and supplant them all.
In conclusion, we need to remember two things:

  1. Competition is good
  2. “Those who don’t understand UNIX are condemned to reinvent it, poorly.” – Henry Spencer