Jump to content
adam5532

QBI Quickbooks Import

Recommended Posts

This contribution is a work of art. Good job :)

 

I'm having just one trouble:

 

I am unable to match any products (no problems with shipping). I get only blank drop-down boxes. I am able to see the full list of my QG files when I upload them at the "Set Up Products" section (see the name and description).

 

I'm at a loss as to what to do, ciapo seemed to have a similar problem- but I do not deal with taxable products. I suspect a settings error on my part, but can't think of what it could be. Any ideas?

Share this post


Link to post
Share on other sites
This contribution is a work of art.  Good job :)

 

I'm having just one trouble:

 

I am unable to match any products (no problems with shipping).  I get only blank drop-down boxes.  I am able to see the full list of my QG files when I upload them at the "Set Up Products" section (see the name and description).

After uploading them in the "Set Up Products" page, go to another page in QBI, and then go back to the "Set Up Products" page. It will read back the Products list from the database. Tell me if you see the list of products, or if it just asks you if you want to import the list but does not list the products that have already been imported below.

 

This will let me know if the problem is putting the items in the database, or if it is a problem with the drop-down boxes.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

I re-uploaded the products in "Set up Products" and then clicked on "utilities". I immediately clicked back on "Set up Products", and my QB items were listed there still. They still do not appear in the pull-downs for "match products".

Share this post


Link to post
Share on other sites
I re-uploaded the products in "Set up Products" and then clicked on "utilities".  I immediately clicked back on "Set up Products", and my QB items were listed there still.  They still do not appear in the pull-downs for "match products".

Try changing the "Sort By" Name or Description option in the Config and let me know if that does anything.

 

Your items are set up as Inventory Items, yes? Because non-inventory items are not supported yet, and neither are Services (unless they are part of a group).

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Ah. All of our items are listed as service-type items. That solves that mystery.

 

Will version 1.1 support these types of items? Thanx.

Share this post


Link to post
Share on other sites
Ah.  All of our items are listed as service-type items.  That solves that mystery.

 

Will version 1.1 support these types of items?  Thanx.

I'll see. I'm close to getting version 2.0 (not 1.1 anymore, because there are many significant improvements) out. I'll see, but it might wait till 2.1.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Some helpful advice for the transaction out of balance problem.

Please note: this solves a very select problem, most stores will not need to do this. This is needed in my store, because we sell fractional items, basically fabric, which might be sold by the 1/4 yard (0.25) rather than full quantity increments. This causes rounding errors in the order total.

 

First, let me explain the problem. To simplify it, we sell fabric, say at $8.99 a yard. If someone buys 3 quarter yard cuts, the items are correctly priced at $2.50 each (8.99 / 4) but the basket total will be only $7.49, rather than the expected $7.50. This is visible in the basket display. No customer ever complained, I never bothered to fix it.

 

This causes problems though, when you import into quickbooks, since the transaction will be out of balance. Given how much of a time saver this contribution is, I decided it's finally time to resolve the rounding problem.

 

I'll say it again though, unless you sell fractional items, this fix probably won't do much for you. Also, I was a bit lazy, and just rounded to 2 decimal places, if you need something else, you'll have to change the rounding function to the correct number of decimal places, or modify it so it switches to the currency default.

 

In includes\classes\shopping_cart.php find:

$this->total += tep_add_tax($products_price, $products_tax) * $qty;

 

and change it to:

$this->total += tep_round((tep_add_tax($products_price, $products_tax) * $qty),2);

 

You'll then see that the basket adds up correctly, so the quickbooks export can work correctly.

 

What does this do? The basket total is calculated by taking the price of each item times the quantity of each item and adding them together. Since it assumes the price of each item is already correctly rounded, then the total should come out correct. Since our item price ends up not being rounded (due to division), this rounds the product price * quantity to 2 decimal places for each item before it gets added to the basket total.

 

Dan


Dan Stevens

Share this post


Link to post
Share on other sites

Quickbooks Import QBI version 2.00 has just been released!

 

I have moved it to a new location and put QBI at the end, so that it would be easier to find with the other Quickbooks contributions.

 

http://www.oscommerce.com/community/contributions,2847

 

2.00 1/23/05

New Features:

1. Support for discounts, coupons, and fees

2. Automatic detection of installed payment, shipping, and discount modules in osC

3. Automatic database installation and upgrading

4. Support for products with multiple attributes (pass-through only, no matching yet)

5. Option to only import orders with a specific status (to support PayPal IPN)

