Jump to content
mpiscopo

EZ QuickBooks Integration Contribution

Recommended Posts

This is a new contribution for integrating QuickBooks with osCommerce. This contribution makes integration with QuickBooks easy as there is no manual matching of inventory products in osCommerce and has some other features I needed.

 

Features Include:

1) Completely Automated matching of products between QuickBooks and osCommerce (no need to manually match products - handles

matching products in osCommerce with products which are in categories in QuickBooks). Products are matched according to Product Model (osCommerce)

and Item Name/Number (QuickBooks).

2) Updates inventory quantities in osCommerce with quantities from QuickBooks (option not to update included). This ensures that QuickBooks is the master

inventory record and allows you to sell from multiple websites and keep inventory quantities updated on all sites.

3) Imports orders into QuickBooks, creates account in QuickBooks if it doesn't exist.

4) Options for different account naming formats when creating new accounts in QuickBooks (6 options).

5) Sales tax configuration option.

6) Invoice Memo configuration option (custom memo such as Thank You for Shopping Acme Products).

7) Log file retention time is configurable.

8) Ability to update values from Custom Fields in QuickBooks to product values in osCommerce.

 

Contribution Link:

http://addons.oscommerce.com/info/5803

Edited by mpiscopo

Share this post


Link to post
Share on other sites

WOW!! This contribution looks GREATT!!

 

Can you answer some questions about it?

 

1) What is used to match customers from OSC to Customers in QB and how does it prevent duplicate customers?

2) Does it handle items in OSC with attributes (like sizes) to Quickbooks itmes which are all different SKUs?

3) I am assuming that once I import the orders from OSC, it will reduce inventory in QB, then its simply a matter of syncing back the other way? (if I also took offline orders that day)

4) This is awesome!!!! Thanks!

Share this post


Link to post
Share on other sites

Seems sweet!

 

But what is the difference between this one and Quickbooks Import QBI from Adam Liberman??

Share this post


Link to post
Share on other sites

great stuff!! i was waiting for this type of contribution for long...had a look at QB Import but doesn't appeal me that much...

 

before i try this contrib, can i know if this contrib for QB is for any particular quickbooks version? i'm using Quickbooks Premier 2006/07 and is Australian based software...will this work?

 

anyhow, cheers to you!

Share this post


Link to post
Share on other sites

Hi Ethan,

 

There are six options for naming accounts - it works in the same way as the QBI version.

1 - Last Name, First Name

2 - Last Name, First Name and osCommerce Account Number

3 - First Name Last Name

4 - First Name Last Name and osCommerce Account Number

5 - Company Name

6 - Company Name and osCommerce Account Number

 

No, it doesn't handle items with attributes. I haven't used this in my website so I'm not familiar of the functionality. It may be good to add that in a future version.

 

Yes, when you import from osCommerce your QuickBooks inventory is adjusted. No need to sync back unless you have multiple websites selling from the same inventory. For example, if you sell from both your website and Amazon or Ebay you can update your website with new inventory counts after you sell from other stores. I use this option because I have two osCommerce stores selling from the same inventory.

 

Mark

 

 

WOW!! This contribution looks GREATT!!

 

Can you answer some questions about it?

 

1) What is used to match customers from OSC to Customers in QB and how does it prevent duplicate customers?

2) Does it handle items in OSC with attributes (like sizes) to Quickbooks itmes which are all different SKUs?

3) I am assuming that once I import the orders from OSC, it will reduce inventory in QB, then its simply a matter of syncing back the other way? (if I also took offline orders that day)

4) This is awesome!!!! Thanks!

Share this post


Link to post
Share on other sites

Hi Andrea,

 

The biggest difference is that you don't need to manually match the products within osCommerce. Adams version is very good and I used it for years; however, my store had grown and doing the manual match between the QuickBooks items and osCommerce items was taking a lot of time and was the source of many error as people who were entering the new products were making mistakes in matching. With this version you just upload your items.iif into osCommerce and then osCommerce will automatically match the products based on part numbers - even if you're using categories in QuickBooks (which has always been a pain to match).

 

