Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Joe,

 

First, make sure you have already done Set Up: Shipping and Match: Shipping.

 

Then, using phpMyAdmin or similar, look your db tables. Go to the table ot_total, and find an order that is having a problem. Find the line where the field "class" says "ot_shipping". Then, go to the field "title" for that row. It will say something like, "United Parcel Service (1 x 0.84lbs) (Ground):" Do the same for one of your flat rate orders.

 

Now, go to the tale qbi_shiposc. Find the line for the shipping method you are revising. Look at qbi_shiposc_car_text and qbi_shiposc_serv_text. For the example above, this should say "United Parcel Service" and "Ground" (the service is in the parenthesis). If not, correct it. For example, if it says "UPS Ground" instead of just "Ground" it needs to be changed. You'll need to do this for each language that you have installed, so there may be two or more lines for each shipping method. The service can be left blank if there is no service.

 

Thanks,

Adam

 

 

Thanks Adam for your time in providing this information. I had interesting results. I followed your directions and confirmed the mismatch of "Ground" and "UPS Ground". I made the change to the value in the qbi_shiposc table within the qbi_shiposc_serv_text column. I changed "UPS Ground" to "Ground". This indeed did work. I successfully exported an IIS file and imported to Quickbooks Pro 2005 and the previous "Method not Set" was now correct: "UPS" in Quickbooks. However, as soon as I touched the QBI, Match, Shipping administration page (no updates, just view), the qbi_shiposc_serv_text, "Ground" value would immediately revert back to "UPS Ground".

 

So I reversed the thought process. I edited /includes/modules/shipping/ups.php.

 

I changed On line #58:

 

'GND' => 'Ground',

 

To:

 

'GND' => 'UPS Ground',

 

I then placed a new order, exported to IIF, Imported to Quickbooks and it was successful. I then investigated the orders_total table, found the new order number (just created previously) and looked at the "title" column value for the new order. It now showed: "United Parcel Service (1 x 0.5lbs) (UPS Ground):" where it had previously had shown: "United Parcel Service (1 x 0.5lbs) (Ground):" for the previous orders.

 

This is a successful fix. I am OK with this fix but don't know why touching the QBI, Match, Shipping would produce a refresh of the qbi_shiposc table values. If you have a fix for that, it may help others. However, my change is working for me. I will be checking all my other shipping options by importing into Quickbooks and verifying that Method Not Set is not shown soon and will let you know if anything else serious pops up.

 

Anyway, thanks VERY much for your time and answer Adam. I really appreciate it.

 

kivimagi, This should help. Let me know, and I will help of I can.

 

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

 

By the way, this may help someone. When on my local Windows developemnt server, I was easily able to do a grep type command in Windows using the "findstr" command. Here is how I found all the files in my dev web server instantly that had the word "Ground" within it which pointed me to edit the /includes/modules/shipping/ups.php file as mentioned above:

 

At a command prompt, change directory on your windows development server to your /Apache/htdocs folder. Run this command:

 

findstr /s /c:"Ground" *.php

 

It will produce every php in osCommerce that has the word "Ground". I thought this could make life easier for others that didn't know this.

 

Hope this all helps...

 

Joe

Link to comment
Share on other sites

... However, as soon as I touched the QBI, Match, Shipping administration page (no updates, just view), the qbi_shiposc_serv_text, "Ground" value would immediately revert back to "UPS Ground".

This happens because every time the Match:Shipping page loads, it runs a function that goes out and looks at your shipping modules to see if you have new ones or something has changed and then it refreshes the list.

 

I thought it was a pretty clever system :D , but then these occasional discrepencies came up. I'll be looking at this and making some revisions so that this problem doesn't happen in version 3.0.

 

BTW, you can temporarily disable the shipping module update routine by commenting out

FILE: qbi_shipmatch.php

LINE: 40

CODE: ship_methods();

 

Thanks,

Adam

Link to comment
Share on other sites

This happens because every time the Match:Shipping page loads, it runs a function that goes out and looks at your shipping modules to see if you have new ones or something has changed and then it refreshes the list.

 