6. Payment method matching to Quickbooks

7. Indicate which payment methods are prepaid and which aren?t

8. User configurable customer numbers

9. User configurable Invoice and PO numbers

10. Adjustable products dropdown width

11. Create and Utilities pages combined

12. New menu system

13. Added blank line before shipping charges on invoice

14. Confirmation message when updating shipping and product matches

15. Option to maintain proper address label creation with ShipRush

16. Iif creation 5 ? 10 times faster

17. Simplified payment import options

18. Numerous new and clearer configuration options

Fixes:

19. All customers were sent a status email when importing old orders if ?send email? was checked

20. The last setup iif input file would be processed instead of the first that was found

21. Product descriptions could be blank in some configurations

22. Setting customer limit to zero would set limit to $0 instead of no limit

23. Corrected directory names in manual from qbi_import/qbi_export to qbi_input/qbi_output, added permissions

24. Products match would go back page one after updating instead of staying on the same page

25. Previous and Next weren?t translating on matching pages

26. A few other text items weren?t translating

27. Expanded manual with more information and explanations of all options

28. General code cleanup and improvement, moved all page headers and footers into includes

 

- Adam

Share this post


Link to post
Share on other sites

Thanks Adam I've been waiting on this...you're doing a great job. Now I see why the next version was taking a bit longer than you originally thought. :thumbsup:

Share this post


Link to post
Share on other sites

Hello,

 

Awesome mod! Looks very sweet.

 

I tried to install and all went very well until I tried to import products. It gives me this:

Warning: array_diff(): Argument #1 is not an array in /home/betty/public_html/catalog/admin/includes/functions/qbi_functions.php on line 408

Warning: Invalid argument supplied for foreach() in /home/betty/public_html/catalog/admin/includes/functions/qbi_functions.php on line 409

 

I can however import shipping and payments.

 

 

Any suggestions?

 

 

Best regards,

Brett

Share this post


Link to post
Share on other sites

Has anyone tested this contribution on QuickBooks 2005 for Mac?

Or any previous Mac versions?

 

If so, does it seem to work OK?

 

Thx,

 

Marc

Share this post


Link to post
Share on other sites
Hello,

 

Awesome mod!  Looks very sweet.

 

I tried to install and all went very well until I tried to import products.  It gives me this:

Warning: array_diff(): Argument #1 is not an array in /home/betty/public_html/catalog/admin/includes/functions/qbi_functions.php on line 408

Warning: Invalid argument supplied for foreach() in /home/betty/public_html/catalog/admin/includes/functions/qbi_functions.php on line 409

 

I can however import shipping and payments.

Any suggestions?

Best regards,

Brett

Brett,

Are your products all set up as noninventory items or services? QBI does not yet support these types of products (unless they are part of a group). I will fix the code in the future so that the error does not occur, but it would only happen if there are no inventory products in your iif, or something was wrong with the iif. Also, make sure that you checked off the appropriate boxes when making the items.iif or products.iif file.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

hi i have this error

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 '' at line 1

CREATE TABLE qbi_config (

Share this post


Link to post
Share on other sites
hi i have this error

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 '' at line 1

CREATE TABLE qbi_config (

Sorry, problem fixed.

 

A minor problem in v2.00 was preventing the database from installing. Version 2.01 corrects that. This is a full version of the program. However, if you have already tried to install v2.00, you only need to replace the qbi_functions.php and qbi_0-00_2-00.sql files, as the rest have not changed.

 

- Adam

Share this post


Link to post
Share on other sites

Hi Adam,

 

I have this sql error while trying to update

 

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 ''cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0'

ALTER TABLE `qbi_config` CHANGE 'cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0';

[TEP STOP]

 

Any ideas?

 

Though I would get used to mod, in anticipation of UK VAT being added :)

 

Andy


Literally, Laterally Thinking! If you cannot get through it, go round it.

Share this post


Link to post
Share on other sites
Hi

 

Can you advise if V2 is compatible with UK Quickbooks and VAT?

 

Thanks

 

Steve

No, it is only compatible with US versions of QB. UK VAT and support for other international versions is near the top of my list for the future. QBI has taken over my life for the last few months. I will probably concentrate on bug fixes and forum support at least for the next few weeks before adding any additional features, but they will come.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Hi Adam,

 

I have this sql error while trying to update

 

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 ''cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0'

ALTER TABLE `qbi_config` CHANGE 'cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0';

[TEP STOP]

 

Any ideas?

 

Though I would get used to mod, in anticipation of UK VAT being added :)

 

