Jump to content
adam5532

QBI Quickbooks Import

Recommended Posts

I am getting the followin error when I try to import my order.iif

 

"The Transaction is not in balance. Make sure the amounts in the detail are on the form fro this transaction equal the amount at the top of the form"

 

Why is this happening and how can I fix this?

 

Thanks

Moneer

Share this post


Link to post
Share on other sites

Are there any settings that need to be set in QuickBooks for this contribution to import correctly? Perhaps I'm getting errors because of an incorrect setting? Such as class tracking, etc?

 

I'm going to look at my IIF file in Excel and see if I can figure out the problem.

 

Adam, I will give you a nice donation for creating this contribution. Once I get this thing working it will be a tremendous time saver for our business.

Share this post


Link to post
Share on other sites

I also have another question that I forgot to ask on the previous post.

 

How do I set it so that it imports the cost of the items in the inventory. Is there a specific table that the contribution makes that I need to enter the costs in so that it imports them or can I just direct it to import them from any table that I have custom made?

 

Thanks

Moneer

Share this post


Link to post
Share on other sites
Adam, Thanks so much for this contribution.  It looks awesome.  I just finished matching my products up and the whole system is so much better then the older module. 

Thanks!

Also, there is a little error in the instruction manual.  It refers to the input and output folders as "import" and "export" folders, and even tells you to set them up as qb_import and qb_export.  However, the contrib only recognizes the qb_input and qb_output.  Just a little change in the manual is needed.

Thanks for catching that -- I've added it to my fix list.

 

Sorry for the confusion on some of the configuration options. I stayed up till about 4am last night working on the manual, and only made it a little more than half way through explaining alll the options! If you haven't yet, download v1.01 as it has the improved manual (just to get the manual -- you don't have to replace the files, as long as you make the important orders table fix). I will be finishing the explaination of all the options in the manual as soon as I can!

 

I put in 14 continuous hours on the last day to get v1.00 out the door, and about 4.5 hours last night doing minor bug fixes and working on the manual! Plus lots of time here answering questions. I'll continue to answer questions, but it will probably be a few days at least to recover before I can devote a block of time to incorporate more improvements! Thanks everyone for your feedback and suggestions!

 

- Adam

Share this post


Link to post
Share on other sites
Ok, I tried importing (QBPro 2004) and Im getting the following errors in QuickBooks:

 

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

That is most likely due to a sales tax error.

