Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

I have a quick question.  This may be more of a Quickbooks question but i'll ask anyway.

 

We got the mod working after some tweaking (which works fantastically) and ran into a problem.  Our online purchases get deposited directy into our bank account via our merchant service.  My problem is i've got all these orders imported, but i'm duplicating my sales by reporting the orders from QBI and the money from our merchant services.  I can't figure out what the best way is to keep both my merchant deposit records and sales transactions without duplicating sales. 

 

Thanks again for this great contribution!

I'm not quite following your question. I do the same thing -- I use a merchant account.

 

Set up QBI to import your online CC sales as Sales Receipts. The online purchases will show up as Payments to Deposit (but not as deposits).

 

Download your bank statement into Quickbooks (if you do this every day, it is easier).

 

Deposit your Payments so that they match the deposits from your merchant account, and just add the service fees and discount fees as additional checkbook entries.

 

For example, if the bank shows a deposit from the merchant services for $40, and the next two undeposited online sales are for $30 and $10, deposit these in QB to your checking account and they will match. This will of course depend on the sales volume that you have, but I find that if I keep up with it, it is pretty easy to identify which Payments go with each deposit -- usually they are pretty much in order.

 

The only hassle is the awful way that QB handles credit card refunds, because it doesn't allow you to put a negative amount in the Deposits window.

 

Thanks,

Adam

Link to comment
Share on other sites

Hello,

 

first off is this the correct way to post a message for qbi support?

Yes it is! Some people send me private messages or email if they have a specific request or we are trying to work out a problem and I need to see their files. But I prefer that any problems that might help other users be posted here for the benefit of all.

Okay, I have been a quickbooks user for years and a OScommerce users for a few months. 

 

This looks like the best contribution I have ever seen

Thanks!

and as soon as I get it fully operation I plan on making a nice donation to the creaters.

That's me!

Okay, -- Here are the problems I am facing.

I will get back to you in a little bit when I have some more time to go through this and solve it for you.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi,

We've installed QBI on our website and am able to download order and products, but when we try to download Customers, nothing happens. We have approximately 30 customers so I know there's data in there. How can I get this to work?

Absolutely nothing happens, or is an iif file created with no customers? It will only download customers who have actually placed orders.

 

Thanks,

Adam

Link to comment
Share on other sites

When I go to import orders I get the following message. -- " Quickbooks has detected that one of your items does not have an account associated with it. Go to the item List to make sure all items are accociated with accounts"

 

----- okay, well if this is refering to my items in quickbooks already - they all have accounts associated with them..

Make sure that you have put the correct account in the QBI configuration page. Since these items are not matched, QBI uses the account names that you have entered in the configuration page, and if those are blank or not correct then that might be the problem.

I charge sales tax to New York State residents only, currently it is setup properly in oscommerce, how do I match that with quickbooks, (what do I modify in quickbooks?)

First, you need to have a sales tax item set up in Quickbooks. Then, you need to enter the settings for it into the QBI configuration page, copying exactly what you have in QB for your sales tax item so they are identical.

 

Let me know if this solves the problems.

 

Thanks,

Adam

Link to comment
Share on other sites

Absolutely nothing happens, or is an iif file created with no customers? It will only download customers who have actually placed orders.

 

Thanks,

Adam

 

Adam, nothing happens. The browser runs for a moment, but no download is generated. The browser stops after a time, no error message is generated. The other downloads, orders, etc worked fine.

Thanks for your help,

Rocco

Link to comment
Share on other sites

Make sure that you have put the correct account in the QBI configuration page. Since these items are not matched, QBI uses the account names that you have entered in the configuration page, and if those are blank or not correct then that might be the problem.

 

First, you need to have a sales tax item set up in Quickbooks. Then, you need to enter the settings for it into the QBI configuration page, copying exactly what you have in QB for your sales tax item so they are identical.

 

Let me know if this solves the problems.

 

Thanks,

Adam

 

 

 

Adam,

 

I have All the accounts set up in quickbooks that are coming in from oscommerce.

 

Something is still not matching up correctly, Maybe its the products, is there anyway to test to see what the problem is. (ie. maybe create a new product in both quickbooks and oscommerce, make sure it matches, then make a test order buying that item, and then see what happens), I dunno - I will try that. Please let me know if you have any other Ideas.

 

I still am getting the same error messages.

 

This is how I have my oscommerce configured - Invoices:

 

Incoive account: Accounts Receivable

 

Sales Receipt account: Undeposited Funds

 

I have both Accounts Reveivable and Undeposited Funds accounts setup in quickbooks, - shouldnt that make it work?

 

