Quickbooks Integration Module teaser

[AbleMods]

I'll play with some kits tonight - I wasn't aware there were more issues than the price. Maybe I can come up with some quick fixes to some of them for you. If I can package several changes into one, I might put it up as a low-cost module if Able isn't planning on make similar changes soon. I don't want to overlap efforts or step on any toes, but I also see the importance to you.

[compunerdy]

Maybe I am just nuts.. Maybe I am the only one using kits?? I dunno..but in my mind there are issues that need to be addressed.

If you need any clarification or if I can get you a pepsi or something just let me know Joe.

[KCarlson]

I am really interested in this module also. Where do I signup? Does AC7 not offer exports natively anymore? I was just working on the orders screen and was surprised to see that was gone.

[Brian Mueller]

Hello... i am shoping for a e site with a strong affiliate program and kitting features. i am looking for some one to help me get it going that knows how to make it work with QB i use pro. looks like your new mod might be it! Brian

[Robbie@FireFold]

Does AC7 not offer exports natively anymore?

I would like to know this answer. I have just a demo product now.

[jmestep]

Able has a dataport utility that they are still improving,but I don't know what functions in it are ready to go or how customizable they are. It does show export for UPS,Quickbooks, other formats.
viewtopic.php?f=42&t=5707

[AbleMods]

Just a quick update...

Testing is progressing much better now. I just finished working out a sales tax issue that cropped up recently. I will start deployment testing on a clean install to be certain what files are required so they can be packaged up into a zip file.

The new cash account mapping feature is nice - different payment methods map quite nicely to separate cash accounts in Quickbooks. One issue I'm having is due to my business model - everything is drop-shipped and the shipping charge stored in AC7 is not what my distributor will charge me. That makes matching up the automatically-generated QB bills with the actual distributor invoices more challenging. My bookkeeper continues to share her incredible joy at having to pull each one up to verify before paying it

Unfortunately there's not a lot of ways around this, aside from simply not transferring any shipping charge at all from AC7 to QB. At least that way she could quickly match up product totals on the distributor invoices to the QB bill total and add her own shipping line item. I'll add an option to the configuration page to turn this on or off so it covers both bases. Those of you who know the final shipping charge in advance will have it much easier...lucky you

Kits seem to work fine. In the line items table, they're just exploded into individual line items and transfer over to QB like any other order. While dollar amounts may not post exactly as you want, the general idea of a kit is maintained. Specific item numbers are sold and dollars are posted. Your site kit setup may or may not work with my first version - given the variety of setup I've heard about, it will probably take a little more work to get it fine-tuned. I certainly believe it's possible, I just lack the experience with kit configuration to code for it now.

On a personal note, I had absolutely no idea how much work this was going to take the day I said "You know, I could just write my own"...I have a much greater appreciation for Katie @ Able doing all the bug-check testing these many months

[AbleMods]

Ok, quick update:

Sales tax issue resolved. Sort of. AC7 stores sales tax as a separate line item. Quickbooks does not. So I'm not transferring actual calculated sales tax, but instead the sales tax codes themselves. This should (emphasis on the should) allow Quickbooks to compute the same sales tax amount that AC7 did and thus balance orders against payments. More testing during beta will bring out the uglies in this part of the routine I'm sure, but for now it works well enough for me and my business. Your mileage may vary on the first release.

I've got the configuration screen completely done. I've got the export screen completely done. Now it's just a matter of testing deployment to a clean site/clean QB install. Once that's done, I can package the necessary files, whip up some quick documentation and publish the digital good.

Sorry for all the delays - too many irons in the fire at one time is all I can say

[AbleMods]

Progress is going very well this week.

Deployment details are shaping up quite nicely. There will be only two .Net pages that go into your AC7 website. These will handle all the configuration and export functionality.

Then there is the communications utility that will take the export XML file and send the commands to your local install of Quickbooks.

Overall module flow will be as follows:

1. Set configuration parameters in AC7 (one time)
2. initiate export and save the XML file to your local drive
3. Launch Quickbooks on your local PC
4. Load the comm utility and give it the downloaded XML file
5. Rinse and repeat as you desire. The routine will automatically know where to pick up from the last export so no data is missed.