I thought it was a pretty clever system :D , but then these occasional discrepencies came up. I'll be looking at this and making some revisions so that this problem doesn't happen in version 3.0.

 

BTW, you can temporarily disable the shipping module update routine by commenting out

FILE: qbi_shipmatch.php

LINE: 40

CODE: ship_methods();

 

Thanks,

Adam

 

Looks like I had to comment it out to get Flat Rate to import properly. The qbi_shiposc table, qbi_shiposc_serv_text field was blank. The only way to populate the value with "Flat Rate" and keep it that way was to permanently disable the ship_methods() as you mentioned above. All is working now...thanks.

 

Joe

Link to comment
Share on other sites

UPD8: It seems that the meat of this problem lays in whats getting taxed vs. whats getting displayed.

 

Sub-Total: $100.00

United Parcel Service (1 x 10.307lbs) (Ground): $11.75

Discount Coupons:FAM: -$20.00

Sales Tax: $5.80 (7.25%)

Total: $99.00

 

no matter how u add it, if you try doing so it doesn't work out... until you try it with sales tax on unmodified subtotal [$100, making tax $7.25] and things start to click. so! the tax is being computed for the non-discounted subtotal.

 

Alternately, if I set config modules -> order total -> discount -> include tax to true, due to the Order of Operations, I'm getting a proper total of 97.55 (see below)... however, tax is still showing at $5.80 when clearly 7.25% tax on 100 is $7.25. I believe this is the problem when I try to import the related .iif file, which is at http://nettrip.org/temp/qbi_orders-20051121.iif , into quickbooks: it detects that the tax was figured wrong so the tax should be different or the total should be different. Does that sound right? Is quickbooks astute enough for this to cause the dreaded, non-specific shallow error "Transaction is out of balance?" Anyone know of any way around this?

 

Also, I am having trouble figuring out what to do about customers. My client already has 3200 first name-space-last name type customers. Does anyone know of a raw way to edit a QBB or QB database file? ..kuz if I could add "-1" to all their names without using the QB UI to merge one-by-one [by clicking the Client Name-1 and choosing "edit", removing the -1, answering yes to "This customer already exists...merge?", closing the client detail screen, and moving to the next]. Any ideas or solutions that ANYONE has are more than welcome :)

 

Thanks again everyone, and esp. adam, for this amazing contrib.

 

allen

 

--

 

Sub-Total: $100.00

United Parcel Service (1 x 8.25lbs) (Ground): $11.75

Discount Coupons:FAM: -$21.45

Sales Tax: $5.80

Total: $97.55

Link to comment
Share on other sites

After installation, my first click of the "Quickbooks Import QBI" selection in the catalog box of the admin panel shows this message:

 

"Quickbooks Import QBI

Install QBI database tables for version 2.10?"

 

Hitting the button takes me to this point:

 

"Quickbooks Import QBI

Database table installation successfull.

 

Now you must go to the configuration page."

 

Clicking the link to the configuration page takes me back to:

 

"Quickbooks Import QBI

Install QBI database tables for version 2.10?"

 

I've tried uninstalling and reinstalling several times to no avail. The only thing weird I can find is that there is no table 'qbi_taxes' in the database although it is listed in 'database_tables.php'.

 

Thanks in Advance,

Bobby

Link to comment
Share on other sites

... Does that sound right? Is quickbooks astute enough for this to cause the dreaded, non-specific shallow error "Transaction is out of balance?" Anyone know of any way around this?

There's a few problems:

 

1) Your discount is getting added instead of subtracted (all lines items in an iif should be negative, except for discounts which should be positive) -- it is backwards of logical. The total should be positive, and if you add them all together you should get zero. This was a bug in QBI -- please upgrade to version 2.10a if your version is older than 2.10.

 

2) The tax and the total are being pulled from the order_totals table -- QBI doesn't calculate them, except that it rounds the numbers to avoid errors caused by osC.

 

3) I notice that the discount item is being set as "non-taxable" in the iif file.

 

