Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Installed latest v2.02 (upgraded from v1.something) and I have a few questions:

 

--When I click create iif:(all previous orders)download iif file, i get the following

SQL error:

1064 - You have an error in your SQL syntax near '79-83 Pickup' AND pov1.products_options_values_id=pov2.products_options_values_' at line 1

This is happening because the apostrophe before 79 is messing up the SQL statement. I will have this fixed in the next version.

--The downloaded customer iif file seems to give me an iif of customers which have made orders. If a customer has ordered more than once, he shows up that many times.  What's the deal there?

Version 2.x allows you to configure the customer number any way you wish. Because of this, if you configure it differently than it was in version 1.x, then QB will see the customer as a new name. See the instructions for more on how to configure the customer number.

 

Note that the customer iif import is only if you need it for historical reasons. Customers are always imported and updated with each order, so there is no reason to ever import customers more than one time.

 

If you ever change the Customer Number format, then yes, QB will create a duplicate because the Customer Number is different.

--The new orders section says 0 even though i've had orders since upgrading.  Do I need to have matched all my products in order for it to work properly?

Check the setting for "Import Orders with Status" in the config. Also, look in your Orders table and see what you find in the qbi_imported column.

 

Thanks,

Adam

Link to comment
Share on other sites

Everything was working fine until a few weeks back when the module just stopped updating. It has not shown any new orders to download (although there have been plenty). Anyone have any ides how to fix this?

Link to comment
Share on other sites

Everything was working fine until a few weeks back when the module just stopped updating.  It has not shown any new orders to download (although there have been plenty).  Anyone have any ides how to fix this?

Check your setting for the option "Only Import Orders with Status" and make sure it is set correctly (usually to either Processing or All). This would be the most likely problem.

 

Also, look at the database table Orders and look at the column qbi_imported. The new orders should be set to 0, the last batch to 1, and all other previous orders to 2.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi All,

 

Just an update -- sorry for the long delay in releasing the next version. I will try to get it out this weekend if possible. Because of the delay, I am going to release what I have now instead of waiting to add additional features. Many of the requested features have been added, most of the bugs that have been reported are fixed, the screens look nicer, and more css and a bit of object oriented design has been added. My focus for the following release will be International versions and converting the database calls to an object oriented design. This will allow QBI to be adapted to other shopping carts, and also will prepare QBI to work with osC MS3.

 

Thanks,

Adam

Link to comment
Share on other sites

Just started researching your tool yesterday. Will products as services be supported in this next release?

 

I'm trying to set this up for a customer and after a day of not being able to get products in the drop lists, have read through the thread and note that products setup as services are not supported yet.

Link to comment
Share on other sites

Just started researching your tool yesterday.  Will products as services be supported in this next release?

 

I'm trying to set this up for a customer and after a day of not being able to get products in the drop lists, have read through the thread and note that products setup as services are not supported yet.

Yes, the next version supports importing products as Inventory, Non-inventory, or Services. The setting applies to all your products -- you can't set some one way and others another, unless you manually edit the iif file. On the other hand, you can also match to Inventory, Non-inventory, and Service items, so this way you can mix and match.

 

Thanks,

Adam

Link to comment
Share on other sites

Check your setting for the option "Only Import Orders with Status" and make sure it is set correctly (usually to either Processing or All). This would be the most likely problem.

 

Also, look at the database table Orders and look at the column qbi_imported. The new orders should be set to 0, the last batch to 1, and all other previous orders to 2.

 

Thanks,

Adam

 

 

Adam,

Thank you. It was the order status. How did I ever miss that? It was the most obvious!

Link to comment
Share on other sites

Version 2.10 has just been released:

 

New Features:

1. Product export can be set as inventory, non-inventory, or services.

2. Product export supports up to 2 levels of attributes.

3. Products export includes inventory quantity.

4. Added products export for all, previous, or new products.

5. Option added to not export inactive products.

6. Non-inventory and services matching added.

7. Discounts and other charges can now be matched.

8. Discounts and other charges can now be included in Quickbooks groups.

9. Default item option.

10. Setup screens now give name of iif file found.

11. Added customer's fax number and option to import as Alt Phone.

12. Added logging option.

13. Moved menu system to separate file.

14. Added css classes and improved interface styling.

15. Converted some parts to object oriented design.

Fixes:

16. Discounts were being added instead of subtracted.

17. Rounding errors corrected that occasionally caused out-of-balance condition.

18. Top level of a product with attributes would import as a service.

19. Eliminated "not an array" errors on set-up pages.

20. If setup fails to find items in the iif, a failure instead of a success message is now given.

21. Apostrophes in product attribute values no longer cause an SQL error.

