Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Hi I have a small issue and hopefully there's some help.

 

1) While importing the qbi_orders.iif into quickbooks 2005 pro I get out of balance with errors on line numbers.

 

I opened the file in excel and every line error mentioned has "ENDTRNS"

 

2) While importing the prodcuts went great it didn't import the cost of the items.

 

3) I checked the payments matched and doubled checked the payments match to make sure that my carts credit card orders went to the right match and after the import all order were labelled in quick books as check or money order and not paid.

 

I'm stumped :blush:

 

Any assistance would be great!

 

Rick

Link to comment
Share on other sites

I'm also having trouble with insurance making it into QB. In fact, using the usps insurance contrib by Kevin Shelton (http://www.oscommerce.com/community/contributions,2704/page,10) the import will not go at all. Looking at the QBI output in Excel, it is showing the item and shipping price, but no insurance. However, the total does have insurance, so when you attempt to import, QB tells you the numbers dont add up and gives it the boot.

 

Here's the QBI iif content:

TRNS INVOICE 2/7/2006 Accounts Receivable Fuller-5 44.25

SPL INVOICE 2/7/2006 ESF Produced -38

SPL INVOICE 2/7/2006

SPL INVOICE 2/7/2006 Shipping/Insurance Paid -4.05

 

As you can see, the missing 2.20 insurance causes the sum to be off. Would really help to have this fixed as otherwise I won't be able to offer insurance...

Link to comment
Share on other sites

I'm also having trouble with insurance making it into QB. In fact, using the usps insurance contrib by Kevin Shelton (http://www.oscommerce.com/community/contributions,2704/page,10) the import will not go at all. Looking at the QBI output in Excel, it is showing the item and shipping price, but no insurance. However, the total does have insurance, so when you attempt to import, QB tells you the numbers dont add up and gives it the boot.

 

Here's the QBI iif content:

TRNS INVOICE 2/7/2006 Accounts Receivable Fuller-5 44.25

SPL INVOICE 2/7/2006 ESF Produced -38

SPL INVOICE 2/7/2006

SPL INVOICE 2/7/2006 Shipping/Insurance Paid -4.05

 

As you can see, the missing 2.20 insurance causes the sum to be off. Would really help to have this fixed as otherwise I won't be able to offer insurance...

To find extra charges and discounts, QBI presently scans for entries in the order_totals table that correspond to each order, and are not shipping, sub total, or total entries. I don't off hand know how the shipping insurance contribution works. I can look into that, but not until QBI 3.0 is out, which I am working on many hours to complete. If you are shipping by UPS, the UPSXML contribution already has an option to include insurance in the shipping cost.

 

Thanks,

Adam

Link to comment
Share on other sites

To find extra charges and discounts, QBI presently scans for entries in the order_totals table that correspond to each order, and are not shipping, sub total, or total entries. I don't off hand know how the shipping insurance contribution works. I can look into that, but not until QBI 3.0 is out, which I am working on many hours to complete. If you are shipping by UPS, the UPSXML contribution already has an option to include insurance in the shipping cost.

 

Thanks,

Adam

 

 

Hi Adam,

 

Well, I'm with you as far as not knowing how the ship insurance contribution works :)

I'll just turn insurance off for now I guess...hey my customers lived without it for a long time before I setup OSC, so I guess they will have to again. If you have the time once QBI 3.0 comes out I'd greatly appreciate it. Thank you again for a fantastic contrib!!!

 

Eric

Link to comment
Share on other sites

The contrib is installed and I got it working fine, flawless in fact, BUT, I am having trouble in the setup. Does anyone know where and how IN quickbooks I would setup the coupon codes I use in OSC.

OSC uses:

Discount Coupons

Gift Vouchers

Low Order Fee

 

Currently I only use DISCOUNT COUPONS in my cart, so where would I enter this in Quickbooks AND then when I import the LISTS.IIF file, which checkbox would i tick to GET that coupon imported into qbi.

 

I only ask because I put the lists.iif into my server in the import directory, then had qbi find it/import it, the payment, shipping ones worked out fine, was able to edit, but the DISCOUNT/FEES says "No discount or fee items found in the iif file!"

 

Thank you

Edited by stiksandstones
Link to comment
Share on other sites

The contrib is installed and I got it working fine, flawless in fact, BUT, I am having trouble in the setup. Does anyone know where and how IN quickbooks I would setup the coupon codes I use in OSC.