I will work on automating this more as the product develops. For now, this will work well for beta release. Once I finish the comm utility installer setup and testing (today), I will start some sparse documentation - final docs will come after beta testing so they are complete.

So as far as "Installation Requirements" go, you really just need to have Quickbooks loaded on your PC and a company file built. There are only a few prerequisites from an accounting perspective that are needed in a company file...I tried my best to auto-create as much as possible to simplify things.

Troubleshooting will be fairly easy - the export creates an XML input file that can be zipped up and sent to me for analysis. My comm utility also generates an output XML file that details all the Quickbooks responses to each command. This can also be sent for analysis.

The beta testing will help alot with version testing - I want to know exactly what flavors of QB I can support. Everything in the SDK documentation says I will support Quickbooks 2003 and up, but since I don't have those 20 billion flavors of QB available I cannot test them all

[egormsen]

A couple of questions about the module:

1) How do you specify what customer information will be passed from AC7 to QB?

2) I have some custom fields (access via the User.Settings functions) - can those be passed to QB?

3) We currently are using QB and are manually entering data in - what happens first time the integration is run? Do you specificy a start date?

4) On Configure QuickBooks Integration Screenshots you posted, there are default GL accounts for posting items and invoices in AC7. Just to be clear, if I want to have 3 different receivable accounts that I post to currently, I can still maintain those in QB. This screen only is for the items/customers not current set up in QB?

5) How are inventory adjustments handled? i.e lets say we purchase more items of a particular product we sell -if we put the adjusted inventory amount in AC7 does that get passed to QB?

6) Do you need beta testers? We are currently rolling out AC7 (not yet live) and using quickbooks Premier (3 User edition).

Thanks,

Eldon

[AbleMods]

Thank you for your questions.

1) How do you specify what customer information will be passed from AC7 to QB?

AC7 has very little customer information as compared to Quickbooks. All fields that exist in both systems are transferred - basically the address information. I built in an option that allows the user to decide how new customers ID's are assigned in QB when new customers are auto-created. Currently the choices are "LastName, FirstName", "FirstName LastName", "Billing Email Address", "Billing Telephone Number" and "AC7 UserID"

2) I have some custom fields (access via the User.Settings functions) - can those be passed to QB?

Yes as long as the flavor of QB you use supports custom fields for customers. This would require custom modification to my programming but it does not appear difficult.

3) We currently are using QB and are manually entering data in - what happens first time the integration is run? Do you specificy a start date?

Originally I designed it to work by date. But that created additional complexity from a user standpoint. Now it works by PaymentID and OrderID values. At the end of each run, it records the last ID values exported and uses those as the starting point for the next run. To perform an initial full dump, the user will simply set each value to "0". This guarantees any new physical records added to the tables, regardless of any datetime manipulation of the tables will get captured. Modifying existing records will not be captured. It's a new-records-only deal. Any manual edits to AC7 records after they are exported will also have to be done in Quickbooks. From an accounting perspective, you shouldn't be manually editing records anyways - you should always create offset records so a full audit trail exists.
Given the complexity of accounting transitions, it will be up to each user to decide how best to integrate an existing company data file with exported data from their storefront.

4) On Configure QuickBooks Integration Screenshots you posted, there are default GL accounts for posting items and invoices in AC7. Just to be clear, if I want to have 3 different receivable accounts that I post to currently, I can still maintain those in QB. This screen only is for the items/customers not current set up in QB?

That is correct - the configure screen has alot of new functionality since that snapshot. You can maintain anything you want in QB, but my routines will only export to a single AR account. Modification to this would be possible - the effort involved would depend on the complexity of the request.
All I'm doing from a data perspective is telling QB to add it if it doesn't already exist. My routine has no way to know of any existing data and therefore there is no risk of it overwriting existing QB records.

5) How are inventory adjustments handled? i.e lets say we purchase more items of a particular product we sell -if we put the adjusted inventory amount in AC7 does that get passed to QB?

Inventory stock values are passed into QB the first time that particular item is imported and created in QB. After that, no further stock updates are made from AC7 to QB since the item now exists in QB. This functionality is planned for a future release depending on how feasible it is from a functionality standpoint. Stock values can vary wildly in the course of a day and AC7 doesn't keep a good audit trail history of those stock changes.
My routines are a one-way street. There are ways to accomplish a full two-way data sync between AC7 and QB. However the complexity of both the programming and setup are beyond the scope of this module. I can do a two-way now on my development PC, but it requires a local install of the SQL DB and current copy of my store data. Given the complexity of this setup is beyond several AC7 customers, I opted for the one-way street for my first release. Getting server-side website programming code to talk to a local Windows app is easy - getting Windows XP to allow it to happen is a whole different enchilada