22. "All Previous Orders" was erroneously including "Previous Batch" in the count.

23. Hidden Quickbooks items are no longer imported.

24. Improved handling of Quickbooks groups.

25. Customer iif was repeating headers for each customer.

 

The next version will focus on international versions of QB, and conversion of db calls to objects to prepare for MS3.

 

Thanks,

Adam

Link to comment
Share on other sites

I sell products, as well as services. Services are distinct by part number. Is there a section of the code you would suggest I look at to get each tagged appropriately for import.

 

Example:

CSV-***=Service

V01-***=Inventory Part

 

 

 

Version 2.10 has just been released:

 

New Features:

1. Product export can be set as inventory, non-inventory, or services.

 

Link to comment
Share on other sites

Hey Adam,

 

Thanks for the great contribution.

 

I had a little problem installing it. After about 10 minutes of fooling around with it, I realized what was happening. This might help other people who have the same trouble I did.

 

I followed the installation instructions to the letter, after I installed the database tables, I didn't see the success message. I tried again, and again. Droping the tables and fields, like you indicated in your Installation document. Nothing worked.

 

Finally, I realized that it was qbi_versions.php that was giving me the trouble. So I rewrote the code a little to fix the problem. Here is the fix.

 

// Check if QB Import configured and installed
 $check_qbi_config_version_query = tep_db_query("select qbi_config_ver, qbi_config_active from " . TABLE_QBI_CONFIG);
 if ($check_qbi_config_version = tep_db_fetch_array($check_qbi_config_version_query)) {
   if ($check_qbi_config_version['qbi_config_ver'] == QBI_VER) {
     if ($check_qbi_config_version['qbi_config_active'] != 1 && $_SERVER['PHP_SELF'] != DIR_WS_ADMIN . 'qbi_config.php') {
       header("Location: qbi_config.php?msg=1");
     }
   } else {
     header("Location: qbi_db.php?db_ver=" . $check_qbi_config_version['qbi_config_ver'] . "&qbi_vers=" . QBI_VER);
   }
 } else {
   header("Location: qbi_db.php?db_ver=0.00&qbi_vers=" . QBI_VER);
 }

 

The database array variable was not getting initialized before it was being tested. This could have also been fixed by putting brackets around both statements, but I like this solution because it lays the code out nice and easy to read.

 

Thanks

 

JINX

Link to comment
Share on other sites

Opps, sorry the code in the above post will not work unless you change the constants in qbi_definitions.php

 

Update code

 

// Check if QB Import configured and installed
$resultqbc = tep_db_query("select * from " . TABLE_QBI_CONFIG);
if ($myrowqbc = tep_db_fetch_array($resultqbc)) {
  if ($myrowqbc['qbi_config_ver'] == QBI_VER) {
    if ($myrowqbc['qbi_config_active'] != 1 && $_SERVER['PHP_SELF'] != DIR_WS_ADMIN . 'qbi_config.php') {
      header("Location: qbi_config.php?msg=1");
    }
  } else {
    header("Location: qbi_db.php?db_ver=" . $myrowqbc['qbi_config_ver'] . "&qbi_vers=" . QBI_VER);
  }
} else {
  header("Location: qbi_db.php?db_ver=0.00&qbi_vers=" . QBI_VER);
}

Link to comment
Share on other sites

I sell products, as well as services.  Services are distinct by part number.  Is there a section of the code you would suggest I look at to get each tagged appropriately for import.

 

Example:

CSV-***=Service

V01-***=Inventory Part

There isn't any database field in osC to store this information for each product, since it isn't a feature that osC supports. So, there isn't any place in the QBI code to make this change.

 

You can open the iif file in Excel and change your products as needed before importing into QB. After that, orders should import correctly - you don't have to correct anything in the orders.

 

The alternative is to use the matching feature of QBI. Then, you can match osC items to QB items (inventory, non-inventory, and services). This requires that the item is already set up in both osC and QB.

 

Thanks,

Adam

Link to comment
Share on other sites

There isn't any database field in osC to store this information for each product, since it isn't a feature that osC supports. So, there isn't any place in the QBI code to make this change.

 

You can open the iif file in Excel and change your products as needed before importing into QB. After that, orders should import correctly - you don't have to correct anything in the orders.

 

The alternative is to use the matching feature of QBI. Then, you can match osC  items to QB items (inventory, non-inventory, and services). This requires that the item is already set up in both osC and QB.

 

Thanks,

Adam

 

 

I was wondering if you could point me to a place where I could decode the part number string, and then write the field to the iif file on an item by item basis.

 

