Jump to content
adam5532

QBI Quickbooks Import

Recommended Posts

How do I reset the import dates? Right now it only lets me import a few of my orders, but I am just now starting synch with qb and would like to import all my orders.

On the Creat iif page, there is a button to import all orders.

Also, customer import doesn't seem to work. It shows how many customers I have on the page, but when i create the iff, only a few customers show up.

Only customers who have placed orders will be imported. Is this the problem?

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Thanks for this Contribution Adam!

 

When i Click the button ?Install Database Tables Now? it redirects me to the top ranked google search for php_uploads which is the directory my test site is installed in.

 

So I manually added the tables through phpMyAdmin.

 

Now when i click the Update button or any of the tabs it redirects me to this same site.

How do i fix this?

Thanks

Share this post


Link to post
Share on other sites

Has anyone gotten this to work with UPS XML? I installed that module and now when I click on download iif file for New Orders I get this error:

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Estimated Delivery Date'>EDD: March 09, 2006' AND so.

 

SELECT * FROM qbi_shiposc AS so, qbi_shiposc_shipqb AS soqb, qbi_shipqb AS sqb WHERE qbi_shiposc_car_text='United Parcel Service' AND qbi_shiposc_serv_text='UPS Ground, EDD: March 09, 2006' AND so.qbi_shiposc_car_code=soqb.qbi_shiposc_car_code AND so.qbi_shiposc_serv_code=soqb.qbi_shiposc_serv_code AND sqb.qbi_shipqb_refnum=soqb.qbi_shipqb_refnum

 

[TEP STOP]

Share this post


Link to post
Share on other sites

Hi,

 

I am using QBI with UPS XML with no problem, although my version is now mostly 3.0 with parts of 2.10b still mixed in!

 

There is probably an unescaped single quote character in the text that is prematurely ending the SQL statement. But what you've posted is confusing -- the error message says "near 'Estimated Delivery Date" (etc), yet the words 'Estimated Delivery Date' do not appear in the SQL statement which follows (only EDD does). Please make sure that you've copied the error message exactly as it appears.

 

However, I will look at the code and let you know where to modify it. Probably putting in an mysql_real_escape_string will fix it.

 

Thanks,

Adam

 

Has anyone gotten this to work with UPS XML? I installed that module and now when I click on download iif file for New Orders I get this error:

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Estimated Delivery Date'>EDD: March 09, 2006' AND so.

 

SELECT * FROM qbi_shiposc AS so, qbi_shiposc_shipqb AS soqb, qbi_shipqb AS sqb WHERE qbi_shiposc_car_text='United Parcel Service' AND qbi_shiposc_serv_text='UPS Ground, EDD: March 09, 2006' AND so.qbi_shiposc_car_code=soqb.qbi_shiposc_car_code AND so.qbi_shiposc_serv_code=soqb.qbi_shiposc_serv_code AND sqb.qbi_shipqb_refnum=soqb.qbi_shipqb_refnum

 

[TEP STOP]

Share this post


Link to post
Share on other sites

I agree, it was an odd error message. I worked around it by removing the ', EDD: March 09, 2006' portion of the string and now it works.

Share this post


Link to post
Share on other sites

I'm not quite understanding the details you are giving, or what the "top ranked google search" page is. Also, when you say it is a test site, did you already have osC functioning properly before installing QBI? I suspect that there might be a problem with the paths that you've set up, you are using a secure server with a different URL than your non-secure pages, or you haven't put in the database table constants according to the QBI installation manual.

 

If you can send me the URL and admin password (privately) I may be able to see what you are referring to.

 

Thanks,

Adan

 

Thanks for this Contribution Adam!

 

When i Click the button ?Install Database Tables Now? it redirects me to the top ranked google search for php_uploads which is the directory my test site is installed in.

 

So I manually added the tables through phpMyAdmin.

 

Now when i click the Update button or any of the tabs it redirects me to this same site.

How do i fix this?

Thanks

Share this post


Link to post
Share on other sites

I was having problems with importing product lists exported from a Mac version of Quickbooks.

 

Apparently the IIF doesn't always have a HIDDEN column which qbi_products.php assumed was there.

 

I changed the code and it appears to work.

 

Arround line 72 add:

 

$notHidden = ($iifitem["HIDDEN"]=="N" OR $iifitem["HIDDEN"]==null);

 

Then replace

 

($iifitem["HIDDEN"]=="N")

 

in the if / elseif clauses there are 3 places, with:

 

($notHidden)

 

This did the trick and I was able to import all the products into the QBI interface including product groups.

 

cheers

 

Andrew

Share this post


Link to post
Share on other sites
I was having problems with importing product lists exported from a Mac version of Quickbooks.

 

Apparently the IIF doesn't always have a HIDDEN column which qbi_products.php assumed was there.

 