I also had my programmer add a few options which I needed for my store such as matching custom fields from QuickBooks to a field in osCommerce (I use a custom field in QuickBooks to identify a products location in the warehouse - then osCommerce includes this location in the packing slip). It also updates quantities from QuickBooks to osCommerce as I'm selling from multiple osCommerce stores.

 

Adams version is more mature, so it includes features which this one doesn't. I just had my programmer include the features I needed for my stores.

 

Mark

 

 

Seems sweet!

 

But what is the difference between this one and Quickbooks Import QBI from Adam Liberman??

Share this post


Link to post
Share on other sites

The integration is based on QuickBooks IIF files so it should work fine unless there is a difference in IIF file field names in the Australian version - however, I would think these are the same. You would want to try it on a development version of your website first - which is standard practice for installing any osCommerce contributions (or least should be if you don't).

 

As far as osCommerce versions, I'm using both v2.2 RC1 and an older version I installed years ago (I believe it's 1.4).

 

Mark

 

great stuff!! i was waiting for this type of contribution for long...had a look at QB Import but doesn't appeal me that much...

 

before i try this contrib, can i know if this contrib for QB is for any particular quickbooks version? i'm using Quickbooks Premier 2006/07 and is Australian based software...will this work?

 

anyhow, cheers to you!

Share this post


Link to post
Share on other sites

I'm getting an error trying to import the order.IIf file into QB. It says the top line doesn't match the order. I think it is a problem with the config in osc. What needs to go into the tax option in the config area?

Share this post


Link to post
Share on other sites

Hi, I seem to be having problems activating this on my site. I have run the SQL Query on my database and uploaded the files in the folders that were in the download. Then when I go to change some of the php code as stated in the instructions here: i) admin/includes/application_top.php

 

Find the following:

 

// set application wide parameters

$configuration_query = tep_db_query('select configuration_key as cfgKey, configuration_value as cfgValue from ' . TABLE_CONFIGURATION);

while ($configuration = tep_db_fetch_array($configuration_query)) {

define($configuration['cfgKey'], $configuration['cfgValue']);

}

 

Bellow this, add the following

 

//set quickbooks parameters

$quickbooks_configuration_query = tep_db_query('select configuration_key as cfgKey, configuration_value as cfgValue from ' . TABLE_QUICKBOOKS_CONFIG);

while ($quickbooks_configuration = tep_db_fetch_array($quickbooks_configuration_query)) {

define($quickbooks_configuration['cfgKey'], $quickbooks_configuration['cfgValue']);

}

I Get the following error:

1146 - Table 'mydatabasename.TABLE_QUICKBOOKS_CONFIG' doesn't exist

 

select configuration_key as cfgKey, configuration_value as cfgValue from TABLE_QUICKBOOKS_CONFIG

 

[TEP STOP]

Can anyone help me. I am not to familiar with php yet. Thanks

Share this post


Link to post
Share on other sites

Michael,

 

The tax item should match what you have in QuickBooks. For instance, I charge sales tax for the state of Maryland so I have "Comptroller of Maryland - SUT" in the TaxItemDescription field. Let me know if this works for you.

 

I'm getting an error trying to import the order.IIf file into QB. It says the top line doesn't match the order. I think it is a problem with the config in osc. What needs to go into the tax option in the config area?

Share this post


Link to post
Share on other sites
Hi, I seem to be having problems activating this on my site. I have run the SQL Query on my database and uploaded the files in the folders that were in the download. Then when I go to change some of the php code as stated in the instructions here: i) admin/includes/application_top.php

 

Find the following:

 

// set application wide parameters

$configuration_query = tep_db_query('select configuration_key as cfgKey, configuration_value as cfgValue from ' . TABLE_CONFIGURATION);