I haven't looked, but I figure the product iif generation script is a loop, where I could add some logic to create more detailed class/account/type labels on a per product basis. I can likely find it, but was wondering if you had any file/line based pointers on where to look.

Link to comment
Share on other sites

I was wondering if you could point me to a place where I could decode the part number string, and then write the field to the iif file on an item by item basis.

 

I haven't looked, but I figure the product iif generation script is a loop, where I could add some logic to create more detailed class/account/type labels on a per product basis.  I can likely find it, but was wondering if you had any file/line based pointers on where to look.

The product iif is created in qbi_engine_prod.php, and there are comment lines describing what each part of the script does.

 

Thanks,

Adam

Link to comment
Share on other sites

I am setting up QBI and I've ran into a snag with customer discounts. I created a customer discount item in QB as shown in QB's help file and it shows up in the items.iif file like this:

 

INVITEM	Customer Discount	43	1115869698	DISC	Customer Discount  Discounts    0.00	0.00	0.00	Y	Tax            0	N	N	0	N	Y        0.00 	 0.00	0.00	0.00

 

When I try to import the iif file under fees/discounts, it says there are no discount items. Is QBI not looking at this as a discount because the first field is INVITEM and it's not picking up on the DISC in the type field?

 

I looked at the php code for discounts but I couldn't decipher it well enough to understand what's causing it not to import.

 

Thanks!

Eddy

Link to comment
Share on other sites

I am setting up QBI and I've ran into a snag with customer discounts.

 

... When I try to import the iif file under fees/discounts, it says there are no discount items...

 

Thanks!

Eddy

Eddy,

 

The format looks correct -- the line should start with INVITEM. It is working correctly for me, but maybe something in your file is confusing it! First, check that you only have one iif file in the qbi_input directory, and that QBI is reading the right one. For Discounts/Fees, acceptable file names are as follows, priority is left to right, and it is case-sensitive: ("qbi_input/items.iif","qbi_input/items.IIF","qbi_input/lists.iif","qbi_input/lists.IIF").

 

If that is all ok, please email me your iif file so I can test it and resolve the problem. My email is in the QBI instruction manual.

 

Thanks,

Adam

Link to comment
Share on other sites

Eddy,

 

The format looks correct -- the line should start with INVITEM. It is working correctly for me, but maybe something in your file is confusing it! First, check that you only have one iif file in the qbi_input directory, and that QBI is reading the right one. For Discounts/Fees, acceptable file names are as follows, priority is left to right, and it is case-sensitive: ("qbi_input/items.iif","qbi_input/items.IIF","qbi_input/lists.iif","qbi_input/lists.IIF").

 

If that is all ok, please email me your iif file so I can test it and resolve the problem. My email is in the QBI instruction manual.

 

Thanks,

Adam

 

I tried items.iif and lists.iif and stripped out everything but the one discount item but it still says the same thing.

I'll email you the items.iif file.

 

Thanks!

Eddy

Link to comment
Share on other sites

hey all,

i have quickbooks premier 2004 . will this contribution work it?

 

thankx

As far as I know, it will work with all versions of Quickbooks through 2005 except the on-line version. If you have a menu option FILE:IMPORT:IIF FILES then it will work.

 

Thanks,

Adam

Link to comment
Share on other sites

Here's one: All the items are not showing up as paid in Quickbooks. Most of these are paid for instantly with the authorize.net credit card gateway. I've been going over (and over) the docs trying to figure out how this could be an error on my end but everything APPEARS to be set correctly.

Link to comment
Share on other sites

Here's one: All the items are not showing up as paid in Quickbooks.  Most of these are paid for instantly with the authorize.net credit card gateway.  I've been going over (and over) the docs trying to figure out how this could be an error on my end but everything APPEARS to be set correctly.

1) You need to have a payment method set up in QB and imported to QBI. In Setup:Payments, you need to have that method set to Paid Online.

2) You need to match Credit Card Via Authorize.net to this QB payment method in Match:Payments.

3) In config, if you have Payments:Import Payments set to As Invoice and Payment, after importing your orders, you have to go in QB to Customers:Receive Payments and associate each customer payment with the invoice. If you have Payments:Import Payments set to As Sales Receipt, then the payment will be recorded automatically and will appear in your Payments to Deposit window in QB.

4) If it still doesn't work, go into PHPMyAdmin or similar, and look at the Orders table field Payment Method and check that it says Credit Card Via Authorize.net. If it does not exactly match this, then that is the problem and I can help you further at that point.

 

Thanks,

Adam

Link to comment
Share on other sites

As far as I know, it will work with all versions of Quickbooks through 2005 except the on-line version. If you have a menu option FILE:IMPORT:IIF FILES then it will work.

 

Thanks,

