Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

Thanks for your help Adam.  In setup:payments and match:payments I have blank pages with the exception of the 'update matches on this page' button.  When I initially click on setup I get the message 'Did not find iif import file.'  Is this a problem with my installation?

 

I think I may have an idea why this is happening. In the manual it says to put the files to be matched in admin/qbi_import. However, I see that there is also an 'admin/qbi_input.' I think the file might need to go in the latter. Anyone know for sure?

Link to comment
Share on other sites

I think I may have an idea why this is happening.  In the manual it says to put the files to be matched in admin/qbi_import.  However, I see that there is also an 'admin/qbi_input.'  I think the file might need to go in the latter.  Anyone know for sure?

The directories should be qbi_input and qbi_output. Sorry -- this is an error in the manual that I thought I had fixed previously, but I didn't change all the references. It has been corrected for the next version.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Adam,

 

We've been waiting SO LONG for something like this to come around, that we are starting a new quickbooks file for our company. Basically just not using the old one anymore, but keeping on hand if we need to go back and reference it.

 

So here's the situation. We've setup a new QB file and used Retail as the store type. But we are up against the invoice numbering issue. I've read through the posts and it seems nothing can be done about it. We do online and phone sales. We do so much in sales, that manually numbering invoices is troublesome.

 

So I read that we can use a sales receipt instead, which is fine. But we can't seem to get a grasp on this. We import orders, we click on "invoices" and there they are. But how do we switch from using an invoice to using a sales receipt? And what do we need to do in the QBI Configuration to tell it to export our orders as sales receipt types, instead of invoices? I can see where we create a new sales receipt, but not where we would click on "sales receipt" in quickbooks, instead of "invoices" to see our sales.

 

Keeping in mind, we have a new store file and are more than happy to adjust it however necessary.

 

Thanks for any suggestions/help. We've been waiting for a solution like this for a very long time!

Edited by wade
Link to comment
Share on other sites

Adam, this is a great contribution. I am excited to start using it.

 

Question: When importing transactions as invoices and payments, is there any way to give the payment a number, specifically the same number as the invoice?

 

Again, thank you for a very useful contribution.

Link to comment
Share on other sites

So I read that we can use a sales receipt instead, which is fine.  But we can't seem to get a grasp on this.  We import orders, we click on "invoices" and there they are.  But how do we switch from using an invoice to using a sales receipt?

Wade,

Follow the instructions in the manual for exporting your "Payment Method List" from QB and importing it into QBI on the Setup:Payments screen. You need to have a payment method in QB that you have set up for this -- I have one called "Credit Card Online".

Change the drop-down next to this payment method on the same screen to "Paid Online" and click "Update Payment Types."

Now, go to Match:Payments and match your osC payment method to the QB payment method. For example, if you use Authorizenet, you will match "Credit Card via Authroize.net" to "Credit Card Online."

Make sure that the QBI config setting "Payments-Import payments as" is set to "Sales Receipt."

 

You can only do Sales Receipts if your credit card orders are paid online through a payment gateway. If you process payment after importing orders, then you have to use Invoices, as Sales Receipts are only meant for orders that have been paid for.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam, this is a great contribution. I am excited to start using it.

 

Question: When importing transactions as invoices and payments, is there any way to give the payment a number, specifically the same number as the invoice?

 

Again, thank you for a very useful contribution.

I don't see any place for a payment number in QB, although I'm sure it uses one internally.

 

After importing Invoices and Payments, you have to open up your Customer Payment screen in QB, go back through each payment that you've imported, and check the open invoice listed (which can have the osC order number if you've set this up in QBI) to associate it with the payment. There's no way around this (QB isn't smart enough to know that if there is only one invoice listed, it's the one you want to show as paid!).

 

It is far easier to import orders that are paid online as Sales Receipts, and this also solves numbering problems that occur if you do online and offline sales. Unless you have a special reason to want to do "Invoices and Payments", I would recommend importing paid orders as Sales Receipts. This eliminates the need to go back through each payment.

 

