QBI Quickbooks Import

[adam5532]

Adam,

any ideas on my issue?  As per my last post, I have 2 languages, correct permissions, and everything else appears to be according to the guidelines.

<{POST_SNAPBACK}>

Go into your MySQL administrator (php MyAdmin or equiv) and look at the qbi_payosc table. See if it is empty or populated with the osC payment methods, and if it has the payment methods populated in both languages (each should be in twice, once for each language id). This table is automatically populated and updated each time you visit the qbi_paymatch page by running the qbi pay_methods() function at the top of the qbi_paymatch.php page. Either the table is not getting populated, or it is not displaying (you might switch languages and see if the payment methods show up, but all languages get populated by going to the page regardless of what language is current).

 

Thanks,

- Adam

[RyanSmith]

Thanks a million! I knew that if I talked to someone who actually knows how the hell these things worked, it would be much easier than I had expected.

 

So it sounds like as long as I have the sub-items setup properly in QuickBooks, I can have as many attributes per product as I want. Is this correct?

 

I'm quite impressed by the amount of changes to QBI that were in the last release. It is really evolving in to a great piece of software.

 

One note on our old site:

Let me know specifically what you did, so I can determine if you missed something that's already there, or if there is some functionality that I might want to add to QBI.

 

We had some custom functionality that pulled from custom tables for the attribute system. You can see it at http://groupworkcampstore.burtshirts.com/p...&products_id=29 That needed some custom functionality. Also, we had custom text that goes on the T shirts of that site that we had to account for. Every product on this site (that is for every product/attribute combination) is a seperate SKU in QuickBooks. I don't think us or the accountant that we were working with know about subitems, or at least any idea of how they worked. It probably would have made our lives much easier.

 

Anyway, what we did was place $resultopa into a big while loop and added in a switch statement something like:

 	 switch ($myrowopa['products_options']) {
   case 'Color':
  	 $color = $myrowopa['products_options_values'];
  	 break;
   case 'Size':
  	 $size = $myrowopa['products_options_values'];
  	 break;
   case 'Name':
  	 if ($myrowopa['products_options_values'] != '') {
     $name = ' with name';
  	 }
  	 break;
   case 'Camp':
  	 $camp = $myrowopa['products_options_values'];
  	 break;
	 }
	 if ($myrowopa['products_options_values'] != '') {
   $qb_description .= ($qb_description == '') ?  $myrowopa['products_options_values'] : ', ' . $myrowopa['products_options_values'];
	 }

 

Then we added a lookup on a cross reference table to get the corrosponding SKU

 

  $sku_products_name = addslashes($myrowop['products_name']) . $name;
 if ($camp != '') {
	 $sku_query = tep_db_query("SELECT camp_code as sku, account FROM camps WHERE friendly_name = '$camp'");
	 $prod_name = '29';
 } else {
	 $sku_query = tep_db_query("SELECT sku, account FROM qb_xref WHERE color = '$color' AND size = '$size' AND name='$sku_products_name'");
 }

 

Obviously this could have been better, but it worked.

[adam5532]

Thanks a million!  I knew that if I talked to someone who actually knows how the hell these things worked, it would be much easier than I had expected.

 

So it sounds like as long as I have the sub-items setup properly in QuickBooks, I can have as many attributes per product as I want.  Is this correct?

Yes.

Every product on this site (that is for every product/attribute combination) is a seperate SKU in QuickBooks.

<{POST_SNAPBACK}>

That can be done in QBI using the matching feature. But currently, only for one attribute. Multiple attribute support for matching will come later.

 

Thanks,

Adam

[Guest]

I just found this product & thread, looks very nice.

 

I have a small store with QB pro & QB POS. I already configured the POS with about 400 items, spread in nearly 20 categories.

 

Installed osCommerce (actually osCMax variant), and looking for a quick way to import all my items with descriptions, pricing, etc that I have already programmed into QB POS.

 

I have started reading some of the DOCs for this program, the prupose listed sounds like it's setup to export sales from shopping cart to QB.

 

Is there an easy means to go the other way to get these items in? I've exported all the items to an excel spreadsheet.

 

Thanks!

-WC-

[adam5532]

I just found this product & thread, looks very nice.

 

I have a small store with QB pro & QB POS.  I already configured the POS with about 400 items, spread in nearly 20 categories.

 

Installed osCommerce (actually osCMax variant), and looking for a quick way to import all my items with descriptions, pricing, etc that I have already programmed into QB POS.

 

I have started reading some of the DOCs for this program, the prupose listed sounds like it's setup to export sales from shopping cart to QB.

 

Is there an easy means to go the other way to get these items in?  I've exported all the items to an excel spreadsheet.

 

Thanks!

  -WC-

<{POST_SNAPBACK}>

QBI can import a QB item list in an iif file for the purpose of "matching," but can not currently create new products in osC. You can import products from an Excel spreadsheet into osC using the osC contribution "Easy Populate."

 

Thanks,

Adam

[Guest]

QBI can import a QB item list in an iif file for the purpose of "matching," but can not currently create new products in osC. You can import products from an Excel spreadsheet into osC using the osC contribution "Easy Populate."

 