Andy

Are you using v2.00 or v2.01? I uploaded v2.01 that fixes a database installation problem with a fresh install. Let me know if v2.01 fixes the problem.

 

- Adam

Share this post


Link to post
Share on other sites

HYe adam.. i get this message alsoo when i try to go into my Admin

 

 

Parse error: parse error, unexpected T_STRING in /home/nutdeal/public_html/admin/includes/database_tables.php on line 74

 

 

 

------------------

 

<?php

/*

$Id: database_tables.php,v 1.1 2003/06/20 00:18:30 hpdl Exp $

 

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright ? 2003 osCommerce

 

Released under the GNU General Public License

*/

 

// define the database table names used in the project

define('TABLE_QBI_CONFIG', 'qbi_config');

define('TABLE_QBI_GROUPS', 'qbi_groups');

define('TABLE_QBI_GROUPS_ITEMS', 'qbi_groups_items');

define('TABLE_QBI_ITEMS', 'qbi_items');

define('TABLE_QBI_PRODUCTS_ITEMS', 'qbi_products_items');

define('TABLE_QBI_SHIPOSC', 'qbi_shiposc');

define('TABLE_QBI_SHIPQB', 'qbi_shipqb');

define('TABLE_QBI_SHIPOSC_SHIPQB', 'qbi_shiposc_shipqb');

define('TABLE_QBI_TAXES', 'qbi_taxes');

 

 

 

define('TABLE_ADDRESS_BOOK', 'address_book');

define('TABLE_ADDRESS_FORMAT', 'address_format');

define('TABLE_BANNERS', 'banners');

define('TABLE_BANNERS_HISTORY', 'banners_history');

define('TABLE_CATEGORIES', 'categories');

define('TABLE_CATEGORIES_DESCRIPTION', 'categories_description');

define('TABLE_CONFIGURATION', 'configuration');

define('TABLE_CONFIGURATION_GROUP', 'configuration_group');

define('TABLE_COUNTRIES', 'countries');

define('TABLE_CURRENCIES', 'currencies');

define('TABLE_CUSTOMERS', 'customers');

define('TABLE_CUSTOMERS_BASKET', 'customers_basket');

define('TABLE_CUSTOMERS_BASKET_ATTRIBUTES', 'customers_basket_attributes');

define('TABLE_CUSTOMERS_INFO', 'customers_info');

define('TABLE_LANGUAGES', 'languages');

define('TABLE_MANUFACTURERS', 'manufacturers');

define('TABLE_MANUFACTURERS_INFO', 'manufacturers_info');

define('TABLE_NEWSLETTERS', 'newsletters');

define('TABLE_ORDERS', 'orders');

define('TABLE_ORDERS_PRODUCTS', 'orders_products');

define('TABLE_ORDERS_PRODUCTS_ATTRIBUTES', 'orders_products_attributes');

define('TABLE_ORDERS_PRODUCTS_DOWNLOAD', 'orders_products_download');

define('TABLE_ORDERS_STATUS', 'orders_status');

define('TABLE_ORDERS_STATUS_HISTORY', 'orders_status_history');

define('TABLE_ORDERS_TOTAL', 'orders_total');

define('TABLE_PRODUCTS', 'products');

define('TABLE_PRODUCTS_ATTRIBUTES', 'products_attributes');

define('TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD', 'products_attributes_download');

define('TABLE_PRODUCTS_DESCRIPTION', 'products_description');

define('TABLE_PRODUCTS_NOTIFICATIONS', 'products_notifications');

define('TABLE_PRODUCTS_OPTIONS', 'products_options');

define('TABLE_PRODUCTS_OPTIONS_VALUES', 'products_options_values');

define('TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS', 'products_options_values_to_products_options');

define('TABLE_PRODUCTS_TO_CATEGORIES', 'products_to_categories');

define('TABLE_REVIEWS', 'reviews');

define('TABLE_REVIEWS_DESCRIPTION', 'reviews_description');

define('TABLE_SESSIONS', 'sessions');

define('TABLE_SPECIALS', 'specials');

define('TABLE_TAX_CLASS', 'tax_class');

define('TABLE_TAX_RATES', 'tax_rates');

define('TABLE_GEO_ZONES', 'geo_zones');

define('TABLE_ZONES_TO_GEO_ZONES', 'zones_to_geo_zones');

define('TABLE_WHOS_ONLINE', 'whos_online');