OSC uses:

Discount Coupons

Gift Vouchers

Low Order Fee

 

Currently I only use DISCOUNT COUPONS in my cart, so where would I enter this in Quickbooks AND then when I import the LISTS.IIF file, which checkbox would i tick to GET that coupon imported into qbi.

 

I only ask because I put the lists.iif into my server in the import directory, then had qbi find it/import it, the payment, shipping ones worked out fine, was able to edit, but the DISCOUNT/FEES says "No discount or fee items found in the iif file!"

 

Thank you

You need to create an item in QB for each discount or other charge. Go to your Item list, select New, and then for Type choose "Discount" for discounts and "Other Charge" for added fees. These are the types that QBI recognizes for Discounts/Fees. Regular items will not show up on that page.

 

You don't need to change the check boxes -- that's just for products that you sell in your store, and doesn't affect the Discounts/Fees page.

 

Thanks,

Adam

Link to comment
Share on other sites

You need to create an item in QB for each discount or other charge. Go to your Item list, select New, and then for Type choose "Discount" for discounts and "Other Charge" for added fees. These are the types that QBI recognizes for Discounts/Fees. Regular items will not show up on that page.

 

You don't need to change the check boxes -- that's just for products that you sell in your store, and doesn't affect the Discounts/Fees page.

 

Thanks,

Adam

 

I will give that a whirl...you certainly are getting a huge dose of good karma for all your efforts.

Link to comment
Share on other sites

Hey Adam,

First THANK YOU for doing this Contrib. Really helps a ton (i think).

 

My issue is I have installed everything as stated in the instructions and I go into my admin section and there is no mention of the Quickbook Import QBI. I have gone over it now twice so I was just seeing if you had any info. or previous knowledge to this problem. I have installed several contribs. and feel comfortable with code but I dont know what is going on. There is no need to use PhpMyAdmin to run the .sql files b/c the application in admin will do this correct?

TIA.

Link to comment
Share on other sites

Hey Adam,

First THANK YOU for doing this Contrib. Really helps a ton (i think).

 

My issue is I have installed everything as stated in the instructions and I go into my admin section and there is no mention of the Quickbook Import QBI. I have gone over it now twice so I was just seeing if you had any info. or previous knowledge to this problem. I have installed several contribs. and feel comfortable with code but I dont know what is going on. There is no need to use PhpMyAdmin to run the .sql files b/c the application in admin will do this correct?

TIA.

QBI is added to Admin in the installation manual sections 2f, 2g, and 2h. It will not show on the opening Admin screen, but click on anything and then in the boxes on the left it will appear unde the catagory "Catalog." You can also access it at /catalog/admin/qbi_create.php.

 

The database tables will be installed automatically the first time you go to QBI.

 

Thanks,

Adam

Link to comment
Share on other sites

Ok, think I am getting a little further...

 

if I have a few different codes:

MARCHSALE for 15% off

FRIENDSCODE for 10%

etc....do I need to enter EACH name of a code in the quickbooks?

You can match them all to one discount item in QB, or match them to different items if you want to track them separately.

 

Thanks,

Adam

Link to comment
Share on other sites

QBI is added to Admin in the installation manual sections 2f, 2g, and 2h. It will not show on the opening Admin screen, but click on anything and then in the boxes on the left it will appear unde the catagory "Catalog." You can also access it at /catalog/admin/qbi_create.php.

 

The database tables will be installed automatically the first time you go to QBI.

 

Thanks,

Adam

When I go into admin and am looking under "catalog" I see these:

"Categories/Products

Products Attributes

Manufacturers

Reviews

Specials

Products Expected."

 

Isn't this where the QBI link should be? I believe everything is installed correctly because I have gone over it now three times and I feel everything is in place. The only thing that I can think of is my previous Admin Security contrib. Most of the edits for that contrib. were at the beg. of a file so what I did is just count the amount of lines it took up and added that to your suggested line insert. As stated in the instructinos its not critical.

Any idea of how im screwing this up? lol

TIA.

Link to comment
Share on other sites

When I go into admin and am looking under "catalog" I see these:

"Categories/Products

Products Attributes

Manufacturers

Reviews

Specials

Products Expected."

 

Isn't this where the QBI link should be? I believe everything is installed correctly because I have gone over it now three times and I feel everything is in place. The only thing that I can think of is my previous Admin Security contrib. Most of the edits for that contrib. were at the beg. of a file so what I did is just count the amount of lines it took up and added that to your suggested line insert. As stated in the instructinos its not critical.