I changed the code and it appears to work.

 

Arround line 72 add:

 

$notHidden = ($iifitem["HIDDEN"]=="N" OR $iifitem["HIDDEN"]==null);

 

Then replace

 

($iifitem["HIDDEN"]=="N")

 

in the if / elseif clauses there are 3 places, with:

 

($notHidden)

 

This did the trick and I was able to import all the products into the QBI interface including product groups.

 

cheers

 

Andrew

Andrew,

 

Could you please let me know the country and version of Quickbooks you are using on the Mac? Also, could you please could email me a piece of the iif file. Then I'll incorporate this change into QBI.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

When I export orders to QB, how do I get the payments to show up as applied? Right now I get a payment that shows up and an outstanding invoice, but they aren't linked.

Share this post


Link to post
Share on other sites
When I export orders to QB, how do I get the payments to show up as applied? Right now I get a payment that shows up and an outstanding invoice, but they aren't linked.

That's the nature of Quickbooks iif. You have to go into the payment window and put a check next to the invoice and then save it to apply the payment to the invoice.

 

For this reason, it is much easier to import paid orders as sales receipts to avoid this step.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

My client is using QBI to import orders, and he says this is what's happening:

 

"money is applied to the invoice owed by the customer but not posted to the bank account. Making deposits is a second and separate step"

 

and he would like this:

 

"money received is applied to the customers invoice and deposited into the account as one operation"

 

Is this possible?

Share this post


Link to post
Share on other sites
My client is using QBI to import orders, and he says this is what's happening:

 

"money is applied to the invoice owed by the customer but not posted to the bank account. Making deposits is a second and separate step"

 

and he would like this:

 

"money received is applied to the customers invoice and deposited into the account as one operation"

 

Is this possible?

It is possible, as noted in my response above, to create an invoice and apply the payment in one step. In Quickbooks, this is what they call a Sales Receipt, and QBI can be configured to create Sales Receipts instead of a separate Invoice and Payment.

 

As far as deposits go, QBI does not do deposits. The problem is that when you use an online payment gateway like Authorize.net, you get a batch of orders deposited at once. So, a depost may be in the amount of $60, and that might include 3 online orders for $30, $20, and $10. In addition, because payments may take different amounts of time to be funded, those orders may not have all occured on the same day. So, it would make it impossible to reconcile your bank account, because QBI can't know which orders are going to deposited together. For this reason, I try to daily download and reconcile my bank statement in Quickbooks, matching up the individual payments to make a deposit for the proper amount. If you don't do this daily or at least frequently, it can get confusing to match them up.

 

If your gateway works differently, then let me know.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Adam thanks for the contrib. I am working with a client right now and trying to debug this contrib on his osCommerce sites. From reading through here is looks like others have had the same problems, ie the automatic set up doesn't work. I manually installed all the MySQL tables and still the update wouldn't "take" from the config page. So I manually set it to "active" and got it to work but now it still won't download the IIF files.

 

My guess is that this is related to the "register_globals" being off on this host. I have v.2.10 of your contrib and see that you plan on fixing the "register_globals" issue in 3.0. Do you have a plan date for this to be done? Also if you need / want help with this drop me an email and I might be able to put some time into it. Basically I am trying to decide if it would be easier to modify your contrib with a 'one-off' fix or rebuild php on a remote server.

 

Thanks again,

 

Matt Swaffer

Dynamic Dataworks LLC

Share this post


Link to post
Share on other sites

Hi,

 

I have a bunch of products, but can't export them out of OSC into quickbooks. the IIF file is created, but not loaded into quickbooks...

Suggestions??

 

PS: Forgot to mention, I am using QBI Enterprise - Retail, v5.0

Edited by danospv

Share this post


Link to post
Share on other sites
Adam thanks for the contrib. I am working with a client right now and trying to debug this contrib on his osCommerce sites. From reading through here is looks like others have had the same problems, ie the automatic set up doesn't work. I manually installed all the MySQL tables and still the update wouldn't "take" from the config page. So I manually set it to "active" and got it to work but now it still won't download the IIF files.

 

My guess is that this is related to the "register_globals" being off on this host. I have v.2.10 of your contrib and see that you plan on fixing the "register_globals" issue in 3.0. Do you have a plan date for this to be done? Also if you need / want help with this drop me an email and I might be able to put some time into it. Basically I am trying to decide if it would be easier to modify your contrib with a 'one-off' fix or rebuild php on a remote server.

 

Thanks again,

 

Matt Swaffer

Dynamic Dataworks LLC

Share this post


Link to post
Share on other sites

Matt,

 

...

My guess is that this is related to the "register_globals" being off on this host.

Possibly. A number of people have had problems with the automatic installation, although I haven't tracked down the exact cause. Usually when that fails, it still doesn't work after a manual install, which indicates a problem elsewhere. Sometime it works fine after setting active to 1. But, I will be rewriting that part shortly to make it more reliable and to report on the screen if it runs into any problems.

 