Description language:  [Customer's]  *what is this?

Didn't get to that in the manual yet!

 

If you have a multilingual enabled store, and you choose "Customer's," the item description (name in osC) flows through to the invoice in whatever language the customer had the store set to when they ordered. That lets you print your invoices with the descriptions so that the customer can read them in their own language. If you set it to "Default," then the descriptions on the invoices are always converted into the osC default language. All other fields, such as payment methods, are always converted to the store default language so that they will match correctly in Quickbooks. Note that this option only has effect if you have not "matched" the item. If it is matched, then the description is always whatever the sales description is for the item in Quickbooks, sinc Quickbooks descriptions aren't multilingual.

 

   

Taxes: 

Tax turned on:  [checked]

  Tax name:  [GA]

  Tax agency:  [GA Sales Tax]

  Tax class:      [sales Tax Item]

  Tax rate:  [7%]

Did you put the % sign in the text box? If so, then that is likely your problem. You should just put in 7.00

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
I am getting the followin error when I try to import my order.iif

 

"The Transaction is not in balance. Make sure the amounts in the detail are on the form fro this transaction equal the amount at the top of the form"

 

Why is this happening and how can I fix this?

 

Thanks

Moneer

This is likely due to a sales tax issue. Make sure that sales tax is turned on in QBI, that the sales tax fields exactly match what is in QB for tax name and agency, and that the percentage is exactly the same. Don't put the percent sign in, just the number (ie 8.75% shoud just be 8.75) (if this is being a problem I will add code that automatically strips out the % sign).

 

The other reason for the problem could be a gift certificate or low order fee. This is not yet supported, but I hope to add them within the next week or so.

 

Thansk,

Adam

Share this post


Link to post
Share on other sites
Are there any settings that need to be set in QuickBooks for this contribution to import correctly?  Perhaps I'm getting errors because of an incorrect setting? Such as class tracking, etc?

I don't think class tracking matters, but I will test it to see if there is any problem when class tracking is turned off in QB, since the class fields are in the iif. I have it turned on. You don't have to put anything in the class entries in the config if you don't use classes.

I'm going to look at my IIF file in Excel and see if I can figure out the problem.

 

Adam, I will give you a nice donation for creating this contribution.

Thanks, I'll look forward to it! :D

  Once I get this thing working it will be a tremendous time saver for our business.

Thanks!

- Adam

Share this post


Link to post
Share on other sites
I also have another question that I forgot to ask on the previous post.

 

How do I set it so that it imports the cost of the items in the inventory. Is there a specific table that the contribution makes that I need to enter the costs in so that it imports them or can I just direct it to import them from any table that I have custom made?

 

Thanks

Moneer

Just to recap first how products work , QB can not properly set up products that are imported on an invoice if they don't already exist (it will turn them into services). QBI does not import new products with each order -- they have to already be set up. This is intentional by design, so that it can have advanced features, and because if you did this (like the old contribution did), all the other product fields will get overwritten every time as you keep adding the same product over and over with each invoice.

 

You can set up products by having products that are spelled exactly the same already entered into QB and osC, by using the Set Up Products and Match Products features in QBI to correlate any product in QB with any in osC, or by using the QBI utility to export all your osC products into QB. You can mix and match all these methods also.

 

Now back to your question:

What I think you are saying is that you are using the utility function to export all your products from osC into Quickbooks. Yes, this will put in your product, but will not add things that don't exist in osC, like the wholesale cost and the vendor. It will put the account in, because it has to, but it will be what you put in the config screen under Items and will be the same for every item. The reason that I didn't add the ability to add information like cost and vendor in QBI is that I felt it was unnecessary complexity. Since every product has a different cost, and may come from different vendors, you'd have to enter this information separately for every product. So, since you have to do that anyway, why not just do it in Quickbooks, where all the vendor accounts etc already exist? It is about the same amount of work, and eliminates a lot of complication in QBI. In other words, if you use the utility to transfer your osC products into QB, afterwards open each product in Quickbooks and fill in all the remaining information that is only relevent to Quickbooks.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Adam - Thanks for the reply. Actually, I didn't put the "%" sign in the tax field. I just put it in the post that way by accident (sorry)! Well, I've tried importing the orders into QB with the tax option checked and unchecked, and both ways it fails. I think the problem must be that my tax name, agency, and class don't exactly match. Could you please tell me where exactly in QB to find this info? And where in QB do I find the item account, item class, etc? I know a few of these fields, but I had a QB accountant help me set up everything months ago and so I've forgotten a few of these things or don't know exactly where to find the details in QB.

Thanks!!!

 

PS- So I'm wondering, does QBI create a new IIF file on the fly everytime you download new/previous orders? IOW, if I make changes to the configuration, and then go into the utilities section and download the "previous" orders, will it apply the new configurations settings when it creates the IIF file again?

Share this post


Link to post
Share on other sites
IS Attributes working for anyone?

 

 

OK might be me - I do have OPTION type installed but should affect this....

 

Anyway I was able to get them to work but I had to change some code. So I am wondering why...

 

IS there a typo? - is it just me?

 

I found that $prodoptvalid is o even when there are atts is this a type or is there supposed to be :$prodoptvalid and $prodoptvald?

 

OK this is what I did to make it work (only one language so not sure will happpen w multiple languages).

 

Replaced Original Code:

// Format options
         if ($prodoptvalid>0) {
           $prod_name=$prod_name.":".$prodoptvald;
           $prod_desc=$prod_desc." - ".$prodoptc.":".$prodoptvalc;
         }

 

with this:

  // Format options
          if ($prodoptc != '' || $prodoptvalc!= '') {
           $prod_name=$prod_name.":".$prodoptc."-".$prodoptvalc;
           $prod_desc=$prod_desc." - ".$prodoptc.":".$prodoptvalc;
         }


osC Contributions I have published.

 

Note: Some I only provided minor changes, updates or additions!

Share this post


Link to post
Share on other sites

OK I just found a problem (as I figure there would be with my hack) If more than one option - only one is coming through:

TAC Stick?

- Color: red

- Size: 2 (+$5.00)

 

Imports as:

TAC Stick? - Color:red

no size....

 

IS this my hack or does it only handle one attribute?

 

 

Chris


osC Contributions I have published.

 

Note: Some I only provided minor changes, updates or additions!

Share this post


Link to post
Share on other sites

For the tax information look in your items list, you'll see it listed and can match exactly what you have.

 

Adam - Thanks for the reply.  Actually, I didn't put the "%" sign in the tax field.  I just put it in the post that way by accident (sorry)!  Well, I've tried importing the orders into QB with the tax option checked and unchecked, and both ways it fails.  I think the problem must be that my tax name, agency, and class don't exactly match.  Could you please tell me where exactly in QB to find this info?  And where in QB do I find the item account, item class, etc?  I know a few of these fields, but I had a QB accountant help me set up everything months ago and so I've forgotten a few of these things or don't know exactly where to find the details in QB.

Thanks!!!

 

PS- So I'm wondering, does QBI create a new IIF file on the fly everytime you download new/previous orders?  IOW, if I make changes to the configuration, and then go into the utilities section and download the "previous" orders, will it apply the new configurations settings when it creates the IIF file again?

Share this post


Link to post
Share on other sites

One change I would recommend is to increase SQL field size for the field qbi_items_name in the qbi_items table. I was 2/3rds of the way through matching items when I found that some of my QB items were trunkated. Rather than start all over again, I'm manually editing the database to add the missing information.

 

In a future version you might consider an automated way to match the osC items to the QB items. I only have 500 products and it is quite a bit of work to match them one by one, with a good chance for human error (matching to the wrong item).

Share this post


Link to post
Share on other sites

Adam, thanks for the fast reply and your help.

 

You are right, I did import all the products in QB through QBI but the reason I was asking about importing the COGS is that I all ready have them matched up throught linked tables. If I was going to that to each product, then it would take serveral month.

 

I will try to look for a way or a tool to quickly match up products and enter bulk information at once.

 

As for the error tha I am getting, I will check your suggestion and post the results of the findings.

 

Thank you

Moneer

Share this post


Link to post
Share on other sites

Adam,

 

What is the process for adding new products? Do we generate a new export file from QB and import it into QBI? If so, how does this handle the products which were already imported? Will it ignor them if they have already been imported?

 

Thanks,

Mark

Share this post


Link to post
Share on other sites

Before I continue on my misguided ways.....

 

First, the basics of this contribution are absolutely wonderful! It's also simple to install, and easy to manage the iif files. Thanks for your hard work, very much appreciated!

 

Now, my misguided ways.... If I understand correctly, coupons are not dealt with at all in this contribution. I tried importing a few transactions with coupons, and get the infamous "Transaction not in balance" statement. If I modify the iif file so the total does not reflect the coupon discount, then it works without a problem. Okay, yeah, I'm only doing this on a test store.....

 

I've hacked a bit so the ot_coupon total is pulled from the orders_total table, and this is entered as a line item into the iif as a coupon. This isn't completely clean, since you really need a way to match coupons to coupons, similar to the shipping method, but it works for what I need.

 

If coupon support truly isn't there, and I'm not just missing something, and if it will be a while to get solid coupon support, I'll gladly provide my hack for the time being (I'll have to clean it up just a bit and finish testing it). I just don't want to duplicate or derail something that might already be well underway in a cleaner, more robust, manner.

 