while ($configuration = tep_db_fetch_array($configuration_query)) {

define($configuration['cfgKey'], $configuration['cfgValue']);

}

 

Bellow this, add the following

 

//set quickbooks parameters

$quickbooks_configuration_query = tep_db_query('select configuration_key as cfgKey, configuration_value as cfgValue from ' . TABLE_QUICKBOOKS_CONFIG);

while ($quickbooks_configuration = tep_db_fetch_array($quickbooks_configuration_query)) {

define($quickbooks_configuration['cfgKey'], $quickbooks_configuration['cfgValue']);

}

I Get the following error:

1146 - Table 'mydatabasename.TABLE_QUICKBOOKS_CONFIG' doesn't exist

 

select configuration_key as cfgKey, configuration_value as cfgValue from TABLE_QUICKBOOKS_CONFIG

 

[TEP STOP]

Can anyone help me. I am not to familiar with php yet. Thanks

 

 

Hi Matthew,

 

In order that installation is successful you must complete all steps, otherwise this addon won't be functional.

Regarding this issue, please see step vi)

 

vi)admin/includes/database_tables.php

 

Under lines (or wherever you want)

"

define('TABLE_ORDERS_PRODUCTS_DOWNLOAD', 'orders_products_download');

define('TABLE_ORDERS_STATUS', 'orders_status');

define('TABLE_ORDERS_STATUS_HISTORY', 'orders_status_history');

define('TABLE_ORDERS_TOTAL', 'orders_total');

"

 

Add lines:

 

"

define('TABLE_QUICKBOOKS_CONFIG', 'quickbooks_configuration');

define('TABLE_QUICKBOOKS_CUSTOM_FIELDS_TO_PRODUCTS', 'quickbooks_custom_fields_to_products');

define('TABLE_QUICKBOOKS_SESSION', 'quickbooks_session');

define('TABLE_QUICKBOOKS_IMPORT_LOG', 'quickbooks_import_log');

define('TABLE_QUICKBOOKS_TO_OSCOMMERCE_CATEGORIES', 'q_to_o_categories');

"

 

These are the mapping from database tables to constants.

I can help if have further questions.

 

 

Cheers,

Bogdan

Share this post


Link to post
Share on other sites

Dear Bogdan, Thanks for your help. What I ended up doing was skipping the "i) admin/includes/application_top.php"

edit and did the rest of the editing, then came back and did the first again. This eliminated the error that was being created. I'm not sure if I was just doing something wrong or what but that solved that problem.

I am also having a problem getting the items from my Quickbooks file to show up on my OsCommerce catalog. They just aren't showing up at all.

Another question I have is: If after I get my Quickbooks Products to show properly on my web store, could I export an item list from OsCommerce and import it into Quickbooks and have the image source and category ID or name saved as a custom field.

I like what I have seen so far and am very excited about this contribution. Thanks for the help.

Share this post


Link to post
Share on other sites

Matthew,

 

The QuickBooks export will not create the products on your website which you have in QuickBooks. This is because there are often many more products in QuickBooks than the website - for instance discontinued items cannot be deleted from QuickBooks as there are transactions against them so QuickBooks just marks them as inactive, but these products will still be in the export file from QuickBooks.

 

What this application does is to match the products in QuickBooks to the same products on your website according to the part number. You'll need to create your products in osCommerce by hand. Or, there is a contribution called easy populate which can import a file into osCommerce for you; however, you'll need to create your import file first (including all the osCommerce properties such as image file, etc).

 

The image name and category ID will not import into QuickBooks with this contribution. You might be able to use the frugal exporter, or similar contribution, to create an export of your products then edit the file to create an IIF to import to QuickBooks. But my real question is why would you want to put image names and osCommerce categories into a custom field in QuickBooks? I don't see that it would be beneficial. You can organize your products in QuickBooks by putting them into categories and subcategories, but this is independent of osCommerce as you can have products in multiple categories in osCommerce.

 

