Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

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.

 

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.

 

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

 

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

 

 

Okay - Looking at this iif file in excel, makes everything not line up, can I email it to you, so you can maybe take a look at it real quick, to see if something is not lined up correctly. Cause I simply can't tell.

 

Other than that, the items seemed to be configured correctly.

 

I would greatly appreciate it if you can email me @ [email protected] with your email address, so I know where to send the orders iif file (there is only 1 test order on it)

 

Thank you very much.

 

Mark

Link to comment
Share on other sites

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.

 

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.

 

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

 

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

 

 

Adam,

 

I actually tried it again, and again and finally got it to import correctly. However, I have it configured to Import Payments As Sales Recipt, however this came into quickbooks as a invoice only, not even an invoice and a payment.

 

How do I get it to come in as Sales Reciept?, besides switching the last Payment option to As Sales Receipt.

 

Thanks

 

Mark

Link to comment
Share on other sites

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.

 

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.

 

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

 

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

 

 

Adam,

 

I wish I knew how to delete the last 2 messages I sent.

 

Anyway, after playing around for a while. I got it to import correctly, as a sales receipt and also go into undeposited funds.

 

Last thing we can hopefully figure out, then I will be sending over a nice donation. I also encourage others to do so as well as Adam has given us a great contribution.

 

How do I get the order number to come in as the Sale No, in quickbooks, right now the sale number in quickbooks starts at one and goes up one at a time. The order number from oscommerce, comes in sa the check no. - Its not terrible, but I just want to check to see if there is a way to get it in the right place.

 

Thanks again,

 

Mark

Link to comment
Share on other sites

Last thing we can hopefully figure out, then I will be sending over a nice donation.

I also encourage others to do so as well as Adam has given us a great contribution.

:D

How do I get the order number to come in as the Sale No, in quickbooks, right now the sale number in quickbooks starts at one and goes up one at a time. The order number from oscommerce, comes in sa the check no. - Its not terrible, but I just want to check to see if there is a way to get it in the right place.

There isn't a way around that -- it's normal behavior for QB. If you want, you can go into Templates, and rename "Check No." to "Web Sale No."

 

- Adam

Link to comment
Share on other sites

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.

 

Hey! Ok..

 

My client followed your instructions, and as I guessed, we're quickly past the "not in balance" error and now dealing with the real error again.

 

Test 1 below was not expected to work, as the tax settings are different between QBI and QB.

Test 2 shows the error we're trying to kill now.

Test 3 shows the same error with a new order he made. This is the order in the iif file linked to at the end of this message.

 

 

(he writes..)

--*--

I followed the suggestions provided and did more tests:

 

Test #1:

Turned on sales tax in QB, did nothing to QBI.

Exported 9/2 test order again.

Imported.

Error: The transaction is not in balance...

Error: Line 10: can't record invalid transaction

 

Test #2:

Left sales tax on in QB, turned it on in QBI Exported 9/2 test order with updated settings.

Imported latest exported file.

Error: QB has detected that you have no items or that one or more of your amounts is not associated with an item.

Error: Line 11: Can't record invalid transaction.

 

Test #3:

Keeping the same settings, I placed a new order to be exported.

Payment via credit card was prevented by error stating could not contact payflowlink.

Paid via PayPal instead.

Confirmation e-mails received in "customer" e-mail account OK.

In Jessicurl e-mail account, payment confirmation received but no confirmation of order contents.

Exported new order.

Imported new order.

Error: QB has detected that you have no items...

Error: Line 11

--*--

 

 