Please help.

 

Thanks

 

Mark

Edited by oneclickappliances
Link to comment
Share on other sites

Did it give a line number in the error message?

 

Email me the iif file and I will take a look at it for you.

 

Thanks,

Adam

 

Adam,

 

I didn't get an error message from QuickBooks because I looked at the file first and fixed it before I imported. I will see if it does the same thing next time I generate the iif file.

 

Thanks,

Eric

Link to comment
Share on other sites

Let me first thank everyone here, especially Adam, for this great contribution and it's remarkable support. In setting up this osCommerce store for a client, I've investigated and installed half a dozen other contribs and nowhere have I seen such quick, helpful responses. Most of the problems I've encountered have been found by other users and quickly addressed.

 

Unfortunately, I'm still having trouble getting the contrib to actually work for this site. I've managed to import the item list into qbi (my client's items are all listed as non-inventory items for somer reason, so I had to upgrade from the previous version to the newest) and match the items in the osC database to their QB equivalents. So, I can't resist the feeling that I'm SOOO close to having everything work that there must be some small detail being overlooked, and when that is found, all else will go relatively smoothly.

 

We have generally started with all values on the config qbi page set at what we believe their correct value is, and then we tried to change one variable at a time to see if we could make any progress at all...and at this point, even new and different error messages would be welcome.

 

 

Since I don't have QB, or my client's databases, I've had to rely on his import attempts and reporting. He's not a tech either and is only familiar with QB to the extent that his company uses it -- basically as an inventory and order tracker, with two payment accounts (one for paypal and the other for merchant credit card transactions...for the purposes of testing and getting past the current error we are ignoring one account and using the merchent payment account as the destination for imported payments). his test results follow:

--*--

 

Test 1 - import payment as invoice and payment QB has detected no items...

Error on line 11

import reported as Success

 

Test 2 - import payment as sales receipt same sequence of messages

 

Test 3

Order status = any

Invoice Account = Accounts Receivable

Import payments = invoice and payment

same result

 

Test 4

tax turned off

Import payments = invoice and payment

Invoice Account = Customer Invoices

same initial error message, then error on line 10

 

--*--

heres a paste of an iif file. i dont think this is the one he was testing, as line 10 here is ENDTRANS but often it's an SPL line (as you can see, i've learned the basics of reading these damn things)...

 

!CUST NAME BADDR1 BADDR2 BADDR3 BADDR4 BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM EMAIL NOTE CONT1 CONT2 CTYPE TERMS TAXABLE TAXITEM LIMIT RESALENUM REP COMPANYNAME SALUTATION FIRSTNAME LASTNAME

CUST User-24 test user 120 state ave NE #1025 Olympia WA 91436 test user 120 state ave NE #1025 Olympia WA 91436 8776907034 [email protected] test user Sales Tax test user

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 NAMEISTAXABLE TERMS SHIPVIA REP FOB INVMEMO PAYMETH PONUM

!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM TAXABLE PAYMETHOD EXTRA

!ENDTRNS

TRNS INVOICE 09/02/2005 Customer Invoices User-24 21.17 9 N Y test user 120 state ave NE #1025 Olympia WA 91436 test user 120 state ave NE #1025 Olympia WA 91436 Method not set 9

SPL INVOICE 09/02/2005 Bath And Body -12 N -1 12 Body Buffer CL 8 oz (web) Y

SPL INVOICE 09/02/2005 N

SPL INVOICE 09/02/2005 -8.3 N -1 8.3000 N

ENDTRNS

 

 

so, i asked him to give me the exact wording of the newer error, since we generally seemed to be past the "out of balance" error (that was usually caused by the rounding bug). i mentioned that something about payment methods, perhaps causing something to end up in the wrong ledger, might be the culprit, and he said he attached an iif file with payment methods in it, but he forgot. i also asked if he could give us his qb database to work with but, as you can see, he'd rather not.. heres his message:

--*--

 

I set out to get the exact error wording you requested below. Rather than

trying to look up the exact wording from an old e-mail I just tried another

import. Instead of getting the "QB has detected" error I was expecting, I

got "The transaction is not in balance. Make sure the amounts in the detail

area on the form for this transaction equal the amount at the top of the

form." What form? This message was followed by "error on line 10". This

file was the last one imported yesterday so if you refer to the testing

notes I sent you will see the parameters I had selected. I think this was

the only one I did with tax turned off.

 

So I tried it again, importing a different file. This time I got

"Quickbooks has detected that you have no items or that one or more of your

amounts is not associated with an item. Please enter an item." This was

followed by "error on line 11". I believe this is what you were requesting.

 

