Quickbooks Integration Module teaser

[AbleMods]

Thought I'd show off a few snapshots of an AC7 add-on I've been working hard on lately. I'll be ready to start selling it by the end of May 2008. Feel free to offer any suggestions - I'll do my best to get them included. So far, the code is strong and works very well. When it's done it should support Quickbooks all the way back to QB 2003. We'll see how that turns out when I'm done.

Right now it generates invoices from orders, applies AC7 payments to the proper QB order, auto-creates all vendors, tax-codes, inventory items, shipping methods and payment methods. It even lets you exclude specific order status codes so you're not transferring unnecessary orders. The final (optional) steps will be to auto-create the appropriate Purchase Order and Purchases (shipments) for items bought by my customers. Since everything I sell is drop-shipped, might as well build the purchase order for the distributor automatically too. That part will be finished and tested by this weekend.

When everything is complete, there should be almost nothing that requires entry in both AC7 and Quickbooks. Finally I'll be able to generate true profit/loss and sales reports without paying a bookkeeper to re-key everything.

[calvis]

Joe, it looks nice thus far. Is there a particular reason why you went with invoices instead of sales receipts?

[AbleMods]

From an accounting perspective, sales receipts are a cash-sale or point-of-sale transaction. In other words, actual funds transfer as a result of the transaction. In a pure Authorize & Capture scenario, sales receipts would be the correct route.

Invoicing works much the same as sales receipts, however payment information is not required at the time the invoice is created. This scenario is more appropriate for merchants like me who do not capture funds at the time of the order. It also broadens the market support for my module to include merchants who accept purchase orders or offer Net terms to their online customers.

Since both order and payment information are transferred from AC7 to QB, the Invoice route opened more doors.

[calvis]

I have always used sales receipts because we always had stock and we got full payment. I suspect other online companies do the same. The reason why we don't use invoices is because we have to apply the payments which is more effort than just doing sales receipts.

However, if you would automate the payments then it becomes an non-issue. I would be interested in hear how payments are going to be applied. Also, will you allow the vendor to add stuff on invoice such as we like to add paid with last digits of the card, or maybe a custom note on the invoice?

Also how are variants and kits going to be handled on the invoice?

[AbleMods]

QB will auto-apply payments to outstanding invoices beginning with the oldest unpaid invoice.

This is just a transfer of information. If there is a field in AC7, then I can migrate that data to QB as long as there's a place to store it in QB. I have not changed any existing AC7 functionality, I want to keep compatibility as high as possible.

Variants are handled just as AC7 handles them. Once a variant is ordered, it's just another line item on an order. A matching inventory item is auto-created in QB. Kits are the same way I believe but I'll double-check. I don't use kits in my store so I may not support kits in the first version.

[compunerdy]

I prefer to use receipts as well. If it is setup to just transfer the kit parent item that would work as you can just setup the kits in QB.

[AbleMods]

When AC7 stores a kit to the ac_OrderItems table, it doesn't explode the kit into it's individual components as line items?

[compunerdy]

I guess it does but thats sort of the problem for me. Let me give an example kit..

Pencil $5 (parent item)
- Lead $0
- Eraser $0
- Wood $0

We have to set the price via the parent item because if you do it via the sub items then as you know the parent item displays a price of $0 which is not good. The problem is for me and I would imagine others is that the parent item is not really a item but just a holder. So on the transfer to QB it would only make sense if only the sub items OR only the parent item was transferred over and not both. And if it was just the sub items then what would it use for the price of each sub item since I have to set those at $0?

I tried to get an answer on how this would work months ago but nobody seemed to know. But, since you decided to tackle the problem..have fun While I would prefer to be able to just use whatever they include with AC7 and not pay more for another module I am sure your system will be more in-line with what I would want to use.

[AbleMods]

I'll set up a few test orders and kits to see what it does.

So far, I haven't come across anything specific in the data classes that makes it super-obvious that kits require special handling. That's not to say it doesn't exist, I just don't use them (yet).

I'm doing final testing today, everything works quite well. QB is doing something weird on line items when the quantity ordered is greater than 1, but I think that's my mis-interpretation of the API and not QB. Aside from that minor issue, everything else seems ready for my live data testing.

Purchase Orders being auto-created is great for me. I can now easily see how much money committed to "in process" orders with my distributors...for orders that haven't shipped yet. This is all going to be such a huge help with my cash flow projections. I had to shut down all my paid marketing because I didn't have enough info from AC7 reports to clearly know my current cash situation. But hey, it's a storefront package and not an accounting package so that's ok.

[Road Rider]

BAM!!!

[AbleMods]

Heheh

