Quickbooks Integration Module teaser

[AbleMods]

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..

How do you balance your business cash accounts at the end of each month?

[AbleMods]

Product page is ready, see this post: viewtopic.php?f=42&t=7486

[calvis]

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?

We maintain our own inventory and we keep everything in stock 99.9 percent of the time. Because of that we can capture the card and ship out quickly. At the end of the day we have to make a deposit which includes where all the money goes such as Paypal orders would would be deposited into the Paypal bank account. Visa and Master Card would be deposited in the appropriate checking account, and so on. It's very rare that we have any cancelled orders.

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.

Since we capture the card and ship out the current or next business day we never have any Account Receivables.

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.

With Sales Receipts you don't have that 'gotcha' because you already have the money at the onset of a Quickbooks transaction.

[AbleMods]

Very good information, thank you so much Calvis. I've got you covered on the cash accounts - you can easily assign specific AC7 payment methods to post to specific cash accounts in QB.

The vast majority of my business exposure has been with an invoice-now-and-receive-payment-later scenario. In my IT consulting, I invoice my clients and they (hopefully) send a check later. With my online store, I authorize a charge now and capture the funds at a later date because I am a drop-shipper.

So really in your case, you've combined an invoice and a payment into a single transaction known as a sales receipt. Is that understanding correct?

Shouldn't be all that hard to program - I've already got the data classes built. The bigger question is how to integrate it into my existing flow of information. It's almost as if I should focus solely on AC7 Payments for the sales receipt integration since you must have a payment in order to generate one.

I'll add another option to my config screen: Invoice/Payment or Sales Receipt generation. The only risk is things could get squirrelly if someone decides to change from one to the other at a later date.

[AbleMods]

Ok sales receipts will have to wait for the next version release. For now my present design of invoices/payments will have to suffice.

I'm running into a design issue that could delay my release and I need to get this beta going out the door.

Here's the wall I'm hitting:
Sales Receipts are nice - they're an invoice and a payment all in one entry. The problem is AC7 doesn't work that way. In AC7, you can have orders without payments. Whether your particular business model works this way or not isn't at issue. What's at issue is the fact it's still possible from an AC7 data perspective. My whole design is based from the AC7 perspective meaning it's quite possible to have an order without any payment records associated with it.
QuickBooks won't accept a sales receipt in such a way, so I'm faced with rewriting my entire processing routine to work from an AC7 payments perspective instead of an AC7 orders perspective.

In order to support Sales Receipts properly in my module, I would need to completely rewrite my processing routine. It would have to be based solely on AC7 payment records and ignore orders altogether. That's not an easy task while maintaining the ability to do it via Invoices/Payments.

But where there's a will, there's a way. There's got to be a way to allow the option to do either Invoice/Payments or Sales Receipts. I'll start working on a design plan this week and do some testing this weekend.

[AbleMods]

Alrightly, super tremendous progress this week after a total meltdown and subsequent panic attack.

To be completely honest, I blame Sales Receipts. Seriously. It got me thinking alot about how my module was written and why I chose NOT to go that route. I decided to go back to square-one because I couldn't entirely answer the question of "Why not use sales receipts?".

Now I can for certain: Sales Receipts will work, but only if you only ever, and I mean ever, receive one single payment per order. AC7 supports multiple payments, and refunds, and partial captures etc. So because of how AC7 payment processing is designed, I have to use Invoices/Payments to maintain the integrity of the data going from AC7 into QB.

So now that I have answered the question, what's the solution?

First, a completely new error-handling routine was built. I noticed my debugging was incredibly inefficient...always sorting through hundreds of hard-to-read log entries. Redesigning the error-handling routines has helped tremendously and should have been the FIRST stupid program I wrote.

Second, I completely redesigned the processing loops. The module will now only process orders that have been Paid and Shipped. Because of the way AC7 creates a record and updates that same record later, this is the only way to maintain data integrity without making your daily QuickBooks transmit session a 5-hour process.
Here's an example: payments are recorded separately in ac_Payments. However a payment that is authorized today and is captured tomorrow is still only one single payment record. If you dump the authorized version today and run another dump tomorrow, my old design would never catch the capture because it's not a new record in the table. By limiting order exports to those that are Paid/Shipped, this problem is completely eliminated because there will be no more activity on that order. It also lets me leverage the Exported flag already built into the ac_Orders data class.

Third, efficiency. I had to write an even more advanced two-way street to improve message efficiency. The module now pulls all the major components from QuickBooks back into AC7. Components like customers, vendors, inventory items etc. Now I can see if I even need to bother building a customer request or item request because I know if it already exists in your QuickBooks company file.
Every request to QB is a separate message. Well if you count up all the orders, all the line items, all the payments, all the products, payment methods, shipping methods etc the message list can get huge. I have the oldest AC7 store db in existence - nobody else went live before me. So dumping my entire store to QB took far too long and I'm not even a big store.
Now all of my routines eliminate duplication where ever possible - total message count is down about 60% from what it used to be. As more and more entries in AC7 get put into Quickbooks, efficiency goes up. This will make daily updates absolutely fly after the first few weeks of use.

Fourth, because my data is old, there are ALOT of anomolies like deleted payment methods, deleted shipping methods, missing vendors etc. With the improved error-handling, these anomolies are handled gracefully.

I have some minor duplication issues to resolve tomorrow (lol today) morning. Then I will test again using my full live store. Yeesh, it's 2:25am

[AbleMods]

OK campers, we're looking really good this morning. All classes have been compiled into release DLL versions and uploaded to my live site. I'm doing a full dump of my entire store (thousands of orders, payments etc) as a final test before uploading the digital good and unlocking the product record. Wish me luck

I made massive progress yesterday. The new error-checking makes development and testing so much faster. Here are the new features:

1. Discounts are now handled. Sweet!
2. Coupons are now handled. Double-sweet!!
3. Sales tax now handled.
4. Removal of unnecessary configuration settings.

Some interesting notes:

1. Refunds will not be supported in this version. You'll have to post them twice - once in AC7 and once in your QuickBooks. Refunds are so strangely handled by the QB SDK that I'll probably have to write a completely separate web service just to handle them. Why they designed the refund aspects the way they did is completely beyond me

2. if you have any orders that were paid with a payment method that no longer exists, you'll see duplicate payments posted to QuickBooks. The Able data class methods get a little funky when that's the case and the payments aren't pulling in exactly as I would expect. Not to worry though, they're easy to spot in QB because the customer will have a credit balance. It's not my fault, I swear

[AbleMods]

Product is ready for purchase. Really. I mean it this time

[AbleMods]

HA!

Got Sales Receipts fully implemented

You can now choose whether to generate Invoices/Payments in AC7 or just make a Sales Receipt for each order.

I have a pretty strong accounting background and the concept of Sales Receipts was a little foreign to me. So I decided to bone up on the subject and see how it's implemented by QuickBooks. The result was one of those "Ohhhhhhhhhh" moments

New update being released this weekend - I'll contact everyone via email to arrange for the update files to be delivered.

Many thanks to everyone for their great feedback!