You might try putting

php_flag register_globals on

into an .htaccess file for the admin directory, if you are using Apache 1.x, and see if that works. I think you can also do it with Apache 2.x, but the procedure is a little different.

 

Open your error_log file on the host and look at it, as that will give you information that may help you to identify the problem.

 

I have v.2.10 of your contrib and see that you plan on fixing the "register_globals" issue in 3.0. Do you have a plan date for this to be done?

I'm trying hard to get the beta out in the next few weeks. Don't hold your breath, but that's what I'm trying for. I've rewritten everything now except for the item/product parts, and the automatic installation.

Also if you need / want help with this drop me an email and I might be able to put some time into it.

I appreciate the offer. But right now, it would be difficult to split up the work. There's over 2,000 lines of code, and I know how it all works! Version 2.10b is almost all procedural code, but version 3.0 is completely object oriented. Also, I'm going to run it through php documentor when it is done. So, in the future, it will be easier to accept offers of help, or for people to make modifications that they need, or for other people to see what's going on!

Basically I am trying to decide if it would be easier to modify your contrib with a 'one-off' fix

You might. You just have to change variables that are passed in the create and config pages to $_POST['variablename']. I looked at this briefly -- it can be done if you study it a while. If it was really easy I'd just say right here what to do, but it isn't instantly clear, and I want to spend all my time on 3.0 now.

or rebuild php on a remote server.

I assume then that your client is on a shared server and not a virtual host, so you can't change php.ini? But try the .htaccess thing above, or search the other php sites for turning on register_globals at runtime.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Hi,

 

I have a bunch of products, but can't export them out of OSC into quickbooks. the IIF file is created, but not loaded into quickbooks...

Suggestions??

 

PS: Forgot to mention, I am using QBI Enterprise - Retail, v5.0

Please explain. When you import it into Qucibkooks, do you get an error message?

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
No, it says file imported successfully, but the items don't show up in the items list...

Check that the type of items they are (Inventory, Non-inventory, or Services) is selected in the Config page or they won't show. If you want all types to show, you can also leave them all unchecked. Assemblies are supported in the next version, although a work-around is noted in earlier posts.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

All are checked, and items are still not imported properly. They are in the IIF file when i open it in notepad...

Could it be my Quickbooks configuration?

Share this post


Link to post
Share on other sites
All are checked, and items are still not imported properly. They are in the IIF file when i open it in notepad...

Could it be my Quickbooks configuration?

Not unless you have Quickbooks set up only for services and have not enabled items and inventory. But I assume you are already using Quickbooks? If not, make sure that everything works in QB before trying to use QBI. Also, make sure that you have specified the item accounts in the QBI configuration.

 

If it still doesn't work, send to me by private email the iif file and your QBI log file (in the qbi output directory).

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

I have other items set up, which I had added manually. So the QB is set up correctly. I also processed some orders exported from QBI (using manually added + matched items).

I will send you the info tomorrow, after checking it over again.

Thanks

Share this post


Link to post
Share on other sites

I'm sure this has probably already been asked but I've tried searching.

 

Which versions of quickbooks does this contribution work with? Which version of quick books are people using?


__________________________________________________________

Favourite and essential contributions in no particular order.

 

product tabs, ultra pics, description in product listings, ultimate SEO URLs, header tags controller, all products with pictures, RSS feed, easy populate, protx direct 2.0, gift cert and voucher, step by step manual order entry, guest account, featured products, live support, enhanced whos online, super tracker also main page before column left thread.

Share this post


Link to post
Share on other sites
I'm sure this has probably already been asked but I've tried searching.

 

Which versions of quickbooks does this contribution work with? Which version of quick books are people using?

Currently US versions of Quickbooks Pro and Premier, and Quickbooks Mac. It does not work with Quickstart or Online. I'm not sure about Basic, but it is no longer available. Enterprise has not been tested with QBI, so I don't know. Most years back to 1998 will work. International versions will be supported in the near future.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

Adam, awesome contribution! :thumbsup:

 

I'm setting up a new shop. I'm using Quickbooks Premier Retail 2005. We have about 100 items in QB so far and plan to add several hundred more. Mostly these are inventory items but there are a few non-inventory and a few service items. I've been able to export my items to OSC via QBI. What I don't have is all the items created in OSC yet. I did have 4 products in OSC (for testing) that I was able to match to service items from QB so I know the product upload worked (QB->OSC)

 

Does anyone have a nifty way to create all my Quickbooks items as products in correct categories in OSC? Do I need another contribution that allows me to do some sort of mass item creation in OSC? I plan to keep the item number/names the same between QB and OSC so the flow through without special matching works.

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

×