Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Dan,

Open an order in osC and tell me what it says under "Payment Method" for PayPal orders, in all the languages you have in your store.

Credit card orders should say Credit Card.

 

Then I can do what you request.

 

Thanks,

Adam

 

You would think this would be easy :) Unfortunately, the payment method in paypal orders is obscured by the use of a paypal logo, not text.

 

Given that, I'll give the options.... In the orders database, the payment method is listed as 'PayPal IPN' (as opposed to 'Credit Card'). In the iif file, it is listed as 'PayPal'. I'm not sure which of these is meaningful in this case.

 

Also, in my case, order status should be 1, 2, or 3 (Pending, Processing or Shipped). Other order statuses, such as 99999 PayPal Processing, do not make for valid orders, and probably should not be exported.

 

I only have english, so I don't have other translations.

 

For the order export, it would be very handy to have an option in the config menu that lists all of the order statuses, and an option to check of the order statuses that you want to have exported. This can be a few versions out, but something to keep in mind.

 

Just to clarify why order status is important in paypal.... The way the module works (and probably others, like 2CO) is it captures the order and inserts it into the order table, before the payment is made, to allow the customer to go offsite (to Paypal) to complete the transaction. If they don't complete the payment method, the order is still in the system, but in a defined state, such as PayPal processing, so you know it hasn't been paid for yet. Once the IPN is received, the order status is updated, to pending, and then the order is made valid (and the customer emails go out, etc).

 

Thanks!

Dan

Dan Stevens

Link to comment
Share on other sites

Hi I'm looking at integrating an existing OSC site with quickbooks, this contribution looks great a few questions:

 

I keep seeing QBI and iif mentioned here, maybe a daft question but what is QBI and iif?

 

Is there a new version coming out soon, should I wait for that to be released?

 

If i understand correctly, if this contribution is set up correctly and osc and quickbooks are properly set up, then this will allow OSC to handle all front end ordering and Quickbooks will do all the backend invoicing, stock control, etc.

 

If i update stock in Quickbooks will the stock in OSC get updated too? If I add/Delete products in quickbooks will OSC get updated also?

 

Credit card processing is done via LinkPoint does quickbooks intergrate with this? or does osc pass all payment info to quickbooks?

 

Sorry for all the questions, I'm basically trying to figure out if Quickbooks will become a OneStop Admin area to handle everything regarding stock/invoicing/payments etc for the store?

 

 

QBI is the module (probably stands for QuickBooks Import), and iif is an Intuit format for importing into quickbooks (Intuit Import Format).

 

The iif format does not support importing credit card info for processing in QuickBooks, but if your using your own gateway, then you can import the payment info. All payment info????? - Can you be specific on what your looking to have imported, and if your doing credit card processing through a gateway?

 

OneStop Admin? Short answer is no. Changes in Quickbooks will not be reflected back to OSC, and changes to things like inventory (besides an order) in OSC will not be reflected in Quickbooks. Also, order statuses are not updated beyond the one time update that is possible when you do the export of the iif.

 

What this module does is takes your orders from osc, along with the customer info, and export it into a file that can be imported into quickbooks for record keeping purposes. If your processing several or more orders a day, this can be a HUGE time saver. Even with fewer orders, this helps with the duplicate typing of bringing in customer info, or inaccuracies caused by pricing changes due to things like specials.

 

If you use quickbooks, it's worth using this contribution. If you haven't installed it yet, and use a lot of coupons, I'd probably wait for the next version. The current version works well, so you can go ahead and use it, but it does not yet support coupons.

 

Dan

Dan Stevens

Link to comment
Share on other sites

I can't see how everyone is getting this program to work but me!! I'm still getting balance errors. I'll just have to wait until a more complete user guide comes out. Adam, I was wondering if you could update the guide sometime and put very clear explanations of where to find all of the accounts, names, classes, etc, for every field in the configuration. And also explain if the fields can be left blank, etc. I think that would really help me.

 

I did manage to get some invoices to import (even with errors), but the sales receipt option doesn't work at all for me.

Link to comment
Share on other sites

Thanks for the reply djs

 

QBI is the module (probably stands for QuickBooks Import), and iif is an Intuit format for importing into quickbooks (Intuit Import Format). 

 