Thanks,

Adam

Link to comment
Share on other sites

I don't see any place for a payment number in QB, although I'm sure it uses one internally.

 

Thanks for the quick reply.

 

I did find the QB payment number in the iif files, so I may try a hack to include them. In Quickbooks, the number shows up in the "Ref/Check No" field on the Receive payments screen. The variable is "DOCNUM" in the iif format for payments:

 

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM PAYMETH MEMO

!SPL SPLID TRNSTYPE DATE ACCNT NAME AMOUNT DOCNUM PAYMETH MEMO

!ENDTRNS

 

Also, I noticed that if your cart also has the authorize.net contribution any customer orders with credit card declines will cause "blank transaction" errors when inputing into quickbooks. I think this happens because the declined orders still exist in osc they just have zeros as their amount.

 

Once again, thank you for a great contribution.

Link to comment
Share on other sites

Thanks for the quick reply.

 

I did find the QB payment number in the iif files, so I may try a hack to include them. In Quickbooks, the number shows up in the "Ref/Check No" field on the Receive payments screen. The variable is "DOCNUM" in the iif format for payments:

Ok, I will look at putting the invoice number in that field.

Also, I noticed that if your cart also has the authorize.net contribution any customer orders with credit card declines will cause "blank transaction" errors when inputing into quickbooks. I think this happens because the declined orders still exist in osc they just have zeros as their amount.

Create a status called "Declined" and have Authorize.net Consolidated change the status of declined orders to "Declined." I believe I modified it myself to do this, but I'd have to take a look at what I did! Then, in QBI Config, set Customers:Import Orders with Status to "Pending". This will cause QBI to ignore the declined orders.

Once again, thank you for a great contribution.

Thanks!

 

- Adam

Link to comment
Share on other sites

My match:payments area is blank, even though I've included payments in my 'lists.iif. file.  Any ideas?

The QB payment methods are imported into QBI from the Setup:Payments screen. This has to be completed before going to the Match:Payments screen. Follow the instructions I gave to Wade a few posts back, and let me know if it works.

 

Thanks,

Adam

Link to comment
Share on other sites

