QBI Quickbooks Import

[thiendp]

QBI Quickbooks Import aims to be the ultimate tool for tranferring you osC sales into Quickbooks.

 

Please add onto this thread for support issues and I will address them as soon as possible. - Adam Liberman

 

I will add the link to the contribution in the next post.

 

Features:

a) Imports new invoices and new customers in one iif file

B) Imports payments with invoices or as sales receipts

c) Can go back and reimport previous invoices

d) Can import all customers or products

e) Converts osC products to QB item groups

f) Converts osC products with attributes to separate QB items

g) Converts osC products with attributes to QB sub-items

h) Converts any osC product to a QB item with a different name

i) Converts osC shipping methods to QB shipping names

j) Configurable for QB with sales tax turned on or off

k) Creates unique QB customer names

l) Almost no alteration of osC files

m) Multilingual interface

n) Invoice description language options

o) Converts essential data to default store language

p) No file editing needed -- all settings done in a graphical interface

q) Configuration saved in a database table making upgrades easy

r) Table creation is automatic after installation

s) Table updates are automatic when new versions are installed

t) Sanitizes data to avoid iif file errors

u) Strips blank lines and line feeds in addresses for proper importing to different QB versions

v) Shows country name only for foreign orders (option)

w) Changes state/province names to postal codes (option)

x) Adds customer comments to invoices (option)

y) Updates status and sends emails to customers (option)

z) Deletes credit card numbers (option)

 

Please tell me the method to import customer, I can't find this function

[wendys]

Ed,

 

