Jump to content
Latest News: (loading..)
adam5532

QBI Quickbooks Import

Recommended Posts

Taxes:

 

One reminder, not explained yet in the manual.

 

When troubleshooting problems with taxes, remember that:

 

If taxes is turned on in QB, it must be turned on in QBI.

If taxes is turned off in QB, it must be turned off in QBI.

 

There is a differnece in the iif file, and they must match. It doesn't matter whether you are actually using taxes or not in your sales -- what's important is the settings.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
This is one of my top priorities, and I plan to try to incorporate it within the next week.

 

Thanks,

Adam

 

 

Wonderful! In my case, we use coupons sparingly in the store, so I'm okay with just a line that says coupon discount and the total. That's all my hack did. I know others run multiple coupons at the same time, and it would be good to capture the coupon name and discount applied.

 

Just out of curiousity, will your addition deal with things like price level discounts? Or better, is there a way to figure out anything in orders_total db for that order that isn't already accounted for and add them to the export? This would account for any of the more obscure contributions that people add, and should allow the transaction to stay in balance.

 

Dan


Dan Stevens

Share this post


Link to post
Share on other sites
Wonderful!  In my case, we use coupons sparingly in the store, so I'm okay with just a line that says coupon discount and the total.  That's all my hack did.  I know others run multiple coupons at the same time, and it would be good to capture the coupon name and discount applied.

 

Just out of curiousity, will your addition deal with things like price level discounts?

I don't use that in my store, so I'd have to look at how it is done. If it just changes the price for the item, then that's no problem. The price for the items does not have to match what is in Quickbooks (unless you are matching to a group).

  Or better, is there a way to figure out anything in orders_total db for that order that isn't already accounted for and add them to the export?  This would account for any of the more obscure contributions that people add, and should allow the transaction to stay in balance.

 

Dan

Yes, that is what I am going to do. Easily adaptable for any order_total contribution, possibly in the config panel so that no code editing is needed.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

look the pic is it Normal ?

 

and when I activate TAX that does not export the TAX??

!INVITEM	NAME	INVITEMTYPE	DESC	PURCHASEDESC	ACCNT	ASSETACCNT	COGSACCNT	PRICE	COST	TAXABLE	PAYMETH	TAXVEND	TAXDIST	PREFVEND	REORDERPOINT	EXTRA

INVITEM 2x2cabel INVENTORY Adapter OBD2 auf 2x2 Adapter OBD2 auf 2x2 8400 7140 8980 17.16 0 Y

INVITEM AK 2180 INVENTORY D-SUB Verl?ngerung, 1:1, 9-pol., BU/BU, 1,8m D-SUB Verl?ngerung, 1:1, 9-pol., BU/BU, 1,8m 8400 7140 8980 2.5 0 Y

INVITEM AK 230 INVENTORY D-SUB Verl?ngerung, 1:1, 9-pol., ST/BU, 1,8m D-SUB Verl?ngerung, 1:1, 9-pol., ST/BU, 1,8m 8400 7140 8980 4.22 0 Y

INVITEM AK 234 INVENTORY D-SUB Verl?ngerung, 1:1, 9-pol., ST/BU, 5m D-SUB Verl?ngerung, 1:1, 9-pol., ST/BU, 5m 8400 7140 8980 4.3 0 Y

INVITEM Benz cable INVENTORY Benz Kabel Benz Kabel 8400 7140 8980 17.16 0 Y

INVITEM BENZ-38P INVENTORY BENZ-38P BENZ-38P 8400 7140 8980 25 0 Y

INVITEM BMW cable INVENTORY BMW cable BMW cable 8400 7140 8980 17.16 0 Y

INVITEM BMW-20P INVENTORY BMW-20P BMW-20P 8400 7140 8980 25.85 0 Y

INVITEM C-BMW INVENTORY Adapter-BMW Adapter-BMW 8400 7140 8980 11.98 0 Y

INVITEM CAB4100 INVENTORY CAB4100 CAB4100 8400 7140 8980 861.21 0 Y

INVITEM carcode INVENTORY BLAUPUNKT UND FORD AUTO RADIO CODE FINDER BLAUPUNKT UND FORD AUTO RADIO CODE FINDER 8400 7140 8980 162.93 0 Y