I am not using QBI yet. We are selling online and our deposits arrive batched together (to clarify the terminology I am using without knowing whether it's correct: a few transactions become one deposit when sent to our bank account).

 

We are using Quickbooks and we are downloading transactions from our Bank.

 

Here is my question: In our online banking interface in Quickbooks, the batched deposits arrive. But instead of recording these batched depsits, I would like to record the singel transactions (as we need to seperately record sales taxes).

 

When using the module, I am guessing that I will download the transactions seperately into Quickbooks.

 

Do I

 

a. not use online banking with QB at all and just go by what's recoded in OSC? (and the reconcile with the bank statements)

b. break up the batched deposits into it's parts by splitting the deposit (lots a work...)

c. some other magical trick?

 

Any help would be greatly appreciated. Cheers,

 

Stephan

Link to comment
Share on other sites

I am not using QBI yet. We are selling online and our deposits arrive batched together (to clarify the terminology I am using without knowing whether it's correct: a few transactions become one deposit when sent to our bank account).

...

You can definately use online banking. Do not deposit your online sales until you download your banking information. When you do, for each batch deposit, you will check off the appropriate Sales Receipts as part of the deposit. For example, to match to a deposit of $100, do a Customer:Deposit for a group of paid Sales Reciepts for $50, $20, and $30. If you do online banking frequently, it will not be too much of a problem, and normally the Sales Receipts will be in order so it won't be hard to match them to the correct deposit.

 

If your merchant service takes the discount fee out of each transaction, instead of listing it as a separate item, then you will have a harder time reconciling.

 

Every sale in osC will produce a separate Invoice or Sales Receipt when imported into QB with QBI. Do all your accounting in QB.

 

Thanks,

Adam

Link to comment
Share on other sites

your correct, I cant import iif files. Would you happen to know any other contributions that might help me out? Thanks again

There is no other contribution that will help you. Quickbooks Simple Start will not work with iif, nor will it work with the newer integration via the QB SDK. It is a bare bones simple accounting system and is not designed for advanced usage, such as integration with an online store. I suggest you consider using QB Basic or Pro instead.

 

Thanks,

Adam

Link to comment
Share on other sites

You can definately use online banking. Do not deposit your online sales until you download your banking information. When you do, for each batch deposit, you will check off the appropriate Sales Receipts as part of the deposit. For example, to match to a deposit of $100, do a Customer:Deposit for a group of paid Sales Reciepts for $50, $20, and $30. If you do online banking frequently, it will not be too much of a problem, and normally the Sales Receipts will be in order so it won't be hard to match them to the correct deposit.

 

If your merchant service takes the discount fee out of each transaction, instead of listing it as a separate item, then you will have a harder time reconciling.

 

Every sale in osC will produce a separate Invoice or Sales Receipt when imported into QB with QBI. Do all your accounting in QB.

 

Thanks,

Adam

 

Thanks a lot Adam! Stephan

Link to comment
Share on other sites

Adam,

 

I'm gettting a out of balance error when I try and import as a sales receipt into QB, and after looking at the .iif file in Excel, I've determined that it is because the sales tax is always 1 cent off, and I think it only happens whenever the customer chooses a shipping method besides free shipping (which is of course $0.00). How could this be? Why is the tax off only 1 cent?

 

If I can get this error licked, I'll be set!! Thanks!

Link to comment
Share on other sites

Adam,

 

One other problem--> How do I import the customer name as just "Last Name, First Name" ? I know that isn't the best way to do things, but I need to because I have 1500 customers in quickbooks in that format already, and now QBI keeps creating new customers every time I import.

Link to comment
Share on other sites

Update - Ok, about the tax being off 1 cent... It appears that it is always off 1 cent, even if the customer chooses free shipping. If tax is charged, the .iif file is showing it as 1 cent below what it should be. So on the checkout confirmation screen it may say that tax is .60. But the .iif file will show the tax as .59.

 

Thanks!

Link to comment
Share on other sites

Adam,

 

I'm gettting a out of balance error when I try and import as a sales receipt into QB, and after looking at the .iif file in Excel, I've determined that it is because the sales tax is always 1 cent off, and I think it only happens whenever the customer chooses a shipping method besides free shipping (which is of course $0.00).  How could this be?  Why is the tax off only 1 cent?

 

If I can get this error licked, I'll be set!!  Thanks!

I got a rounding error on the sales tax myself, but only on one order out of many. It should only happen if the sales tax works out to a half cent. I will check the code shortly and make sure that the rounding fix is applied to the sales tax.

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

One other problem-->  How do I import the customer name as just "Last Name, First Name" ?  I know that isn't the best way to do things, but I need to because I have 1500 customers in quickbooks in that format already, and now QBI keeps creating new customers every time I import.

There is a complete explanation in the instruction manual on page 7 of how to configure the customer number (name) to your liking.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Adam,

I would like to thank you and everyone who has helped, for the work you have done on this contribution. It has made things possible in my business that were not possible before. I am relieved and thrilled about this contribution, even though it took weeks of effort for me to get it right and integrated into my current Quickbooks. I also learned a lot about Quickbooks in the process, which didn't hurt. You obviously took the only path you could while developing within limitations of QB.

 

 

(Adam, the check is in the email)

 

Thank you.

 

Drew

Edited by Johnson
Link to comment
Share on other sites

Hi Adam,

I would like to thank you and everyone who has helped, for the work you have done on this contribution. It has made things possible in my business that were not possible before. I am relieved and thrilled about this contribution...

Drew,

 

Thank you greatly for the appreciation, and for the donation!

 

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