zerodash Posted January 18, 2005 Share Posted January 18, 2005 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? Quote Link to comment Share on other sites More sharing options...
adam5532 Posted January 18, 2005 Author Share Posted January 18, 2005 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 Quote Link to comment Share on other sites More sharing options...
zerodash Posted January 19, 2005 Share Posted January 19, 2005 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". Quote Link to comment Share on other sites More sharing options...
adam5532 Posted January 19, 2005 Author Share Posted January 19, 2005 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". <{POST_SNAPBACK}> 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 Quote Link to comment Share on other sites More sharing options...
zerodash Posted January 20, 2005 Share Posted January 20, 2005 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. Quote Link to comment Share on other sites More sharing options...
adam5532 Posted January 20, 2005 Author Share Posted January 20, 2005 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. <{POST_SNAPBACK}> 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 Quote Link to comment Share on other sites More sharing options...
djs Posted January 22, 2005 Share Posted January 22, 2005 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 Quote Dan Stevens Link to comment Share on other sites More sharing options...
adam5532 Posted January 24, 2005 Author Share Posted January 24, 2005 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 Quote Link to comment Share on other sites More sharing options...
Guest Posted January 24, 2005 Share Posted January 24, 2005 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: Quote Link to comment Share on other sites More sharing options...
cornernote Posted January 24, 2005 Share Posted January 24, 2005 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 Quote Link to comment Share on other sites More sharing options...
marccraw Posted January 24, 2005 Share Posted January 24, 2005 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 Quote Link to comment Share on other sites More sharing options...
clarocque Posted January 24, 2005 Share Posted January 24, 2005 Marc, not sure but you can get some sample files to atleast test the import here: http://www.quickbooks.com/support/faqs/qbw2001/117503.html you could also compare your exports... Report back what you find out ;) Quote osC Contributions I have published. Note: Some I only provided minor changes, updates or additions! Link to comment Share on other sites More sharing options...
adam5532 Posted January 24, 2005 Author Share Posted January 24, 2005 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 <{POST_SNAPBACK}> 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 Quote Link to comment Share on other sites More sharing options...
cipao Posted January 24, 2005 Share Posted January 24, 2005 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 ( Quote Link to comment Share on other sites More sharing options...
stevep123 Posted January 24, 2005 Share Posted January 24, 2005 Hi Can you advise if V2 is compatible with UK Quickbooks and VAT? Thanks Steve Quote Link to comment Share on other sites More sharing options...
adam5532 Posted January 24, 2005 Author Share Posted January 24, 2005 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 ( <{POST_SNAPBACK}> 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 Quote Link to comment Share on other sites More sharing options...
volumax Posted January 24, 2005 Share Posted January 24, 2005 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 Quote Literally, Laterally Thinking! If you cannot get through it, go round it. Link to comment Share on other sites More sharing options...
adam5532 Posted January 24, 2005 Author Share Posted January 24, 2005 Hi Can you advise if V2 is compatible with UK Quickbooks and VAT? Thanks Steve <{POST_SNAPBACK}> 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 Quote Link to comment Share on other sites More sharing options...
adam5532 Posted January 24, 2005 Author Share Posted January 24, 2005 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 <{POST_SNAPBACK}> 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 Quote Link to comment Share on other sites More sharing options...
superman123 Posted January 24, 2005 Share Posted January 24, 2005 (edited) 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 January 24, 2005 by superman123 Quote Link to comment Share on other sites More sharing options...
adam5532 Posted January 25, 2005 Author Share Posted January 25, 2005 HYe adam.. i get this message alsoo when i try to go into my AdminParse 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') ?> <{POST_SNAPBACK}> 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 Quote Link to comment Share on other sites More sharing options...
superman123 Posted January 25, 2005 Share Posted January 25, 2005 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 <{POST_SNAPBACK}> 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] Quote Link to comment Share on other sites More sharing options...
superman123 Posted January 25, 2005 Share Posted January 25, 2005 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] <{POST_SNAPBACK}> nm.. i just did a fresh install. it seemed simpler. thanks! Quote Link to comment Share on other sites More sharing options...
superman123 Posted January 25, 2005 Share Posted January 25, 2005 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 Quote Link to comment Share on other sites More sharing options...
volumax Posted January 25, 2005 Share Posted January 25, 2005 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 Quote Literally, Laterally Thinking! If you cannot get through it, go round it. Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.