6) Do you need beta testers? We are currently rolling out AC7 (not yet live) and using quickbooks Premier (3 User edition).

Thank you for the offer to test. I will be rolling out the first release within the next few days. Anyone purchasing early will get it at a reduced price and be considered a beta tester. Once I'm comfortable with issue resolution (probably 2-4 weeks), the module will go final and the price will go up. Since I can't figure out how to compile .Net code into a DLL, your purchase will include full source code if I don't get my head wrapped around it - there's a free copy in it for someone who can help me get that accomplished

[Robbie@FireFold]

Looking forward to the final of this. It will help my move to Able be even better.

[AbleMods]

I’m very close to having a fully functional QuickBooks web service module for AC7. The first version will be a one-way street, but the technology certainly exists to make it a full two-way sync - that'll come with version 2. It’s pretty slick how Intuit set all of this up. The hard part has been designing the data classes so they can be properly serialized into the XML format required by QB.

The way QB has built this from a user standpoint is rather elegant. All the user will have to do is download and install the free QuickBooks Web Connector from Intuit and double-click a config file provided by me. The config file automatically sets up the connection parameters between the local QuickBooks and the remote AC7 site. Since all of this uses .Net webservice technology, there are no issues to worry about with firewalls and the transmissions are all automatically encrypted using SSL.

p.s. I hate XML

[jmestep]

Ditto:

p.s. I hate XML

It wasn't too bad once using SQL Server forxml, but if you can't work with the db directly, it gets nasty for those of us who don't work with it every day.
My hat's off to you for getting this far with your module. You will probably make about $1.00 an hour based on the learning curve, but when you do the next things, you'll fly thru them.

[egormsen]

I have a question on the integration module from our accountant, she wants to know the following:

How does the interface work with Classes on transactions?

Thanks,

Eldon

[AbleMods]

There is no ability to directly class a transaction in AC7, therefore no functionality exists for it in the web service. I'm not fluent in the use of the class field in Quickbooks. If there's a way to use it that benefits everyone, I'm happy to consider it.

It wouldn't hard to support it - I just don't know what I'd populate it with from the AC7 tables.

[AbleMods]

Ok great progress this week....

I hit some major walls deploying the web service to my live AC7 installation and getting it to work with HTTPS. Long story short, I'm sleep-deprived but I got all the issues resolved.

I've been putting together alot of logging and a few features to turn debug logging on/off. Tracking down issues with SOAP communications thru HTTPS is a nightmare in .Net, so extensive logging is a necessity. The first version will spam the daylights out of your AC7 Error Log. The next version will use the log4net functionality included with AC7 to have far more subtle (and circular) logging. For now, we'll have to suffer through it if we have to turn logging on for some reason.

Error Trapping was sorely needed, so I've incorporated a ton of it today. Subtle inconsistencies in the data appear to throw QuickBooks for a tizzy. Odd extended ASCII characters like the like ® symbol in some of the UPS shipping method names blows it all up. Same thing for certain international alphabetic characters in customer names/addresses. Lovely

I finally wrapped my head around the whole send/receive process. Now the routine creates many one-command messages instead of one giant message with many commands. The reason is huge - if any one message fails, QuickBooks fails the entire message. So breaking up everything into single one-command messages means my web service is no longer an all-or-nothing situation. I've got some logging in place to help identify what failed.

I've laid a little foundation for a 2-way street down the road. Right now, my brain hurts from the one-way street, but this strengthens my plans for a two-way street in a later version.

Installation will require two changes to your web.config:
1. My code is VB and AC7 is in C-Sharp. To make both flavors work in the same ~/App_Code folder, you have to make sub-folders and change your web.config to indicate the folder names.
2. .Net 2.0 defaults for SOAP HTTP traffic is that POST/GET support is turned off. This must be turned on via web.config as it's required by the Quickbooks Web Connector.