My QB is version 2003 Pro. The updates looked current but I ran an update

anyway. I then attempted to import the same file as in the paragraph

immediately above this one. I got the same result.

 

The place in QB where I select the items list to be exported also shows

payment methods. I exported that list though I'm not sure if anything is in

it or how to add anything. I'm attaching the file.

 

Perhaps the contribution's author is the best person to get help from. I'm

not sure of the best way to collaborate in that situation, but I am not

comfortable sending off my financial records as part of it.

 

--*--

and that is where I'm left. ANY suggestions as to what might be causing the "Quickbooks has detected that you have no items.." error would be very extremely helpful... especially because, unlike my experience with ANY error in ANY program or system that i've encountered before, a google search on part of the exact text of the error is turning up NO HITS, so now I'm not sure where else to turn...

 

thank you all so very very much,

allen

Link to comment
Share on other sites

Let me first thank everyone here, especially Adam, for this great contribution and it's remarkable support.

Thanks!

 

It is a struggle to read the iif files posted in the forum because the columns don't line up and there are no line numbers.

 

First of all, tax on or off MUST be the same in Quickbooks and QBI. It doens't matter if the particular sale is taxable -- if Sales Tax is globally turned on in QB, then it must be turned on in QBI and the sales tax configuration must be entered.

 

So, configure QBI to match QB, with sales tax on or off as it should be.

 

Then, please have your client write down the exact error message(s) with the line number(s).

 

Then, please email me the iif file to look at. If I don't respond, please remind me!! I'm swamped at work and getting behind on answering private QBI inquiries!

 

Thanks,

Adam

Link to comment
Share on other sites

Hi All,

 

I'm swamped at work at the moment, and devoting time to catching up there. So, I apologize for not having time to solve some of your issues, or respond quickly to personal emails about QBI. But I hope to be able to devote more time to QBI, hopefully in a week or so.

 

Thanks,

Adam

Link to comment
Share on other sites

Ok I have what might be a simpile but stupid question.

I am upgrading from 2.02b to 2.10 and am looking at the instructions

and it says

QBI Upgrade Notes Any Version to v2.10:

1. The upgrade will remove all Quickbooks items from their groups in QBI. To restore the Quickbooks

group items, go to Setup:Products and reimport the iif that has your Quickbooks items. This is necessary

due to an improvement in the database. If you have any osC products matched to Quickbooks groups,

this matching will be preserved. If you do not use groups in Quickbooks, then this does not affect you.

2. If you have the authorize.net consolidated contribution installed,

Add: $order->info['total']+=0.000001;