Dan


Dan Stevens

Share this post


Link to post
Share on other sites
Before I continue on my misguided ways.....

 

First, the basics of this contribution are absolutely wonderful!  It's also simple to install, and easy to manage the iif files.  Thanks for your hard work, very much appreciated!

 

Now, my misguided ways....  If I understand correctly, coupons are not dealt with at all in this contribution.  I tried importing a few transactions with coupons, and get the infamous "Transaction not in balance" statement.  If I modify the iif file so the total does not reflect the coupon discount, then it works without a problem.  Okay, yeah, I'm only doing this on a test store.....

 

I've hacked a bit so the ot_coupon total is pulled from the orders_total table, and this is entered as a line item into the iif as a coupon.  This isn't completely clean, since you really need a way to match coupons to coupons, similar to the shipping method, but it works for what I need.

 

If coupon support truly isn't there, and I'm not just missing something, and if it will be a while to get solid coupon support, I'll gladly provide my hack for the time being (I'll have to clean it up just a bit and finish testing it).  I just don't want to duplicate or derail something that might already be well underway in a cleaner, more robust, manner.

 

Dan

 

 

Dan,

 

Coupons, Gift Vouchers, COD Fees, Low Order Fees etc. are not currently supported. I have hacked them in and sent the code to Adam. I believe Adam is looking in to a way to allow users to add the additional options themselves so it can handle whatever additional options they have. I believe this is planned for the next release.

 

Chris


osC Contributions I have published.

 

Note: Some I only provided minor changes, updates or additions!

Share this post


Link to post
Share on other sites
Adam - Thanks for the reply.  Actually, I didn't put the "%" sign in the tax field.  I just put it in the post that way by accident (sorry)!  Well, I've tried importing the orders into QB with the tax option checked and unchecked, and both ways it fails.  I think the problem must be that my tax name, agency, and class don't exactly match.  Could you please tell me where exactly in QB to find this info?

Lists | Item List

Sort by Type (click on the column)

Go to the bottom and find Sales Tax Item

Choose the tax item you are using and double click

Cut and paste the fields show:

Tax name, tax description, tax rate, tax agency

  And where in QB do I find the item account, item class, etc?

Same list, click on the item.

Look at field "Income Account" (I will rename it in the config from item account to income account to avoid confusion).

This only has effect when you export the entire product list from osC to QB., or if you don't do product matching. If you do product matching, then the income account used for each QB item is transferred.