define('TABLE_ZONES', 'zones');

 

define('TABLE_QBI_DISC', 'qbi_disc');

define('TABLE_QBI_OT', 'qbi_ot');

define('TABLE_QBI_OT_DISC', 'qbi_ot_disc');

define('TABLE_QBI_PAYOSC', 'qbi_payosc')

define('TABLE_QBI_PAYOSC_PAYQB', 'qbi_payosc_payqb');

define('TABLE_QBI_PAYQB', 'qbi_payqb');

 

?>

Edited by superman123

Share this post


Link to post
Share on other sites
HYe adam.. i get this message alsoo when i try to go into my Admin

Parse error: parse error, unexpected T_STRING in /home/nutdeal/public_html/admin/includes/database_tables.php on line 74

------------------

 

  define('TABLE_QBI_PAYOSC', 'qbi_payosc')

 

?>

I left the semicolon off at the end of the line above in the upgrade instructions. Change it to:

 

 

define('TABLE_QBI_PAYOSC', 'qbi_payosc');

 

and it should work!

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
I left the semicolon off at the end of the line above in the upgrade instructions. Change it to:

  define('TABLE_QBI_PAYOSC', 'qbi_payosc');

 

and it should work!

 

Thanks,

Adam

 

THANK ADAM!! but.. unfortunatly i also get this error when i try to upgrade. its the 2.01 version

 

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 ''cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0'

 

ALTER TABLE `qbi_config` CHANGE 'cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0';

 

[TEP STOP]

Share this post


Link to post
Share on other sites
THANK ADAM!! but.. unfortunatly i also get this error when i try to upgrade. its the 2.01 version

 

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 ''cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0'

 

ALTER TABLE `qbi_config` CHANGE 'cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0';

 

[TEP STOP]

 

 

nm.. i just did a fresh install. it seemed simpler. thanks!

Share this post


Link to post
Share on other sites

hey adam.. sorry again. i get this message when i try to import al my previous orders. I read on your contribabout something about replace the qbi_functions.php. is that included in the latest d/l?

 

 

Warning: Variable passed to each() is not an array or object in /home/nutdeal/public_html/admin/includes/functions/qbi_functions.php on line 35

Warning: Variable passed to each() is not an array or object in /home/nutdeal/public_html/admin/includes/functions/qbi_functions.php on line 35

Warning: Variable passed to each() is not an array or object in /home/nutdeal/public_html/admin/includes/functions/qbi_functions.php on line 35

Warning: Variable passed to each() is not an array or object in /home/nutdeal/public_html/admin/includes/functions/qbi_functions.php on line 35

 

etc.. all the way down the page

Share this post


Link to post
Share on other sites

Hi adam,

 

Got the sql error on ver 2.00 and 2.01.

 

Have now tracked down the problem I was having:

 

For me it's in this block of SQL statement in qbi_1-00_2-00.sql

 

ALTER TABLE `qbi_config` CHANGE 'cust_limit' 'cust_limit' int(10) unsigned NOT NULL default '0';
ALTER TABLE `qbi_config` CHANGE 'invoice_rep' 'invoice_rep' varchar(41) NOT NULL default '';
ALTER TABLE `qbi_config` CHANGE 'invoice_fob' 'invoice_fob' varchar(13) NOT NULL default '';
ALTER TABLE `qbi_config` CHANGE 'tax_rate' 'tax_rate' float NOT NULL default '0';

ALTER TABLE `qbi_config` DROP 'invoice_salesrec';
ALTER TABLE `qbi_config` DROP 'tax_class';
ALTER TABLE `qbi_config` DROP 'pmts_import';

 

I removed the ' around the field names to read as follows and it worked for me!

 

 
ALTER TABLE `qbi_config` CHANGE cust_limit cust_limit int(10) unsigned NOT NULL default '1';
ALTER TABLE `qbi_config` CHANGE invoice_rep invoice_rep varchar(41) NOT NULL default '';
ALTER TABLE `qbi_config` CHANGE invoice_fob invoice_fob varchar(13) NOT NULL default '';
ALTER TABLE `qbi_config` CHANGE tax_rate tax_rate float NOT NULL default '0';

ALTER TABLE `qbi_config` DROP invoice_salesrec;
ALTER TABLE `qbi_config` DROP tax_class;
ALTER TABLE `qbi_config` DROP pmts_import;

 

My version of MySQL just does not like those ' !

 

Andy


Literally, Laterally Thinking! If you cannot get through it, go round it.

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

×