Any idea of how im screwing this up? lol

TIA.

 

Just for reference, here is mine:

 

Catalog

 

Categories/Products

Products Attributes

Attributes Sets

Easy Populate

Products Options

Manufacturers

Reviews

Specials

Featured Products

QBI QB Import

Products Expected

 

Just for trial, have you tried typing in the URL directly?

 

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

 

Just a thought.

Link to comment
Share on other sites

Thanks for the reply. When I type that in I get a page not responding error. I didnt need to run these sql files through PhpMyAdmin right? I really have no idea what is wrong b/c I felt I did everything correct and multiple times at that. :dunno: thanks for the help though :)

If page is not found, then you need to check that the URL is correct and that it is actually uploaded.

 

People have had problems before because of the Admin Security (or a similar) contribution. I believe you need to tell it about QBI before it will let you access it. I can't help you with that, as I don't have that contribution.

 

As far as the menus, I'd guess that there is a typo somewhere or the file name definitions aren't updated.

 

The MySQL tables are set up automatically once you are able to open QBI.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi

 

I used this with Quickbooks Pro 2005 but had a client then say it would not work with his 2004. Now I have a customer who wants to go 2006 and I want to know if anyone is using it with 2006 yet?

 

thanks

Kym

 

I had it working fine with 2005. I now have 2006 and it works the same, but I just import the .iif file, nothing fancy.

Link to comment
Share on other sites

Can anyone possibly help with this error?

 

"Quickbooks has detected that you have no items or that one or more of your amount sis not associated with an item. Please enter an item"

 

Not sure exactly what the issue is. I then get the invalid transaction error. Is this becuase an actual item does not match up? I have matched all items in OSC with the exported list from QB - no problems there.

 

order iif content below.

 

!CUST NAME BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM EMAIL NOTE CONT1 CONT2 CTYPE TERMS TAXABLE TAXITEM LIMIT RESALENUM REP COMPANYNAME SALUTATION FIRSTNAME LASTNAME

CUST Transcend-2 Fraser Britton Transcend 3030 Oneal boulder CO 80303 Fraser Britton Transcend 3030 Oneal boulder CO 80303 514 880 8237 [email protected] Fraser Britton Transcend Fraser Britton

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 NAMEISTAXABLE TERMS SHIPVIA REP FOB INVMEMO PAYMETH PONUM

!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM TAXABLE PAYMETHOD EXTRA

!ENDTRNS

TRNS INVOICE 02/13/2006 Accounts Receivable Transcend-2 11.47 54 N Y Fraser Britton Transcend 3030 Oneal boulder CO 80303 Fraser Britton Transcend 3030 Oneal boulder CO 80303 Method not set 54

SPL INVOICE 02/13/2006 Accessories -3 Beverage Koozie - E13 N -1 3 KOZ.BER.E13 N

SPL INVOICE 02/13/2006 N

SPL INVOICE 02/13/2006 -8.47 N -1 8.4700 N

SPL INVOICE 02/13/2006 N

SPL INVOICE 02/13/2006 testing 1 2 3 N

ENDTRNS

!CUST NAME BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM EMAIL NOTE CONT1 CONT2 CTYPE TERMS TAXABLE TAXITEM LIMIT RESALENUM REP COMPANYNAME SALUTATION FIRSTNAME LASTNAME

CUST Transcend-2 Fraser Britton Transcend 3030 Oneal boulder CO 80303 Fraser Britton Transcend 3030 Oneal boulder CO 80303 514 880 8237 [email protected] Fraser Britton Transcend Fraser Britton

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 NAMEISTAXABLE TERMS SHIPVIA REP FOB INVMEMO PAYMETH PONUM

!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM TAXABLE PAYMETHOD EXTRA

!ENDTRNS

TRNS INVOICE 02/14/2006 Accounts Receivable Transcend-2 128.46 55 N Y Fraser Britton Transcend 3030 Oneal boulder CO 80303 Fraser Britton Transcend 3030 Oneal boulder CO 80303 Method not set 55

SPL INVOICE 02/14/2006 Bashguards -54.99 Supercharger - 4-104 - 40T - Orange N -1 54.99 SPC.40.104O N

SPL INVOICE 02/14/2006 Apparel:T-shirts -20 Short Sleeve e13 T - LRG N -1 20 SST.E13.LRG N