Thanks,

Adam

<{POST_SNAPBACK}>

 

Great news, thank you!

 

-WC-

[sinryder]

It looks like an extra apostrophe is getting in here as well. I will check this and get back to you.

 

Thanks,

Adam

<{POST_SNAPBACK}>

 

hey adam, just wanted to know if you had time to fix this ?

 

thankx

[adam5532]

hey adam, just wanted to know if you had time to fix this ?

 

thankx

<{POST_SNAPBACK}>

Not yet. I'll try to make some time in the next few days to do that.

 

Thanks,

Adam

[adam5532]

hey adam, just wanted to know if you had time to fix this ?

 

thankx

<{POST_SNAPBACK}>

In the file admin/includes/functions/qbi_functions.php, add the indicated code as the first line in the following functions:

 

Function: find_country_id

Add: $country_name=mysql_escape_string($country_name);

 

Function: find_zone_id

Add: $state=mysql_escape_string($state);

 

Function: get_state_code

Add: $state_name=mysql_escape_string($state_name);

 

These changes will be included in the next version.

 

Thanks,

Adam

Edited June 14, 2005 by adam5532

[adam5532]

Adam,

any ideas on my issue?  As per my last post, I have 2 languages, correct permissions, and everything else appears to be according to the guidelines.

<{POST_SNAPBACK}>

Did you make any progress on this (see post #551 for my troubleshooting suggestions)?

 

Thanks,

Adam

[envy]

Hey Adam or anyone with an answer...

 

I just downloaded your latest version of QBI. I am new to your contribution and have a bit of a problem....

 

Instruction H and I

H points me to admin/includes/filenames.php

 

and I points me to admin/includes/database_tables.php

 

both of which do not exist in my files. Could someone point me in the right direction if this is a misprint or help me to take the next step??

 

THanks in advance

Elaine

[envy]

Hey Adam or anyone with an answer...

 

I just downloaded your latest version of QBI.  I am new to your contribution and have a bit of a problem....

 

Instruction H and I

H points me to admin/includes/filenames.php

 

and I points me to admin/includes/database_tables.php

 

both of which do not exist in my files.  Could someone point me in the right direction if this is a misprint or help me to take the next step??

 

THanks in advance

Elaine

<{POST_SNAPBACK}>

 

 

OK....dingy me...I wasn't in the catalog folder....lots of backpeddling, fixing and redoing and I am back on track....

 

thanks anyway

[Guest]

Go into your MySQL administrator (php MyAdmin or equiv) and look at the qbi_payosc table. See if it is empty or populated with the osC payment methods, and if it has the payment methods populated in both languages (each should be in twice, once for each language id). This table is automatically populated and updated each time you visit the qbi_paymatch page by running the qbi pay_methods() function at the top of the qbi_paymatch.php page. Either the table is not getting populated, or it is not displaying (you might switch languages and see if the payment methods show up, but all languages get populated by going to the page regardless of what language is current).

 

Thanks,

- Adam

<{POST_SNAPBACK}>

 

Using phpMyAdmin I can see the table. It appears there are 5 fields. It also appears the table is empty. THIS IS PROGRESS!!! YAY!!!! Now, where do you suppose the broken code is that would populate the table?

[HobbyBoards]

I have two questions. I have just setup a site with QBI and I have 8 new orders that it should see but it is only seeing 4 of them. On the orders table the qbi_imported field is 0 on all of them. Is there any other place that QBI stores what orders have been imported?

 

Also I have one of the PayPal IPN contributions and I woud like to grab the PayPal fee and include it in the iif file created by QBI for the orders. I don't mind adding it to the code but if someone could point me to the code that creates the iif entries for the orders it would save me a bunch of time searching.

 

Thanks,

Eric

[HobbyBoards]

Never mind on the first question. It is because the status of the orders is not pending anymore. Doh :)

 

Eric

[adam5532]

I have two questions. I have just setup a site with QBI and I have 8 new orders that it should see but it is only seeing 4 of them. On the orders table the qbi_imported field is 0 on all of them. Is there any other place that QBI stores what orders have been imported?

 

Also I have one of the PayPal IPN contributions and I woud like to grab the PayPal fee and include it in the iif file created by QBI for the orders. I don't mind adding it to the code but if someone could point me to the code that creates the iif entries for the orders it would save me a bunch of time searching.

 

Thanks,

Eric

<{POST_SNAPBACK}>

I don't have the PayPal IPN contribution, but if it adds the fee as an Order Total item, then you can set that up in QBI using the Setup and Match Discounts/Fees. To see if it is an ot_ item, look in the table orders_totals.

 

- Adam

[adam5532]

Never mind on the first question. It is because the status of the orders is not pending anymore. Doh :)

 

Eric

<{POST_SNAPBACK}>

Note that in the QBI configuration screen, you can set that orders of a particular Order Status should be shown as new orders (or orders of any status). Also, you can set what Order Status QBI will change the orders to when it creates the iif file.

 

- Adam

[HobbyBoards]

Adam,

 

Thanks for the quick replies.

 