The iif format does not support importing credit card info for processing in QuickBooks, but if your using your own gateway, then you can import the payment info.  All payment info????? - Can you be specific on what your looking to have imported, and if your doing credit card processing through a gateway?

- The client uses LinkPoint to process creditcard information, If QB links to that does it have a Customer Login function so they can update their creditcard info if needed? Does QB have a facility to do Monthly Batches to update LinkPoint for recurring billing?

OneStop Admin?  Short answer is no.  Changes in Quickbooks will not be reflected back to OSC, and changes to things like inventory (besides an order) in OSC will not be reflected in Quickbooks.  Also, order statuses are not updated beyond the one time update that is possible when you do the export of the iif.

So if a new product was added for example i would have to alter QB and OSC manually?

What this module does is takes your orders from osc, along with the customer info, and export it into a file that can be imported into quickbooks for record keeping purposes.  If your processing several or more orders a day, this can be a HUGE time saver.  Even with fewer orders, this helps with the duplicate typing of bringing in customer info, or inaccuracies caused by pricing changes due to things like specials. 

 

If you use quickbooks, it's worth using this contribution.  If you haven't installed it yet, and use a lot of coupons, I'd probably wait for the next version.  The current version works well, so you can go ahead and use it, but it does not yet support coupons.

When is the next version due for release?

Link to comment
Share on other sites

Ok, update. My invoices are being imported, but with balance errors. I have a tax account called "Out of State" and it isn't set to any tax% at all, it is just left blank. The only problem is, if you leave the tax% field blank in the QBI config, it just puts a 0 in the IIF file. I don't know if that might be cuasing a problem.

 

Also, the "VIA" pulldown menu on the invoice says "Method not set", but it should say FedEx. I matched up the shipping using the match shipping in QBI. On my store, we only offer FedEx. So if someone chooses anything else besides free shipping (they have to spend over $25 to get free shipping) then it should say FedEx. unfortunately, it just says "method not set".

 

Also, I would like the Shipping Description to say "Shipping and Handling"

 

And finally, when I check the "import as sales receipt" option, and then I look at the output in Excel, the IIF file still is creating invoices and putting them in the Accounts Receivable account. I'm confused as to why the "sales receipt" option isnt working.

 

Wish me luck, and thanks for any help!

Link to comment
Share on other sites

You are using "Utilities" "Create iif file from all osC products?"

 

I just tested it with my store and it is working properly, exporting all products (with or without attributes) and their descriptions.

 

Open the iif file in Excel and see if the other products are missing from the iif, or just don't show up in Quickbooks. This will help in troubleshooting.

 

Also, which version of QBI did you download (it will say 1.00 in the config screen for both versions 1.00 and 1.01 -- this has been corrected)? And what version of QB are you using?

 

Has anyone else had this problem?

 

Thanks,

Adam

 

Hi Again

 

Thanks for your answer. I downloaded the version 1.01 , I opened the iff file with excel and I see that there are all products there.

Maybe I am configuring something worng?

 

I read this in the manual:

 

preparing Quickbooks Using the Product Import Utility

If you have never used Quickbooks with your on-line store, and you wish to import all your store items into Quickbooks, click the ?Create iif file from all osC products? button in the QBI Utilities to make an iif file that you can import into Quickbooks. This will work properly with osC products that have attributes, but will not work for osC products that you wish to match to QB item groups. Unless you have a large number of items that are not already in Quickbooks, and you will not wish to change the names or use item groups, this method is not recommended. It is preferable to add your items directly in Quickbooks, and then match them to the items in osC using the ?Match osC to Quickbooks? menu above. Note that the item import, if you choose to do it, only needs to be done one time.

 

I am a little lost here. Thanks for the help

Link to comment
Share on other sites

Hi Again

 

Thanks for your answer. I downloaded the version 1.01 , I opened the iff file with excel and I see that there are all products there.

Maybe I am configuring something worng?

If all the products are in the iif, but they don't all show up when you import the iif into QB, then there is a problem somewhere. If you can send me the iif to the email listed in the manual, then I can analyze it for you and see where the problem is.

I am a little lost here. Thanks for the help

