Jump to content
adam5532

QBI Quickbooks Import

Recommended Posts

Hi all,

 

I am currently trying to get QB and osC to line using this module. I am having some problems when I try to import orders etc. The main problem I get is that when i ty to do the import it says that "you cannot associate an item with VAT control, undeposited funds,accounts receivable, accounts payable, non-posting or suspense accounts" - does anyone know what part of my settings is causing this error?

 

Whn I open the .iif file all the details in there match the invoices from which they have been exported.

 

Have heard a horribe rumour that this does not work with UK Quickbooks, with VAT at 17.5%. Is this true? I figured that if it works with US sales taxes then is no reason why it shouldnt work with UK sales tax?

 

Thanks in advance for your attention

Edited by accountancyboy

Share this post


Link to post
Share on other sites
Have heard a horribe rumour that this does not work with UK Quickbooks, with VAT at 17.5%. Is this true? I figured that if it works with US sales taxes then is no reason why it shouldnt work with UK sales tax?

 

Thanks in advance for your attention

 

Hi,

 

Your heard right, at the moment QBI Import does not support UK quickbooks and also Europe I believe, I think I saw a question abut it from Germany.

 

Last time I asked Adam, he said it was going to be in a future release. Unless anything has changed recently, but remember Adam has put in around 250 hours of hard work in, on this contrib so far.

 

I have tried all the contribs for QB and OSC since I started using OSC on 2003, now 2 years down the line and still have not got a decent solution, except this contrib, but am having to wait until Adam puts the support for UK VAT in to his contribution.

 

There are other difference as well I believe including VAT I think they total 5.

 

Patience is a virtue, and after 2 years I have a lot of virtue :D

 

Living in hope

Andy


Literally, Laterally Thinking! If you cannot get through it, go round it.

Share this post


Link to post
Share on other sites

hey adam.. quick one

 

if i change the model #'s on the website will it affect the way it imports the item to quickbooks? like.. would i create a new item in qb?

Share this post


Link to post
Share on other sites
hey adam.. quick one

 

if i change the model #'s on the website will it affect the way it imports the item to quickbooks? like.. would i create a new item in qb?

Both osC and QB items are matched by reference number. So, you should be able to change the model # and/or name and they will still stay matched. If you are not using matching, then you would have to either change the name in QB or make a new item with the same model #.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Both osC and QB items are matched by reference number. So, you should be able to change the model # and/or name and they will still stay matched. If you are not using matching, then you would have to either change the name in QB or make a new item with the same model #.

 

Thanks,

Adam

 

where can i find the reference #? i want to reinput the infomration that i erased so that i don't have to get those balance errors messages while inputing

Edited by superman123

Share this post


Link to post
Share on other sites

Okay,

 

I'm very close to having everything work, but cannot get Discounts to import properly. The iif is found, and the Contrib claims success, but then nothing is available in my match screen.

 

My iif looks like this:

!HDR	PROD	VER	REL	IIFVER	DATE	TIME	ACCNTNT	ACCNTNTSPLITTIME                                      
HDR	QuickBooks Pro	Version 15.0D	Release R3P	1	3/1/05	1109695826	N	0                                      
!CUSTITEMDICT	INDEX	LABEL	INUSE                                         	 
!ENDCUSTITEMDICT                                              
CUSTITEMDICT	0  N                                         	 
CUSTITEMDICT	1  N                                         	 
CUSTITEMDICT	2  N                                         	 
CUSTITEMDICT	3  N                                         	 
CUSTITEMDICT	4  N                                         	 
ENDCUSTITEMDICT                                              
!INVITEM	NAME	REFNUM	TIMESTAMP	INVITEMTYPE	DESC	PURCHASEDESC	ACCNT	ASSETACCNT	COGSACCNT	QNTY	QNTY	PRICE	COST	TAXABLE	SALESTAXCODE	PAYMETH	TAXVEND	TAXDIST	PREFVEND	REORDERPOINT	EXTRA	CUSTFLD1	CUSTFLD2	CUSTFLD3	CUSTFLD4	CUSTFLD5	DEP_TYPE	ISPASSEDTHRU	HIDDEN	DELCOUNT	USEID	ISNEW	PO_NUM	SERIALNUM	WARRANTY	LOCATION	VENDOR	ASSETDESC	SALEDATE	SALEEXPENSE	NOTES	ASSETNUM	COSTBASIS	ACCUMDEPR	UNRECBASIS	PURCHASEDATE
INVITEM	GIFTCERTIFIC	511	1108487035	INVENTORY	Gift Certificate	Gift Certificate  Inventory Asset	Cost of Goods Sold	0	0	1	0	N	Non            0	N	N	0	N	N        0 	 0	0	0	
INVITEM	RETURNCREDIT	2462	1108487174	INVENTORY	Return Credit	Return Credit  Inventory Asset	Cost of Goods Sold	0	0	0	0	N	Non            0	N	N	0	N	N        0 	 0	0	0	
INVITEM	COD-FEE	5367	1109694885	OTHC	Cash on Delivery Fee  Sales:Shipping and Handling    0  0	N	Non            0	N	N	0	N	Y        0 	 0	0	0	
INVITEM	LOW-FEE	5369	1109695039	OTHC	Low Order Fee  Sales:Merchandise    0	0	0	Y	Tax            0	N	N	0	N	Y        0 	 0	0	0	
INVITEM	DIS-CPN	5368	1109694945	DISC	Discount Coupon  Sales:Discounts Given    0	0	0	Y	Tax            0	N	N	0	N	Y        0 	 0	0	0

 

I would love to input directly into the db, but am unsure how to apply the correct relationships within the multiple tables, qbi_disc, qbi_ot, qbi_ot_disc.

 

I could probably figure it out if someone posted a dump of these three tables...

Share this post


Link to post
Share on other sites
Okay,

 

I'm very close to having everything work, but cannot get Discounts to import properly.  The iif is found, and the Contrib claims success, but then nothing is available in my match screen.

 

My iif looks like this:

!HDR	PROD	VER	REL	IIFVER	DATE	TIME	ACCNTNT	ACCNTNTSPLITTIME                                      
HDR	QuickBooks Pro	Version 15.0D	Release R3P	1	3/1/05	1109695826	N	0                                      
!CUSTITEMDICT	INDEX	LABEL	INUSE                                         	 
!ENDCUSTITEMDICT                                              
CUSTITEMDICT	0  N                                         	 
CUSTITEMDICT	1  N                                         	 
CUSTITEMDICT	2  N                                         	 
CUSTITEMDICT	3  N                                         	 
CUSTITEMDICT	4  N                                         	 
ENDCUSTITEMDICT                                              
!INVITEM	NAME	REFNUM	TIMESTAMP	INVITEMTYPE	DESC	PURCHASEDESC	ACCNT	ASSETACCNT	COGSACCNT	QNTY	QNTY	PRICE	COST	TAXABLE	SALESTAXCODE	PAYMETH	TAXVEND	TAXDIST	PREFVEND	REORDERPOINT	EXTRA	CUSTFLD1	CUSTFLD2	CUSTFLD3	CUSTFLD4	CUSTFLD5	DEP_TYPE	ISPASSEDTHRU	HIDDEN	DELCOUNT	USEID	ISNEW	PO_NUM	SERIALNUM	WARRANTY	LOCATION	VENDOR	ASSETDESC	SALEDATE	SALEEXPENSE	NOTES	ASSETNUM	COSTBASIS	ACCUMDEPR	UNRECBASIS	PURCHASEDATE
INVITEM	GIFTCERTIFIC	511	1108487035	INVENTORY	Gift Certificate	Gift Certificate  Inventory Asset	Cost of Goods Sold	0	0	1	0	N	Non            0	N	N	0	N	N        0 	 0	0	0	
INVITEM	RETURNCREDIT	2462	1108487174	INVENTORY	Return Credit	Return Credit  Inventory Asset	Cost of Goods Sold	0	0	0	0	N	Non            0	N	N	0	N	N        0 	 0	0	0	
INVITEM	COD-FEE	5367	1109694885	OTHC	Cash on Delivery Fee  Sales:Shipping and Handling    0  0	N	Non            0	N	N	0	N	Y        0 	 0	0	0	
INVITEM	LOW-FEE	5369	1109695039	OTHC	Low Order Fee  Sales:Merchandise    0	0	0	Y	Tax            0	N	N	0	N	Y        0 	 0	0	0	
INVITEM	DIS-CPN	5368	1109694945	DISC	Discount Coupon  Sales:Discounts Given    0	0	0	Y	Tax            0	N	N	0	N	Y        0 	 0	0	0

 

