Jump to content
adam5532

QBI Quickbooks Import

Recommended Posts

Hi adam,

 

Got the sql error on ver 2.00 and 2.01.

 

Have now tracked down the problem I was having:

 

Thanks for tracking that down! Glad you got it to work!

 

- Adam

Share this post


Link to post
Share on other sites
hey adam.. sorry again. i get this message when i try to import al my previous orders. I read on your contribabout something about replace the qbi_functions.php. is that included in the latest d/l?

Warning: Variable passed to each() is not an array or object in /home/nutdeal/public_html/admin/includes/functions/qbi_functions.php on line 35

The only thing changed in the qbi_functions.php in 2.01 is in the executesql function, which was preventing the database installation from working properly, and a drop table statement in the qbi_0-00_2-00.sql file that referred to the wrong table.

 

Make sure you are using version 2.01.

 

Make sure to do the semicolon correction noted a few posts back.

 

Try reinstalling the database again. To do so:

 

Delete the qbi_config table.

Drop the field qbi_imported in the orders table.

 

Then go to QBI in your osC admin area and this will force it to do a fresh install.

 

Let me know what happens. I've confirmed all the other errors, but this error I don't have with mine, so that's why I suspect the installation. If this doesn't help, I'll have to troubleshoot further. For those who might have this problem and know php, arraycombine is being called in qbi_engine_orders.php at line 217.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
The only thing changed in the qbi_functions.php in 2.01 is in the executesql function, which was preventing the database installation from working properly, and a drop table statement in the qbi_0-00_2-00.sql file that referred to the wrong table.

 

Make sure you are using version 2.01.

 

Make sure to do the semicolon correction noted a few posts back.

 

Try reinstalling the database again. To do so:

 

Delete the qbi_config table.

Drop the field qbi_imported in the orders table.

 

Then go to QBI in your osC admin area and this will force it to do a fresh install.

 

Let me know what happens. I've confirmed all the other errors, but this error I don't have with mine, so that's why I suspect the installation. If this doesn't help, I'll have to troubleshoot further. For those who might have this problem and know php, arraycombine is being called in qbi_engine_orders.php at line 217.

 

Thanks,

Adam

 

mm i removed everything.. and did a fresh intsall but i get the same error. I can d/l the products and customers but not any of the orders. You said that i should install the 2.01 Well i did d/l the latest upload on the contrib site but i noticed that it still says "would you like to install 2.0" instead of 2.1. Is it supposed to say 2.1?

Share this post


Link to post
Share on other sites
mm i removed everything.. and did a fresh intsall but i get the same error. I can d/l the products and customers but not any of the orders. You said that i should install the 2.01 Well i did d/l the latest upload on the contrib site but i noticed that it still says "would you like to install 2.0" instead of 2.1. Is it supposed to say 2.1?

It's 2.01, and yes it will still say 2.00. I know that's a little confusing -- I'll rectify that in a while.

 

- Adam

Share this post


Link to post
Share on other sites
also. i was thinking.. Does how i setup my product matching or configure affect if it works or not?

The problem is in the section that deals with products that have multiple attributes. The reason the prouduct import works and orders doesn't is because I haven't added it yet to the product only import.

 

What it's doing is finding the attribute values, but not the names. For example, it's finding "Large" but not "Size." I'm not sure why that is happening, as it works correctly on my store. Tell me if you have products with attributes, and how many different types of attributes per product. Also, how many languages are you using in your store, and do you have the option names and values set up for each language. If necessary, I'lll have you send me your database files so that I can try to reproduce it. I'll need to install another copy of osC first though to do that.

 

First, try the Config option Items:Description language as both Default and Customer's, and tell me if there is any difference. If this doesn't solve it, then make the following code change and let me know what happens:

 

File: qbi_engine_orders.php

Lines: Replace the section titled "// Construct product model and name", lines 210 through 221 with the following:

 

