Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Hi,

 

Normally, all the discounting should be done in osCommerce. That way, the invoice in osC will show the correct purchase prices. If your customer uses a payment gateway like Authorize.net, then obviously the discounts have to already be applied in osC or the incorrect amount would be charged to their credit card. I don't know if there is any contribution that allows pricing discounts for specific customers. There are discount coupons and vouchers.

 

When QBI imports an order, the price paid for an item in the shopping cart will supersede the price in QB, so it doesn't matter if the price in osC is lower than the price for it in QB. That's why the price, and any discounts, need to be set up in osC, not in QB.

 

As far as the price levels getting deleted, this is an unfortunate issue with iif files. The iif format is not very sophisticated, and when you import a customer again who has purchased before, some fields will get over-written. This is the intended behavior for QBI, so that the customer's contact information and billing/shipping addresses will get over-written and updated to match the latest order. QBI 4.0 will solve this issue by using xml instead of iif.

 

Thanks,

Adam

 

I have QBI 3.02 installed.

I was able to succesfully generate discounted orders. When I go to import the orders, the following msg shows up in the QBI import Orders file: 'Discount and Fee matching not set up'Understandably, because when I go to QBI setup->Discount/Fees, i see this: Quickbooks discounts/fees have not been set up. Before matching, you need to complete Set up: Discounts/Fees.

Ok, so I import the lists.iif, but it doesn't change anything ... still the same message.

Any ideas ? Is this why you suggested the new version of QBI ? I created a Test Discount and it's still active and in the table.