I would love to input directly into the db, but am unsure how to apply the correct relationships within the multiple tables, qbi_disc, qbi_ot, qbi_ot_disc.

 

I could probably figure it out if someone posted a dump of these three tables...

Please clarify: Do your extra charges import properly (low order fee, etc), but not the Discount Coupon? Or do none of them import?

 

Note that you have "Gift Certificate" and "Return Credit" set up as inventory items, not discounts, so these will import as products and not discounts unless you change them to be discounts. But the "Discount Coupon" should show up.

 

What is the name of your import iif file?

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
where can i find the reference #? i want to reinput the infomration that i erased so that i don't have to get those balance  errors messages while inputing

You don't really need to get the reference numbers -- they are internal to osC and QB, and are just a unique number that follows each item, so that if you change its name the program will know that it is still the same item.

 

I'm not clear what you are trying to do.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Please clarify: Do your extra charges import properly (low order fee, etc), but not the Discount Coupon? Or do none of them import?

 

Note that you have "Gift Certificate" and "Return Credit" set up as inventory items, not discounts, so these will import as products and not discounts unless you change them to be discounts. But the "Discount Coupon" should show up.

 

What is the name of your import iif file?

 

Thanks,

Adam

 

I have Gift and Return set up as inventory items with 9999999 levels so I can sell them on an inventory tracking site, and add them via Order Editor. I'm not too sure how to properly set up accounts within QB and within Sales to properly place items, but I have 1200 pages of exciting prose on my desk at the moment...

 

 

When I try to import via either items.iif or lists.iif from QBI:Set Up: Discounts/Fees, I'm told:

1. Found iif import file. Import now?

2. Setup Successful

 

However, when I try to use QBI:Match:Discounts/Fees, there are no items tro match. There are also no Rows inside of qbi_disc or qbi_ot_disc. qbi_ot shows the default four.

Share this post


Link to post
Share on other sites

Additional observation:

 

When using Mozilla 1.7 on a Macintosh running 10.3.8, I am able to hang the browser's navigation abilities by choosing Match, and then choosing a sub page other than products. This is similar to the behavior experienced when an alert is open, but hidden under active windows. You can select tabs, enter date into text fields, but cannot follow links, or move forward or back. I can replicate this error about 50% of the time, and will investigate (time permitting) to see if it has something to do with whether the page has parsed/loaded completely.

 

It is happening right now, and happened on my last post, so I'll be relaunching Mozilla to quit before I actually post this.

Share this post


Link to post
Share on other sites
I have Gift and Return set up as inventory items with 9999999 levels so I can sell them on an inventory tracking site, and add them via Order Editor.  I'm not too sure how to properly set up accounts within QB and within Sales to properly place items, but I have 1200 pages of exciting prose on my desk at the moment...

When I try to import via either items.iif or lists.iif  from QBI:Set Up: Discounts/Fees, I'm told:

1. Found iif import file. Import now?

2. Setup Successful

 

However, when I try to use QBI:Match:Discounts/Fees, there are no items tro match.  There are also no Rows inside of qbi_disc or qbi_ot_disc.  qbi_ot shows the default four.

Make sure that your qbi_disc table rows are correctly specified (types are correct, auto-increment is correct, fields spelled properly), if you added it manually after upgrading from version 1 to version 2.00 - 2.02, or after doing a fresh install with version 2.00 or 2.01. (This bug was fixed in version 2.03). If there is an SQL error when writing to the qbi_disc table, you will not see this displayed on your browser.

 

Otherwise, I don't know what the problem is. You can email me your iif file, and I'll try it here.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Additional observation:

 

When using Mozilla 1.7 on a Macintosh running 10.3.8

I'm using Firefox 1.0, so as far as I know there isn't a browser problem. Try with IE and see if it is the same.

 