I have improved the manual to cover all the options, and will be adding diagrams to it to make all this much more clear! It will be ready soon!

 

Thanks,

Adam

Link to comment
Share on other sites

Ok, update.  My invoices are being imported, but with balance errors.  I have a tax account called "Out of State" and it isn't set to any tax% at all, it is just left blank.  The only problem is, if you leave the tax% field blank in the QBI config, it just puts a 0 in the IIF file.  I don't know if that might be cuasing a problem.

I'm not quite following, but your tax setup in QBI must exactly match your tax in QB -- account, description, and amount. I will be clarifying the labels in the config in the next versions.

 

If you use more than one tax rate, this is not yet supported but will be added in the future.

Also, the "VIA" pulldown menu on the invoice says "Method not set", but it should say FedEx.  I matched up the shipping using the match shipping in QBI.  On my store, we only offer FedEx.  So if someone chooses anything else besides free shipping (they have to spend over $25 to get free shipping) then it should say FedEx.  unfortunately, it just says "method not set".

It says "Method not set" because it isn't finding a match. You have to add FedEx shipping methods directly to the database yourself, because QBI does not come with them. I'm not using FedEx right now, so I didn't know exactly what to put -- that's why.

 

In the qbi_shiposc table, you need to add FedEx as the carrier and also add each service. The text must exactly match what is used in osC. On an osC invoice, you'll see the shipping method, plus the number of packages and the weight. QBI looks to see if it can find the shipping carrier and method in that text string -- when it finds something that matches an entry in the qbi_shiposc table, then it replaces it with its match. You must have also matched it to your QB shipping types in QBI's shipping set-up screen.

Also, I would like the Shipping Description to say "Shipping and Handling"

You can do that in QBI's config screen under shipping.

And finally, when I check the "import as sales receipt" option, and then I look at the output in Excel, the IIF file still is creating invoices and putting them in the Accounts Receivable account.  I'm confused as to why the "sales receipt" option isnt working.

QBI will only convert orders that are paid with the method "Credit Card" to a sales receipt, when that option is checked. This is because orders paid by "Check / Money Order" can never import as already paid, regardless of the config setting, since they aren't paid yet. I can add support for other payment methods, but I'll need to know what it is and what payment module you are using with your store.

 

Thanks,

Adam

Link to comment
Share on other sites

You would think this would be easy :)  Unfortunately, the payment method in paypal orders is obscured by the use of a paypal logo, not text.

 

Given that, I'll give the options....  In the orders database, the payment method is listed as 'PayPal IPN' (as opposed to 'Credit Card').  In the iif file, it is listed as 'PayPal'.  I'm not sure which of these is meaningful in this case.

QBI pulls the method from the order table column "Payment Method." That should match what is in the iif -- are you sure you have the column width wide enough to see the entire text? They should be the same.

Also, in my case, order status should be 1, 2, or 3 (Pending, Processing or Shipped).  Other order statuses, such as 99999 PayPal Processing, do not make for valid orders, and probably should not be exported.

Ok, PayPal looks a little more complicated. Thanks for the explanation. I'm sure I can do it, but I already have a big list of fixes / improvements, so it won't be in version 1.1

 

Thanks,

Adam

Link to comment
Share on other sites

I keep seeing QBI and iif mentioned here, maybe a daft question but what is QBI

It does indeed stand for Quickbooks Import! There are a number of older Quickbooks contributions, and I wanted to distiguish it somehow.

Is there a new version coming out soon, should I wait for that to be released?

Soon, yes. If you can, I'd wait as the instructions will be clearer and some problems will be fixed!

If i update stock in Quickbooks will the stock in OSC get updated too?

There is no connection for keeping stock in sync, but if you start with the same stock in QB and osC, and don't do off-line sales, then they will stay in sync.

If I add/Delete products in quickbooks will OSC get updated also?

If you re-import your QB product list to QBI, then "Matches" will get updated. But QBI does not remove or add products to osC.

Credit card processing is done via LinkPoint does quickbooks intergrate with this? or does osc pass all payment info to quickbooks?

QBI will pass the payment to Quickbooks. I may need to make a slight modification depending on what gets put in the Payment Method field for LinkPoint orders.

 