Adam

 

 

thankx, yes i do have file menu, with import> iff files option

:)

 

will start installing now

Link to comment
Share on other sites

Adam:

 

I don't know where to begin. This is great. I'm an artist, but I also give QuickBooks Seminars to other artists and galleries about how to utilize Quickbooks. I try to keep up on the different plug-ins that are of value to my peers so I can share the information. For some time, I've been waiting for a great and affordable webstore to QuickBooks link to emerge. Two years ago, I bought StoreFront.net, thinking that was the great solution. It's not. Their link is very buggy and hard to get to work right. It was too much for a non-programmer and I've all but abandoned the whole program.

 

Well, last week, I noticed that my website had osCommerce pre-loaded. I don't have a burning need for a webstore, since about 85% of my sales are to galleries. But I'm a curious guy, so I poked around enough to get the idea of the project. I remember thinking that it might be a good thing, if it only had a way to input into QuickBooks.

 

Two days ago, I was doing a semi-routine Google search to root out any overlooked tidbits, with "ecommerce" and "QuickBooks" as the search terms. I clicked a couple of links that I already knew about, and just as I was about to move on, I caught the osCommerce title. It sent me to version 1, and I spent time reading and following the trail to get to this spot. All I can say is "WOW"! Fantastic job!!! What you are doing is wonderful!

 

So, I'm getting ready to dive into this. I'm not a dedicated web designer, so there's a bit of a learning curve for me. I'm just now getting ready to dump my FrontPage extensions and start using Dreamweaver (with some hand-coding thrown in). I also wanted to learn how to use Mysql and PHP, so I've *just* purchased a beginner book (by W. Jason Gilmore) to get a basic understanding (before using a GUI). I have a basic understanding of CSS, and know how it works and where to find answers. But I imagine it will be a little while before I'm ready to jump into working on incorporating osCommerce and QBI. But that doesn't stop my burning desire to find answers to some questions. If you don't mind, may I ask a few?

 

I have about 70 active galleries. I figure the best way for me to start working with osCommerce is to set it up for their use (wholesale). I try not to sell too much retail on the web because it often ends up being my galleries' customers trying to get a "deal". This is a fast way to lose a gallery. I'll need to explore the best way to hide the (wholesale) prices from non-gallery customers, but that's not really related to QBI.

 

Question 1: About 25% of my gallery orders are drop-shipments. Can my galleries log in, place and order, and enter a different "ship-to" address?

 

Question 2: If #1, above, is yes, can the ship-to field contain a phone number at the bottom? This is a feature desired for using the ShipRush FedEx Ground plug-in. By putting the phone number for the ship-to customer at the bottom of the shipping address field, ShipRush will automatically enter it in the correct phone field in the FedEx shipping screen. This would totally automate the entire placement and fulfillment process if it worked.

 

Question 3: Can the web orders go into QBs Premier as Sales Orders?

 

That's about it for now. I'm sure I'll have more as I chew on the idea of getting this up and running. Once again I want to thank your for doing this contrabution. I'm thrilled at the thought of having an affordable (very affordable) webstore link available.

 

Brian

Link to comment
Share on other sites

Adam:

 

I don't know where to begin.  This is great.

Thanks for the kudos!

Question 1:  About 25% of my gallery orders are drop-shipments.  Can my galleries log in, place and order, and enter a different "ship-to" address?

Yes. osC allows different billing and shipping addresses, and so does QB, so both addresses get imported with QBI.

Question 2:  If #1, above, is yes, can the ship-to field contain a phone number at the bottom? This is a feature desired for using the ShipRush FedEx Ground plug-in.  By putting the phone number for the ship-to customer at the bottom of the shipping address field, ShipRush will automatically enter it in the correct phone field in the FedEx shipping screen.  This would totally automate the entire placement and fulfillment process if it worked.

I'm not quite following, as if you click "Address Details" in QB it doesn't expect to see a phone number in the address area.

 

I use ShipRush UPS (the stand-alone Pro version, not the built-in version which is limited in features), and using "Quickbooks Customer List" address mode it pulls the phone number from the Phone field in the QB Edit Customer screen, which QBI imports from osC.

 

Unfortunately, despite the complexity and features of both programs, neither osC nor QB allows you to enter separate phone numbers for the billing and shipping addresses, so I'm not quite following why you would put the phone number in the shipping address area instead of the phone number field, unless you made the Fax number in osC the Shipping Phone number and wanted it imported there.

Question 3: Can the web orders go into QBs Premier as Sales Orders?

Yes.

 

I'll try to post an updated version this weekend to resolve the problem with new installations and a couple of minor things.

 

Thanks,

Adam

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