4) $5.80 / .0725 (tax rate 7.25%) is $80. I don't get $80, no matter how I add up the line items. Make sure that the tax rate entered in the QBI configuration page exactly matches the rate entered in osC. QBI can not presently support multiple tax rates. Also, check that no line items are missing in the iif file.

 

5) I'm not clear from your description if you want the tax to apply to the pre or post discount amount. That is going to depend on the tax laws in your state.

 

6) Compare every item and total line for the order in the iif file with the invoice screen for the order in osC. Then you can see exactly which line in the iif is wrong or missing. Or, you'll see if there is a problem in osC or another contribution.

Also, I am having trouble figuring out what to do about customers. My client already has 3200 first name-space-last name type customers. Does anyone know of a raw way to edit a QBB or QB database file? ..kuz if I could add "-1" to all their names without using the QB UI to merge one-by-one [by clicking the Client Name-1 and choosing "edit", removing the -1, answering yes to "This customer already exists...merge?", closing the client detail screen, and moving to the next]. Any ideas or solutions that ANYONE has are more than welcome :)

I'm not following exactly what you want to do, but the easiest way to do mass edits in QB is to export your customer list as an iif file or an Excel file. From there, you can work with it in Excel or in a database program. Then import it back to QB. If using iif, save as text instead of Excel format, and do a test run on a copy of the company file to make sure there's no problems.

 

Thanks,

Adam

Edited by adam5532
Link to comment
Share on other sites

After installation, my first click of the "Quickbooks Import QBI" selection in the catalog box of the admin panel shows this message:

 

"Quickbooks Import QBI

Install QBI database tables for version 2.10?"

 

Hitting the button takes me to this point:

 

"Quickbooks Import QBI

Database table installation successfull.

 

Now you must go to the configuration page."

 

Clicking the link to the configuration page takes me back to:

 

"Quickbooks Import QBI

Install QBI database tables for version 2.10?"

 

I've tried uninstalling and reinstalling several times to no avail. The only thing weird I can find is that there is no table 'qbi_taxes' in the database although it is listed in 'database_tables.php'.

 

Thanks in Advance,

Bobby

 

Make sure that you've added the QBI tables to the database_tables.php file as instructed in the installation instructions, and that there's no typos or end of line markers missing where you inserted them in the file. QBI is unable to find the qbi_config table.

 

Thanks,

Adam

Link to comment
Share on other sites

I am facing problem of having control over suppliers invoices which are duplicated at times and overlooked at times when they are due.All i need is if this module can solve following :-

Suppliers invoices track

Suppliers amount paid and to be paid.

Customers payment track

Vat payable.

If some one who has used it and can reply to above then it will be appreciated please.

Regards.

luqi

Link to comment
Share on other sites

Hey Adam,

 

I added a new contribution to my site "Purchase Without Account v0.91 (PWA)" and everything is fine with QBI imports to Quickbooks 2005 with a very small problem so I just have a simple question about where QBI pulls the first and last names fields from the database. When I do an IIF Export from QBI, users that have purchased without an account show up like this in the exported IIF file and in Quickbooks:

 

This is a snippet from a QBI IIF Export from a user that has an account:

 

CUST Smith, Frank-11 Frank Smith 123 Fake Way Beverly Hills CA 90210 Frank Smith 123 Fake Way Beverly Hills CA 90210 555-555-5555 [email protected] Frank Smith Check/Money Order N Sales Tax Frank Smith

 

This is a snippet from a QBI IIF Export from a user that has purchased without an account:

 

CUST , -12 Jim Jones 456 Fake Way Beverly Hills CA 90210 Jim Jones 456 Fake Way Beverly Hills CA 90210 [email protected] Jim Jones Check/Money Order Y Sales Tax

 

The only difference here is that the QBI is not pulling the Last, First name from PWA customers as I have set it to do in the QBI Configuration screen: %L15W, %F15W-%I

 

