JLGarcia Posted March 31, 2005 Share Posted March 31, 2005 Hi all: I have a problem, and I`m dealing with it for 3 days without solution. The problem is that when I try to import the IIF file from Sales to quickbook it popup an error menssage saying quickbooks do not support blanks entries and that kind of things, all the other IIF files are working great, payments, custumers and items, just Sales does`nt work. Can some body give me a hand with it, or some idea? Thanks Quote Link to comment Share on other sites More sharing options...
adam5532 Posted March 31, 2005 Author Share Posted March 31, 2005 Hi all: I have a problem, and I`m dealing with it for 3 days without solution. The problem is that when I try to import the IIF file from Sales to quickbook it popup an error menssage saying quickbooks do not support blanks entries and that kind of things, all the other IIF files are working great, payments, custumers and items, just Sales does`nt work. Can some body give me a hand with it, or some idea? Thanks <{POST_SNAPBACK}> 1) Make sure that if you are using sales tax that it is set up properly in QBI. 2) Please write down the exact text of the error messages that pop up, expecially line numbers if it gives them. This will help me to troubleshoot. 3) You can send the iif file to me by private email and I will check it. Thanks, Adam Quote Link to comment Share on other sites More sharing options...
adam5532 Posted March 31, 2005 Author Share Posted March 31, 2005 Thanks Adam. Here are the two payment methods that show up in the orders table: "Visa, MasterCard or American Exp" and "Purchase Order" What should I change in QBI to match "Visa, MasterCard or American Exp" as a credit card order? Thanks again so very much. <{POST_SNAPBACK}> The Orders payment_method field is only 32 characters long, so that's why it gets cut off when it gets stored and then doesn't match. Either change the field to make it longer, or else shorten your text to 32 characters or less. If you do so, you'll need to do Payments Set-Up and Match again for the new name. Thanks, Adam Quote Link to comment Share on other sites More sharing options...
linnecards Posted April 1, 2005 Share Posted April 1, 2005 I just found this through a search. Can I use this with a mac? Quote Link to comment Share on other sites More sharing options...
jpepper Posted April 1, 2005 Share Posted April 1, 2005 As far as I understand it should be platform independent (assuming you are running Quickbooks on your Mac). The IIS file is specific to QB, not the OS. I just found this through a search. Can I use this with a mac? <{POST_SNAPBACK}> Quote Link to comment Share on other sites More sharing options...
jpepper Posted April 6, 2005 Share Posted April 6, 2005 Adam: Thanks again for all your help. I was able to get my credit card orders to work properly via QBI. Unfortunately, I need to ask another question of the group. I am having difficulties with setting up the shipping method. I am using two shipping methods: Bulk Freight Delivery and United Parcel Service. The Bulk Freight Delivery module only triggers when orders are above $2,000. So, for the most part, everyone is using the United Parcel Service shipping module. Right now when I create a QBI IIF file, I get a "Method Not Set" warning for shipping. Here is some relevant information. QBI Version 2.02 Config ----------------------- Shipping: Shipping Shipping name: Shipping Shipping account: Postage and Delivery Shipping class: [not set] Shipping taxable: [unchecked] Set Up > Shipping ------------------------ Quickbooks Methods: 3 Day Select 3rd Party Airborne DHL Federal Express Roadway UPS UPS 2nd Day UPS overnight US Mail USPS Priority Match > Shipping ------------------------ Bulk Freight Delivery: 3rd Party Federal Express: Federal Express Flat Rate: 3rd Party FREE SHIPPING!: 3rd Party Individual Shipping: 3rd Party Per Item: 3rd Party Table Rate: 3rd Party United Parcel Service - 2nd Day Air Early AM: UPS 2nd Day United Parcel Service - 3 Day Select: UPS United Parcel Service - Canada Standard: UPS United Parcel Service - Next Day Air: UPS overnight United Parcel Service - Next Day Air Early AM: UPS overnight United Parcel Service - Next Day Air Saver: UPS overnight United Parcel Service - UPS Ground: UPS United Parcel Service - Worldwide Expedited: UPS overnight United Parcel Service - Worldwide Express: UPS overnight United Parcel Service - Worldwide Express Plus: UPS overnight United States Postal Service - Express Mail: USPS Priorit United States Postal Service - Parcel Post: US Mail United States Postal Service - Priority Mail: USPS Priority Zone Rates: UPS I suspected that the problem is with the actual match routine in QBI. So I went to see how OSC stores the shipping methods in its tables. I could only find one reference to shipping method in the OSC tables. It is in orders_total. Here is an excerpt for that table. orders_id title text value class sort_order 31 Sub-Total: $95.90 95.9000 ot_subtotal 1 31 United Parcel Service (1 x 7lbs) (Ground): $14.00 14.0000 ot_shipping 2 31 Total: <b>$109.90</b> 109.9000 ot_total 800 33 Sub-Total: $51.22 51.2200 ot_subtotal 1 33 United Parcel Service (1 x 6lbs) (Ground): $13.67 13.6700 ot_shipping 2 33 Total: <b>$64.89</b> 64.8900 ot_total 800 34 Sub-Total: $64.95 64.9500 ot_subtotal 1 34 United Parcel Service (1 x 5lbs) (2nd Day Air): $12.90 12.9000 ot_shipping 2 As you can see, shipping method is stored as the following for these sample orders: United Parcel Service (1 x 7lbs) (Ground) United Parcel Service (1 x 6lbs) (Ground) United Parcel Service (1 x 5lbs) (2nd Day Air) I suspect that QBI gets a false match because of the variations in the way OSC stores this shipping data. Or is there another table that stores shipping method? Ultimately what I need is to somehow know the shipping method when we print out a sales receipt from Quickbooks. This is particularly important because we need to know if we should ship the order via UPS Groud or 2nd Day Air (or other UPS method). Right now we have no way of knowing how to ship the item. As a work around is it possible to include the 'title' field from the 'order_total' table as a comment or even a line item on the sales receipt or invoice? Thanks so very much! Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 7, 2005 Author Share Posted April 7, 2005 Adam: Thanks again for all your help. I was able to get my credit card orders to work properly via QBI. Unfortunately, I need to ask another question of the group. I am having difficulties with setting up the shipping method. <{POST_SNAPBACK}> This is due to an inconsistency in the UPS shipping module you are using. In the code it uses "Ground" as the method, but in the language files that QBI extracts the shipping types from, it is listed incorrectly as "UPS Ground". Go to Catalog:Includes:Languages:English:Modules:Shipping:UPS. On about line 15, change "UPS Ground" to "Ground". Do this for the other languages you are using as well. Now go to QBI Match:Shipping. By just going to the page, the tables should update automatically and you should see listed "United Parcel Service - Ground" instead of "United Parcel Service - UPS Ground". Thanks, Adam Quote Link to comment Share on other sites More sharing options...
jpepper Posted April 7, 2005 Share Posted April 7, 2005 Thanks so very much Adam. I made the change you suggested below and it worked. The only problem is that it only works for UPS Ground shipments. I have a customer that requested shipping via 2nd Day Air and his IIF export shows Method Not Set for his shipping. Based on the matches I set upin QBI, 2nd Day Air should link to UPS overnight in Quickbooks. Here are my shipping matches from QBI... United Parcel Service - 2nd Day Air Early AM: UPS 2nd Day United Parcel Service - 3 Day Select: UPS United Parcel Service - Canada Standard: UPS United Parcel Service - Next Day Air: UPS overnight United Parcel Service - Next Day Air Early AM: UPS overnight United Parcel Service - Next Day Air Saver: UPS overnight United Parcel Service - Ground: UPS United Parcel Service - Worldwide Expedited: UPS overnight United Parcel Service - Worldwide Express: UPS overnight United Parcel Service - Worldwide Express Plus: UPS overnight Do I need to make additional changes in the language file? define('MODULE_SHIPPING_UPS_TEXT_TITLE', 'United Parcel Service'); define('MODULE_SHIPPING_UPS_TEXT_DESCRIPTION', 'United Parcel Service'); define('MODULE_SHIPPING_UPS_TEXT_OPT_GND', 'Ground'); define('MODULE_SHIPPING_UPS_TEXT_OPT_1DM', 'Next Day Air Early AM'); define('MODULE_SHIPPING_UPS_TEXT_OPT_1DA', 'Next Day Air'); define('MODULE_SHIPPING_UPS_TEXT_OPT_1DP', 'Next Day Air Saver'); define('MODULE_SHIPPING_UPS_TEXT_OPT_2DM', '2nd Day Air Early AM'); define('MODULE_SHIPPING_UPS_TEXT_OPT_3DS', '3 Day Select'); define('MODULE_SHIPPING_UPS_TEXT_OPT_STD', 'Canada Standard'); define('MODULE_SHIPPING_UPS_TEXT_OPT_XPR', 'Worldwide Express'); define('MODULE_SHIPPING_UPS_TEXT_OPT_XDM', 'Worldwide Express Plus'); define('MODULE_SHIPPING_UPS_TEXT_OPT_XPD', 'Worldwide Expedited'); Again, thanks so very much. I'm sorry for all the questions. Go to Catalog:Includes:Languages:English:Modules:Shipping:UPS. On about line 15, change "UPS Ground" to "Ground". Do this for the other languages you are using as well. Now go to QBI Match:Shipping. By just going to the page, the tables should update automatically and you should see listed "United Parcel Service - Ground" instead of "United Parcel Service - UPS Ground". <{POST_SNAPBACK}> Quote Link to comment Share on other sites More sharing options...
wroughtec Posted April 11, 2005 Share Posted April 11, 2005 Great package but like a few I will need to wait for the UK version. I presume this will also include all UK Shipping options both couriers and Royal Mail options (www.royalmail.com) Anyway I will keep checking for updates. :thumbsup: Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 11, 2005 Author Share Posted April 11, 2005 Great package but like a few I will need to wait for the UK version. I presume this will also include all UK Shipping options both couriers and Royal Mail options (www.royalmail.com) Anyway I will keep checking for updates. :thumbsup: <{POST_SNAPBACK}> QBI scans osC to find your shipping modules, so it will work with any shipping module that is properly coded. - Adam Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 11, 2005 Author Share Posted April 11, 2005 Thanks so very much Adam. I made the change you suggested below and it worked. The only problem is that it only works for UPS Ground shipments. I have a customer that requested shipping via 2nd Day Air and his IIF export shows Method Not Set for his shipping. Based on the matches I set upin QBI, 2nd Day Air should link to UPS overnight in Quickbooks. <{POST_SNAPBACK}> Sorry, I have been quite busy so I have not had time to look at this yet. Try all your shipping methods and see if any others do not work. Then, it is a matter of tracking down where the text is not matching correctly. - Adam Quote Link to comment Share on other sites More sharing options...
jpepper Posted April 12, 2005 Share Posted April 12, 2005 Sorry, I have been quite busy so I have not had time to look at this yet. Try all your shipping methods and see if any others do not work. Then, it is a matter of tracking down where the text is not matching correctly. - Adam <{POST_SNAPBACK}> Thanks Adam! I followed your advice and ran some test orders with the different shipping methods. It turns out that the only one that was acting up was UPS 2nd Day Air (2DA). The problem was that there was no language definiation for that method in the language file. I added the definition and the problem was solved. Thanks so very much. Quote Link to comment Share on other sites More sharing options...
gazzzzzza Posted April 13, 2005 Share Posted April 13, 2005 Adam This looks like a fantastic contribution and hopefully will be very useful to me as Quickbooks severely confuses me!! It certainly seems to be popular amongst the community :D I have a problem with making use of the contribution though. I have built a new admin area to use for clients OSC based websites as the old one offered too many dangerous options for a regular admin user of the site to use and I felt that it would be easier to add new site admin functionality through my own admin area for things not linked to OSC and even some functionality that is. This is where my first problem occurs. Using your contribution is not really an ideal solution for me as it involves using the old default OSC admin panel. I would like to make use of your functionality and modify it to fit my admin panel if possible. I have built an export of orders which exports the data as either a CSV or an XLS file. My problem is that I do not know enough about quickbooks to know how to import the details correctly. Would it be possible for you to provide me with the column names etc that you use so I can layout my data correctly for the import? I have installed your contribution onto the original OSC admin panel but when I try to import I get the same error as alot of people. (Transaction is not in balance ...) I have tried setting tax to be the same in both QBI and QB and tried exporting and importing with tax off but I cannot get it to work. The initial site I am testing on uses tax at 17.5% and the tax class is called 'Taxable Goods' in OSC. THe client sells some items which require tax and some which do not - these have a tax class of Non-Taxable. Do you have any ideas on where I need to setup tax things and what I should set up within both QB + QBI? I have also had trouble inporting the product list. This may be due to the fact I have modified the OSC products to include extra data like carbohydrates, calories etc etc. (THese are all added through the product adding function in my admin area). Do you think this will affect your contribution and make it so that I cannot get it to work? I do not get any error messages but cannot find the products anywhere within QB. This may be down to my limited knowledge of the program though!! I managed to get the customers to import into QB and I have also modified the tables relating to them by adding extra fields. This makes me think that maybe this is not the issue with my failure importing products. Basically, I would be grateful for any help on any of the above issues as quickbooks is proving to be a bit of a nightmare for me. I am just a programmer not an accountant and have no experience with the program :blink: And I noticed one person waaaay back on page 5 of this topic was asking about using offline orders with OCS. I built functionality to allow people to add orders through the admin console aswell as add users etc. This enables my client to put all of his orders through OSC. I'm sure programmers with the skills on show here would be able to replicate similar functionality into the original OSC admin. This would be a very useful conribution I believe. The only problem with the idea is that people who order offline do not always have email addresses so signing them up for an account is not easy unless you modify OSC to use usernames instead of email addresses for the login etc. Obviously they cannot purchase via OSC without an email address but it does allow you to get them into the system. Unfortunatley, I have not made a single modification to the default OSC admin and do not know my way around the code at all so I am not currently able to build this contribution :( It took me months to figure out the front end so I could modify it at all !! anyways - sure i've taken up enough of everyones time. Cheers Gareth Quote always here to offer some useless advice.... Link to comment Share on other sites More sharing options...
adam5532 Posted April 14, 2005 Author Share Posted April 14, 2005 Adam This looks like a fantastic contribution and hopefully will be very useful to me as Quickbooks severely confuses me!! It certainly seems to be popular amongst the community :D I have a problem with making use of the contribution though. I have built a new admin area to use for clients OSC based websites as the old one offered too many dangerous options for a regular admin user of the site to use and I felt that it would be easier to add new site admin functionality through my own admin area for things not linked to OSC and even some functionality that is. This is where my first problem occurs. Using your contribution is not really an ideal solution for me as it involves using the old default OSC admin panel. QBI does not need the osC admin panel at all to function. You just need to modify admin/includes/qbi_page_top.php and qbi_page_bot.php to remove it. I would like to make use of your functionality and modify it to fit my admin panel if possible. I have built an export of orders which exports the data as either a CSV or an XLS file. My problem is that I do not know enough about quickbooks to know how to import the details correctly. Would it be possible for you to provide me with the column names etc that you use so I can layout my data correctly for the import? Sorry, but I have my hands full supporting and improving QBI. Information on the QB iif file format is available in the QB program help and in the QB website knowlege base. I have installed your contribution onto the original OSC admin panel but when I try to import I get the same error as alot of people. (Transaction is not in balance ...) I have tried setting tax to be the same in both QBI and QB and tried exporting and importing with tax off but I cannot get it to work. The initial site I am testing on uses tax at 17.5% and the tax class is called 'Taxable Goods' in OSC. THe client sells some items which require tax and some which do not - these have a tax class of Non-Taxable. Do you have any ideas on where I need to setup tax things and what I should set up within both QB + QBI? If you charge tax in osC, then tax must be turned on in QB and QBI. The tax settings for QBI must exactly match your tax item in QB. If you also have a low order fee, this must be set up in QBI. If you are using discounts or coupons, this may create an error which is fixed in the next not-released-yet version. If this doesn't help locate the cause, email me the iif file to check. Also, if you get any error messages in Quickbooks, please tell me what they are (including the line numbers, if any). I have also had trouble inporting the product list. This may be due to the fact I have modified the OSC products to include extra data like carbohydrates, calories etc etc. (THese are all added through the product adding function in my admin area). Do you think this will affect your contribution and make it so that I cannot get it to work? I do not get any error messages but cannot find the products anywhere within QB. This may be down to my limited knowledge of the program though!! Product import only supports one level of attributes, and has a little problem at that. This has been fixed in the upcoming version, which also supports two levels of attributes. You must give each product a "model" in osC. If you still have trouble, email me your products iif. I built functionality to allow people to add orders through the admin console aswell as add users etc. <{POST_SNAPBACK}> That should not be any problem for QBI, as it reads data from the tables and it doesn't care how it got there. Thanks, Adam Quote Link to comment Share on other sites More sharing options...
stiksandstones Posted April 15, 2005 Share Posted April 15, 2005 The ! should not be there. The headers have the !, and the detail line doesn't. The second ENDTRANS should not have it. I think you are just causing QB to ignore the whole transaction by changing it. It does appear to add up correctly. Is you shipping set as taxable or non-taxable in QB? Also, please tell me which line is line 13, then we'll know where the error is. Thanks, Adam <{POST_SNAPBACK}> I get the error when importing the ORDERS iif to QB too, with error on line number...so I looked at the line numbers and its all on the line with !ENDTRNS. I tried taking the ! out, but same errors, can anyone help? Quote Link to comment Share on other sites More sharing options...
drsatch Posted April 15, 2005 Share Posted April 15, 2005 Ok I've searched and searched and can't find the answer. Certainly I'm not the only one to run into this... Quickbooks limits the amount of products/accounts to 15,000 (correct me if I'm wrong) unless I'm using the Enterprise version. My store is automatically updated everyday which includes active and inactive items. I'm over the 15,000 product limit when including the inactive products and only around 6000 not including them. My question is...how can I import only the active items instead of both active and inactive? Thanks in advance! Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 15, 2005 Author Share Posted April 15, 2005 I get the error when importing the ORDERS iif to QB too, with error on line number...so I looked at the line numbers and its all on the line with !ENDTRNS.I tried taking the ! out, but same errors, can anyone help? <{POST_SNAPBACK}> This just indicates that there is a problem in that transaction, not that there is a problem on that particular line. If it is an out-of-balance error, then sales tax, fees, discounts (may be a program error), etc is not set up correctly. Please send me a copy (to my email in the instruction manual) of your iif file (without any changes made to it) and I would be glad to check it for you. Thanks, Adam Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 15, 2005 Author Share Posted April 15, 2005 Ok I've searched and searched and can't find the answer. Certainly I'm not the only one to run into this... Quickbooks limits the amount of products/accounts to 15,000 (correct me if I'm wrong) unless I'm using the Enterprise version. My store is automatically updated everyday which includes active and inactive items. I'm over the 15,000 product limit when including the inactive products and only around 6000 not including them. My question is...how can I import only the active items instead of both active and inactive? Thanks in advance! <{POST_SNAPBACK}> There isn't currently a way to do that, as no-one has brought up this particular issue before! But, it is easy to implement and I will try to add an option in the next version to import or not import inactive items. But, I hope you are aware that importing items from QBI is essentially meant to be a one-time event. There will be a feature in the next version to allow you to import only new products, as you add them to osC. But if you try to import a product into QB that is already there, all the fields (vendor, cost, etc) will get over-written. That's the unfortunate behavior of iif files. The next version will also have a way for you to set a default item in QB, which was also requested by someone with a similar product limit issue. But if you do this, all products will import as the same item, so it won't be helpful if you keep inventory in QB. Thanks, Adam Quote Link to comment Share on other sites More sharing options...
stiksandstones Posted April 16, 2005 Share Posted April 16, 2005 This just indicates that there is a problem in that transaction, not that there is a problem on that particular line. If it is an out-of-balance error, then sales tax, fees, discounts (may be a program error), etc is not set up correctly. Please send me a copy (to my email in the instruction manual) of your iif file (without any changes made to it) and I would be glad to check it for you. Thanks, Adam <{POST_SNAPBACK}> Thanks...email sent Quote Link to comment Share on other sites More sharing options...
stiksandstones Posted April 17, 2005 Share Posted April 17, 2005 Thanks...email sent <{POST_SNAPBACK}> I sent to the email [email protected] is that the one? Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 18, 2005 Author Share Posted April 18, 2005 I sent to the email [email protected] is that the one? <{POST_SNAPBACK}> Look again. - Adam Quote Link to comment Share on other sites More sharing options...
alark Posted April 21, 2005 Share Posted April 21, 2005 I'm getting this error when I try to create any iif files: Warning: fopen(qbi_output/qbi_cust.iif): failed to open stream: Permission denied I've already set read/write permissions on this folder. Also, it only allows me to choose to download the order for the current month, nothing before then. Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 21, 2005 Author Share Posted April 21, 2005 I'm getting this error when I try to create any iif files: Warning: fopen(qbi_output/qbi_cust.iif): failed to open stream: Permission denied I've already set read/write permissions on this folder. QBI can't create the iif file. It pretty much has to be just what the error message says. Recheck that permissions are set to 777, and that it applies to files in the folder as well as the folder itself. Also, it only allows me to choose to download the order for the current month, nothing before then. <{POST_SNAPBACK}> I'm not following -- QBI doesn't care what the date is. Your choices are new orders, previous batch of orders, or all previous orders. When you first install QBI, all your existing orders are previous orders, and any that come in after that will be your first batch of new orders. Thanks, Adam Quote Link to comment Share on other sites More sharing options...
adam5532 Posted April 21, 2005 Author Share Posted April 21, 2005 I'm getting this error when I try to create any iif files: Warning: fopen(qbi_output/qbi_cust.iif): failed to open stream: Permission denied <{POST_SNAPBACK}> Also make sure that you've located the directory in the right place, and that the name is all lower case. - Adam Quote Link to comment Share on other sites More sharing options...
knchris Posted April 27, 2005 Share Posted April 27, 2005 Installed latest v2.02 (upgraded from v1.something) and I have a few questions: --When I click create iif:(all previous orders)download iif file, i get the following SQL error: 1064 - You have an error in your SQL syntax near '79-83 Pickup' AND pov1.products_options_values_id=pov2.products_options_values_' at line 1 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=''79-83 Pickup' AND pov1.products_options_values_id=pov2.products_options_values_id AND pov2.language_id='1' I noticed the 2 single quotes in the query, so i changed that entry in the products_options_values table and the same error still comes up. Refreshed multiple times, restarted browser, etc... with no avail. weird, any ideas? --The downloaded customer iif file seems to give me an iif of customers which have made orders. If a customer has ordered more than once, he shows up that many times. What's the deal there? --The new orders section says 0 even though i've had orders since upgrading. Do I need to have matched all my products in order for it to work properly? Quote 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.