Mark

 

 

Dear Bogdan, Thanks for your help. What I ended up doing was skipping the "i) admin/includes/application_top.php"

edit and did the rest of the editing, then came back and did the first again. This eliminated the error that was being created. I'm not sure if I was just doing something wrong or what but that solved that problem.

I am also having a problem getting the items from my Quickbooks file to show up on my OsCommerce catalog. They just aren't showing up at all.

Another question I have is: If after I get my Quickbooks Products to show properly on my web store, could I export an item list from OsCommerce and import it into Quickbooks and have the image source and category ID or name saved as a custom field.

I like what I have seen so far and am very excited about this contribution. Thanks for the help.

Share this post


Link to post
Share on other sites

Another thing I am interested in is: is there a way to 'simply' add a 'Printer Friendly Version' button to print of item descriptions and categories. This doesn't have much to do with the Quickbooks add-in but I thought I could ask. Thanks

Share this post


Link to post
Share on other sites

If you use the Froogle exporter you can open the export file in MS Excel and print from there (remove the unwanted columns though). If you don't have that contribution installed I highly recommend that you do and get your products listed in Froogle. It's free and I get sales from there quite often. Otherwise, I don't know of a way to do a quick print of item descriptions and categories.

 

Another thing I am interested in is: is there a way to 'simply' add a 'Printer Friendly Version' button to print of item descriptions and categories. This doesn't have much to do with the Quickbooks add-in but I thought I could ask. Thanks

Share this post


Link to post
Share on other sites
But my real question is why would you want to put image names and osCommerce categories into a custom field in QuickBooks? I don't see that it would be beneficial.

The reason I thought it would be beneficial would be for future updates, and to simplify the first upload. Since I had to start a new install I lost all of my previously uploaded products. I backed up the database, but the products just don't come back. I thought it would be simpler to export from Quickbooks and open it in Excell. There I can add the image references in bulk, since I have a lot of items that require the same images. Then I could upload that file and restore that item list to Quickbooks in case the database should be lost, for some reason.

 

Thanks for the help.

Share this post


Link to post
Share on other sites

I run a cron job to backup my entire database - it works nicely as I've restored my entire site to my development server using that database (nothing was missing). I'm not sure of the contribution name; however, the actual backup file the cron job runs is cron_backup.php. If you can't find it in the contributions, I can email you the file I'm using. This would save you a lot of work in the future should you have a problem with your database.

 

The reason I thought it would be beneficial would be for future updates, and to simplify the first upload. Since I had to start a new install I lost all of my previously uploaded products. I backed up the database, but the products just don't come back. I thought it would be simpler to export from Quickbooks and open it in Excell. There I can add the image references in bulk, since I have a lot of items that require the same images. Then I could upload that file and restore that item list to Quickbooks in case the database should be lost, for some reason.

 

Thanks for the help.

Share this post


Link to post
Share on other sites

Hi,

 

Trying to get this working but having no luck with importing the products. It just wont process the file.

 

Secondly I can create the export file but it appeard to truncate the item number (model number) to 12 even though this is now set to the maximum 255 in the SQL database and appearing correctly elsewhere.

 

Could really use some help, not wanting to reinvent the wheel but I don't know why this is being so difficult. I havn't altered the code and am using a totaly new and unedited (apart from this contrib) shop.

 

Diana

Share this post


Link to post
Share on other sites

Hi Diana,

 

If can send me some details about the errors you have (some sample of file exported wrong , more details about the error of file processing at import) i'll take a look.

This addon was tested on a fresh install of osCommerce + Apache (1.3 and 2.0 or 2.2) + PHP (version > 5).

 

Let me know,

Bogdan

 

 

 

 

Hi,

 

Trying to get this working but having no luck with importing the products. It just wont process the file.

 

Secondly I can create the export file but it appeard to truncate the item number (model number) to 12 even though this is now set to the maximum 255 in the SQL database and appearing correctly elsewhere.

 