As far as the "Setup Successful" message, it always does this after reading the file! I'll be modifying this in the future so that it doesn't give success if no rows are written.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

mm.. i'm having some trouble..

 

i never d/l the new orders from QBI but when i updated the status on my orders from pending to processing the counter in QBI went to ZERO. how can i prevent that? i unchecked "update status" on the config.

 

thanks

Edited by superman123

Share this post


Link to post
Share on other sites
mm.. i'm having some trouble..

 

i never d/l the new orders from QBI but when i updated the status on my orders from pending to processing the counter in QBI went to ZERO. how can i prevent that? i unchecked "update status" on the config.

 

thanks

Change the option Orders|Import Orders with Status: from "Pending" to "(any)".

 

Orders|Update Status determines in QBI should change the status itself when you download new orders. You'll probably find that this is the fastest way to do it, as then you don't need to open each order yourself to change the status.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

I saw a couple posts about QBI not working with European tax systems. Is this due to the Quickbooks version (e.g. US Version versus German-Version) or because of the configuered taxes? Put it in another way, can I uses the US-Version of quickbooks and configure the taxes according to German standards for example???

Share this post


Link to post
Share on other sites
I saw a couple posts about QBI not working with European tax systems. Is this due to the Quickbooks version (e.g. US Version versus German-Version) or because of the configuered taxes? Put it in another way, can I uses the US-Version of quickbooks and configure the taxes according to German standards for example???

There are differences in the required tax fields. This will be added in the future, but is not presently supported.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Hello Adam,

 

Great contribution, thankyou! It saves heaps on the shipping/Fedex side as we process heaps of orders per week.

 

However, here is what I am running into. Using Pro 2005 and osCMAX2.2 ver 1.7.2. Install went ok - no problem there. I set a new test company to handle just store orders, so as to not confuse the main company file and thank *** I did.

 

After doing the "All Products - download iif file" (over 14,500 products later and the maximum allowed for this version of QuickBooks!), the products found their way into every conceivable Item and/or Chart of Account list.

 

In Chart Of Accounts, there are:

 

1. products in Name, under Other Current Asset, Income, Cost Of Goods Sold.

2. prices in Name and under Cost of Goods Sold.

 

In Item List, there are:

 

1. Products in Name, Description and Account fields. I must have 1000's os products now labeled as an Account.

2. Product Descriptions as their own Accounts.

 

Anyway, major screw up and luckily just a test company as the thought of deleting over 14,500 items one at a time is beyond me! (Actually there are 7,000 products with 16,000 product attributes) The thing is, I do not keep inventory and really don't want all products in the QBKS. How can I get by this, but still pull the invoice through into QuickBooks as a Materials>>Non-Inventory Part>> Reimbursed Expense? Is this the right way, or do you have another suggestion?

 

Thanks again,

Greg

Share this post


Link to post
Share on other sites
Hello Adam,

 

After doing the "All Products - download iif file" (over 14,500 products later and the maximum allowed for this version of QuickBooks!), the products found their way into every conceivable Item and/or Chart of Account list. 

Sounds quite strange. Send me the iif file by private email so I can test it and see what is happening.

The thing is, I do not keep inventory and really don't want all products in the QBKS.  How can I get by this, but still pull the invoice through into QuickBooks as a Materials>>Non-Inventory Part>> Reimbursed Expense? Is this the right way, or do you have another suggestion?

 

Thanks again,

Greg

You can match all the osC products to one QB item. This would be rather time consuming, although you could do it in Excel and then import back into the db table.

 

This has been requested by a number of people. I am working on the next version, and will add an option to have all products match to a single QB item (non-inventory), with the product description flowing through to the invoice. Maybe I'll have that ready in a week or so.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Hello Adam,

 

Sorry about the large size, I suppose I could have shortened it but I'm sure you wanted to see it in its entirety.

 

Obviously, I would like to see all the products as I do a lot of quotes for customers and I'm having a quoting system being written as we speak. It will make the accounting process easier. However, I do realize that I won't see my products if I elect to match to one QB item. Right now I have 20 orders to ship out Monday and I have hand write all FedEx tickets!

 

It does appear in the excel view that all is in order, maybe my QuickBooks malfunctioned at the time of importing?

 

Thanks and I look forward to your reply.

 