SPL INVOICE 02/14/2006 Chainguides:Spare Parts -45 Back Plate - SRS - ISCG05 N -1 45 BPL.SRS.I05 N

SPL INVOICE 02/14/2006 N

SPL INVOICE 02/14/2006 -8.47 N -1 8.4700 N

SPL INVOICE 02/14/2006 N

SPL INVOICE 02/14/2006 testing N

ENDTRNS

Edited by Transcend
Link to comment
Share on other sites

Hello!

 

First of all, I want to thank you, Adam for this contribution! I am impressed!

 

Second of all, I would like to ask a question. Here is the situation: I downloaded and installed QBI. I matched all the fields with the exception of Products. There I get this error:

 

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

 

Why do I get this error? And How do I correct that?

 

I have to say that on the set up--> products page the assembleys are not displayed, instead all the items that are included in that assembley are shown...

 

Thanks again, Adam! :thumbsup: And thank you everybody for reading my post!

Link to comment
Share on other sites

Hello!

 

First of all, I want to thank you, Adam for this contribution! I am impressed!

 

Second of all, I would like to ask a question. Here is the situation: I downloaded and installed QBI. I matched all the fields with the exception of Products. There I get this error:

 

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

 

Why do I get this error? And How do I correct that?

 

I have to say that on the set up--> products page the assembleys are not displayed, instead all the items that are included in that assembley are shown...

 

Thanks again, Adam! :thumbsup: And thank you everybody for reading my post!

The error message occurs because you haven't matched any products and the array variable is empty. This error will be corrected in the next version (3.0), but it shouldn't functionally cause a problem.

 

Assemblies are not supported in the current version, but will be included in 3.0. If you search the posts back a bit you'll find discussion of this and a work-around.

 

Thanks,

Adam

Link to comment
Share on other sites

The error message occurs because you haven't matched any products and the array variable is empty. This error will be corrected in the next version (3.0), but it shouldn't functionally cause a problem.

 

Assemblies are not supported in the current version, but will be included in 3.0. If you search the posts back a bit you'll find discussion of this and a work-around.

 

Thanks,

Adam

 

Thank you, Adam, for your quick response! After posting my message, I found Wendy's work-around. I'll try that!

Now, there is something I don't understand... how am I supposed to match products if there is no product online? We started with OSC 2 weeks ago and I was hoping to upload all my products from Quickbooks to OSC. We have no categories or anything and we deleted the DVD-Movies and whatever categories/products were before as an example. If there is a drop-down for matching products, where is it? On my QBI set up/products and match/products pages I can't see anything... :(

 

Another thing... it is my understanding that I have to upload the images for the products, right? Could you, please, tell me exactly where the image folder is?

 

I am a beginner so, please, bear with me! I've been reading the forum trying to find some answers; I started reading this thread from page one and it took me quite some time... Any help is v e r y appreciated!! :thumbsup:

 

Thank you!!

Simona

Edited by magicsenses
Link to comment
Share on other sites

Simona,

 

Thank you, Adam, for your quick response! After posting my message, I found Wendy's work-around. I'll try that!

Now, there is something I don't understand... how am I supposed to match products if there is no product online? We started with OSC 2 weeks ago and I was hoping to upload all my products from Quickbooks to OSC. We have no categories or anything and we deleted the DVD-Movies and whatever categories/products were before as an example. If there is a drop-down for matching products, where is it? On my QBI set up/products and match/products pages I can't see anything... :(

QBI can not add products to osC. The ability of QBI to read the QB product list is so that you can match osC and QB products within QBI.

 

If you don't have many products, just create them again in osC and then match them in QBI if you wish.

 

Otherwise, you might look at the contribution Easy Populate for importing a product list into osC. You might be able to use a QB iif items export file with Easy Populate to do this.

Another thing... it is my understanding that I have to upload the images for the products, right? Could you, please, tell me exactly where the image folder is?

yourwebsite/catalog/images/manufacturename

In my experience osC doesn't seem to find images that you've uploaded already. When you create a new product, there is a box that will allow you to choose an image on your computer to upload for the product.

 

Thanks,

Adam

Link to comment
Share on other sites

How do I reset the import dates? Right now it only lets me import a few of my orders, but I am just now starting synch with qb and would like to import all my orders.

 

Also, customer import doesn't seem to work. It shows how many customers I have on the page, but when i create the iff, only a few customers show up.

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