QBI imports the payment amount, date, and method to QB. It does not import the credit card number, as 1) iif can't do this, and 2) once your gateway processes the order, you don't need it and it is more secure for your customers not to store credit card numbers.

Sorry for all the questions, I'm basically trying to figure out if Quickbooks will become a OneStop Admin area to handle everything regarding stock/invoicing/payments etc for the store?

That's the idea of it!

 

Thanks,

Adam

Link to comment
Share on other sites

Also, order statuses are not updated beyond the one time update that is possible when you do the export of the iif.

Dan, thanks for answering this question! I'm ending up spending hours just answering questions, and it helps out!

 

One slight correction: -- I do plan to add a utility so that you can update the prior order batch to "shipped," after you've packed and prepared your orders (if you don't wish to just do it when you create the iif). Also, I may add a check-off list so that you can un-check orders that for some reason won't ship with the rest. Check / Money order orders could be un-checked by default, and show until they are converted to shipped. Something like this -- the idea being that you never have to go into each order one-by-one to update it.

 

Thanks,

Adam

Edited by adam5532
Link to comment
Share on other sites

Hi Adam, thanks for writing this great importer/exporter.

 

I've been setting it up for my store and found a couple of little bugs you might like to take a look at.

 

First, I found that in the documentation you refer to creating two folders called qbi_import and qbi_export if I remember correctly, but then I found that the code needs folders called something more like qbi_input and qbi_output so the documentation may need to be modified.

 

Second, I am not using item groups. I'm trying the qbi_orders.iif to get all my current store orders into quickbooks for a fresh new company setup. I first used qbi to get all my items into quickbooks with the appropriate options so that I should not have to set up any matching and just let the item:option method work as default. I found that qbi was creating an export of all the orders but with items and no item option tagged onto them. I followed up on the bug to a line in qbi_functions that appears incorrect related to the default language. In qbi_engine_orders.php you are using a function call to get the language id for the default language and it calls this function:

 

function get_language_id($language_code) {

$languages_query = tep_db_query("select languages_id from " . TABLE_LANGUAGES . " where code = '" . (int)$language_code . "'");

while ($languages = tep_db_fetch_array($languages_query)) {

$language_id=$languages['languages_id'];

}

return $language_id;

}

 

This appears wrong because you are converting a string language code ("en" in my case) into an integer by casting in the SQL where clause. This is causing the function to return blank as my default language id instead of returning '1' as it should.

 

Once I removed the (int) cast above, the export works fine for me. I didn't know if you use this function elsewhere, so I just made another fixed copy of the function and called that one from qbi_engine_orders.php instead which seems to work for me.

 

Thanks again for this cool importer/expoter. I was modifying some of the older quickbooks iif creators for OsC when I checked back and found you had just created this great comprehensive system.

 

BTW, my name is Jim ... This forum seems to be putting my post under another users name (?)

Edited by User Name
Link to comment
Share on other sites

Hi Adam.. heh sorry for all the quetsions it seems like i'm having a lot of trouble doing this. i don't know why. Prety much here are the most import issues i'md ealing with

 

W/ the Orders that i d/l and i check mark the "Use Sales Receipts for paid orders and "Import payments with invoice:" But after i import it. It still imports my orders in the invoice section of QB and nothing in the sales receipt section in QB. therefore its not being automatically set as paid and gives me outstanding balances. I did fix the "method not set" part like you said but i still have this issue. Am i supposed to have certain settings in QB? I enabled inventory and classes.

 

 

thanks

 

I really want this to work like it should but i'm having some trouble w/ the whole setu p thing.

Link to comment
Share on other sites

Hi Adam, thanks for writing this great importer/exporter.

 

I've been setting it up for my store and found a couple of little bugs you might like to take a look at.

 

First, I found that in the documentation you refer to creating two folders called qbi_import and qbi_export if I remember correctly, but then I found that the code needs folders called something more like qbi_input and qbi_output so the documentation may need to be modified.

Thanks, already fixed for upcoming ver 1.1.

This appears wrong because you are converting a string language code ("en" in my case) into an integer by casting in the SQL where clause. This is causing the function to return blank as my default language id instead of returning '1' as it should.

 