INVITEM carpc INVENTORY abidig Kompletette Auto Computer mit 7 abidig Kompletette Auto Computer mit 7 8400 7140 8980 775 0 Y

annt.jpg

Share this post


Link to post
Share on other sites

Adam, you are right. I have a table linked to the products table that has all the cogs info. I will look at the php code and see if I can add the queries that export the cost of the item in the qiff file.

 

If you can put this feature in the contribution, that would be great. Or an option to select the table where this info is found. It is easier to upload them to OSC using sql databases than QB.

 

Thanks

Moneer

Share this post


Link to post
Share on other sites
look the pic is it Normal ?

 

and when I activate TAX that does not export the TAX??

I'm not sure exactly what we are looking at. If you could email me the enitre iif file I would be able to look at it easier.

 

The taxation system works for US taxes, and for systems where the tax is not added to the price of each product but is added at the end only. If your products include the VAT in them, I'll have to look at the code and figure out how I can make it work with that also. Is that the issue do you think?

 

If you could also email me your German (I assume) translation of QBI, I would be most grateful! I only speak English and Spanish (and Spanish is not my first language, so if any native speakers have corrections please let me know), so I only included those languages in the first release.

 

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.

 

Send me a private message from the osC forum and I'll respond back to you with my email address.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

The automated method to match between osC and QB assumes that the part numbers are the same for both products, the only difference is that the grouping in QB prepends the product group(s) to the beginning of the part number. I was working on a routine a long time ago which would use matrixed variables for each line of the iif export from QB, using the colon as the determination between each value within the matrixed variable. Once stripped it would be left with the part number which could then be added to a table along with the original QB value. Since I'm not much of a programmer I wasn't able to figure out how to do this.

 

I haven't used options for products in osC, I do understand to match products with options to a product in QB would be a manual process.

 

It's pretty cool that I can re-do the products import and have it update the existing record. It'll be really cool when Monday comes and I press a single button to import all of the customers and orders. :)

Share this post


Link to post
Share on other sites

Sounds wonderful!

 

Could you make it clear in the description on the next update that this is for an American version of QB.

 

I didn't find that out till I reached the end of the installation instructions. Looking forward to the international version - Thanks

Share this post


Link to post
Share on other sites
I haven't used options for products in osC, I do understand to match products with options to a product in QB would be a manual process.

Actually it could be automatic also. If you have a product with an option in osC, it will import as a QB Item and Subitem, unless you choose to match it. For example:

 

osC Product MOVBELT with options of 123T or 193T will import into QB as:

 

MOVBELT:123T

MOVBELT:193T

 

In QB the semicolon indicates:

 

Item name: MOVBELT

Subitem name: 123T

Subitem name: 193T

 

On the other hand, you could manually match it if you wish to use different names. For example, in my store

 

MOVBELT with option 123T maches to QB Item:

SDP A6G6-123025

 

and MOVBELT with option 193T matches to QB item:

SDP A6G6-193025

 

I have, however, just discovered an issue with the proper import of sub items when you use the "Create iif file from all Products" utility. I will fix that in the next release.

 

Also, if you use multiple option choices for each product, like T-Shirt with options for both color and size, then this is not yet supported. I will look at adding that in the next release.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Sounds wonderful!

 

Could you make it clear in the description on the next update that this is for an American version of QB.

 

I didn't find that out till I reached the end of the installation instructions. Looking forward to the international version - Thanks

 

Sorry about that! I will add that note close to the top of the instructions! I'll add UK support soon, but I have a long list now so it may not be in the next release.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Adam,

 

I performed my first import and everything went great. Now I can take a nap in the afternoons with all of the time you have saved me. ;)

 

Two things I did notice:

1) The shipping account and shipping name is reversed. I have "Shipping" set as the account and "Shipping Charge" as the shipping name. When I did the import it is showing the account as "Shipping Charge" and description as "Shipping".

 

2) There is no comma between city and state in the customer address.

 

Next thing I'll have to do is shop around for a merchant account and gateway - and get off of the QuickBooks merchant account. Then I'll be able to forget the nap and just sleep all afternoon... :)

Share this post


Link to post
Share on other sites
Adam,

 

I performed my first import and everything went great.  Now I can take a nap in the afternoons with all of the time you have saved me.  ;)

Thanks, glad it worked!

Two things I did notice:

1) The shipping account and shipping name is reversed.  I have "Shipping" set as the account and "Shipping Charge" as the shipping name.  When I did the import it is showing the account as "Shipping Charge" and description as "Shipping".

Thanks, I will check this.

2) There is no comma between city and state in the customer address.

That's on purpose. I'm a little fussy about properly formatting the address, and the US Postal Service actually specifies no commas or for that matter any punctuation in an address. But if you'd like a comma, you can add one:

qbi_engine_orders Line 104, 105. Put the comma inside the quotes between city and state.

Next thing I'll have to do is shop around for a merchant account and gateway - and get off of the QuickBooks merchant account.  Then I'll be able to forget the nap and just sleep all afternoon...  :)

I ended up with Wells Fargo, using Authorize.net. Only about $26/month account & gateway fee. Tip: When asked to put in the promotion code, do a Google search for the code to save the $99 sign-up fee!

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
I ended up with Wells Fargo, using Authorize.net. Only about $26/month account & gateway fee. Tip: When asked to put in the promotion code, do a Google search for the code to save the $99 sign-up fee!

 

Thanks,

Adam

 

Funny you said Wells Fargo, I just left their website. Probably because they were the QuickBooks merchant account prior to QB coming up with their own. How well does it work for bank statement reconcilation? I like how QB keeps the charges for the discount rate, transaction charge seperated from the deposits. Does WF do this also?

 

Also, I noticed that the accounts imported have a credit line of $1,000. Is there a way to keep this blank? I didn't see anything in the iif file regarding this, but the utility I had before to import customer information left the credit line blank.

 

Thanks again,

Mark

 

BTW, I got my comma - I'm so happy now. I don't ship through the post office, try to keep everything going through UPS (they give us free computer equipment and hockey tickets, neither USPS or FedEx has offered this to us).

Share this post


Link to post
Share on other sites
Also, I noticed that the accounts imported have a credit line of $1,000.  Is there a way to keep this blank?  I didn't see anything in the iif file regarding this, but the utility I had before to import customer information left the credit line blank.

You can set this in the config screen. I'll set it to blank by default in the next release.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

We also put this into production last night, and it is a MAJOR time saver. Adam - if there was a webbie award for osC, I'd nominate you for it :)

 

Just some suggestions (and questions) for future....

 

- Right now, you import the order number as the invoice number. This only works if all of your invoices are done with oscommerce. Can this be changed so the order number imports as the PO Number? We don't generate an osc order if the customer calls or comes and visits, so unless we're careful, we'll end up with duplicate invoice numbers.

 

- Is there a way to have a blank line between shipping and the items? Is this just a matter of having an empty line generated in the iif file?

 

Overall, great work!

 

Dan


Dan Stevens

Share this post


Link to post
Share on other sites

Hi Adam,

 

I haven't installed the contribution yet as I have two quick questions..

1) How does QBI know which sales have already been imported so you don't record a sale twice?

 

2) I record all sales paid for with a credit card as a sales receipt/not an invoice and our osC order numbers are not in sync with our Sale No. as we use QB to record all sales (not just on osC). Is their anyway for the import to find the next available sales receipt number?

Maybe I customized my sales receipt (it's been awhile so I don't really remember) but on the sales receipt I have the following 3 boxes below the Date & Sale No.

Check No., Payment Method and Order No. I use the last box (Order No.) to record the osC order number. Will I be able to use this?

 

Thanks.

Stacie

Share this post


Link to post
Share on other sites
We also put this into production last night, and it is a MAJOR time saver.  Adam - if there was a webbie award for osC, I'd nominate you for it :)

Thanks!

- Right now, you import the order number as the invoice number.  This only works if all of your invoices are done with oscommerce.  Can this be changed so the order number imports as the PO Number?  We don't generate an osc order if the customer calls or comes and visits, so unless we're careful, we'll end up with duplicate invoice numbers.

I haven't found a good solution to this yet, and I agree it is a pain -- I have the same situation for my store (on and offline sales).

 

To summarize the issue, after importing a sale from osC (ei order number 604), then the next off-line sale Invoice in QB will automatically be numbered 605. Then, if you import the next on-line order, that order will be 605 also and you'll get a duplicate. To avoid this, when you make an off-line sale after doing an import, you need to manually change the Invoice number to match your off-line Invoice sequence (ei 11078) before saving the Invoice. Then QB will be reset and will autonumber invoices in your off-line sequence (11079, 11080, ...) until you import again. Quickbooks always picks up the last invoice number that was used (has to be the first time an invoice was saved -- if you change it, and then resave it, this will not reset the auto-numbering).

 