Right now everything is pulling just fine from my live site except Orders. Being well-stocked on Pepsi and Marlboro means I should have this finished this evening. The last step will be wrapping everything necessary into a zip file and drawing up some draft documentation.

For once in my life, I look forward to typing instructions

[AbleMods]

My in-house live testing is complete.

Some new features:

1. Basic two-way street implemented. My routines will pull General Ledger Accounts, Vendors and Items from your QuickBooks so it can populate the dropdowns on the configuration page. This will greatly reduce initial setup errors. This also lays some foundation work for a more robust two-way street later on.

2. Figured out how to compile classes into DLL files Compiled DLLs eliminate several installation steps. Now installation is as simple as two DLL files in your /Bin folder, two ASPX pages and a quick change to your web.config file. If you want the config page as a menu option on the admin side, that's an easy change and will be documented.

3. The Intuit Web Connector uses a config file to tell it how to talk to your web service. I have completely automated the construction of this config file - you only need to set the login username (populated from your site users table) and click "Install Connector". The connection is automatically installed into Web Connector and ready to talk to your site.

4. Error checking out the wazoo - I'm sure there'll be more little issues with unique data in your store that doesn't exist in my store. We'll cross those bridges as they come, but for now it does my entire store.

5. Debug mode is now selectable - it spams the AC7 Error Log heavily so it badly needed an on/off switch.

6. There will be two flavors available for sale - one compiled and one with full source code. All source is in VB.

I'm now working on documentation. First version will be basic - I'll polish it up as I move through the beta release phase.

This has been a really long road

[compunerdy]

Thanks for all the hard work Joe.. I for one will be glad to get this stack of orders off my desk =)

[calvis]

Hi Joe,

I look forward to seeing the fruits of your labor. As stated in a previous post sales receipts have been a way of life for us since day one, and I am not sure how using invoices would affect our current accounting methods. But in all fairness, I will examine your module with due justice and diligently examine if it will be useful for our current business processes.

Be prepared for lots of questions.

[AbleMods]

I already have a configuration option to choose whether to build a PO, a Bill or neither. I could add a toggle so you could choose whether to build a Sales Order or an Invoice if you'd like.

If I poo-poo'd it in a previous post, I apologize. My knowledge base has grown significantly since I started whole project. Now that I know how to build the data classes and serialize them into XML, the foundation is built to add more stuff like that. As long as it doesn't require some sort of existing-record test before it's sent to QB...this is still a one-way street at this point.

What about Payments versus Sales Receipts? I recall someone mentioning that previously as well.....currently I'm building payments in QB.

Documentation is coming along nicely. It's rough, but it'll do the job for now. It's 1:15am and I need some shut-eye. If I can get the docs done in the morning, I might be able to put the beta up for sale in the afternoon.

Thanks alot for everyones encouragement - you've really helped give me a strong sense of purpose during this project

[calvis]

Actually I was referring to Sales Receipts. I corrected my previous post.

If you can implement sales receipts I believe that it would have greater appeal for those that are already doing business that way. Invoices might be acceptable, but I will have to examine your module more thoroughly and discuss it with our accountant.

[compunerdy]

I do them as sales receipts also.

[AbleMods]

Ok, I've studied sales receipts.

Here's the catch: Sales Receipts require a deposit account. In other words, you must have already captured the funds in AC7 to post a sales receipt in QB. Otherwise you're recording a cash transaction before it actually occurs. Is that how you're operating?

Generating invoices gets me around this - orders can be dumped at any time. When the payment is eventually captured, it automatically gets posted to the same customer and applied to the outstanding invoice. This gives you the opportunity to see what "outstanding" cash you have to collect in open orders (known as Accounts Receivable) via QuickBooks.

The only gotcha with my invoices route is when an open order is cancelled before funds are captured. Cancelling an order in AC7 doesn't cancel it in QuickBooks - you'll have to do that manually until I get a two-way street going. Sales Receipts wouldn't have this problem because you aren't generating anything in QuickBooks until the cash transaction actually occurs i.e. funds are captured.

Thoughts?

[compunerdy]

For me I dont use quickbooks to handle accounts. I only use it to record sales and figure out profit, etc.. for figuring out tax's. If you post the orders as receipts instead of invoices it keeps quickbooks from telling you all of your accounts need to be balanced etc..