Once I removed the (int) cast above, the export works fine for me.

Thanks for finding that! Removed (int) from the function. Will be fixed in v1.1.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Adam.. heh sorry for all the quetsions it seems like i'm having a lot of trouble doing this. i don't know why. Prety much here are the most import issues i'md ealing with

 

W/ the Orders that i d/l  and i check mark the "Use Sales Receipts for paid orders and "Import payments with invoice:" But after i import it. It still imports my orders in the invoice section of QB and nothing in the sales receipt section in QB.

Open your orders table, do a reverse sort on orders_id, and tell me what it says in the payment_method field.

 

Alternately, open the iif and tell me what it says in the PAYMETH and TERMS fields. You said "Method not set" before, but that's in the SHIPVIA field, not PAYMETH and TERMS.

 

Thanks,

Adam

Link to comment
Share on other sites

I havent used quickbooks before and was wondering can it be used as a stock inventory program?

 

Quickbooks does track inventory. It is actually a full accounting program, so it does a lot more than just inventory. There are some limitations though, so it depends what you need. For most store type applications it works well.

 

I'd suggest going to www.quickbooks.com to find out more about quickbooks, and perhaps order the demo cd so you can try out quickbooks. It's probably by far the most common small business accounting tool.

 

Dan

Dan Stevens

Link to comment
Share on other sites

HELP! I am getting Error 1064

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 't care. Ship whatever is' AND pov1.products_options_values_id

 

SELECT pov2.products_options_values_id AS products_options_values_id_pov2, pov2.products_options_values_name AS products_options_values_name_pov2 FROM products_options_values As pov1, products_options_values As pov2 WHERE pov1.products_options_values_name='I don't care. Ship whatever is' AND pov1.products_options_values_id=pov2.products_options_values_id AND pov2.language_id=''

 

[TEP STOP]

 

What can cause this?

Link to comment
Share on other sites

QBI pulls the method from the order table column "Payment Method." That should match what is in the iif -- are you sure you have the column width wide enough to see the entire text? They should be the same.

 

Ok, PayPal looks a little more complicated. Thanks for the explanation. I'm sure I can do it, but I already have a big list of fixes / improvements, so it won't be in version 1.1

 

Thanks,

Adam

 

Your right, I had looked in the db at some earlier orders, several months ago, before I did a large paypal update. In the db, it is now just PayPal, which matches the iif.

 

It's okay to wait until after 1.1 for this. I'd suggest limiting what you put into 1.1 to be coupon support, cleanup of code, and bug fixes. Basically, stabilize the contribution before going all out with more features. Of course, it works amazingly well already, but it's always nice to have a solid, clean version before going to far with features.

 

Dan

Dan Stevens

Link to comment
Share on other sites

Open your orders table, do a reverse sort on orders_id, and tell me what it says in the payment_method field.

 

Alternately, open the iif and tell me what it says in the PAYMETH and TERMS fields. You said "Method not set" before, but that's in the SHIPVIA field, not PAYMETH and TERMS.

 

Thanks,

Adam

 

it says Payment via Credit Card

Link to comment
Share on other sites

HELP! I am getting Error 1064

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 't care. Ship whatever is' AND pov1.products_options_values_id

 

SELECT pov2.products_options_values_id AS products_options_values_id_pov2, pov2.products_options_values_name AS products_options_values_name_pov2 FROM products_options_values As pov1, products_options_values As pov2 WHERE pov1.products_options_values_name='I don't care. Ship whatever is' AND pov1.products_options_values_id=pov2.products_options_values_id AND pov2.language_id=''

 

[TEP STOP]

 

What can cause this?

"I don't care. Ship whatever is" has an apostrophe in "don't" that is confusing it. Take the apostrophe out for now -- I will put in on the fix list.

 

Thanks,

Adam

Link to comment
Share on other sites

Just to clarify, the exact text, in proper case, is as follows within the quotes:

 

"Payment via Credit Card" ?

 

Thanks,

Adam

 

yea. Thats how the authorize.net w/ curl fix contrib was setup in OSC as default. I dunno what is wrong cuz no matter what i check on the configureation. It still imports as in invoice and not as a sales receipt.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...