// Construct product model and name
       if (is_array($prod_opt_val) AND is_array($prod_opt)) {
         foreach ($prod_opt_val as $prod_opt_vals) {
           $prod_model.=":".$prod_opt_vals;
         }
         $prod_opt_val_opt=arraycombine($prod_opt,$prod_opt_val);
         foreach ($prod_opt_val_opt as $prod_opts=>$prod_opt_vals) {
           $prod_name.=", ".$prod_opts.": ".$prod_opt_vals;
         }
       }

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

The problem is in the section that deals with products that have multiple attributes. The reason the prouduct import works and orders doesn't is because I haven't added it yet to the product only import.

 

What it's doing is finding the attribute values, but not the names. For example, it's finding "Large" but not "Size." I'm not sure why that is happening, as it works correctly on my store. Tell me if you have products with attributes, and how many different types of attributes per product. Also, how many languages are you using in your store, and do you have the option names and values set up for each language. If necessary, I'lll have you send me your database files so that I can try to reproduce it. I'll need to install another copy of osC first though to do that.

 

First, try the Config option Items:Description language as both Default and Customer's, and tell me if there is any difference. If this doesn't solve it, then make the following code change and let me know what happens:

 

File: qbi_engine_orders.php

Lines: Replace the section titled "// Construct product model and name", lines 210 through 221 with the following:

 

// Construct product model and name
? ? ? ?if (is_array($prod_opt_val) AND is_array($prod_opt)) {
? ? ? ? ?foreach ($prod_opt_val as $prod_opt_vals) {
? ? ? ? ? ?$prod_model.=":".$prod_opt_vals;
? ? ? ? ?}
? ? ? ? ?$prod_opt_val_opt=arraycombine($prod_opt,$prod_opt_val);
? ? ? ? ?foreach ($prod_opt_val_opt as $prod_opts=>$prod_opt_vals) {
? ? ? ? ? ?$prod_name.=", ".$prod_opts.": ".$prod_opt_vals;
? ? ? ? ?}
? ? ? ?}

 

Thanks,

Adam