I have already changed your contribution a bit to exclude only orders that are in the 'preparing' state. The reason the other orders didn't show up was because I had already marked them as 'shipped'.

 

The paypal fees are stored in their own paypal table which I no problem going and getting the fees from there I just need to know where in your code I need to insert the new code to get the fees from the paypal table.

 

Thanks,

Eric

[adam5532]

Adam,

 

Thanks for the quick replies.

 

I have already changed your contribution a bit to exclude only orders that are in the 'preparing' state. The reason the other orders didn't show up was because I had already marked them as 'shipped'.

 

The paypal fees are stored in their own paypal table which I no problem going and getting the fees from there I just need to know where in your code I need to insert the new code to get the fees from the paypal table.

 

Thanks,

Eric

<{POST_SNAPBACK}>

Eric,

 

catalog/admin/includes/qbi_engine_orders.php

 

All sections are commented, so you should be able to find the place to insert your code.

 

Thanks,

Adam

[ajnin]

i am getting ready to purchase quickbooks and will be needing a contribution such as this for intergration to quickbooks. what version of quickbooks should i purchase. thank you in advance this seems to be a great contibution to this community.

[adam5532]

i am getting ready to purchase quickbooks and will be needing a contribution such as this for intergration to quickbooks. what version of quickbooks should i purchase. thank you in advance this seems to be a great contibution to this community.

<{POST_SNAPBACK}>

Assuming you in the US or using US versions, I would get Quickbooks Pro 2005. As far as I know, Premiere 2005 will also work with QBI if you need the added features.

 

Quickbooks Basic 2005 will also work. However, at some point in the future I will adapt QBI to work with the QB XML interface, which will give improved functionality and will be necessary to support future versions at some point. Basic does not support the XML interface.

 

Quickbooks Simple Start and Online do not work with QBI. Non-US versions of QB do not work either, although in the future support will be added for them. I'm also not sure if QB Mac 2005 works, but I know that some Mac versions will work.

 

Thanks,

Adam

[Guest]

Adam, could you outline for me how a payment goes from a module like Authorize.net to the payment method section of qbi and gets entered into the iif file that is used for import? It would help if I didn't have to reverse engineer the process. I still can not figure out why I have no payment methods being listed to match up to. Thank you!

[adam5532]

Adam, could you outline for me how a payment goes from a module like Authorize.net to the payment method section of qbi and gets entered into the iif file that is used for import?  It would help if I didn't have to reverse engineer the process.  I still can not figure out why I have no payment methods being listed to match up to.  Thank you!

<{POST_SNAPBACK}>

Sorry I haven't had time to follow up on this for you.

 

If you look at the page qbi_paymatch.php, you'll see that it runs the function pay_methods near the top. This function looks for all your payment modules, extacts the methods, and adds or updates them in the qbi_payosc table. To do this, it also needs to determine what languages you are using.

 

The function pay_methods is located in catalog/admin/includes/functions/qbi_functions.php

 

If you know some php you should be able to track down the problem. Let me know what you find!

 

Thanks,

Adam

[adam5532]

Sales Tax Rounding Error:

 

I have found a bug in the sales tax rounding that may cause occasional out-of-balance errors. I had thought I had fixed this previously, but I made an error in the code. Make sure anyway that you are using QBI version 2.10, as the previous versions will have the rounding error even if you make the fix below. This fix will be incorporated in the next version.

 

FILE: catalog/admin/includes/qbi_engine_orders.php

 

CHANGE LINE 326

FROM: $tax_amt=round_amt(-$myrowott["value"]);

TO:

$tax_amt=round_amt($myrowott["value"]);

 

CHANGE LINE 332

FROM: $order_data.="SPL\t\t$transtype\t".$myrowo["purchdate"]."\tSales Tax Payable\t".TAX_AGENCY."\t\t$tax_amt\t\t\tN\t\t".TAX_RATE."%\t".TAX_NAME."\tN\t\tAUTOSTAX\n";

TO:

$order_data.="SPL\t\t$transtype\t".$myrowo["purchdate"]."\tSales Tax Payable\t".TAX_AGENCY."\t\t".-$tax_amt."\t\t\tN\t\t".TAX_RATE."%\t".TAX_NAME."\tN\t\tAUTOSTAX\n";

 

Note that the code above goes all on one line although it may be split into two lines on your screen.

 

Thanks,

Adam

[HobbyBoards]

Adam,

 

Thanks to your help I've everything working just about right. One last thing that I either couldn't find or didn't understand it correctly in the help and didn't find the answer searching the forum. I have all my products in osC and imported them into QB. I added accounts to them in QB and then exported them back into osC. I didn't do any matching because if I was reading your help correctly I didn't need to because they are exactly the same. When I try to import the iif of my new orders I get the error "QuickBook has detected that one of your items does not have an account associated with it." I have check the item list and all the items have accounts associated with them. When I look at the iif file there are no accounts listed. Some additional info. All my items are non-inventory items and I didn't use the accounts for items in the QBI configure screen because I have different accounts for some of the items.

 

Thanks for any help you can give.

Eric