This is not that bad and I am not asking for any help here for modifications or integration with PWA or other contributions (I am sure this is not in your charter). All I really want to know is exactly what database table and fields are being used by QBI IIF exports to pull data from the "%L" and "%F" variables. I am sur these are blank for PWA orders and I want to get PWA to populate these fields. If you have a moment to answer this, I would be very much appreciate it.

 

Thanks,

 

Joe

Link to comment
Share on other sites

Hey Adam,

 

I added a new contribution to my site "Purchase Without Account v0.91 (PWA)" and everything is fine with QBI imports to Quickbooks 2005 with a very small problem so I just have a simple question about where QBI pulls the first and last names fields from the database. When I do an IIF Export from QBI, users that have purchased without an account show up like this in the exported IIF file and in Quickbooks:

 

....

 

This is not that bad and I am not asking for any help here for modifications or integration with PWA or other contributions (I am sure this is not in your charter). All I really want to know is exactly what database table and fields are being used by QBI IIF exports to pull data from the "%L" and "%F" variables. I am sur these are blank for PWA orders and I want to get PWA to populate these fields. If you have a moment to answer this, I would be very much appreciate it.

 

Thanks,

 

Joe

Joe,

 

QBI pulls the first and last name and fax number from the Customers table, and the rest of the customer information from the Orders table. Unfortunately, this is necessary, as the Orders table only has one name field, and the Customers table has a first and last name field. The fax field only appears in the Customers table. But, the Customers table only holds one address, where the Orders table holds the billing and shipping addresses. Go figure! So, because of this, QBI has to pull from a combination of both tables.

 

There's no way to get around this in the QBI config screen, but you could go into the code and make changes. I do plan to support integration with your contribution, and I will fix this issue in the upcoming version 3.0 or 3.1.

 

Thanks,

Adam

Link to comment
Share on other sites

I am facing problem of having control over suppliers invoices which are duplicated at times and overlooked at times when they are due.All i need is if this module can solve following :-

Suppliers invoices track

Suppliers amount paid and to be paid.

Customers payment track

Vat payable.

If some one who has used it and can reply to above then it will be appreciated please.

Regards.

luqi

Lugi,

QBI only imports customer invoices and sales -- it does not have anything to do with supplier or vendor invoices - it is assumed that you will handle all of that within Quickbooks. Same with taxes -- it will import the tax information for each sale, but tracking of taxes is done using the features of Quickbooks.

 

Also, I notice you mention VAT. QBI only works with US versions of Quickbooks and is not compatible with other versions, with or without taxes enabled. Support for other versions will be added in version 3.0, due pretty soon!

 

Thanks,

Adam

Link to comment
Share on other sites

Hey Adam,

 

1) Are you sure the iif file I provided shows evidence of that error?? I changed that line according to the post quite some time ago.. but just to be sure, i replaced the whole file, re-downloaded an iif and did a compare. nothing's changing so far as whats positive and whats negative...

 

2) That's sort of what I figured. So, the discount coupon module could be to blame somehow for that part. BUT what i'd really like to know is, will quickbooks catch an incorrect tax amount and give this error, or any other...OR should it let the transaction go through as long as the totals seem to match?

 

3) thru 5): the tax rates are set correctly. 100 [item price] - $20 [1/5 coupon] and b4 tax does =80. right now what it seems to be doing is taking the subtotal, adding 7.25% tax to it [$100+$7.25=$107.25], figuring out what 1/5 of that is for the discount [107.25 * 1/5 = 21.45]. then it must take 100 + 7.25 - 21.45 + 11.75 shipping to arrive at 97.55...which is the correct answer...but IDEALLY what i'd like to have happen is, as california tax law states is proper: perform the discount first, 100-20 = 80, get 7.25 tax on that (5.80) and add that and shipping to 80 to get 97.55 as well. it seems osC discount module is not doing what its supposed to then..

 

6) i compared them line by line and they match. osC allowed me to check out even though things didn't quite add up. I think its just qb thats catching it..

 

also, i dont see anywhere in quickbooks that will allow you to specify what kind of export to do! any idea where that might appear in qb pro 2003?

 

thanks a mil!

allen

 

There's a few problems:

 