Thanks in advance ... I searched for this problem and didn't see it anywhere ... :'(

Link to comment
Share on other sites

I have QBI 3.02 installed.

I was able to succesfully generate discounted orders. When I go to import the orders, the following msg shows up in the QBI import Orders file: 'Discount and Fee matching not set up'Understandably, because when I go to QBI setup->Discount/Fees, i see this: Quickbooks discounts/fees have not been set up. Before matching, you need to complete Set up: Discounts/Fees.

Ok, so I import the lists.iif, but it doesn't change anything ... still the same message.

Any ideas ? Is this why you suggested the new version of QBI ? I created a Test Discount and it's still active and in the table.

Thanks in advance ... I searched for this problem and didn't see it anywhere ... :'(

Mike,

 

You have QBI 2.02 or 3.02? QBI 3.x issues need to be addressed in the QBI website forum and not here. This forum is for the free version of QBI.

 

Regardless of the version, in order to do Setup and Match of discounts, you need to have some discount items set up in Quickbooks. These are in the QB Items list, and must have Type "Discount." Then they will appear in Setup:Discounts when you import them. The dollar amount of the discount in QB doesn't matter, as the discount amount in each order will replace it. If they still don't appear, make sure that QBI is actually reading the correct file, as sometimes there can be an issue if there is both a lists.iif and lists.IIF file. Delete all the files in your qbi_input directory and try to upload it again.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam - I have a client that has t-shirts (different sizes/colors) wants to keep inventory on the site, then upload to quickbooks and adjust the individual inventory

 

QTPro seems to be able to handle the seperate inventory side of the problem - same shirt, differnt size and color breaking down the quantity for each, but will your contribution work to integrate with quickbooks?

 

I hope that makes sense. Thank you

Link to comment
Share on other sites

Adam - I have a client that has t-shirts (different sizes/colors) wants to keep inventory on the site, then upload to quickbooks and adjust the individual inventory

 

QTPro seems to be able to handle the seperate inventory side of the problem - same shirt, differnt size and color breaking down the quantity for each, but will your contribution work to integrate with quickbooks?

 

I hope that makes sense. Thank you

Hi,

 

I have not tested QBI with QTPro. However, if this is the same contribution that is integrated into CRE, then it does work with QBI 3.x. I can not guarantee compatibility with QBI 2.x.

 

Absolute inventory counts are not transferred with each order. If the count in osC and QB is correct to start with, then the count will get properly decremented in both osC and QB when an order is placed and transferred. There is no need to manually adjust the inventory in QB. Furthermore, since QBI imports products with attributes as item:subitem into QB (unless you use the matching feature in QBI, to make any arbitrary relationship), you can keep correct inventory counts of each size and color in QB whether or not you have modified osC to track inventory of attributes. The only case where you would need to make any manual inventory adjustment is if you do any offline sales in QB, and then you'd have to adjust the count in osC.

 

I believe with the contribution, each product/attribute combination is actually treated as a separate product.

 

If your client does not yet have their items set up in QB, it is best to create a Products iif file with QBI, and then import the list into QB. That way, all the items in QB will be sure to be listed correctly so that they are the same as when orders are imported.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi, and thanks for such a great contribution. I was wonder if it has been tested with the random order number / order number scrambler etc type of contributions. I don't really like the standard order numbers and contribution obviously uses this information and didn't know if anyone has tested them together. Many thanks.

 

Jonathan

Link to comment
Share on other sites

Thanks so much for creating this it looks exactly what we need if it can do this one thing.

 

We have 2 locations for selling our products, 1 is based in Florida and the Other in England. Any thing sold from Florida is sold from our US company and anything from England our UK company. All the products for each country are under 2 parent categopries eg one Says American Store and the Other UK Store.

 

We obviously have to do accounts for each. We were looking at how to integrate so it is not such a manual process and would buy quick books if it would be possible to set it up so we could download the sales from everything under one parent category to one quickbooks and everything form the other parent category into the other.

 

Many Thanks

 

Stuart

Link to comment
Share on other sites

Adam,

 

I read a post from last year regarding using the QBI with Quick books Merchant Processing. Is this feasible yet?

 

Thanks,

 

Michael

Michael,

You can use most payment gateways that process the CC charge within osC, like authorize.net. iif files can not contain credit card numbers, so you can not import orders and then process the credit card within QB (unless of course you copy the CC # in manually, which doesn't make much sense for most people). QBI 4.0 will support import of CC #s, but is a ways off being done.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi, and thanks for such a great contribution. I was wonder if it has been tested with the random order number / order number scrambler etc type of contributions. I don't really like the standard order numbers and contribution obviously uses this information and didn't know if anyone has tested them together. Many thanks.

 

Jonathan

I haven't tested this contribution, but QBI reads whatever order number is in the orders table and doesn't care what it is, so it shouldn't matter.

 

Thanks,

Adam

Link to comment
Share on other sites

Thanks so much for creating this it looks exactly what we need if it can do this one thing.

 

We have 2 locations for selling our products, 1 is based in Florida and the Other in England. Any thing sold from Florida is sold from our US company and anything from England our UK company. All the products for each country are under 2 parent categopries eg one Says American Store and the Other UK Store.

 

We obviously have to do accounts for each. We were looking at how to integrate so it is not such a manual process and would buy quick books if it would be possible to set it up so we could download the sales from everything under one parent category to one quickbooks and everything form the other parent category into the other.

 

Many Thanks

 

Stuart

If you can figure out a way to add a column to the orders table to differentiate the two types, then the QBI Create page can be modified pretty easily to make separate downloads for each one. But you have some issues as I see it -- you have no way to prevent someone from ordering some items from both the American Store and the UK Store. Also, you have issues with different currency display, and different shipping methods. And, the taxation setup is different. It would seem much easier to just install two copies of osC (and two copies of QBI) and have two different storefronts. Also, QBI does not yet work with UK Quickbooks. This feature will be added in QBI 4.0.

 

Thanks,

Adam

Link to comment
Share on other sites

If you can figure out a way to add a column to the orders table to differentiate the two types, then the QBI Create page can be modified pretty easily to make separate downloads for each one. But you have some issues as I see it -- you have no way to prevent someone from ordering some items from both the American Store and the UK Store. Also, you have issues with different currency display, and different shipping methods. And, the taxation setup is different. It would seem much easier to just install two copies of osC (and two copies of QBI) and have two different storefronts. Also, QBI does not yet work with UK Quickbooks. This feature will be added in QBI 4.0.

 

Thanks,

Adam

 

Thanks for the reply we have the taxation sorted out now OSC is really quite flexible in that regards and we have MVS (multi vendor shipping) installed to take care of shipping from different locations.

 

We will have it setup so if orders come from the American store (parent cat) then at checkout only the authorise.net module will be shown. If they order from UK store (parent cat no 2) then the PROTX module will be shown and if they order from both a message comes up and we collect the credit card and process the order manually and charge their cc to each store in the correct currency for what ever product they have bought.. Although with your advice it might be easier to not let them order form both stores at the same time to simplify things.

 

We don't actually expect many people will actually order form both stores at the same time and thinking it through as I said we may not allow it.

 

But it is very important for our UK customers to be able to buy non CE approved products form the US store without having to re register again and there will be some products our US customers will want to order form the UK store.

 

In terms of currency we have that sorted out with a contribution which lets you manually set price in correct currency so UK Store only has price in pounds and US store only has price in dollars and when you are in those stores the currency displayed gets changed to the correct one and you can not change it.

 

Not been the easiest to think around!!! The only thing left is to sort out some accounting software which is why this module looks a godsend.

 

Would there be anything stopping us using the US quickbook software for the UK as well as the US store do you think?

 

Thanks for your help and I am sure will be asking you just what needs changing on the QBI create page when we have added the extra column to the order table - what would you want the extra column to store - just a numerical value eg 1 for US and 2 for UK ?

 

Thanks so much for your advice and help.

 

Stuart

Link to comment
Share on other sites

I have just done some digging in the order table and I notice that an easy way to differentiate between orders from each country & store is the fact that the currency column in the orders table shows which store it is from eg for all orders from the American store this always shows USD and from the UK store always shows GBP.

 

 

With that differentiaiton allready there is it fairly easy then to modify the QBI create page to only download the orders for the appropriate store based on the currency into seperate quick books instances?

 

If that is the case we will set it so they can only have products in their basked and hence an order for one store at a time.

 

Many Many Thanks

 

Stuart

Edited by schford
Link to comment
Share on other sites

I have just done some digging in the order table and I notice that an easy way to differentiate between orders from each country & store is the fact that the currency column in the orders table shows which store it is from eg for all orders from the American store this always shows USD and from the UK store always shows GBP.

With that differentiaiton allready there is it fairly easy then to modify the QBI create page to only download the orders for the appropriate store based on the currency into seperate quick books instances?

 

If that is the case we will set it so they can only have products in their basked and hence an order for one store at a time.

 

Many Many Thanks

 

Stuart

Stuart,

 

In the file qbi_create.php, add after line 48:

$iif_form->where_clause("currency='USD');

 

Then, copy the entire orders section over below this section and make the other copy say =GBP.

 

At least you can download only the US orders this way.

But, I'm warning you, QBI will not work with UK QB until version 4.0. The iif file columns are different, and tax has to be included with the cost of each item.

 

Thanks,

Adam

Link to comment
Share on other sites

Stuart,

 

In the file qbi_create.php, add after line 48:

$iif_form->where_clause("currency='USD');

 

Then, copy the entire orders section over below this section and make the other copy say =GBP.

 

At least you can download only the US orders this way.

But, I'm warning you, QBI will not work with UK QB until version 4.0. The iif file columns are different, and tax has to be included with the cost of each item.

 

Thanks,

Adam

 

Thanks so much Adam I really appreciate all your help.

 

So I think what you are saying is I can not just buy 2 USA quick books and use one for the UK store as the tax works differently?

 

Stuart

Link to comment
Share on other sites

Thanks so much Adam I really appreciate all your help.

 

So I think what you are saying is I can not just buy 2 USA quick books and use one for the UK store as the tax works differently?

 

Stuart

I don't claim to be an expert on UK taxation, so I'm not quite sure the answer to that. There is a UK version of Quickbooks. In the UK, VAT is added to each product and the price for each line item includes VAT, unlike the US where tax is added at the bottom. UK customers are normally used to receipts including the VAT on each line item.

 

In the UK, there are multiple tax rates depending on the class of item, and some items are not taxed. In addition, if you sell to customers in other countries, there may be other considerations. US Quickbooks sets a tax rate per customer, not per item.

 

Also, it looks like "Businesses with annual sales below £62,000 don't have to register for (and therefore charge) VAT."

 

So, if you qualify to not collect VAT, or everything is at the same VAT rate, then maybe you can get by using US QB? I'm not sure that that would be a good idea, but I can't advise on that.

 

There is plenty of clamor for a UK compatible QBI, and I do have the software development kit and UK QB (as well as Canadian QB) and assure you all it will happen!

 

There's more info on UK taxation at:

http://www.direct.gov.uk/en/MoneyTaxAndBen...oTax/DG_4015895

 

Thanks,

Adam

Edited by adam5532
Link to comment
Share on other sites

Saw several old post reguarding problem with online and offline sales and the invoice number auto incrementing. Has there been any changes with this. I just want it to each time download an iif file and import it to be the next invoice number. If this is possible what is the code i should enter in the setup because i don't want to use the osc order number. Many thanks and love the contribution.

Link to comment
Share on other sites

Saw several old post reguarding problem with online and offline sales and the invoice number auto incrementing. Has there been any changes with this. I just want it to each time download an iif file and import it to be the next invoice number. If this is possible what is the code i should enter in the setup because i don't want to use the osc order number. Many thanks and love the contribution.

Hi,

 

Please read the QBI Configuration and User Manual page 9 and 10 (config options "Invoice Number" and "Invoice/Sales Receipt PO Number") for a complete explanation of how to set up Invoice and Sales Receipt numbers. For what you want to do, you would leave "Invoice Number" blank and leave "Invoice/Sales Receipt PO Number" set to the default %I.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

Great contrib! I have had it up and running for a while now. Just recently we moved servers. All the databases have been moved succesfully. I went to down load my orders and They are only showing from Jan31 (which is where it should start) to Feb 20. We have had sales since then, and I have also dowloaded the "All orders.iif, but it only shows these orders also.

This is what is show:

 

All Previous Orders

Quan: 1028

No.: 4 to 1183

Date: Apr 26, 2005 5:38 pm to Apr 4, 2007 5:58 am

 

But that is not the dates of the file I get.

Any suggestions?

 

2dogrc

Edited by 2dogrc
Link to comment
Share on other sites

Adam,

 

Great contrib! I have had it up and running for a while now. Just recently we moved servers. All the databases have been moved succesfully. I went to down load my orders and They are only showing from Jan31 (which is where it should start) to Feb 20. We have had sales since then, and I have also dowloaded the "All orders.iif, but it only shows these orders also.

This is what is show:

But that is not the dates of the file I get.

Any suggestions?

 

2dogrc

Make sure that the qbi_input and qbi_output directories are set to CHMOD 777 to enable full permissions. If QBI is unable to write, you may be downloading an old file.

 

Thanks,

Adam

Link to comment
Share on other sites

Hello,

 

I installed the import a while ago, and just got my first order. YAY!

 

Then I got the dreaded, transaction not in balance.

 

Once I figured out that I didn't have the shipping setup correctly, and then I had to create an account for discounts. So that was cool. But then I still got that error.

 

So I opened it up in a spreadsheet, and it had negatives for the revenue (product and shipping) and as well as the discount coupon. So I multiplied all the places where I saw the amount for the discount by -1 and then it imported correctly.

 

So I suppose I could do this for all of my discounts as I don't have very many of them, but I am wondering if I setup something incorrectly. For discounts, I am using ot_discount_coupon_codes_2.1

 

Thanks much.

 

--Brett

Link to comment
Share on other sites

Hello,

 

I installed the import a while ago, and just got my first order. YAY!

 

Then I got the dreaded, transaction not in balance.

 

Once I figured out that I didn't have the shipping setup correctly, and then I had to create an account for discounts. So that was cool. But then I still got that error.

 

So I opened it up in a spreadsheet, and it had negatives for the revenue (product and shipping) and as well as the discount coupon. So I multiplied all the places where I saw the amount for the discount by -1 and then it imported correctly.

 

So I suppose I could do this for all of my discounts as I don't have very many of them, but I am wondering if I setup something incorrectly. For discounts, I am using ot_discount_coupon_codes_2.1

 

Thanks much.

 

--Brett

Link to comment
Share on other sites

Hello,

 

I installed the import a while ago, and just got my first order. YAY!

 

Then I got the dreaded, transaction not in balance.

 

Once I figured out that I didn't have the shipping setup correctly, and then I had to create an account for discounts. So that was cool. But then I still got that error.

 

So I opened it up in a spreadsheet, and it had negatives for the revenue (product and shipping) and as well as the discount coupon. So I multiplied all the places where I saw the amount for the discount by -1 and then it imported correctly.

 

So I suppose I could do this for all of my discounts as I don't have very many of them, but I am wondering if I setup something incorrectly. For discounts, I am using ot_discount_coupon_codes_2.1

 

Thanks much.

 

--Brett

Brett,

 

There is disagreement among the various discount coupon contributions about whether a discount should be positive or negative. If the sign is wrong with the module you are using:

In QBI 2.10c,

File: /catalog/admin/includes/qbi_engine_orders.php

Line: 292

Change: if ($myrowotw['qbi_disc_type']=='DISC') $discvalue= -$discvalue;

To: // if ($myrowotw['qbi_disc_type']=='DISC') $discvalue= -$discvalue;

 

The // comments out the line so that the sign is not reversed.

 

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