[/quote

 

oh.. the it was cuz of the language was set on default and not customers Thanks!! however.. it still can't produce all my old orders still has that timeout issue :'( :'( :'( :'( :'( . thanks for this great econtrib still!

Share this post


Link to post
Share on other sites
oh.. the it was cuz of the language was set on default and not customers Thanks!! however.. it still can't produce all my old orders still has that timeout issue  :'(  :'(  :'(  :'(  :'( . thanks for this great econtrib still!

Glad you got it working. I'll upload an update with the fixes found so far soon. I was hoping your timeout problem would be solved, as it is much faster now. I'll put this on my list for future improvements.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Glad you got it working. I'll upload an update with the fixes found so far soon. I was hoping your timeout problem would be solved, as it is much faster now. I'll put this on my list for future improvements.

 

Thanks,

Adam

 

Regarding Matching. After i import the IFF file from QB to the QBI. I goto "match" section and the drop menus are empty. Is that normal? Also is Matching only relevanet when using it with attributes? Because i have a lot of attributes on my site but it's like option products like, "rush order, phone cable etc. " non-inventory items, therefore it's not really necessary for me to have attributes when i d/l it. So in other words Is there a awy to disable that?

Edited by superman123

Share this post


Link to post
Share on other sites
Regarding Matching. After i import the IFF file from QB to the QBI. I goto "match" section and the drop menus are empty. Is that normal? Also is Matching only relevanet when using it with attributes? Because i have a lot of attributes on my site but it's like option products like, "rush order, phone cable etc. " non-inventory items, therefore it's not really necessary for me to have attributes when i d/l it. So in other words Is there a awy to disable that?

Currently, non-inventory items and services are not supported. So, if all your items are non-inventory, they will not show up in the drop-down menus. Support for non-inventory items and services will be added in a future version.

 

Yes, you can match QB items, sub-items, and groups with osC products, and products with attributes (currently only one attribute like Size: 10, but not more than one, like Size: 10, Color: Brown). Any QB type will match with any osC type listed.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Found a err...

 

If you are using QBI with taxes turned on and are also using discounts when you try to go into set up after importing your IIF file from quickbooks I got an unknown field err for "qbi_tax". After some digging it seems as the wrong field name was used in an array.

 

In admin/includes/functions/qbi_functions at line 207 you find :

 

tep_db_query("INSERT INTO ".TABLE_QBI_DISC." (qbi_disc_name,qbi_disc_refnum,qbi_disc_desc,qbi_disc_accnt,qbi_disc_price,qb
i_disc_type,qbi_tax) VALUES ('$qname','$qrefnum','$qdesc','$qaccnt','$qprice','$qitemtype','$tax')");

 

It should be :

tep_db_query("INSERT INTO ".TABLE_QBI_DISC." (qbi_disc_name,qbi_disc_refnum,qbi_disc_desc,qbi_disc_accnt,qbi_disc_price,qb
i_disc_type,qbi_disc_tax) VALUES ('$qname','$qrefnum','$qdesc','$qaccnt','$qprice','$qitemtype','$tax')");

 

It was causing out of balance problems on import of new orders.

Share this post


Link to post
Share on other sites
Found a err...

 

If you are using QBI with taxes turned on and are also using discounts when you try to go into set up after importing your IIF file from quickbooks I got an unknown field err for "qbi_tax".  After some digging it seems as the wrong field name was used in an array.

 

In admin/includes/functions/qbi_functions at line 207 you find :

 

tep_db_query("INSERT INTO ".TABLE_QBI_DISC." (qbi_disc_name,qbi_disc_refnum,qbi_disc_desc,qbi_disc_accnt,qbi_disc_price,qb
i_disc_type,qbi_tax) VALUES ('$qname','$qrefnum','$qdesc','$qaccnt','$qprice','$qitemtype','$tax')");

 

It should be :

tep_db_query("INSERT INTO ".TABLE_QBI_DISC." (qbi_disc_name,qbi_disc_refnum,qbi_disc_desc,qbi_disc_accnt,qbi_disc_price,qb
i_disc_type,qbi_disc_tax) VALUES ('$qname','$qrefnum','$qdesc','$qaccnt','$qprice','$qitemtype','$tax')");

 

It was causing out of balance problems on import of new orders.

 

Thanks for finding this! Maybe I should have called it 2.00 beta :( Anyway, I hope people are using and enjoying it! In a few days I will post a version 2.02 so that all the fixes I and others have found will be incorporated into the download.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Adam

 

I sended you the iff file generated with the other vertion I was having problem with. Remember? Any idea what could be the problem? Do you think that with the new vertion it will be solved? What sholud I do to upgrade?

 

Thanks.

Share this post


Link to post
Share on other sites
Adam

 

I sended you the iff file generated with the other vertion I was having problem with. Remember? Any idea what could be the problem? Do you think that with the new vertion it will be solved? What sholud I do to upgrade?

 

Thanks.

Sorry I haven't looked at it yet. Getting version 2.0 done has sort of taken over my life for a while, and now I'm recovering for a bit.

 

Yes, I would upgrade to the new version and then let me know if you still have the problem. But, wait a few days until I get version 2.02 out -- that will include all the fixes posted in the forum and make for a more troublefree upgrade.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

I still get that "trasnaction out of balance" message. Mm.. i been looknig in the preivous post. but can't find a solution. What causes this? THis is with Both old and new orders

Edited by superman123

Share this post


Link to post
Share on other sites

I still get that "trasnaction out of balance" message. Mm.. i been looknig in the preivous post. but can't find a solution. What causes this? THis is with Both old and new orders

 

I opened up the IFF File and i noticed that the Column SHip VIA says, "method not set"

Could it be the Config? i put this

 

 

Shipping name: Shipping

Shipping description: USPS/UPS

Shipping account: Endicia

Shipping class:

 

i dunno what else to put there

Share this post


Link to post
Share on other sites

Adam,

 

This is a great looking contribution. I recently installed the v2.01 with mostly success. I managed to get everything set-up correctly (as far as I know). When I try to import I get the "out of balance" error and then an error on Line # 13. Upon reviewing the IIF file I see that the last portion is "ENDTRNS" (missing the ! mark). I put the ! mark in and I seem to be able to import; atleast, it says it is successful. In reality it doesn't add a sales order to my Quickbooks (2004 version).

 

Any ideas. I already verified the taxes is set correctly and the shipping via is correct.

 

I appreciate any help you can give me to point me in the correct direction.

 

Here is the coding I have gotten as a result of the exported IIF file(the sensitive information has been changed, everything else is the original text):

 

!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 Rea-18 Joe Rea 111 somehwere Concord CA 94519 Joe Rea 111 somewhere Concord CA 94519 5551112222 jrea@somewhere.com Joe Rea Y CA-TAX Joe Rea

!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 01/26/2005 Accounts Receivable Rea-18 1610.76 145136 N Y Joe Rea 111 Somewhere Concord CA 94519 Joe Rea 111 Somewhere Concord CA 94519 Y UPS Ground Concord, CA Cash 145136

SPL INVOICE 01/26/2005 Sales 1474.99 2U Rackmount Dual Xeon Barebones,RD400W,6 1-in Hot-Sw N -1 1474.99 SY-15675 Y

SPL INVOICE 01/26/2005 N

SPL INVOICE 01/26/2005 Shipping Charges - Income -14.08 Shipping Charges N -1 14.0800 Shipping Charges N

SPL INVOICE 01/26/2005 N

SPL INVOICE 01/26/2005 Test order # 3 N

SPL INVOICE 01/26/2005 Sales Tax Payable State Board of Equalization -121.69 N 8.25% CA-TAX N AUTOSTAX

ENDTRNS

Share this post


Link to post
Share on other sites
Adam,

 

This is a great looking contribution. I recently installed the v2.01 with mostly success. I managed to get everything set-up correctly (as far as I know). When I try to import I get the "out of balance" error and then an error on Line # 13. Upon reviewing the IIF file I see that the last portion is "ENDTRNS" (missing the ! mark). I put the ! mark in and I seem to be able to import; atleast, it says it is successful. In reality it doesn't add a sales order to my Quickbooks (2004 version).

 

Any ideas. I already verified the taxes is set correctly and the shipping via is correct.

 

I appreciate any help you can give me to point me in the correct direction.

 

Here is the coding I have gotten as a result of the exported IIF file(the sensitive information has been changed, everything else is the original text):

 

!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 Rea-18 Joe Rea 111 somehwere Concord CA 94519  Joe Rea 111 somewhere Concord CA 94519  5551112222  jrea@somewhere.com  Joe Rea    Y CA-TAX      Joe Rea

!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 01/26/2005 Accounts Receivable Rea-18  1610.76 145136  N Y Joe Rea 111 Somewhere Concord CA 94519  Joe Rea 111 Somewhere Concord CA 94519  Y  UPS Ground  Concord, CA  Cash 145136

SPL  INVOICE 01/26/2005 Sales  1474.99  2U Rackmount Dual Xeon Barebones,RD400W,6 1-in Hot-Sw N -1 1474.99 SY-15675 Y 

SPL  INVOICE 01/26/2005      N     

SPL  INVOICE 01/26/2005 Shipping Charges - Income  -14.08  Shipping Charges N -1 14.0800 Shipping Charges N 

SPL  INVOICE 01/26/2005      N     

SPL  INVOICE 01/26/2005      Test order # 3 N     

SPL  INVOICE 01/26/2005 Sales Tax Payable State Board of Equalization  -121.69  N  8.25% CA-TAX N  AUTOSTAX

ENDTRNS

 

I'm not a 100% sure if I understand what your asking, but this is an invoice transaction, not a sales transaction. After you import this, you should be able to search for invoice number 145136.

 

I'm not sure how you process your charges, but you can possibly change this to come in as a sales transaction if you prefer.

 

Dan


Dan Stevens

Share this post


Link to post
Share on other sites

Dan,

 

Thank you for the reply. I was mistaken in stating "Sales Order"; I am actually looking through the Invoices for the transaction. I simply just enter the invoices screen and try to view the last invoice which shows an invoice from an earlier transaction. I have not tried using the search option and inputing the invoice # it should be. I will try that next.

 

If that doesn't seem to work I will then try to import it as a Sales Order and see if that resolves the situation.

 

Thank you for the prompt response.

 

James

Share this post


Link to post
Share on other sites
I still get that "trasnaction out of balance" message. Mm.. i been looknig in the preivous post. but can't find a solution. What causes this? THis is with Both old and new orders

Open your iif and compare the amounts to the invoice in the osC admin panel. You will find that something is either missing or the incorrect amount. It is probably sales tax, or you haven't set up you low order fee or discounts.

 

- Adam

Share this post


Link to post
Share on other sites
I still get that "trasnaction out of balance" message. Mm.. i been looknig in the preivous post. but can't find a solution. What causes this? THis is with Both old and new orders

 

I opened up the IFF File and i noticed that the Column SHip VIA says, "method not set"

Could it be the Config? i put this

Shipping name:  Shipping

Shipping description:  USPS/UPS

Shipping account:  Endicia

Shipping class:

 

i dunno what else to put there

This doesn't have anything to do with the transaction out of balance.

 

The shipping settings in config are to put the Quickbooks item and account that you use in Quickbooks for shipping charges. That's what you choose in QB when you add a shipping charge line to an invoice. That's what you need to put in here.

 

The shipping methods are set in the Setup and Match screens for shipping. I don't think you've set those up.

 

This should solve the "method not set" problem.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Upon reviewing the IIF file I see that the last portion is "ENDTRNS" (missing the ! mark). I put the ! mark in and I seem to be able to import; atleast, it says it is successful. In reality it doesn't add a sales order to my Quickbooks (2004 version).

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.

Any ideas. I already verified the taxes is set correctly and the shipping via is correct.

 

I appreciate any help you can give me to point me in the correct direction.

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

Share this post


Link to post
Share on other sites
Open your iif and compare the amounts to the invoice in the osC admin panel. You will find that something is either missing or the incorrect amount. It is probably sales tax, or you haven't set up you low order fee or discounts.

 

- Adam

 

the tax is 8.25% w/o the % sign in the config. also.. i dont' have any low order fee's or discounts. Is it still necessary to setup? it happens w/ any ordres i d/l old or new

Edited by superman123

Share this post


Link to post
Share on other sites
the tax is 8.25% w/o the % sign in the config. also.. i dont' have any low order fee's or discounts. Is it still necessary to setup? it happens w/ any ordres i d/l old or new

It isnt necessary to set up if all your products are exactly the same in osC and QB. Open the iif and see what amount isn't matching your invoice in oSC.

 

- Adam

Share this post


Link to post
Share on other sites
It isnt necessary to set up if all your products are exactly the same in osC and QB. Open the iif and see what amount isn't matching your invoice in oSC.

 

- Adam

 

everything is the same.. the only thing i noticed were the decimal place. Lest say ist 12.50 then it will show up at 12.5 or 8.00 would be 8. I dunno if this even matters. As fr the products being the same.

 

As for seting up the produts between QB to OSC i downloaded all the products using your contrib. Import them to QB. then export the item list from QB back into QBI via ftp.

Edited by superman123

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×