Putting the number in PONUM I don't think would do anything, since PONUM isn't used for Invoices.

 

I could give you the option not to import the invoice number. That would leave it blank, but then you'd still need to give it a number, and QB would still pick up its numbering squence from that number, so this doesn't really gain you anything.

 

So, in short, I don't have a better solution right now, as this behaviour is limited by QB and the iif format.

 

One possibility is that you import online orders as Sales Receipts (if you have a payment gateway) and do off-line sales as Invoices, or vice-versa. Then the numbering sequences will be separate. There's an issue right now, however, that the Sales Receipt is autonumbered by QB, and doesn't pick up the osC order number, so for now the order number is imported as a comment line on the sales order. I believe this is a bug in QB.

- Is there a way to have a blank line between shipping and the items?  Is this just a matter of having an empty line generated in the iif file?

You can't have a completely blank line in the iif, but yes I can make it leave a blank line on the invoice.

I will do this for the next release.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Hi Adam,

 

I haven't installed the contribution yet as I have two quick questions..

1) How does QBI know which sales have already been imported so you don't record a sale twice?

QBI adds a field to the osC orders table to indicate whether an order has been imported. It also keeps track of the last group imported, so you can reimport those orders if you need to.

So, QBI doesn't have a problem with this -- but, if you import the same iif file into Quickbooks twice, then you will get duplicates. You won't have this problem if you make it a habit to produce the iif with QBI and then immediately import it into QB.

2) I record all sales paid for with a credit card as a sales receipt/not an invoice and our osC order numbers are not in sync with our Sale No. as we use QB to record all sales (not just on osC). Is their anyway for the import to find the next available sales receipt number?

I suspect that QB's behaviour is a bug, but in this case it looks like it will do exactly what you want.

 

For sales receipts, they will always be sequencially numbered, whether done offline in QB or imported from the iif.

 

But since the Sales Reciept number doesn't match the Order number in osC, I've made QBI add the order number a a comment line on the invoice, so you know what osC order it goes with.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Just a progress report on QBI:

 

I am working on version 1.1.

 

The following things are planned for it:

 

Support for non-inventory items.

Support for low order fee / coupons / gift vouchers etc.

Support for multiple product options.

Additional configuration options.

Customer account number options.

Completion of the automatic database installation and upgrade system.

Maybe some other things!

 

I hope to have it ready in about 1 week!

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Adam,

 

I deleted the entry for customer limit (credit limit) and it keeps putting a zero in there. What would be your recommendation to get the credit limit to be left blank?

 

I figure I'll go with the autonumbering of invoices, as you spoke about a couple of posts above. Where would I turn off the importing of invoice numbers?

 

I processed a bunch of orders today and everything went quickly and smoothly. Your contribution is a great time saver.

 

Thanks,

Mark

Share this post


Link to post
Share on other sites
Adam,

 

I deleted the entry for customer limit (credit limit) and it keeps putting a zero in there.  What would be your recommendation to get the credit limit to be left blank?

That's because it is a numeric field and QBI interprets blank as zero. I will fix this in the next release, so that QBI will leave it blank. For now, just put in a high number so that QB won't give a credit limit warning.

I figure I'll go with the autonumbering of invoices, as you spoke about a couple of posts above.  Where would I turn off the importing of invoice numbers?

There's no way to do that now. In version 1.1, I will give options for order numbers. However, due to the limitations of QB and iif, if you import an Invoice with no number, QB will not autonumber. It will leave it blank and I believe prompt for an invoice number -- I'll experiment with it.

I processed a bunch of orders today and everything went quickly and smoothly.  Your contribution is a great time saver.

Thanks!

 

- Adam

Share this post


Link to post
Share on other sites

Good summary of the issue. Nice to know that I'm not the only one that is facing this :)

 

It turns out that PONUM is supported in the invoice, at least in my version of quickbooks (2002 Pro). I could change DOCNUM to PONUM, but then the invoice number is blank, and your right, that causes the same problem, just in a different way.

 