Got the quantity issue handled - apparently a data layout having both "Quantity" and "Amount" fields was supposed to tell me the amount is the Extended Amount i.e. Quantity X Unit Price. No clue why Intuit got creative on that design, but it's fixed and works properly now. All possible data is now dumping to QB and initial testing is done.

Now to spend a few weeks dumping daily orders and fine-tuning the processes. Then it's ready for PRIME TIME BABY!

[heinscott]

Hey Joe...
What's your status on this project? Do you know how much you are going to be selling it for?
Would you be willing to trade for a Purchase Order / Recieving Report / Inventory Management mod??
Just kidding about the trade, but, it looks really nice. I'd rather purchase something that has been tested extensively, rather than writing something myself, delaying deployment even further.
Let me know, so I can inform my boss that a quality solution exists.
Thanks,

Scott

[AbleMods]

Thank you for the interest. Production testing has been delayed due to my part-time bookkeeper having obligations this week and last. Hopefully she'll have that wrapped up today and can resume work next week. I need about 2-4 weeks of real use to work out any kinks from the time she can resume work.

Right now it's about 90% solid. Orders come through flawlessly along with payments matching up quite nicely. I made some tweaks to automatic purchase order generation so that the user can generate a Bill or Purchase Order depending on their business model. Product records, shipping methods and tax codes are all automatically created in QB and come through quite nicely. My tax model is simple though so there may be some issues with tax codes I haven't encountered yet. Inventory values are untested since I do not use them.

What I am seeing is some issues with specific refunds and adjustments I've made in the early days of my website. My procedures today are more effective and consistent than they were 6 months ago, so I think those issues will disappear as we reconcile closer to present-day data. Maybe. Sorta.

I will make the changes this weekend to pull everything by the stored maxid value. That way it's consistent across the board from a programming standpoint and easier to grasp from a user standpoint.

[JonW]

I'm patiently watching the progress here.

I currently use QBMS for our QB transactions and Chase PaymenTech for our AC 5.5 transactions. Since AC 7.0 supports neither of these merchant processors, I may be inclined to look at your solutions and just use the QB merchant services for both.

Any thoughts on pricing yet?

[AbleMods]

I currently use QBMS for our QB transactions and Chase PaymenTech for our AC 5.5 transactions. Since AC 7.0 supports neither of these merchant processors, I may be inclined to look at your solutions and just use the QB merchant services for both.

Any thoughts on pricing yet?

I appreciate your interest. Keep in mind that my routine will not actually process transactions. It's only designed to take the data from AC7 and create appropriate matching transactions in QB.

My bookkeeper and I met to resolve the balancing problems we were seeing this week. We've discovered some issues that need resolved. I believe I know the solution. I just need to find the time amongst my other projects and commitments to get the coding done. The lack of industry-standard invoicing procedures from one of my distributors isn't helping much

I should be cranking hard from now through this weekend and be back to testing revisions by Sunday.

I haven't decided a price yet. My confidence in the first release will decide if a reduced price would be appropriate to work out any final issues.

[AbleMods]

All revisions tested with a few new features added. We'll do some more testing this week - if no major oopsies come up, I'll probably be ready for a beta release next Friday.

Features added:

1. Admin can choose whether a Quickbooks PO is created based on the customer order and automatically associated to the assigned vendor for the products ordered.

2. Admin can choose whether a Quickbooks Bill is created based on the customer order and automatically associated to the assigned vendor for the products ordered.

3. Each payment method can be assigned to a specific cash account for payment posting. Cash accounts are automatically created if they don't exist. This greatly facilitates transaction register reconciliation for each of your payment gateways like Authorize.Net, PayPal etc. Even supports multiple payment methods posting to the same cash account i.e. Visa/MC go to the ANet cash account but Google Checkout goes to it's own cash account.

4. Continuation from previous export has been redesigned to use the ID of the AC7 records. Previously I was tracking the DateTime values of the last transactions dumped so the program would know where to continue from. But some AC7 records lack an Add Date field, so now the PaymentId or OrderId value is used.

5. Support for a "Unknown Vendor" when creating POs and Bills. If an AC7 product record referenced on a order is deleted after the order is taken, the Vendor name is no longer available. Same if the AC7 Vendor is deleted. Now the system properly handles this by allowing the user to specify what default Vendor these POs or Bills should be assigned.

6. Orders with multiple shipments are now handled.

Since I don't know what final shipping costs might be on any given bill from my distributor, I will probably add a checkbox so the user can disable creating shipping charge line items on PO's and bills. That will make it a little easier to match up Quickbooks POs/Bills with distributor invoices using Product Total instead of Order Total.
It's not elegant, but not much else I can do about it at this point. AC7 really needs a COGS for shipping - every order doesn't get billed exactly what the freight was calculated at, especially if the shipper decides the destination was residential versus commerical etc.