In file admin->qbi_products.php file, find:

	  if (($iifitem["INVITEMTYPE"]=="INVENTORY" OR $iifitem["INVITEMTYPE"]=="SERV" OR $iifitem["INVITEMTYPE"]=="PART" OR $iifitem["INVITEMTYPE"]=="DISC" OR $iifitem["INVITEMTYPE"]=="OTHC") AND ($iifitem["HIDDEN"]=="N")) {

in the first line add

OR $iifitem["INVITEMTYPE"]=="ASSEMBLY"

to make it look like:

if (($iifitem["INVITEMTYPE"]=="INVENTORY" OR $iifitem["INVITEMTYPE"]=="ASSEMBLY" or $iifitem["INVITEMTYPE"]=="SERV" OR $iifitem["INVITEMTYPE"]=="PART" OR $iifitem["INVITEMTYPE"]=="DISC" OR $iifitem["INVITEMTYPE"]=="OTHC") AND ($iifitem["HIDDEN"]=="N")) {

 

This will allow assembly item to be uploaded.

 

But it's not done yet. You may take a look of your item.iif file on assembly items. For each regular inventory part item, there is just one line with all information included. But for assembly items, there are several lines. Among these lines, one line has identical structure as an inventory part.

 

The current version of this contribution can only read one line so it doesn't work with multiple lines information. So I used Excel program to manually eliminate those extra lines and just leave one line (the one that's similar to an inventory part line) for each assembly item (an easier way is to sort assembly items by a certain column so that all the useful lines are together while the extra lines stay together for all assembly items). And save the file as item.iif and upload it to this contribution. This way the contribution is able to list assembly item and make it possible for you to match.

 

I think it works the same as group item if you add group to the above code and manually change your iif file.

 

This is definately not a smart way to do it. Adam is working on this.

 

HTH

 

Wendy

 

Wendy,

 

Do you have assemblies working correctly?

 

If you could post how you are doing this in a little more detail I would greatly appreciate it.

 

Thanks

 

Ed

[adam5532]

I think it works the same as group item if you add group to the above code and manually change your iif file.

 

This is definately not a smart way to do it. Adam is working on this.

 

HTH

 

Wendy

Groups are handled completely automatically by QBI -- it reads the lines that make up the components of the group and relates those to the group name that it stores in another table. I imagine assemblies work similarly -- it may just be a matter of making a similar change in the group code to allow for assemblies as well. I will look at this.

 

Thanks,

Adam

[adam5532]

Please tell me the method to import customer, I can't find this function

I think I need to standardize the import and export terms one way or the other to avoid confusion, as exporting from QBI makes a file to import into QB and vice versa.

 

This feature, found near the bottom of the Create IIF page, exports the customer list out of osC so that it can be imported into QB. QBI does not currently have a function to take a customer list file exported from QB and import it into osC.

 

Thanks,

Adam

[adam5532]

Adam...

 

Based on what I see it sounds like a great contibution.

 

Here is my site: http://www.chaiteadepot.com

 

I have a small problem.

 

I have checked and double checked the installed the 2.10b files are in the right spots. I have loaded the whole admin section to the server twice (Mine is Admin on this server since 'admin' takes you to the server admin control panel). I tried to let the your files execute the SQL and I tried executing within phpMyAdmin. I have checked all file permissions.

 

All looks good based on the instructions. I'm sure it's just an ID-10-T error.

 

When I click the QBI link in the Catalog section i get this:

 

Warning: main(includes/classes/navigation_history.php): failed to open stream: No such file or directory in /home/virtual/site24/fst/var/www/html/includes/application_top.php on line 126

 

Fatal error: main(): Failed opening required 'includes/classes/navigation_history.php' (include_path='.:/php/includes:/usr/share/php') in /home/virtual/site24/fst/var/www/html/includes/application_top.php on line 126

 

Any all help and advice is appreciated!

Make sure that you've done parts 2g and 2h correctly in the install manual. The code in 2g goes on one line.

 

Try opening QBI by typing the address directly in your browser. If it works, then you know that the only problem is in your link. If not, then something else is wrong and the link has nothing to do with it. (If your top level is the root and you don't have a catalog directory, then modify the URL as needed).

 

www.yourwebsite.com/catalog/admin/qbi_create.php

 

Thanks,

Adam

[adam5532]

...

Here is the situation: I have orders from both in state and out of state. In-state orders are charged sales tax on both merchandise AND shipping (Florida sales tax). This works fine on instate orders. However, when I have an out of state order, it is also coming in as shipping being taxed. This doesn't cause a problem with totals or anything, but when I pull my sales tax report, it is included in the taxable sales, which it should not.

 

Products for out of state customers come in as non-taxable versus products for in state coming in as taxable, but I can't get shipping to do the same. When I set the checkbox in QBI to not tax shipping, then all orders are imported as non-tax. Is there a way to make the shipping work like the products? Did I miss something setting this up?

 

Another issue, and I don't know if this is even possible, is the screwy way Florida does sales tax. They have a base rate of 6%, then each county has an additional tax of anywhere from 0.5% to 1.5%. I have these all set up in osC to calculate the tax correctly, but I only have the option to set one tax rate in QBI. Right now I haven't set up all the counties in QB, but if this is doable, I can set up QB with the county tax rates also. Is this something to look forward to in the next version?

 

Thanks for your help, this is an awesome contribution! :lol:

 

John

John,

QBI does not currently support multiple sales tax rates, This was originally planned for version 1.0 but was dropped in order to get the project out the door, and was not added back in. I do not have plans to add it into version 3.0, but will add it at a later time.

 

I going to try to put up a web site or area to collect feature suggestions and bugs in an organized manner so that people can see the lists and make sure that everything is listed and prioritized!

 

Thanks,

Adam

[thiendp]

I think I need to standardize the import and export terms one way or the other to avoid confusion, as exporting from QBI makes a file to import into QB and vice versa.

 

This feature, found near the bottom of the Create IIF page, exports the customer list out of osC so that it can be imported into QB. QBI does not currently have a function to take a customer list file exported from QB and import it into osC.

 

Thanks,

Adam

 

Thanks for your answer

 

But when I export from osc to iif file then import to QB data. The format of iif file is not correct so the error message show that "the setup fail"

Please give me the example of iif file that you can import into QB database.

[Guest]

John,

QBI does not currently support multiple sales tax rates, This was originally planned for version 1.0 but was dropped in order to get the project out the door, and was not added back in. I do not have plans to add it into version 3.0, but will add it at a later time.

 

I going to try to put up a web site or area to collect feature suggestions and bugs in an organized manner so that people can see the lists and make sure that everything is listed and prioritized!

 

Thanks,

Adam

 

Thanks Adam. I totally understand the need to prioritize. I am glad you got this out for everyone to use, it sure has made my life easier so far!

 

Any thoughts on the shipping tax status only being set by the checkbox in QBI versus handling it the way products are? I can work around it by manually changing it on each invoice in QB, but sure would be nice if it could come in with the status based on the customer's tax status.

 

I continue to play with the query to pull the vendor name from the separate table, too. If you have any pointers on how to form that, I am all ears!

 

Thanks again for a great product, and your great support!

 

John

[Guest]

For anyone who is using the Multi-Vendor Shipping contribution, and wants to get your vendor cost and preffered vendor into Quickbooks using the QBI import function, here is the code to change in Admin/Includes/qbi_engine_prod.php:

 

Change Line 38 from:

  $resultpd = tep_db_query("SELECT * FROM ".TABLE_PRODUCTS." AS p, ".TABLE_PRODUCTS_DESCRIPTION." AS pd WHERE p.products_id=pd.products_id AND language_id='$languages_id' AND qbi_imported='$qbimported' ".$whereclause."ORDER BY products_model");

 

To this:

  $resultpd = tep_db_query("SELECT * FROM ".TABLE_PRODUCTS." AS p, ".TABLE_PRODUCTS_DESCRIPTION." AS pd, ".TABLE_VENDORS." AS v WHERE p.products_id=pd.products_id AND v.vendors_id=p.vendors_id AND language_id='$languages_id' AND qbi_imported='$qbimported' ".$whereclause."ORDER BY products_model");

 

Find this section (Line 60):

// Loop through each product
do {
  $prod_id=$myrowpd["products_id"];
  $prod_price=round($myrowpd["products_price"],2);
  $myrowpd["products_tax_class_id"]>0 ? $ptaxable="Y" : $ptaxable="N";
  $prod_prefix="";
  $prod_name=$myrowpd["products_model"];
  $prod_desc=$myrowpd["products_name"];
  $prod_quan=$myrowpd["products_quantity"];

 

And add below the "$prod_quan=$myrowpd["products_quantity"];":

 

	  $prod_cost=$myrowpd["vendors_product_price"];
  $vendor_name=$myrowpd["vendors_name"];

 

Now find this section (was Line 69, should be Line 71 after the last change):

	  $prod_data.="INVITEM\t$prod_name\t$item_type\t$prod_desc\t$prod_desc\t".ITEM_ACCT."\t".ITEM_ASSET_ACCT."\t".ITEM_COG_ACCT."\t$prod_price\t0\t$ptaxable\t\t\t\t\t\t$prod_quan\t\n";

 

And change to this:

	  $prod_data.="INVITEM\t$prod_name\t$item_type\t$prod_desc\t$prod_desc\t".ITEM_ACCT."\t".ITEM_ASSET_ACCT."\t".ITEM_COG_ACCT."\t$prod_price\t$prod_cost\t$ptaxable\t\t\t\t$vendor_name\t\t$prod_quan\t\n";

 

Now when you import the qbi_prod.iif file into Quickbooks, your vendor cost and preferred vendor will be populated!

 

Hope this helps someone else.

 

John

[adam5532]

Thanks for your answer

 

But when I export from osc to iif file then import to QB data. The format of iif file is not correct so the error message show that "the setup fail"

Please give me the example of iif file that you can import into QB database.

I can't give you a sample file, because QBI has to be configured to work with your QB company file, with the correct accounts and items.

 

"Setup failure" isn't a normal error message that I've seen from importing an iif file into QB, since it is an import operation and not a setup operation.

 

Please let me know the exact text of the error message, exactly when it happens, and what version of QB you are using and I might be able to help you further. Also, do you have everything set up in QBI, and is QBI otherwise working properly?

 

Thanks,

Adam

[thiendp]

I can't give you a sample file, because QBI has to be configured to work with your QB company file, with the correct accounts and items.

 

"Setup failure" isn't a normal error message that I've seen from importing an iif file into QB, since it is an import operation and not a setup operation.

 

Please let me know the exact text of the error message, exactly when it happens, and what version of QB you are using and I might be able to help you further. Also, do you have everything set up in QBI, and is QBI otherwise working properly?

 

Thanks,

Adam

 

I'm using 2.1 version

In this version. I find these function

_ create iif( for order, product,customer)

_ set up (product, discount, shipping,payment)

_ match (product, discount, shipping,payment)

_ configuration

_ about

When I use create iff fuction to create iif file, the header is:

!INVITEM NAME INVITEMTYPE DESC PURCHASEDESC ACCNT ASSETACCNT COGSACCNT PRICE COST TAXABLE PAYMETH TAXVEND TAXDIST PREFVEND REORDERPOINT QNTY EXTRA

and some info following but there no REFNUM field that is use for set up product.

so I want to know the fields in items.iif that I can setup product to QBI database.

Please give me the example about items.iif

Thanks

[thiendp]

I get problem in setup data to QB database. I don't know the format of iif file for set up product into QB database. Please show me the example of this file. In case, I want to set up product with attribute or product group. What is the format of iif file.

Thanks

Thien

[sepre]

Can someone please tell me what version of QB this contrib has been tested and/or supported?

 

Thanks,

PP

[sepre]

Can someone please tell me what version of QB this contrib has been tested or works well.

 

Thanks,

PP

[thiendp]

I get problem in setup data to QB database. I don't know the format of iif file for set up product into QB database. Please show me the example of this file. In case, I want to set up product with attribute or product group. What is the format of iif file.

Thanks

[Taking the Lead]

Hi

I have just got around to using this modification.

I am able to import Items into Quickbooks but when I tried to import Customers I got the following error message:

 

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 's' AND zone_country_id='222'' at line 1

 

SELECT * FROM zones WHERE zone_name='Lanc's' AND zone_country_id='222'

 

Now I realise that it is a problem in MySQL but can anyone help me as to what is wrong and how to mend it.

 

Thanks

John

[Taking the Lead]

Hi

I have just got around to using this modification.

I am able to import Items into Quickbooks but when I tried to import Customers I got the following error message:

 

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 's' AND zone_country_id='222'' at line 1

 

SELECT * FROM zones WHERE zone_name='Lanc's' AND zone_country_id='222'

 

Now I realise that it is a problem in MySQL but can anyone help me as to what is wrong and how to mend it.

 

Thanks

John

 

 

Forget the last post I have got that sorted out now BUT another question.

 

I have exported the items from Quickbooks as item.iif - where do I upload it so I can compare it. I have tried the input folder in admin but OSC still does not seem to recognise it - can someone please help me

Thanks

John

[primadude]

Forget the last post I have got that sorted out now BUT another question.

 

I have exported the items from Quickbooks as item.iif - where do I upload it so I can compare it. I have tried the input folder in admin but OSC still does not seem to recognise it - can someone please help me

Thanks

John

 

I think the file must be called "items.iif".

 

Joe

[Taking the Lead]

I think the file must be called "items.iif".

 

Joe

 

 

THank you - that was it - OSC now sees the file.

 

When I have uploaded it - it has added the additions which I guess is correct.

 

When I click on "match" the drop down boxes for Quickbooks are empty and when I click on "update matches on this page" I get the following error message:

 

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

 

Any ideas??

John

[adam5532]

I'm using 2.1 version

In this version. I find these function

_ create iif( for order, product,customer)

_ set up (product, discount, shipping,payment)

_ match (product, discount, shipping,payment)

_ configuration

_ about

When I use create iff fuction to create iif file, the header is:

!INVITEM NAME INVITEMTYPE DESC PURCHASEDESC ACCNT ASSETACCNT COGSACCNT PRICE COST TAXABLE PAYMETH TAXVEND TAXDIST PREFVEND REORDERPOINT QNTY EXTRA

and some info following but there no REFNUM field that is use for set up product.

so I want to know the fields in items.iif that I can setup product to QBI database.

Please give me the example about items.iif

Thanks

REFNUM is not included in the iif file when you are importing items into QB, because REFNUM is a unique ID assigned by QB automatically when the product is imported and created.

 

You've listed the fields, but you don't need to know them because QB will make the correct iif file when you export items to an iif file from within QB.

 

Thanks,

Adam

[Taking the Lead]

I am making little headway. I now have OSC seeing my lists.iif file.

 

But it seems to be only transferring and updating my non-stock items but not the stocked items - has anyone got any ideas.

 

I been clicking the right boxes and been over the manual several times - I am confused.

 

John

[Taking the Lead]

I am making little headway. I now have OSC seeing my lists.iif file.

 

But it seems to be only transferring and updating my non-stock items but not the stocked items - has anyone got any ideas.

 

I been clicking the right boxes and been over the manual several times - I am confused.

 

John

 

 

Still having a problem - is there someone with knowledge of this mod and quickbooks who could email me so I could discuss it or post here.

 

I am sure this is a good mod except mine seems to be economic with the communicating to Quickbooks.

 

John

[adam5532]

Still having a problem - is there someone with knowledge of this mod and quickbooks who could email me so I could discuss it or post here.

 

I am sure this is a good mod except mine seems to be economic with the communicating to Quickbooks.

 

John

Check in your QBI configuration settings that for "match types" you have both inventory and non-inventory checked, or have no types checked (which includes everything).

 

Thanks,

Adam

[banebots]

Wendy,

 

Thanks for the help. I have made the change you outlined and edited my items.iif file to be correct. I can import the items.iif file OK and I can see my assemblies in the products list under the ?Set Up?/?Products? tab. However, when I go to ?Match?/?Products? I don?t see the assemblies. I?m guessing there is another if statement somewhere that creates the contents of the list boxes but I have not been able to find it.

 

I did discover that if I unchecked all the boxes in ?Match Types? section of the ?Configuration? tab that the assemblies showed up OK in ?Match?/?Products?. Just thought I post this for anyone else trying to get your method of importing assemblies to work.

 

Thanks for your help.

 

Ed

 

 

Ed,

 

In file admin->qbi_products.php file, find:

	  if (($iifitem["INVITEMTYPE"]=="INVENTORY" OR $iifitem["INVITEMTYPE"]=="SERV" OR $iifitem["INVITEMTYPE"]=="PART" OR $iifitem["INVITEMTYPE"]=="DISC" OR $iifitem["INVITEMTYPE"]=="OTHC") AND ($iifitem["HIDDEN"]=="N")) {

in the first line add

OR $iifitem["INVITEMTYPE"]=="ASSEMBLY"

to make it look like:

if (($iifitem["INVITEMTYPE"]=="INVENTORY" OR $iifitem["INVITEMTYPE"]=="ASSEMBLY" or $iifitem["INVITEMTYPE"]=="SERV" OR $iifitem["INVITEMTYPE"]=="PART" OR $iifitem["INVITEMTYPE"]=="DISC" OR $iifitem["INVITEMTYPE"]=="OTHC") AND ($iifitem["HIDDEN"]=="N")) {

 

This will allow assembly item to be uploaded.

 

But it's not done yet. You may take a look of your item.iif file on assembly items. For each regular inventory part item, there is just one line with all information included. But for assembly items, there are several lines. Among these lines, one line has identical structure as an inventory part.

 

The current version of this contribution can only read one line so it doesn't work with multiple lines information. So I used Excel program to manually eliminate those extra lines and just leave one line (the one that's similar to an inventory part line) for each assembly item (an easier way is to sort assembly items by a certain column so that all the useful lines are together while the extra lines stay together for all assembly items). And save the file as item.iif and upload it to this contribution. This way the contribution is able to list assembly item and make it possible for you to match.

 

I think it works the same as group item if you add group to the above code and manually change your iif file.

 

This is definately not a smart way to do it. Adam is working on this.

 

HTH

 

Wendy

[intensivex]

Hello all,

 

Pardon my interuption, but maybe someone can kindly answer a few questions before i install this contrib:

 

Im looking for a QB utility that will automatically import each sale into QB after its status is set to "Delivered/Shipped". Is this done only by cron job or is there a way to automatically set this up? The description is light on this feature.

 

Ideally, whats i'd like to happen is ... when my order gets approved, a tracking label is generated, and said order is queued to be sent or is directly sent to QB upon completion of order.

 

Am i on the right track? My client uses QB and would like this feature installed. Im a novice when it comes to QB and will have my develper install the contrib. Is my client making the right desicion? Whats the benifit of using QB? Accounting? Should i convince him its a waste of time?

 

Thanks much all ...

 

Chris