The quickbooks site doesn't seem to be much help on this. Do you know of a way to import an iif file and autonumber the invoice number on the imported invoices? I couldn't find anything that actually discusses the iif files at intuit, though I did find the sample iif files they provide.

 

Dan

 

 

Thanks!

 

I haven't found a good solution to this yet, and I agree it is a pain -- I have the same situation for my store (on and offline sales).

 

To summarize the issue, after importing a sale from osC (ei order number 604), then the next off-line sale Invoice in QB will automatically be numbered 605. Then, if you import the next on-line order, that order will be 605 also and you'll get a duplicate. To avoid this, when you make an off-line sale after doing an import, you need to manually change the Invoice number to match your off-line Invoice sequence (ei 11078) before saving the Invoice. Then QB will be reset and will autonumber invoices in your off-line sequence (11079, 11080, ...) until you import again. Quickbooks always picks up the last invoice number that was used (has to be the first time an invoice was saved -- if you change it, and then resave it, this will not reset the auto-numbering).

 

Putting the number in PONUM I don't think would do anything, since PONUM isn't used for Invoices.

 

I could give you the option not to import the invoice number. That would leave it blank, but then you'd still need to give it a number, and QB would still pick up its numbering squence from that number, so this doesn't really gain you anything.

 

So, in short, I don't have a better solution right now, as this behaviour is limited by QB and the iif format.

 

One possibility is that you import online orders as Sales Receipts (if you have a payment gateway) and do off-line sales as Invoices, or vice-versa. Then the numbering sequences will be separate. There's an issue right now, however, that the Sales Receipt is autonumbered by QB, and doesn't pick up the osC order number, so for now the order number is imported as a comment line on the sales order. I believe this is a bug in QB.

 

You can't have a completely blank line in the iif, but yes I can make it leave a blank line on the invoice.

I will do this for the next release.

 

Thanks,

Adam


Dan Stevens

Share this post


Link to post
Share on other sites
Good summary of the issue.  Nice to know that I'm not the only one that is facing this :)

 

It turns out that PONUM is supported in the invoice, at least in my version of quickbooks (2002 Pro).  I could change DOCNUM to PONUM, but then the invoice number is blank, and your right, that causes the same problem, just in a different way.

You're right. And, it looks like there's also a field S.O. number (SONUM?), but they don't say anywhere what that's for.

The quickbooks site doesn't seem to be much help on this.  Do you know of a way to import an iif file and autonumber the invoice number on the imported invoices?

No. I've experimented, and it doesn't seem to want to do it, even if you leave out DOCNUM. I think that's a quirk of iif -- that's why the sdk is the best way to interface to QB, but as I've noted before, a big project and if I ever do it you'll still need QBI as the front-end for downloading the data. If you can figure out a way around it, I'd be overjoyed, but I don't think it's possible. For me, I am going to get my gateway working with QB, and import all my orders as Sales Orders. My offline sales will be Invoices, so that's how I'll get around the problem. Only problem will be if a customer calls about order number xx, I won't be able to do a search in QB by the osC order number. And, check/money order sales will still have to import as Invoices.

  I couldn't find anything that actually discusses the iif files at intuit, though I did find the sample iif files they provide.

There's no support from Intuit on iif, except for the sample files, and not a lot on the web either. It's a lot of figuring out and trial and error!

 

- Adam

Share this post


Link to post
Share on other sites

Hi Adam,

 

Would the 'Maybe some other things' be UK support as mentioned a few posts back?

 

Waits with anticipation :D

 

Have not installed the 1.01 version yet as waiting for uk import support.

 

Keep up the good work

 

A McDonald

 

Just a progress report on QBI:

 

I am working on version 1.1.

 

The following things are planned for it:

 

Support for non-inventory items.

Support for low order fee / coupons / gift vouchers etc.

Support for multiple product options.

Additional configuration options.

Customer account number options.

Completion of the automatic database installation and upgrade system.

Maybe some other things!

 

I hope to have it ready in about 1 week!

 

Thanks,

Adam


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

Share this post


Link to post
Share on other sites
Hi Adam,

 

Would the 'Maybe some other things' be UK support as mentioned a few posts back?

I think the things listed are the priority, since those things affect all users and are the issues that are causing proplems.

I'll try to add UK support in v1.2 shortly after.

 

Thanks,

Adam

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

×