Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

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

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

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.

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

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

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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:

QBI scans osC to find your shipping modules, so it will work with any shipping module that is properly coded.

 

- Adam

Link to comment
Share on other sites

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.

 

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

Link to comment
Share on other sites

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

 

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.

Link to comment
Share on other sites

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

always here to offer some useless advice....

Link to comment
Share on other sites

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.

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

Link to comment
Share on other sites

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

 

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?

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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?

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

Link to comment
Share on other sites

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!

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

Link to comment
Share on other sites

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

Thanks...email sent

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

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

Link to comment
Share on other sites

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

Also make sure that you've located the directory in the right place, and that the name is all lower case.

 

- Adam

Link to comment
Share on other sites

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?

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...