Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

adam5532

QBI Quickbooks Import

Recommended Posts

Hi Adam,

 

My Qbi has been working perfectly till recently. I'm getting an error message saying

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 'oise' AND zone_country_id='73'' at line 1

 

SELECT * FROM zones WHERE zone_name='val d'oise' AND zone_country_id='73'

 

I think it is orders shipped to France that caused the problem. I don't know if our hosting company has made any changes to SQL software.

 

Please help me to fix the error.

 

Thanks so much.

 

Wendy

Wendy,

 

It doesn't like the single quote character in the zone name. This has been fixed in later versions of QBI. For QBI 2.10, you can fix it by doing the following:

 

File: qbi_functions.php

After Line: 308

function get_state_code($state_name,$country_id) {

Add:

$state_name=mysql_real_escape_string($state_name);

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Wendy,

 

It doesn't like the single quote character in the zone name. This has been fixed in later versions of QBI. For QBI 2.10, you can fix it by doing the following:

 

File: qbi_functions.php

After Line: 308

function get_state_code($state_name,$country_id) {

Add:

$state_name=mysql_real_escape_string($state_name);

 

Thanks,

Adam

 

 

Adam, I know this has been stated a million times; but I can't get this to work anymore. It gives me the error:

 

1060 - Duplicate column name 'qbi_imported'

 

ALTER TABLE `orders` ADD `qbi_imported` TINYINT(2) UNSIGNED DEFAULT '2' NOT NULL;

 

[TEP STOP]

 

when I try to install the tables. Can you tell me how to fix it, because I know you say to delete the qbi_imports field, but where do you delete it from?

 

Brent

Share this post


Link to post
Share on other sites
Adam, I know this has been stated a million times; but I can't get this to work anymore. It gives me the error:

 

1060 - Duplicate column name 'qbi_imported'

 

ALTER TABLE `orders` ADD `qbi_imported` TINYINT(2) UNSIGNED DEFAULT '2' NOT NULL;

 

[TEP STOP]

 

when I try to install the tables. Can you tell me how to fix it, because I know you say to delete the qbi_imports field, but where do you delete it from?

 

Brent

If you reinstall the QBI tables in version 2.10, then you need to delete the field qbi_imports from the orders and products tables first. But, why are you reinstalling it?

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
If you reinstall the QBI tables in version 2.10, then you need to delete the field qbi_imports from the orders and products tables first. But, why are you reinstalling it?

 

Thanks,

Adam

 

 

Where are the tables? I am reinstalling because it didnt work when I tried to use it today. I had it working before, but added a bunch of attributes and it stoped working.

Share this post


Link to post
Share on other sites

Thanks so much Adam. It solved the problem.

:thumbsup:

 

Wendy,

 

It doesn't like the single quote character in the zone name. This has been fixed in later versions of QBI. For QBI 2.10, you can fix it by doing the following:

 

File: qbi_functions.php

After Line: 308

function get_state_code($state_name,$country_id) {

Add:

$state_name=mysql_real_escape_string($state_name);

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Where are the tables? I am reinstalling because it didnt work when I tried to use it today. I had it working before, but added a bunch of attributes and it stoped working.

QBI 2.10 supports 2 attributes per product for the product download. Later versions support more. Order downloads do not have this limitation. When you say it stopped working, I'll need more details of exactly what stopped working as that is quite general.

 

It is rare for files to get corrupted after they are working fine, so usually re-installation is not the first thing you should do to solve a problem using QBI. When you do reinstall, you need to follow the instructions in the manual.

 

The tables are in your MySQL database, and you'll need to use your MySQL database manager, like phpMyAdmin to access them. If you don't know how to work with them, then I suggest you learn first or have someone help you. I will contact you by PM to follow up further.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

HI!

 

I too am having a problem with the customer export. When I attempt to create the customer export, the file reloads as though having been refreshed, but no download file is ever created. I've checked the output folder, and the products and orders iifs are there, but nothing for customers. So basically, all exports work except for customers. No error messages are generated either.

 

Anyone have any ideas?

 

Ken

Share this post


Link to post
Share on other sites
HI!

 

I too am having a problem with the customer export. When I attempt to create the customer export, the file reloads as though having been refreshed, but no download file is ever created. I've checked the output folder, and the products and orders iifs are there, but nothing for customers. So basically, all exports work except for customers. No error messages are generated either.

 

Anyone have any ideas?

 

Ken

Ken,

 

PHP must be 4.23 or later.

The only reason to create the customers iif is if you want to put old customers into QB for historical reasons. Otherwise, there is no reason to use this feature, as customers are always automatically exported with their orders.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Quick question: Is QBI compatible with EasyPopulate?

 

I noticed that QBI adds a field in the PRODUCTS table. Is that a problem for Easypopulate?

 

Thanks.

Share this post


Link to post
Share on other sites
Ken,

 

PHP must be 4.23 or later.

The only reason to create the customers iif is if you want to put old customers into QB for historical reasons. Otherwise, there is no reason to use this feature, as customers are always automatically exported with their orders.

 

Thanks,

Adam

 

Ahh! okay! Thank you!

 

Ken

Share this post


Link to post
Share on other sites
Quick question: Is QBI compatible with EasyPopulate?

 

I noticed that QBI adds a field in the PRODUCTS table. Is that a problem for Easypopulate?

 

Thanks.

No problem, Easy Populate will ignore it.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Hi!

 

My client sent me their product list from quickbooks and as they had it set up, it failed to populate the match drop-downs. I went into their iif file, and found an invitemtype of part. I compared this to another file that did work, and it had an invitemtype of inventory. So I change part to inventory, and now, viola, the menus populate just fine. I'm guessing they're going to have problems when they import now. What should I tell them to change in quickbooks so that this will work properly?

 

thanks!

 

Ken

Share this post


Link to post
Share on other sites
Hi!

 

My client sent me their product list from quickbooks and as they had it set up, it failed to populate the match drop-downs. I went into their iif file, and found an invitemtype of part. I compared this to another file that did work, and it had an invitemtype of inventory. So I change part to inventory, and now, viola, the menus populate just fine. I'm guessing they're going to have problems when they import now. What should I tell them to change in quickbooks so that this will work properly?

 

thanks!

 

Ken

Ken,

 

In the QBI configuration, section Items, option Match types, you need to select both Inventory and Non-Inventory. Then they will appear in the drop-down. They don't need to change anything in Quickbooks.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Using QBI gives a "method not set" error when using USPS shipping. It was giving this error for UPS too, but I managed to fix that by editing the ups.php file in the "english" directory (the text didn't match what was in the other ups.php file). The usps.php file however (in the "modules/shipping" directory) doesn't seem to use the types defined in the usps.php file in the "english" directory

 

Here are the ones from the one in the "english" directory:

define('MODULE_SHIPPING_USPS_TEXT_OPT_PP', 'Parcel Post');

define('MODULE_SHIPPING_USPS_TEXT_OPT_PM', 'Priority Mail');

define('MODULE_SHIPPING_USPS_TEXT_OPT_EX', 'Express Mail');

 

 

This is the only place I see the types defined in the one in the "modules/shipping" (these are the only USPS shipping methods I'm using):

 

$this->intl_types = array('Express' => 'Global Express Mail (EMS)',

'Airmail Parcel' => 'Airmail Parcel Post');

 

 

How do I fix this?

 

Thanks,

 

Joseph


Joseph McMurry
Web Developer
PHP/MySQL

http://www.mcmwebsite.com
 

Share this post


Link to post
Share on other sites
Using QBI gives a "method not set" error when using USPS shipping. It was giving this error for UPS too, but I managed to fix that by editing the ups.php file in the "english" directory (the text didn't match what was in the other ups.php file). The usps.php file however (in the "modules/shipping" directory) doesn't seem to use the types defined in the usps.php file in the "english" directory

 

Here are the ones from the one in the "english" directory:

define('MODULE_SHIPPING_USPS_TEXT_OPT_PP', 'Parcel Post');

define('MODULE_SHIPPING_USPS_TEXT_OPT_PM', 'Priority Mail');

define('MODULE_SHIPPING_USPS_TEXT_OPT_EX', 'Express Mail');

This is the only place I see the types defined in the one in the "modules/shipping" (these are the only USPS shipping methods I'm using):

 

$this->intl_types = array('Express' => 'Global Express Mail (EMS)',

'Airmail Parcel' => 'Airmail Parcel Post');

How do I fix this?

 

Thanks,

 

Joseph

Joseph,

 

In the file qbi_shipmatch.php. comment out line 43 by changing

ship_methods();

to

// ship_methods();

 

This will disable QBI's automatic updating of the osC shipping methods.

 

Then, in the table qbi_shiposc, make corrections to the text so that it matches the text that osC uses. You can find this by going to the orders_total table and looking at the ot_shipping entries.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Hi!

I searched for this thread becauses a new client based in Ireland is using Quickbooks and wanted to know if it could be integrated with the osCommerce store I built for her. By the time I reached page 29 I figured the answer was probably 'yes!' . Anyway, I have no questions, I 'cheated' and skipped to the end of the thread, only to find Adam patiently answering questions just as he was throughout the pages I read. :blush: Shamefaced, I shall return to page 30 and read to the end out of sheer respect for the effort being shown here.

 

So, I just wanted to big up Adam for being such a star, not only for creating this amazing contrib, but for sticking with it over the years. Good on you, Adam! :thumbsup:

Share this post


Link to post
Share on other sites
Hi!

I searched for this thread becauses a new client based in Ireland is using Quickbooks and wanted to know if it could be integrated with the osCommerce store I built for her. By the time I reached page 29 I figured the answer was probably 'yes!' . Anyway, I have no questions, I 'cheated' and skipped to the end of the thread, only to find Adam patiently answering questions just as he was throughout the pages I read. :blush: Shamefaced, I shall return to page 30 and read to the end out of sheer respect for the effort being shown here.

 

So, I just wanted to big up Adam for being such a star, not only for creating this amazing contrib, but for sticking with it over the years. Good on you, Adam! :thumbsup:

Thanks!

- Adam

Share this post


Link to post
Share on other sites
I'm looking forward to using this contribution. So far I have used and modified a number of other brilliant contributions but I seem to have an issue launching this one.

 

For example, I installed everything and uploaded the files but I get the following browser error:

 

FIREFOX says ... "The page isn't redirecting properly". The url is: [...]/catalog/admin/qbi_config.php?msg=1

 

IE just times out.

 

I get the same error with and without my lists.iif uploaded. With and without the sql files uploaded. I've both uploaded and installed the sql files on my own seeing how the drop tables command is there and I have removed the alter statements so they wouldn't error out. Did the manual db alterations because of the initial error. I'm probably way over-thinking this and a search on "bi_config.php?msg=1" shows some old issues but I can't tell how this would relate to me.

 

Thanks in advance and I hope that this isn't a big deal. Many seem to get it all loaded and started easily. Thanks again for your time.

 

 

I had exactly the same error. It occurs because qbi_config calls qbi qbi_version which can call qbi_config(if config isn't done yet) and so on in an infinite loop.

A quick hack is to comment out

require(DIR_WS_INCLUDES . 'qbi_version.php');

around line 47 of qbi_config for the first run and complete configuration then just put it back in for normal use.

 

It seems to be working for me

Share this post


Link to post
Share on other sites

Adam,

I've got a customer who is using the price levels in QuickBooks to provide discounts to his customers. He says that he is importing the orders for a day, and instead of what he was hoping for (price levels being applied to the imported invoices), he is instead finding that the price levels are being deleted from the customer's account info (and no discount applied).

 

Any answer/solution to this problem ? Have you heard of this happening ?

 

One solution I saw is that osCommerce provides Discount Coupons for an order that I could turn around and modify to apply to specific customers automatically on the creation of an order.

 

The optimal solution would seem to be to keep the Purchase Order imports from deleting the price levels, that way no modifications would have to be made to the existing system.

 

Thanks in advance for your attention on this problem :thumbsup:

Share this post


Link to post
Share on other sites
Adam,

I've got a customer who is using the price levels in QuickBooks to provide discounts to his customers. He says that he is importing the orders for a day, and instead of what he was hoping for (price levels being applied to the imported invoices), he is instead finding that the price levels are being deleted from the customer's account info (and no discount applied).

 

Any answer/solution to this problem ? Have you heard of this happening ?

 

One solution I saw is that osCommerce provides Discount Coupons for an order that I could turn around and modify to apply to specific customers automatically on the creation of an order.

 

The optimal solution would seem to be to keep the Purchase Order imports from deleting the price levels, that way no modifications would have to be made to the existing system.

 

Thanks in advance for your attention on this problem :thumbsup:

Hi,

 

Normally, all the discounting should be done in osCommerce. That way, the invoice in osC will show the correct purchase prices. If your customer uses a payment gateway like Authorize.net, then obviously the discounts have to already be applied in osC or the incorrect amount would be charged to their credit card. I don't know if there is any contribution that allows pricing discounts for specific customers. There are discount coupons and vouchers.

 

When QBI imports an order, the price paid for an item in the shopping cart will supersede the price in QB, so it doesn't matter if the price in osC is lower than the price for it in QB. That's why the price, and any discounts, need to be set up in osC, not in QB.

 

As far as the price levels getting deleted, this is an unfortunate issue with iif files. The iif format is not very sophisticated, and when you import a customer again who has purchased before, some fields will get over-written. This is the intended behavior for QBI, so that the customer's contact information and billing/shipping addresses will get over-written and updated to match the latest order. QBI 4.0 will solve this issue by using xml instead of iif.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

I have a client who sells items that comes in sizes. His current website appears to treat these as individual products. Each with a model number.

 

They are talking of going with Products and Attributes. But their Product has a model number and then teh Attribute has a number too.

 

So we can add a model number to Attributes but how is this going to map to their QB file? I read it can cope with 2 attributes per product. But what does an attribute equate to in QB?


Kym

Projects Director @ ozEworks.com

Share this post


Link to post
Share on other sites
I have a client who sells items that comes in sizes. His current website appears to treat these as individual products. Each with a model number.

 

They are talking of going with Products and Attributes. But their Product has a model number and then teh Attribute has a number too.

 

So we can add a model number to Attributes but how is this going to map to their QB file? I read it can cope with 2 attributes per product. But what does an attribute equate to in QB?

osC Product:Attribute = QB item:subitem. If you have multiple Attributes, then you have for example Product:Attribute1:Attribute2 = QB item:subitem:subitem.

 

If you use the matching feature of QBI, then you can match Product:Attribute1:Attribute2 to any QB item, item:subitem, or group. But if you have a lot of product and attribute combinations, it is much more convenient to avoid matching.

 

If your client would like to export their osC product list into QB and automatically create the item:subitem combinations, the free version of QBI supports up to two attributes (item:subitem:subitem), and newer versions support up to six attributes.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×