PS- So I'm wondering, does QBI create a new IIF file on the fly everytime you download new/previous orders?  IOW, if I make changes to the configuration, and then go into the utilities section and download the "previous" orders, will it apply the new configurations settings when it creates the IIF file again?

Yes, the iif file is regenerated every time before downloading when you click the button. So, if you make any configuration or code changes, this will be reflected in the new iif.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
OK I just found a problem (as I figure there would be with my hack)  If more than one option - only one is coming through:

TAC Stick?

  - Color: red

  - Size: 2 (+$5.00)

 

Imports as:

TAC Stick? - Color:red

no size....

 

IS this my hack or does it only handle one attribute?

Chris

Chris,

 

Attributes seemed to be working fine in my testing, but I will look at your code change (it may be a few days) and see why there was a problem.

 

Multiple options for one product are not supported. Whoops, I didn't think of that -- I only use one option per item in my store! I will look at this and try to incorporate in the next release, hopefully within a week or less.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
One change I would recommend is to increase SQL field size for the field qbi_items_name in the qbi_items table.  I was 2/3rds of the way through matching items when I found that some of my QB items were trunkated.

The qbi_items_name field is 40 characters, and the Item Name limit in QB is 31. Maybe these are items with sub-items in QB? I will look at this.

  Rather than start all over again, I'm manually editing the database to add the missing information.

Not necessary. All your work is saved. After increasing the field length, just go to Set Up Products and click the button again to reimport. You can also make changes to your Item list in QB and upload a new iif file and then reimport it. QBI looks at the unique QB product ID (this is in the iif and in QB, but is hidden from view normally) and will add, update, and delete according to any differences from the previous file. Any matches that you have made are preserved, as long as that product still exists in QB. In fact, you can change the names of your QB products, and as long as you upload and Set Up the new iif, the matches will still be preserved because the hidden QB Item id hasn't changed.

In a future version you might consider an automated way to match the osC items to the QB items.  I only have 500 products and it is quite a bit of work to match them one by one, with a good chance for human error (matching to the wrong item).

I'm not sure I understand. The purpose of matching is to match Item names that are different between osC and QB, so since this is an arbitrary thing there would be no way to do this automatically. This lets you match "Tea Kettle" in QB with "Bicycle Tire" in osC if you want.

 

The other reason is to match things that aren't equivelent, like "Tea Set" in osC to a "Tea Set" group in QB. The reason that this is necessary is that osC does not support product groups, but QB does. In osC, "Tea Set" is one item, but in QB "Tea Set" may be 6 items. The iif file must not only list the group name, but must contain all the group's items and their quantities. This is information that osC doesn't know, but when you do Product Set Up and match them, then it knows! I guess QBI could look for a QB group that has the same name as an osC item -- if you'd like this feature, then let me know.

 

Otherwise, there is no need to match products. If the names are exactly the same in osC and QB, then matching is not necessary. Also, if you have a product with one option type in osC, like Shoes:Large and you already have this in QB as Item "Shoes" and Subitem of Shoes "Large", then matching is also not necessary.

 

I will be adding nice graphics to the manual in a while which will hopefully make this all clearer.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Adam, thanks for the fast reply and your help.

 

You are right, I did import all the products in QB through QBI but the reason I was asking about importing the COGS is that I all ready have them matched up throught linked tables. If I was going to that  to each product, then it would take serveral month.

 

I will try to look for a way or a tool to quickly match up products and enter bulk information at once.

Thank you

Moneer

I'm not quite following. So, you have a linked table in osC that already holds the COGS information for each osC product? Is this your own mod, or is it a contribution that already exists? It would save time if you had already added this information at the time you added your products to osC, and didn't want to have to do it again.

 

If you know php, it would be pretty simple to add a few lines in the qbi_engine_prod.php file to pull this information into the iif that it generates.

 

Please clarify to make sure I am understanding you correctly, and I will consider adding this feature to QBI.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Adam,

 

What is the process for adding new products?  Do we generate a new export file from QB and import it into QBI?  If so, how does this handle the products which were already imported?  Will it ignor them if they have already been imported?

 

Thanks,

Mark

Yes. Please see my more complete explanation a few posts back. In short, yes, just generate a new export file, go to Products Set Up, and click the button again. Only changes are made, and any product matches are preserved. In addition, if you've made any changes in your QB groups, all this information is updated as well.

 

One caution: When you match to a group, make sure that your selling price in QB for the whole group exactly matches your selling price in osC.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Before I continue on my misguided ways.....

 

If coupon support truly isn't there, and I'm not just missing something, and if it will be a while to get solid coupon support, I'll gladly provide my hack for the time being (I'll have to clean it up just a bit and finish testing it).  I just don't want to duplicate or derail something that might already be well underway in a cleaner, more robust, manner.

 

Dan

This is one of my top priorities, and I plan to try to incorporate it within the next week.

 

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

×