Intuit’s QuickBooks software organizes business information: contacts, vendors, orders, invoices, inventory, and more. Many small businesses use QuickBooks as their back-end accounting software, alongside FileMaker. Often these two applications perform separate functions, but sometimes there may be a need to integrate them. I’m going to briefly discuss one such method, using the FM Books Connector plugin (FMBC) from Productive Computing, Inc.

I’m not going to cover installing the plugin, as the files that are included with the plugin explain how, in detail. However, there are some items to keep in mind:

• FMBC runs only on Windows.
• You need to have QuickBooks, install the plugin into the directory FileMaker uses for plugins, and run a couple of installers that allow for XML communication via QuickBooks. Again, PCI has step by step instructions for this process, along with a sample database that will test each required piece.
• QuickBooks needs to be up and running when you make your queries, edits, and new record requests from FileMaker.
• Finally, the plugin needs to be registered, or it can run in demo mode for 30 days, though you will run into time-out issues while in demo mode.

On Screen Reference Manual

All requests from FileMaker to QuickBooks are made through function calls from FMBC. These function calls are contained in scripts, and at the very basic, contain steps to open the connection, send data, return a result, and close the connection. Exactly what takes place in the “send data” part depends on your action, and any data returned may include confirmation, content from QuickBooks, or an error message. FMBC comes with a handy developer’s guide, but this is only half the picture. The other half entails understanding Intuit’s On Screen Reference Manual and deciphering the XML tree.

Each action such as adding a customer, adding a sales order, editing a customer, etc., are grouped under Message Types.

When you select from the list of types, you can view all the requests, response, and XML tree. To initiate a request and retrieve a response you need to know the XML element names (i.e., field or column names) in QuickBooks. For example, adding a customer would require something like this:

Certain elements are required; others are optional, but they must appear in the same order in your script as in the XML tree. Otherwise, your result will contain an error message. Luckily, FMBC comes with a few sample scripts, and the developer’s guide contains some sample code. As with many plugins, sending the action can be accomplished by setting the value of a global field or a variable with the function call, such as:

Set Variable [ $Result ; Value: PCQB_RqNew( "CustomerAdd" ) ]

Which Message Type?

The key in writing any scripts to query QuickBooks via FMBC is first to outline which message type will be used, and then review the XML for fields in QuickBooks. From there, map the QuickBooks’ fields to fields in FileMaker, and use the appropriate function call. So, adding a customer, for example, requires the “CustomerAdd” message type and the Name. Salutation is an optional value that comes after the CompanyName. Additional fields and values are then added as needed.

Once you add the various fields you want in your new customer action, you need to deal with the result. You cannot assume the data will be added, so handling errors is critical. Successful calls via the plugin result in a zero, so checking for this value or !!ERROR!! text when sending the data will indicate whether something is wrong. If an error is detected, FMBC includes a handy function called PCQB_GetStatus that will show the error message. A few of the errors I’ve run into include values expected in the wrong order, the QuickBooks application being unavailable, and certain pre-conditions or values not in place.

Along with adding records, FMBC lets you add related records, query existing data, and edit data already in QuickBooks. Here are a couple of example “gotchas” to keep in mind.

• Adding sales orders requires that items already exist in QuickBooks’s Item Inventory table.
• Certain elements like Terms and Ship Via options will need to exist in QuickBooks when sending over sales order data, if you include these values. This isn’t always spelled out, and must be deciphered from error messages.
• You will want to make sure updates only are attempted from Windows. So if you run a cross-platform user environment, prevent the scripts from running on the Mac OS using the Get( SystemPlatform ) function.

Integrating QuickBooks with your FileMaker solution is quite feasible, but requires careful planning and testing. Once you set up the first few connections and learn how to interpret error messages the process begins to flow more smoothly. An alternative to running each script in debug mode is to create an error table and log any errors there as they occur. Then you can review the table and pin point where the script faltered. Integrating FileMaker and QuickBooks should hopeful reduce data entry (no longer in both places), and allow users a friendly front end method to create and update data, and also to see the data you already have in QuickBooks.