Greg

Share this post


Link to post
Share on other sites

Hi Adam,

Great contribution, thanks so much for all your efforts.

 

1. Instead of beeing able to import orders with a certain status, is there a way to mask out orders with one or a group of status flags?

I also use my store for quoting certain products and as long as they are still on status "Quoted" or "Waiting for Customer", I don't want to import these into QB. Orders are charged externally.

 

2. I use QB Pro 2000 (which btw. supports 5 address lines) and when importing orders, QB suddenly crashes. Is QB 2000 Pro supported or should I buy a newer version. If it is supported, any idea why it crashes?

 

 

Thanks

Uli

Share this post


Link to post
Share on other sites
Hi Adam,

Great contribution, thanks so much for all your efforts.

 

1. Instead of beeing able to import orders with a certain status, is there a way to mask out orders with one or a group of status flags?

I also use my store for quoting certain products and as long as they are still on status "Quoted" or "Waiting for Customer", I don't want to import these into QB. Orders are charged externally.

I could do that, but I don't know if there is any demand for such a feature. Couldn't you just change those "Quoted" or "Waiting for Customer" orders back to "Pending" and set QBI to only import Pending orders?

2. I use QB Pro 2000 (which btw. supports 5 address lines)

Ok, I'll fix that.

and when importing orders, QB suddenly crashes. Is QB 2000 Pro supported or should I buy a newer version. If it is supported, any idea why it crashes?

I don't think it has anything to do with the version. QB can crash if it doesn't like something in the iif file. Have you completed all the setup for products, payment methods, shipping, and charges/discounts?

 

Note that you should not try to match items that have more than one set of attributes -- that is not yet supported.

 

Are you using sales tax, and is that set up correctly?

 

Are you using the US version of QB (international versions are not yet supported)?

 

Also, I just discovered a bug in the discounts / gift vouchers feature that will cause an error (they get added instead of subtracted). This is fixed in the next release, due out in maybe a week or two.

 

Look at the iif in Excel, and look for any orders where the sum of all the items does not equal the negative of the total in the TRANS line. That will help track down the problem.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Question for those in the UK:

 

Do you need to handle more than one tax code and rate? In other words, are your orders all the same tax code and rate, or does it vary for different items? Does the tax charged vary depending on the location of the purchaser? Is it determined by the payment or shipping address? If the order is shipped out of the country, is VAT charged? Is the price always displayed with VAT included?

 

Thanks!

Adam

Share this post


Link to post
Share on other sites
Question for those in the UK:

 

Do you need to handle more than one tax code and rate? In other words, are your orders all the same tax code and rate, or does it vary for different items? Does the tax charged vary depending on the location of the purchaser? Is it determined by the payment or shipping address? If the order is shipped out of the country, is VAT charged? Is the price always displayed with VAT included?

 

Thanks!

Adam

 

Adam,

 

there can be a variety of tax rates - 5%/17.5% - in most cases it will be 17.5%. Any sales abroad will not have VAT charged though

 

In terms of the programming to handle this UK progrmas tend to have a tax code attached to the product in the first place - i.e it is taxable or not. This can then be overridden by the customer tax field - i.e a taxable product sold to a customer who does not get charged tax (e.g. abroad) will show up as not being taxable

 

Webistes are a mix of those that display only ex-VAT price on the browsing part and then add sales tax at the checkout / those that show with and without slaes tax prices next to each other in shopping areas / those that just display the full price including sales tax.

 

Invoices however will always separate the sales tax part - either by line or based on a subtotal

 

Hope this helps

Share this post


Link to post
Share on other sites

I am getting empty drop downs and I have completed the product setup procedure which input my qb products into QBI, and the qb product names show up under the product setup screen, but the matching drop downs are blank. The osCommerce column is blank, the quickbooks column has my osc product names, then the short, blank dropdowns. I can set up the payments and shipping screens, just no product matching?

 

How can I get the dropdowns to show the qb products?

 

Thank you.

 

 

post http://forums.oscommerce.com/index.php?sho...post&p=515316...

I see that nothing is showing in the dropdowns. You have to complete the Product Set Up procedure by importing your QB products into QBI before you can use the Product Matching feature.

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

×