[compunerdy]

How is the item matching handled? The T-hub software I used to use would stop and ask if it found items that did not exactly match in QB and then you could link the item from one to the other and it would remember that when it ran into that same item again.

[AbleMods]

Item matching is based on SKU. If an item being moved to QB doesn't exist in QB, it is automatically created.

The concept of asking for similar matches when not found would be possible, however it would prove detrimental on anything but the smallest of stores. Stopping the whole process for unknown products on a 20,000 item catalog would be very inefficient.

Also, this is a one-way street. There is no connectivity from QB back into AC7, so my routine will have no way of knowing if other similar items exist.

[compunerdy]

Not that big of a issue I guess. Just need to make sure my sku's match. Would be happy to test how kits work for you when it comes time Joe.

[AbleMods]

Not that big of a issue I guess. Just need to make sure my sku's match. Would be happy to test how kits work for you when it comes time Joe.

Actually you don't need to create any inventory items in QB. The routine automatically creates any non-existent product records as each order is transferred. It transfers everything in AC7 that has an equivalent field in QB.
Same thing for payment methods, shipping methods, tax codes, customer records, general ledger cash accounts, vendors etc - almost everything specified in the interface setup screen that doesn't exist in QB gets automatically created. I've tried really hard to make it so there are very few pre-existing entries required in QB for the first successful transfer.

I'll study kits again this weekend, but I think we're ok because kits are exploded into individual items once they're stored to the ac_OrderItems table. If not, I'll take you up on your offer to help me test kit transfers.

[compunerdy]

My issue is I already have 2+ years of info in quickbooks so having it just create new items etc wouldnt be the best route since I might have 2 of the same item then etc.. So like I said I would need to do some house cleaning.

Here is why I see kits will be a problem and have tried to discuss it a bit with Logan.

With the current setup kits need to be setup so that they work this way (atleast for me)

Parent item (not an actual item..just used as kit header)
- sub item
- sub item
etc..

Here are some of my reasons for this..

1. With the price bug we have to set all sub items at 0 price so we can set the parent item to have the total price or it will show as $0 on grid pages.

2. If you use a actual item as the header item then it cant be used in other locations or sold by itself.

3. If you use a actual item as the header item then you can't specify how it is listed on pull sheets etc..
So if you label it as "acme lunchbox kit" then it will show as that on all invoices etc even though it has now been dumped out of kit form and made to look like any other item.


So anyways.. I dont see any way that it was not designed to work this way. The problem is they where not finished on the admin side. You cant set them up to work this way on the customer side and then just dump all the kit items as individual items when you get to the admin side.

When you just do a normal dump to quickbooks you will have.

1. A bunch of items that arent actually items.
2. A bunch of items with $0 for sale price.

I am sure there are some issues I have missed. This is why I kept asking how this would work months ago.

If anyone can point out some flaws in my conclusions please do so. Maybe I am just looking at them wrong.

[AbleMods]

When you just do a normal dump to quickbooks you will have.

1. A bunch of items that arent actually items.
2. A bunch of items with $0 for sale price.

The real issue is not how it handles kits, but rather how the kit price is initially displayed in catalog views. Reinventing the (kit) wheel to solve a display issue seems like the wrong direction to take.

So Able still hasn't fixed kits to show at least some sort of price instead of "0"? I had the same issue but with variants - I just modified the code to show the first variant cost and the problem was solved.

Perhaps your kits issue would be just as simple....

[jmestep]

The price of kit items is still not stored in the order items table- the main item there shows the total price of all items.

[compunerdy]

Fixing the price issue is only one of the problems..What about these?

2. If you use a actual item as the header item then it cant be used in other locations or sold by itself.

3. If you use a actual item as the header item then you can't specify how it is listed on pull sheets etc..
So if you label it as "acme lunchbox kit" then it will show as that on all invoices etc even though it has now been dumped out of kit form and made to look like any other item.

Unless those are fixed you cant fix this problem

1. A bunch of items that arent actually items.


JUDY - your sig image is asking people to log into your site to access it.

JOE - sorry for somewhat derailing your thread but these issues are related for me atleast.

[jmestep]

Sig image- it was gone when I just checked it, then it came back. Maybe my site went to sleep- it's DNN low traffic.
You might want to post a bug on using the item as a header in a kit- I think that was something new in RC3 so that a customer could have a non-selection so maybe it isn't supposed to be a product, just something like "Select".
(But I haven't re-read the whole thread here, so I could be off base)