1) Your discount is getting added instead of subtracted (all lines items in an iif should be negative, except for discounts which should be positive) -- it is backwards of logical. The total should be positive, and if you add them all together you should get zero. This was a bug in QBI -- please upgrade to version 2.10a if your version is older than 2.10.

 

2) The tax and the total are being pulled from the order_totals table -- QBI doesn't calculate them, except that it rounds the numbers to avoid errors caused by osC.

 

3) I notice that the discount item is being set as "non-taxable" in the iif file.

 

4) $5.80 / .0725 (tax rate 7.25%) is $80. I don't get $80, no matter how I add up the line items. Make sure that the tax rate entered in the QBI configuration page exactly matches the rate entered in osC. QBI can not presently support multiple tax rates. Also, check that no line items are missing in the iif file.

 

5) I'm not clear from your description if you want the tax to apply to the pre or post discount amount. That is going to depend on the tax laws in your state.

 

6) Compare every item and total line for the order in the iif file with the invoice screen for the order in osC. Then you can see exactly which line in the iif is wrong or missing. Or, you'll see if there is a problem in osC or another contribution.

 

I'm not following exactly what you want to do, but the easiest way to do mass edits in QB is to export your customer list as an iif file or an Excel file. From there, you can work with it in Excel or in a database program. Then import it back to QB. If using iif, save as text instead of Excel format, and do a test run on a copy of the company file to make sure there's no problems.

 

Thanks,

Adam

Link to comment
Share on other sites

Hey Adam,

 

1) Are you sure the iif file I provided shows evidence of that error?? I changed that line according to the post quite some time ago.. but just to be sure, i replaced the whole file, re-downloaded an iif and did a compare. nothing's changing so far as whats positive and whats negative...

That's what it looks like -- the discount should be positive, and the items negative. If it were negative, then it would be adding a surcharge. I don't know if it's an issue with the discount module -- you'll need to determine that, and adjust QBI if necessary to work with it (inlcude the - sign or not).

...

 

6) i compared them line by line and they match. osC allowed me to check out even though things didn't quite add up. I think its just qb thats catching it..

First, you need to fix your discount module in osC. Everything must add upcorrectly. osC might let it through, but QB won't. You've got to elimiate all the osC errors before trying to use QBI.

also, i dont see anywhere in quickbooks that will allow you to specify what kind of export to do! any idea where that might appear in qb pro 2003?

ur right! File:Export:Lists to iif Files

That's ok, because you can open an iif in Excel, do your edits, and then do File:Import:iif Files. When it asks if you want to save it as an Excel file, say no to keep it in text format.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam --

 

Wanted to let you know that we JUST installed Version 6 of Enterprise Solutions (aka QB2006) and QBI works with like it did with vsn 4.

 

BJ Chadduck

MilitaryWives.com

Thanks for letting me know! I just bought QB Pro 2006, but haven't installed it yet. However, I did confirm with Intuit that iif import was still supported! That's good, as QBI's XML eport isn't ready yet!

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Folks,

I'm reading over this thread and wonder does this contribution allow a user to import as well as export data to QB

You may import your products list, shipping methods, discounts/charges, and payment methods, so that you can match your QB names to your osC names for these things. In the future, QBI will also be able to update inventory, for those that sell both on and off line. Otherwise, QBI's main purpose is to export data to QB, and all other financial functions you do in QB as it is designed to be an accounting system and osC isn't.

 

Thanks,

Adam

Link to comment
Share on other sites

  • 2 weeks later...

hello all, and thanks to Adam for a neat contribution. sorry for my newb question. i know nothing about QB and won't be configuring the app, but once installed, i did try to pull a couple of iif files. only the customers and products ran, the orders iif creation returned a 1064 which referenced line 180 in qbi_functions.php. so i cheated and typed in

 

$result=tep_db_query("select countries_id from ".TABLE_COUNTRIES." where countries_name like 'United States'");

 

just to see what happened and it ran. now predictably, the client says the imports aren't importing complete data. i told them to RTFM and finish configuration with the uploads of whatever QBI organization they have.

 