Two new snapshots of the config page and the resulting iif file (from Test #3):

 

http://nettrip.org/temp/config1005-1.jpg

http://nettrip.org/temp/config1005-2.jpg

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

 

thanks again!

allen

Link to comment
Share on other sites

Allen,

 

Hey! Ok..

 

My client followed your instructions, and as I guessed, we're quickly past the "not in balance" error and now dealing with the real error again.

Your client needs to finish configuring QBI. The Items "Invoice Account" normally needs to be "Accounts Receivable" and the "Sales Receipt Account" normally is "Undeposited Funds." Check that "Combinations:Accessories" is really an income account. Fill in the required set-up information for the "Shipping" section - the shipping charge is missing needed information. Ask your client to please re-read the manual section describing each configuration setting and make sure that everything is set up! Then it should work!

 

Thanks,

Adam

Link to comment
Share on other sites

Hello All,

 

I've run into a problem matching up my oscommerce products with those in quickbooks. In oscommerce I have only the child upc so for instance Child009 but in quickbooks it is listed as Parent009:Child009. When I download the order the products don't match because the two SKU's aren't the same. I've looked at the php and it seems like I can circumvent if I was able to do a table lookup that could match a substring from the qbi items table with the product id in the qbi engine order file but i'm unsure if you can even do that with sql. Do you guys know of a better solution?

Link to comment
Share on other sites

Hello All,

 

I've run into a problem matching up my oscommerce products with those in quickbooks. In oscommerce I have only the child upc so for instance Child009 but in quickbooks it is listed as Parent009:Child009. When I download the order the products don't match because the two SKU's aren't the same. I've looked at the php and it seems like I can circumvent if I was able to do a table lookup that could match a substring from the qbi items table with the product id in the qbi engine order file but i'm unsure if you can even do that with sql. Do you guys know of a better solution?

This capability and the tables needed are already built into QBI. You need to do the Set Up:Products and Match:Products procedure to make a correspondence in QBI between Child009 in osC and Parent009:Child009 in QB. See the details on how to do this in the instruction manual.

 

Thanks,

Adam

Edited by adam5532
Link to comment
Share on other sites

Thanks Adam!! Not only a well written contribution, but excellent documentation and support. I have just installed on my test store and have been "practicing" will a test QB company. So far so good, everything seems to be working, but I did noticed that in the Taxes section, the docs mention a "Description" field that must match exactly... This field is missing from the Configure page. I haven't tested a sale with sales tax yet so I don't know if it will be an issue.

Link to comment
Share on other sites

Allen,

Your client needs to finish configuring QBI. The Items "Invoice Account" normally needs to be "Accounts Receivable" and the "Sales Receipt Account" normally is "Undeposited Funds." Check that "Combinations:Accessories" is really an income account. Fill in the required set-up information for the "Shipping" section - the shipping charge is missing needed information. Ask your client to please re-read the manual section describing each configuration setting and make sure that everything is set up! Then it should work!

 

Thanks,

Adam

 

Hey Adam et al,

 

HERES TO PROGRESS! We're further than we've ever been before, but not quite out of the woods yet.

 

--*--

Allen,

 

The customer invoices settings were correct; tax needed to be turned on and checked off; shipping needed to be listed. With this combo I have managed to successfully import the test order but not the payment. I have been working on this but I think I've hit a wall. "Undeposited Funds" is where the payment is supposed to go; this is the name of a default account that QB won't let me delete, and it won't let me change its type. It is an Other Current Asset. I think we want it to be a Bank account. In wrestling with this, I have managed to create a Bank account called Undeposited Funds, placing my PayPal and VeriSign ledgers beneath it as sub-accounts (though this placement is not currently relevant, it is our eventual goal to direct payments into one of the two payment-specific accounts). However, the default account persists, now preceded by an *, and the import succeeds repeatedly without payment.

 

Clearly the payment is the next main focus. I also want to better understand the naming convention for assigning the customer name, and figure out how to require the least amount of rejiggering of our historical data.

 

Further...

--*--

 

Further indeed!

 

So, like last time, the links to images of the QBI config page and the iif file are at the end of this post...

 

The payment didnt import...is there something missing in the iif file, perhaps? a special line with a payment that QB is expecting?

 

Do you have any suggestions as far as the Undeposited Funds stuff goes, and whether it should be a Bank Account or Other Current Asset?

 

The 'eventual goal' mentioned above refers to the hope my client has that after we get the order and payment to successfully import, the payment can be automatically sorted into the proper sub-ledgers according to which of the two possible pay methods was used to complete the transaction. That is, of course, quite secondary to the import working at all, but if you have any comments on the current or likely future feasability of such a thing (including any musings you might have about what it would require if it's not an available feature so that we can look into the possibility of making it so) I would be glad to hear them.

 

As always, thanks a lot!

 

Allen

Link to comment
Share on other sites

Hi Allen,

Don't mess with changing undeposited funds or your bank accounts. You don't need to change anything in QB as far as that goes.

 

Your payments are not importing because you have Payments:Import Payments set to "Sales Receipt" instead of "Invoice and Payments"

 

If you want payments to import with the invoices, then you need to select this. Most people opt to use Sales Receipts, so no payments need to be imported (a sales receipt is essentially an invoice and payment in one).

 

In order to get Sales Reciepts to import, you need to do Set Up:Payments and Match:Payments.

 

I also see that your shipping methods have not been set up yet either.

 

Please tell your client that they'll save a great deal of time by reading the Config and Users' Manual completely, and following all the steps to set up and configure everything, without skipping anything.

 

Thanks,

Adam

Link to comment
Share on other sites

Thanks Adam!! Not only a well written contribution, but excellent documentation and support. I have just installed on my test store and have been "practicing" will a test QB company. So far so good, everything seems to be working, but I did noticed that in the Taxes section, the docs mention a "Description" field that must match exactly... This field is missing from the Configure page. I haven't tested a sale with sales tax yet so I don't know if it will be an issue.

You are so correct! I will look at this and fix it in the next version. But, it does work fine regardless!

 

Thanks,

Adam

Link to comment
Share on other sites

Hi

 

After installing QBI i get a fatal error on admin config - any ideas?

 

Fatal error: Cannot instantiate non-existent class: form_fields in /home/www/xxxxxxx/config.php on line 56

 

 

thx

Link to comment
Share on other sites

Hi

 

After installing QBI i get a fatal error on admin config - any ideas?

 

Fatal error: Cannot instantiate non-existent class: form_fields in /home/www/xxxxxxx/config.php on line 56

thx

Make sure you have installed the file qbi_classes.php in the directory catalog/admin/includes/classes/

 

Thanks,

Adam

Link to comment
Share on other sites

This capability and the tables needed are already built into QBI. You need to do the Set Up:Products and Match:Products procedure to make a correspondence in QBI between Child009 in osC and Parent009:Child009 in QB. See the details on how to do this in the instruction manual.

 

Thanks,

Adam

 

 

THanks for the reply adam. The only problem was that I had to match up 1000 products. In the end I found a work around in the matching system where I would do a match with the SKU's in qbi item table lookup instead of the matching table. It saved me hours believe me. The last few things that I have to work out are:

 

Company Information being overwritten when importing. There are fields such as price levels that I don't want to be overwritten. My go around rite now is importing as temp and then changing the customer.

 

UPC codes. When invoicing to wholesalers the UPC field is necessary. Right now there is no UPC field built into OSCommerce that I can work with. My thoughts are to build in a new field in the product table, tell easy populate to populate that field with my given upc's and then have quickbooks pull that upc into the right field. Does anyone know what the UPC field name might be so that I can import into quickbooks? On the same note is there anyway to export an invoice from quickbooks into a iif file. From what I've read and found, the answer is no.

Link to comment
Share on other sites

Company Information being overwritten when importing. There are fields such as price levels that I don't want to be overwritten. My go around rite now is importing as temp and then changing the customer.

Are you referring to Customer information that is over-written when a customer makes a second order?

UPC codes. When invoicing to wholesalers the UPC field is necessary. Right now there is no UPC field built into OSCommerce that I can work with. My thoughts are to build in a new field in the product table, tell easy populate to populate that field with my given upc's and then have quickbooks pull that upc into the right field. Does anyone know what the UPC field name might be so that I can import into quickbooks?

I'm not quite following. There are "Custom Fields" in QB for items that you could use for the UPS, and then configure your Invoice template to show them as a column on your invoices.

On the same note is there anyway to export an invoice from quickbooks into a iif file. From what I've read and found, the answer is no.

No, you can't.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam,

 

I was wondering what versions of quickbooks this is compatible with? Is it also compatible with a Quickbook Multi User enviroment?

 

I also want to know if I can run reports in QB with how much revenue each sales rep did per month and etc. If I made a custom field in the order manager with drop downs with the sales persons name is it possible to have this data imported into QB and then is it possible for me to run a report by sales person?

 

Thank you

Link to comment
Share on other sites

Adam,

 

I have the configuration set as Input as Sales Receipt, however the iif file its creating is for a invoice.

 

What can possible cause this.

 

I need to import to quickbooks as sales receipt not an invoice.

 

Please help.

 

Thanks

You must do Set Up:Payments and Match:Payments. QBI will only make it a Sales Receipt if it can match the payment method and the payment method is set as "Paid Online." This is so it can distinguish orders that are not prepaid, like check or money order.

 

Thanks,

Adam

Link to comment
Share on other sites

You must do Set Up:Payments and Match:Payments. QBI will only make it a Sales Receipt if it can match the payment method and the payment method is set as "Paid Online." This is so it can distinguish orders that are not prepaid, like check or money order.

 

Thanks,

Adam

 

 

So there is no way to import a check or money order - order as a sales receipt??

 

 

-- One last question hopefully, How do I get the Qty to import?

 

Thanks,

 

Mark

 

So there is no way to import a check or money order - order as a sales receipt??

-- One last question hopefully, How do I get the Qty to import?

 

Thanks,

 

Mark

 

 

-- Sometimes the quantiny and rate get imported, and sometimes not.

 

Do you know why, or how I could fix it?

 

Mark

Link to comment
Share on other sites

Are you referring to Customer information that is over-written when a customer makes a second order?

 

I'm not quite following. There are "Custom Fields" in QB for items that you could use for the UPS, and then configure your Invoice template to show them as a column on your invoices.

 

No, you can't.

 

Thanks,

Adam

 

 

In terms of the customer information, it is additional information that I have in quickbooks such as price levels that seems to be overwritten when I import from the iif file. Basically what quickbooks seems to be doing is instead of matching the customer, it overwrites it. What I'm trying to do is that I have repeat customers from the website and I don't want to re-establish all the other special fields I have for them after I import.

 

In terms of the UPC, yes I forgot it is a custom field, I remember seeing somewhere a CUSTFLD1, can I tag that to the end of the transaction line and have it input it into quickbooks as a UPC?

 

In reply to the other fellows question, you need to set your check and money order to prepaid and then it will import as a sales receipt. To make sure you did it right open up your iif file and make sure it says CASH SALE instead of INVOICE in the transaction lines.

Link to comment
Share on other sites

So there is no way to import a check or money order - order as a sales receipt??

Yes, you can import check / money order as a sales receipt, by setting it to "Paid Online" in the Set Up:Payments screen. But you wouln't want to, as it would mess up your accounting. Sales Receipts are only for orders that are already paid for, not orders that aren't paid for yet.

-- One last question hopefully, How do I get the Qty to import?

I'm not quite clear on your question. Quantity of course is put in the invoice or sales receipt. As far as product import goes, QBI does not yet have the ability to update stock quantities, but this feature will be added in the future.

 

Thanks,

Adam

Link to comment
Share on other sites

Yes, you can import check / money order as a sales receipt, by setting it to "Paid Online" in the Set Up:Payments screen. But you wouln't want to, as it would mess up your accounting. Sales Receipts are only for orders that are already paid for, not orders that aren't paid for yet.

 

I'm not quite clear on your question. Quantity of course is put in the invoice or sales receipt. As far as product import goes, QBI does not yet have the ability to update stock quantities, but this feature will be added in the future.

 

Thanks,

Adam

 

 

I am only importing orders when the order is shipped, the order only ships after the payment is received and cleared. -- Thats why I want to bring in check orders along w/ prepaid.

 

As for the qty, I mean it is not showing up on certain orders on the sales receipt.

 

any ideas ?

 

Thanks

 

Mark

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