Before: $form_data = array(

In: authorizenet_direct.php

This will correct rounding errors in the module. A similar fix has already been incorporated in QBI.

and then it says

QBI Upgrade Notes v2.00 or v2.01 to v2.02:

Replace these files:

admin/includes/qbi_engine_orders.php

admin/includes/qbi_version.php

admin/includes/functions/qbi_functions.php

Add these files:

admin/qbi_0-00_2-02.sql

admin/qbi_1-00_2-02.sql

admin/qbi_2-00_2-02.sql

Removed these files:

admin/qbi_0-00_2-00.sql

admin/qbi_1-00_2-00.sql

Also, remove this file that was not indicated in the previous v2.00 upgrade instructions:

admin/includes/languages/language/qbi_setup.php

Now, go to QBI in the osC admin area and follow the prompts to upgrade the database and save the

configuration. There are actually no changes in the database, except that this procedure is required to set the

version number to 2.02.

 

am I missing something? This seems that it does not tell you what to do to make the upgrade from 2.02 to 2.10 only how to make an upgrade to 2.02

and then just tells you what the upgrade from any version to 2.10 does but not how to do it.

What should we do to make an upgrade to version 2.10?

 

Thanks

Steel

Link to comment
Share on other sites

Thanks!

 

It is a struggle to read the iif files posted in the forum because the columns don't line up and there are no line numbers.

 

First of all, tax on or off MUST be the same in Quickbooks and QBI. It doens't matter if the particular sale is taxable -- if Sales Tax is globally turned on in QB, then it must be turned on in QBI and the sales tax configuration must be entered.

 

So, configure QBI to match QB, with sales tax on or off as it should be.

 

Then, please have your client write down the exact error message(s) with the line number(s).

 

Then, please email me the iif file to look at. If I don't respond, please remind me!! I'm swamped at work and getting behind on answering private QBI inquiries!

 

Thanks,

Adam

 

Hey Adam et al,

 

My client and I still haven't really gotten anywhere on this problem. Due to our mutual lack of complete recording of our previous attempts (neither of us thought this problem would be so sticky that every detail of previous attempts should be tracked), neither of us know exactly where we started! But we're certianly still getting errors, so I figure the best thing to do is return to our best guesses for correct setup, and then vanquish the errors one-by-one with the generous help of this forum.

 

--*--

I definitely couldn't be sure about the original settings so here is what I

did.

 

First, I looked in QB at whether sales tax was turned on or off. It is

turned off. QBI has an unchecked box for sales tax so I take this to mean

it too is turned off. There are fields below the check box and these have

settings identical to the sales tax settings that QB is set up to handle

manually. That means when a CA customer places an order I have to manually

enter the sales tax like any other item in the order and there is nothing

that QB does automatically. As far as I can tell QB is functioning

correctly on sales tax, and QBI has settings that mirror QB.

 

Next I decided I would try to do another import. Thinking it may be

possible that Allen's most recent (9/2) test order may have been done

improperly (I recall a workaround that avoided actually paying with a credit

card, instead "fooling" the system), I tried to put in a new test order that

could be used for the export/import. I loaded the cart and tried to check

out but I received an error saying it couldn't connect to VeriSign. This is

the first I've seen of this.

 

So I decided to fall back one last time on attempting to import the 9/2

order. When I did, I first received the error message "The transaction is

not in balance. Make sure the amounts in the detail area on the form for

this transaction equal the amount at the top of the form." When I clicked

OK I got "Error on Line 10: Can't record invalid transaction." This was

followed by the appearance of the import progress bar and then the

announcement that the import had succeeded. There is no evidence of new

data in QB.

--*--

 

the 2nd paragraph above is mostly ignorable since it largely talks about a test order that didnt go through; but for reference sake, the 9/2 test order that he mentions was an order I made. During the attempt I realized that verisign would have to redirect back to the store upon success, but since the store doesnt have a domain name associated with it at the moment, I instead manually hit the checkout_success page to complete the order in oscommerce's eyes.

 

just to get this straight--is there any difference in the iif file you get if an order is new vs. if an order is a "previous order"?

 

here are two snapshots from the config page of the qbi module of this store, along with the qbi_orders that caused the errors described above:

http://nettrip.org/temp/config1.jpg

http://nettrip.org/temp/config2.jpg

http://nettrip.org/temp/qbi_orders-3.iif

 

any help that anyone can give is very appreciated. once again, everyone, and especially Adam -- I really appreciate the time you take to help out!

 

allen

Link to comment
Share on other sites

First, I looked in QB at whether sales tax was turned on or off. It is

turned off. QBI has an unchecked box for sales tax so I take this to mean

it too is turned off. There are fields below the check box and these have

settings identical to the sales tax settings that QB is set up to handle

manually. That means when a CA customer places an order I have to manually

enter the sales tax like any other item in the order and there is nothing

that QB does automatically. As far as I can tell QB is functioning

correctly on sales tax, and QBI has settings that mirror QB.

This sounds like where the problem is. It looks like sales tax is set up incorrectly in QB. If sales tax is not turned on in QBI, it will not add the sales tax to the iif file and the transaction will not be in balance. And, in order to import the iif file with sales tax, sales tax has to be turned on in QB. So, I suggest that your client turn on sales tax in QB (and QBI). In this case, the sales tax will appear in the tax field at the bottom of the invoice or sales order and not as a line item. Out of state customers will still not be charged sales tax, as the "taxable" field in the iif will be set to N for customers that are not taxes in osC.

When I clicked

OK I got "Error on Line 10: Can't record invalid transaction." This was

followed by the appearance of the import progress bar and then the

announcement that the import had succeeded. There is no evidence of new

data in QB.

That's normal QB behaviour.

During the attempt I realized that verisign would have to redirect back to the store upon success, but since the store doesnt have a domain name associated with it at the moment, I instead manually hit the checkout_success page to complete the order in oscommerce's eyes.

That may not work. Just going to a page manually is not necessarily going to pass the data to it that it wants to receive. On the other hand, if no data is passed to the success page, then loading the page manually isn't going to make any difference. It sounds like you have issues here that need to be solved separately.

just to get this straight--is there any difference in the iif file you get if an order is new vs. if an order is a "previous order"?

No, it is identical. But, since the iif file is newly created when you hit the button, any changes you make in the QBI configuration will be reflected in the iif file.

 

Thanks,

Adam

 

 

Ok I have what might be a simpile but stupid question.

I am upgrading from 2.02b to 2.10 and am looking at the instructions

and it says

 

and then it says

am I missing something? This seems that it does not tell you what to do to make the upgrade from 2.02 to 2.10 only how to make an upgrade to 2.02

and then just tells you what the upgrade from any version to 2.10 does but not how to do it.

What should we do to make an upgrade to version 2.10?

 

Thanks

Steel

To upgrade from 2.02 to 2.10, just copy all the QBI files in the 2.10 download to your server. Then, follow the notes given in the section about upgrading to 2.10.

 

I see that this is a little unclear. I will improve the upgrade instructions in the future.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Adam

 

I am unable to create an iif file for the customers in my shop, i am able to however create an iif of my products. If i try and create an iif for the customers it reloads the page but doesnt produce a pop up to save an iif

 

Regards

Link to comment
Share on other sites

Hi Adam

 

I am unable to create an iif file for the customers in my shop, i am able to however create an iif of my products. If i try and create an iif for the customers it reloads the page but doesnt produce a pop up to save an iif

 

Regards

Try it in different browsers and let me know if there is any difference. Please let me know what browser you are using, and how many customers and orders you have in your database.

 

Thanks,

Adam

Link to comment
Share on other sites

Hello,

 

Do you think this would work with the Canadian Version of Quickbooks? Has anyone tried it? Have you had success?

 

I searched this thread and noticed that as of May 2005 there was no support for International Versions.

 

I'm wondering if I should try it and see if it works. (Backing everything up beforehand of course!) The Canadian Version of Quickbooks is very similar to the U.S. Version and the import\data transfer features seem very similar.

 

Thanks!

 

Sydney

Link to comment
Share on other sites

Hello,

 

Do you think this would work with the Canadian Version of Quickbooks? Has anyone tried it? Have you had success?

 

I searched this thread and noticed that as of May 2005 there was no support for International Versions.

 

I'm wondering if I should try it and see if it works. (Backing everything up beforehand of course!) The Canadian Version of Quickbooks is very similar to the U.S. Version and the import\data transfer features seem very similar.

 

Thanks!

 

Sydney

Sydney,

It will not work because the taxation systems are different and therefore the iif files are different (why Intuit didn't make a common iif file format that could be used across different international versions, I don't know, but that's the way it is!).

I'm currently rewriting QBI to make it much easier for me to do all the international versions. So, don't hold your breath, but it is on its way!

- Adam

Link to comment
Share on other sites

Make sure that you have put the correct account in the QBI configuration page. Since these items are not matched, QBI uses the account names that you have entered in the configuration page, and if those are blank or not correct then that might be the problem.

 

First, you need to have a sales tax item set up in Quickbooks. Then, you need to enter the settings for it into the QBI configuration page, copying exactly what you have in QB for your sales tax item so they are identical.

 

Let me know if this solves the problems.

 

Thanks,

Adam

 

 

Adam,

 

I have All the accounts set up in quickbooks that are coming in from oscommerce.

 

Something is still not matching up correctly, Maybe its the products, is there anyway to test to see what the problem is. (ie. maybe create a new product in both quickbooks and oscommerce, make sure it matches, then make a test order buying that item, and then see what happens), I dunno - I will try that. Please let me know if you have any other Ideas.

 

I still am getting the same error messages.

 

This is how I have my oscommerce configured - Invoices:

 

Incoive account: Accounts Receivable

 

Sales Receipt account: Undeposited Funds

 

I have both Accounts Reveivable and Undeposited Funds accounts setup in quickbooks, - shouldnt that make it work?

 

Also - How should my Items be configured?

 

What should the Item income account be, the Item Class be and the default name be set as??

 

Thanks again

 

Mark

Edited by oneclickappliances
Link to comment
Share on other sites

Adam,

 

I have All the accounts set up in quickbooks that are coming in from oscommerce.

 

Something is still not matching up correctly, Maybe its the products, is there anyway to test to see what the problem is. (ie. maybe create a new product in both quickbooks and oscommerce, make sure it matches, then make a test order buying that item, and then see what happens), I dunno - I will try that. Please let me know if you have any other Ideas.

Open the iif file in Excel. Look at an order and compare it to the invoice in osC. Then you will see if a product or tax is missing. Also compare the prices for each and the total.

Also - How should my Items be configured?

 

What should the Item income account be

Open an item in QB. Under "Sales Information" is "Income Account." Notice the account you are using. This is what you put in the config. It will apply to items that are not matched using the matching feature in QBI.

the Item Class be

If you don't use classes for items then leave this blank.

and the default name be set as??

You can leave this blank, if "Use default name" is not checked. This was added at the request of people who have maxed out the number of products available in QB and want to import everything using one product name only.

 

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