Could really use some help, not wanting to reinvent the wheel but I don't know why this is being so difficult. I havn't altered the code and am using a totaly new and unedited (apart from this contrib) shop.

 

Diana

Share this post


Link to post
Share on other sites

I am very interested in this contribution. Before I attempt an install I wanted to ask some questions.

 

First, my store is heavily modified. STS, MVS, QTpro w attributes, product weights and dimensions, Easy Populate, Custom Fields in Product Info, Username instead of email login, and a lot more. Lots of additions and changes to the database.

 

Will this contribution give me a problem with the DB changes my site has? Especially those in the user information and product information tables? If so are fields easily added?

 

Second, will this work directly with Quickbooks Point of Sale, or would I need to use Quickbooks standard as an intermediary between QBPOS and OScommerce?

Share this post


Link to post
Share on other sites

Hi Billy,

 

I don't believe it will give you any problems with the database as the fields/tables are unique to this contribution; however, you should always test the installation on a test version of your website (as with all programming/database changes). My site is highly modified and it worked fine. But always Test before going live.

 

I've never used the QuickBooks Point of Sale application. I'm using the Enterprise version of the Manufacturing and Whosale Edition of QuickBooks - this is still QuickBooks core application with some add-ons. I believe that QuickBooks Point of Sale is the same, it's just a version of QuickBooks with the POS add-ons. If so, then it should work flawlessly. But, as with website changes you should make a copy of your QuickBooks database and test against it.

 

Mark

 

I am very interested in this contribution. Before I attempt an install I wanted to ask some questions.

 

First, my store is heavily modified. STS, MVS, QTpro w attributes, product weights and dimensions, Easy Populate, Custom Fields in Product Info, Username instead of email login, and a lot more. Lots of additions and changes to the database.

 

Will this contribution give me a problem with the DB changes my site has? Especially those in the user information and product information tables? If so are fields easily added?

 

Second, will this work directly with Quickbooks Point of Sale, or would I need to use Quickbooks standard as an intermediary between QBPOS and OScommerce?

Share this post


Link to post
Share on other sites

Thanks for the reply mark. I am going to give this a try. And yes, I always make changes on a test server first before uploading to the live site :D.

Share this post


Link to post
Share on other sites

I didnt get a chance to install yet but I did just think of something else.

 

Since I am using multiple vendors on the site, will I be able to make sure the quickbooks integration only affects one of the vendors? I would only want it to update inventory from my actual store, not from the dropshipper!

Share this post


Link to post
Share on other sites

Well, that's a unique twist. As it is, it'll update your quantities in QuickBooks for all the products you have in the order. If there is a product in the order which doesn't exist in QuickBooks, QuickBooks will create that product and subtract the appropriate quantities from inventory. You'll need to customize the contribution for your needs. What you need is a field added to your products table to flag whether to import that product into QuickBooks. But what I don't understand is how you are creating invoices currently. If you have an order which contains both your items and those from another vendor how do you record this in QuickBooks (do you create an invoice only for your products, or do you create one for both - if for both how do you account for their products?). The changes you need are pretty easy if you map your logic out correctly. Do you do your own programming? If not, then you can contact Bogdan - he's the programmer I hired to write this contribution and he can make the changes for you (for a very reasonable fee). He's very good with programming for osCommerce - I use him exclusively for all my changes.

 

Mark

 

I didnt get a chance to install yet but I did just think of something else.

 

Since I am using multiple vendors on the site, will I be able to make sure the quickbooks integration only affects one of the vendors? I would only want it to update inventory from my actual store, not from the dropshipper!

Share this post


Link to post
Share on other sites

I just read that the QBI contribution isn't compatible with QuickBooks POS. If so, then this one probably isn't either as they both create pretty much the same import file for orders. If you know what the differences are, then I'm pretty sure the changes should be pretty easy.

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

×