FileBooks Link and QuickBooks = FM solutions

[DanBrill]

Hi everyone,

I keep an eye on the accounting section and see more questions than answers, and more frustration than fruition.

So I thought I'd throw in my experience with QuickBooks, FM, and FileBooks link.

Happy software produces FileBooks Link, a plug-in for FM that allows communication between FM and QB. Both Intuit (the makers of QuickBooks) and FM have put a good deal of thought into the use of xml. The 2 programs can communicate between each other in xml, and it seems to be a happy marriage.

The Happy Software folks have some example files, but the examples are designed to send one transaction in real time between FM and QB.

But my needs are different than what is assumed by the example files. I don't really want to execute transactions from FM, but to pull all of the data out of QB into a FM database for further analysis and manipulation. I had almost given up on the plug-in because it just wasn't doing what I needed it to do.

Fortunately, there is a little-discussed feature of FileBooks link that lets you send a simple command to QB, and return the results to a plain text file in xml format. You can then import the xml files into a FM db and you have the data you need.

The trouble is, you need xml translators known as xslt's. There is one burried in the FileBooks Link demo package, but the documentation is pretty thin. I knew only slightly more than nothing about xml when I started this, but dove in and hammered out what I needed. Based on the Happy Software example I created all of the xslt's that I need to bring the raw xml files into a FM database.

I'm attaching the xslt's that I've created. By using these in import script steps I can link to QuickBooks at the beginning of my FM session, extract all the data I need into my relational database. I'm thrilled with the outcome. I hope some of you will find these to be useful.

I'd love to continue the conversation about FileBooks Link, QB, and FM, and learn any tricks that you might know.

PS - note that some of these (most notably the xlst for Deposit data) will only work with QB Pro 2003.

Take care,

Dan

QB xslt files.zip

[Charles Delfs]

Dan

Is there another way to send an xml request to QB besides FileBooksLink, that is can I send some type of query to the developers interface or something?

[DanBrill]

Hi Charles,

If there is an easy way, I wish I knew it. I'd love to save the cost of FileBooks Link. The short answer is yes. The long answer is Yes, but....

You can do it if you can develop your own link in C+ or something similar. One would think that Intuit would make it easiser, but they haven't. Since I'm not a real programmer, I thought I'd save more by just paying for FBL and relying on the work already done.

If anyone else has ideas on how to get xml queries into QuickBooks from FM without FileBooks Link, I'd love to hear them.

Dan

[Charles Delfs]

I just found a command line version of the QBXMLRP request processor in the QK developers kits. you pass it a xml file for a request and it produces a file.

Using translators like yours the return data can be imported into FMP. (I think, I hav not tested it yet but will soon.)

The regular version of the QBXMLRP (quickbooks xml request processor) I thnk might be able to process simularly.

If the command version does not work out I will modify the source code for the request processor and make my own qb link. to work the same way. FileBooks link is to pricey for developers.

Charles

[DanBrill]

Hi Charles,

Any luck with the request processor from the developer's kit? I haven't gotten to look at it yet, but it seems that it should be able to do what I'm currently doing with FileBooks Link. I'd love to save the price of the plug-in, which is a bit steep.

I'm suspecting that it might be difficult to get all of the interactivity that you might get from FileBooks Link, but as I'm structuring my solution to pull out all the data into an xml file, I might be able to make a go of this.

Thanks for keeping us posted.

Dan

[DanBrill]

Hi Charles,

You've really gotten me thinking that I can get away with not using FileBooks Link at all. I've started poking around with the Intuit SDK and it seems that what is needed is there. I just need to digest it.

Looking at QBSDK2.0samplesqbdtvbqbxmlexpensereport there are examples there that should show the way. I'm more a database guy than a real programmer, so it may take me a while to figure out the code. But what I'd like to do is create a program that can take command line instructions so that the command would be

myprog.exe qbpath xmlrq xmlrsp

myprog.exe would send an xml request that is the file xmlrq to QuickBooks via the request processor. QuickBooks would send the xml response to a response file, xmlrsp. I could then import the xml response via an xslt as I have been doing.

Any thoughts?

Dan

[rgold52]

Dan ...

I have developed all the xslt's for 2.0 (2003) with the release r7 (2.1) there are a couple more for sales orders. They are tried and true.

I haven't designed for multiple data extensions, but that would take only a couple of minutes.

You can pick up a few free at http:/www.e-fmpro.com with the QBPro Schedule download.

Robert

[Lisu]

Dear Dan,

After reading what you have written I can say that romanian software is so different from yours, but I think our accounting is also different from yours.

I have never heard of QuickBooks and FileBooks but the software we are using at work containes all the databases we need. And above all we can create your database without being an expert because it is so simple designed.

[Lisu]

Dear Dan,

After reading what you have written I can say that romanian software is so different from yours, but I think our accounting is also different from yours.

I have never heard of QuickBooks and FileBooks but the software we are using at work containes all the databases we need. And above all we can create our database without being an expert because it is so simple designed.

[Lisu]

Dear Dan,

After reading what you have written I can say that romanian software is so different from yours, but I think our accounting is also different from yours.

I have never heard of QuickBooks and FileBooks but the software we are using at work containes all the databases we need. And above all we can create our databases without being an expert because it is so simple designed.