so two questions:

 

1. they do in fact have to run the uploads that set QBI up with their whatever-you-call-ems to make sensible imports, right?

 

2. what's the deal with the 1064 i was getting? is this because of lack-of-configuration as above? should i return the code to original condition and let them finish configuring?

 

i guess that was more than two questions. but thanks in advance....

Link to comment
Share on other sites

...

so two questions:

 

1. they do in fact have to run the uploads that set QBI up with their whatever-you-call-ems to make sensible imports, right?

 

2. what's the deal with the 1064 i was getting? is this because of lack-of-configuration as above? should i return the code to original condition and let them finish configuring?

 

i guess that was more than two questions. but thanks in advance....

1) You must completely configure Quickbooks and everything needs to be working properly.

2) osC needs to be configured and working properly.

3) Then, you need to completely configure QBI on the QBI configuration page, to match your settings in QB.

4) Then, you need to do Set-up and Match in QBI.

4) Also, it is important that you completely read the instruction manual for QBI.

 

I would not alter any code until it is all set up, and you shouldn't need to alter the code you mentioned above.

 

After all this is done, if you still have problems, THEN I would be glad to help you.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

Thanks so much for this great contribution.

 

I just installed the contribution and so far it works fine. I'm in the matching products stage. I have more than a thousand of items so it would be quite a while.

 

One thing I noticed is that assembly is not uploaded. Does that mean this contribution doesn't support assembly? If so, do you have any plan on upgrade the contribution? Sorry for sounding pushy. I'm trying to decide what should I do with those assembly items. I have lots of them. Is there any way to work around it?

 

TIA

 

Wendy

Link to comment
Share on other sites

Adam,

 

Thanks so much for this great contribution.

 

I just installed the contribution and so far it works fine. I'm in the matching products stage. I have more than a thousand of items so it would be quite a while.

 

One thing I noticed is that assembly is not uploaded. Does that mean this contribution doesn't support assembly? If so, do you have any plan on upgrade the contribution? Sorry for sounding pushy. I'm trying to decide what should I do with those assembly items. I have lots of them. Is there any way to work around it?

 

TIA

 

Wendy

Wendy,

At this time assembly is not supported (although groups are). Right now I am doing a major rewrite of the program, so I'm putting adding features on hold. Once I get version 3.0 done, I will add some more features, and assembly is one I plan to add.

 

Someone else had this same issue, and was able to get it to work with assembly. I'm not sure what they did, but I don't think anything major is necessary. Maybe they'll read this, or when I have time I'll look back at my emails and try to contact them.

 

I don't have the premier version myself to fully test it, but I believe Pro will work with assembly but you can't create or edit assemblies. When I have time to work on it, I may ask for your product iif file for testing purposes.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

Thanks for your quick reply. :-)

 

Sure I can send you product iif file for testing. I'm using premier version and would love to test code for you.

 

One suggestion for this contribution is that when create iif file for orders, is it possible to let the user decide for orders xxx to orders xxx. I have a couple of thousands of orders to export and haven't had a success yet since it takes too long and the admin section automatically logged off before it even completed.

 

I'm switching from Peachtree to Quickbooks so I'm quite new to Quickbooks. Is it true that quickbooks can't export transactions? I didn't see transactions as an option under Export section. I was just trying to see how an assembly looks like in a transaction in iif file but seems like Quickbooks can't export transactions to iif file.

 

Wendy

 

Wendy,

At this time assembly is not supported (although groups are). Right now I am doing a major rewrite of the program, so I'm putting adding features on hold. Once I get version 3.0 done, I will add some more features, and assembly is one I plan to add.

 

Someone else had this same issue, and was able to get it to work with assembly. I'm not sure what they did, but I don't think anything major is necessary. Maybe they'll read this, or when I have time I'll look back at my emails and try to contact them.

 

I don't have the premier version myself to fully test it, but I believe Pro will work with assembly but you can't create or edit assemblies. When I have time to work on it, I may ask for your product iif file for testing purposes.

 

Thanks,

Adam

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