Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QBI Quickbooks Import


adam5532

Recommended Posts

I having issues with this as well.

 

I clicked to install it, it said that the db was successfull and to click here to configure.

 

 

I clicked and nothing, no page ever shows.

 

Any suggestions???

 

Note that this is a fresh install, just downloaded the file today.

Need Hosting? Just ask!

Link to comment
Share on other sites

I don't see anything from the source to help. Do you? Thanks again, Craig :)

No. Ok, I'm going to need to do a fresh install again myself to see if I can duplicate the problem. I won't have time to do that today.

 

In the meantime, in the qbi_config table, try setting the qbi_config_active field to 1 and see if this lets you proceed.

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Adam or anyone,

 

Any chance anyone else has had that problem that I had in post #248? I thought I'd look in my phpadmin but I actually have no idea if I should be adding in a new database or what.

 

Just thought I'd check back here again because I don't want to be fooling around trying to fix it and then make it worse :-)

 

Thanks,

Michelle

Link to comment
Share on other sites

I've uploaded this contribution (thank you btw for this, it will save me a lot of time) but in trying to download my previous orders I am getting this answer:

 

1146 - Table 'bare-ess_osc1.qbi_disc' doesn't exist

 

SELECT * FROM qbi_ot_disc AS otdisc, qbi_disc AS disc WHERE otdisc.qbi_ot_mod='ot_coupon' AND otdisc.qbi_disc_refnum=disc.qbi_disc_refnum

 

[TEP STOP]

Apparently I don't have some discount table set up in my sql or ?  Everything else in the instructions went perfectly so not sure what this is all about or where I'm supposed to go to fix this. I am using the CCGV contribution (gift voucher/coupon) if that helps any.

 

TIA,

Michelle

No-one has reported this problem before. Go into phpMyAdmin and see if the qbi_disc table exists. If it doesn't, follow the reinstallation instructions in the printed manual to properly reinstall the database tables. Make sure you are using version 2.02 and not an earlier version.

 

Thanks,

Adam

Link to comment
Share on other sites

Wow. What a brilliant contribution. I just installed the latest version and it worked excellent. The only thing I had trouble with was that my orders were importing as Invoices rather than Sales reciepts. I opened the iif file in excell and my paymethods were indeed blank.

 

Upon further review of my database tables, the:

qbi_payosc

qbi_payosc_payqb

 

were both empty. I just manually added the relationships and the contribution now works flawlessly. I have checked and rechecked the configuration and I do not believe it would have been possible to configure these from the admin section. Additionally, they did not propogate during the install.

 

The only reason I post is not to gripe but only to help others who may have experienced a similar problem. Thanks again for this wonderful contribution.

Link to comment
Share on other sites

Wow. What a brilliant contribution. I just installed the latest version and it worked excellent. The only thing I had trouble with was that my orders were importing as Invoices rather than Sales reciepts.  I opened the iif file in excell and my paymethods were indeed blank.

 

Upon further review of my database tables, the:

qbi_payosc

qbi_payosc_payqb

 

were both empty. I just manually added the relationships and the contribution now works flawlessly. I have checked and rechecked the configuration and I do not believe it would have been possible to configure these from the admin section. Additionally, they did not propogate during the install.

 

The only reason I post is not to gripe but only to help others who may have experienced a similar problem. Thanks again for this wonderful contribution.

You have to go to Set Up:Payments to import your QB payment methods. Then, you need to indicate which ones are prepaid (the ones you want to import as Sales Receipts) and save your changes. Then, you go to Match:Payments and match osC payment methods to QB payment methods. Complete instructions for setting up payments are in the manual.

 

Thanks,

Adam

Link to comment
Share on other sites

I have an qbi_ot_disc table but not one without the ot.  I will try the reinstall.

 

Thanks,

Michelle :D

 

Adam

 

I ran into the same problem, turns out there is a typo in the sql file that upgrades from version 1 to version 2.

 

in the qbi_1-00_2-00.sql file, line 84 you have:

 

DROP TABLE IF EXISTS qbi_disc;

 

should be:

 

DROP TABLE IF EXISTS qbi_ot_disc;

 

It's actually dropping a table that was installed just a few lines above.

 

Dan

Dan Stevens

Link to comment
Share on other sites

Hi Adam

 

Tremendous work on this contribution. It's come a long ways in a short time.

 

I just got around to doing the upgrade today to version 2.02 from 1.01. I've always had trouble getting the shipping methods to match. Okay, not the biggest deal, but it's actually becoming more important nowadays....

 

Looking into this a bit, in my orders_total table, I have shipping entries that look like:

United States Postal Service (1 x 0.55lbs) (First-Class Mail (2 - 5 Days)):

 

Looking at the code in qbi_functions, it looks like this will cause a problem, since it's parsing for the last '(' to find the shipping method, which in my case ends up returning a delivery time, not the method.

 

I cheated to make this work for me, and just parsed $title for things like Priority, First Class, Parcel, etc, and did a direct set of $shipvia based on those. Unfortunately, not very portable for others....., but it solved my problem :)

 

I'm just wondering if I'm misreading this, or if I should really change the string parse to strip off the days first, and then search for the method, so it becomes a bit more portable????

 

Dan

Dan Stevens

Link to comment
Share on other sites

Also, is it possible to get paypal payments to come in as paid? I set paypal up in Setup / Payment as paid online, but the transactions still come in unpaid. I haven't spent much time looking into this yet, so it's likely to be something simple, just looking for confirmation that it should work.

 

Thanks!

Dan

Dan Stevens

Link to comment
Share on other sites

Adam

 

I ran into the same problem, turns out there is a typo in the sql file that upgrades from version 1 to version 2.

 

in the qbi_1-00_2-00.sql file, line 84 you have:

 

DROP TABLE IF EXISTS qbi_disc;

 

should be:

 

DROP TABLE IF EXISTS qbi_ot_disc;

 

It's actually dropping a table that was installed just a few lines above.

 

Dan

Thanks Dan! I thought I had fixed this, but I guess it got back in by accident. I will fix it in the next revision.

- Adam

Link to comment
Share on other sites

Hi Adam

 

Tremendous work on this contribution.  It's come a long ways in a short time. 

 

I just got around to doing the upgrade today to version 2.02 from 1.01.  I've always had trouble getting the shipping methods to match.  Okay, not the biggest deal, but it's actually becoming more important nowadays....

 

Looking into this a bit, in my orders_total table, I have shipping entries that look like:

United States Postal Service (1 x 0.55lbs) (First-Class Mail (2 - 5 Days)):

 

Looking at the code in qbi_functions, it looks like this will cause a problem, since it's parsing for the last '(' to find the shipping method, which in my case ends up returning a delivery time, not the method.

 

I cheated to make this work for me, and just parsed $title for things like Priority, First Class, Parcel, etc, and did a direct set of $shipvia based on those.  Unfortunately, not very portable for others....., but it solved my problem :)

 

I'm just wondering if I'm misreading this, or if I should really change the string parse to strip off the days first, and then search for the method, so it becomes a bit more portable????

 

Dan

 

I overlooked this, as the shipping module I'm using doesn't have the extra set of quotes. It looks like if I parse from the second "(" to the next "(" or ")" then I should capture the shipping method. I wish osC stored the shipping method as an ID referring to a shipping table instead of a text string, but that's life! I'll fix this in the next revision.

 

- Adam

Link to comment
Share on other sites

Also, is it possible to get paypal payments to come in as paid?  I set paypal up in Setup / Payment as paid online, but the transactions still come in unpaid.  I haven't spent much time looking into this yet, so it's likely to be something simple, just looking for confirmation that it should work.

 

Thanks!

Dan

Did you do the Payment matching? When you go to the Match:Payments screen for the first time, it will populate the database with all the payment methods found in osC. Then, you'd match your PayPal payment type in QB with the PayPal payment type in osC. This feature is also multi-lingual, so if a payment type is described differently in different languages, it will still match properly.

 

Some add-on payment and shipping modules do not have language files which are correctly configured. If, after matching above, it still doesn't work, look at the payment tables and see if the payment method matches what is actually in the order payment text. For example, if the language file says PayPal IPN, but the orders table payment_method actually stores just PayPal, then it won't match. In this case, you can manually add PayPal to the qbi_payosc table and then match it to your QB PayPal payment method in the Match:Payments sceen. Then it should work.

 

Thanks,

Adam

Link to comment
Share on other sites

Did you do the Payment matching? When you go to the Match:Payments screen for the first time, it will populate the database with all the payment methods found in osC. Then, you'd match your PayPal payment type in QB with the PayPal payment type in osC. This feature is also multi-lingual, so if a payment type is described differently in different languages, it will still match properly.

 

Some add-on payment and shipping modules do not have language files which are correctly configured. If, after matching above, it still doesn't work, look at the payment tables and see if the payment method matches what is actually in the order payment text. For example, if the language file says PayPal IPN, but the orders table payment_method actually stores just PayPal, then it won't match. In this case, you can manually add PayPal to the qbi_payosc table and then match it to your QB PayPal payment method in the Match:Payments sceen. Then it should work.

 

Thanks,

Adam

 

 

Actually, I did more checking.... should never post late at night :)

Paypal payments are coming in fine, but the invoice is not being marked paid.

 

If I look at the customers register, it does in fact show both transactions (the credit and the debit) so that part is all working. Now what I'm trying to work out is why the invoice doesn't come in as paid. In the IIF file, there does not appear to be a variable for PAID (which according to the documentation is valid), but I tried adding that, and, without a lot of testing, it doesn't seem to work.

 

I guess I need to play a bit more.....

 

Just for the record, I import paypal payments as invoices, and using QB 2002. Everything else appears to be working fine now.

 

Dan

Dan Stevens

Link to comment
Share on other sites

I overlooked this, as the shipping module I'm using doesn't have the extra set of quotes. It looks like if I parse from the second "(" to the next "(" or ")" then I should capture the shipping method. I wish osC stored the shipping method as an ID referring to a shipping table instead of a text string, but that's life! I'll fix this in the next revision.

 

- Adam

 

 

Yeah, I didn't realize how this was done until I looked at it yesterday. Does seem a bit odd way to store it, but it works.

 

Rather than parsing for the ( and )'s, why not just parse for words? I only use USPS, so it was easy for me to set that up, I'm not sure if it gets more complicated if you have other shipping methods enabled.

 

You could even pretty easily build an admin table that allowed a person to enter a parse term (such as Parcel in the case of USPS) and the shipvia term to export. Since the term will simply import into QB, whether it matches anything or not, it seems a bit simpler to me. You could then build a case statement that does a strpos($title, $term), determine if it exists, and if so, set $shipvia=$term, and exit.

 

I haven't played with how the shipping terms get stored with other methods, but it seems hard to predict where the shipping method might be in all cases.

 

Dan

Dan Stevens

Link to comment
Share on other sites

Actually, I did more checking.... should never post late at night :)

Paypal payments are coming in fine, but the invoice is not being marked paid.

 

If I look at the customers register, it does in fact show both transactions (the credit and the debit) so that part is all working.  Now what I'm trying to work out is why the invoice doesn't come in as paid.  In the IIF file, there does not appear to be a variable for PAID (which according to the documentation is valid), but I tried adding that, and, without a lot of testing, it doesn't seem to work.

 

I guess I need to play a bit more.....

 

Just for the record, I import paypal payments as invoices, and using QB 2002.  Everything else appears to be working fine now.

 

Dan

The invoice is marked as unpaid, because there is no way with iif to correlate a payment with an invoice. If you have set up PayPal as a prepaid method in QBI, and have matching working, and have set the config to import paid invoices as Invoice and Payment, then you should see a payment section in you iif after the invoice, and in QB you should find payments in the Customers:Receive Payments section. You have to manually mark which invoices are paid with each payment. You may find that choosing the config option to import as a Sales Receipt is easier.

 

Also, make sure that you set the config to only import orders with status as Pending. That way, PayPal payments that did not go through will not show up a paid orders.

 

Thanks,

Adam

Link to comment
Share on other sites

FEATURE REQUEST

 

A feature that I'd very much like to see in the next version is the ability in product matching to display only unmatched products. This would allow you to work on only the products that still need to be matched, without having to hunt them down.

 

It would also be helpful(?) if there was an option in the product dropdown list to remove the items that are already matched. When you have 100's to 1000's of products, it can become a pain to scroll down to a matching line item. If matched items are removed, it's likely to be the one on top, or at least, very close to it. Obviously, it has to be an option you can select on the match page, since some stores may match mulitple items to the same part number.

 

Also, an 'Auto-Match' function would be very nice, where if the model number and name match, the items are automatically matched in the table. This would help with the 2 items above. (yeah, I just put the tm on that for the fun of it, until my lawyer says I can trademark the term :)

 

Of course, if we have an sql guru watching this thread, it would also be nice to speed up the match queries for the product match page. Of course, the slowdown could be the population of the dropdown box.....

 

 

While I'm wishing for the moon, it would be nice to have a product status icon in the product match page as well. Or the ability to match only active products (selectable to either active / all / unmatched)

 

Dan

Dan Stevens

Link to comment
Share on other sites

Yeah, I didn't realize how this was done until I looked at it yesterday.  Does seem a bit odd way to store it, but it works.

 

Rather than parsing for the ( and )'s, why not just parse for words?  I only use USPS, so it was easy for me to set that up, I'm not sure if it gets more complicated if you have other shipping methods enabled. 

 

You could even pretty easily build an admin table that allowed a person to enter a parse term (such as Parcel in the case of USPS) and the shipvia term to export.  Since the term will simply import into QB, whether it matches anything or not, it seems a bit simpler to me.  You could then build a case statement that does a strpos($title, $term), determine if it exists, and if so, set $shipvia=$term, and exit.

 

I haven't played with how the shipping terms get stored with other methods, but it seems hard to predict where the shipping method might be in all cases.

 

Dan

It's a question of whether you look at the shipping methods table before or after you parse. The problem with looking at it before, and then looking anywhere in the osC ship string for a match, is that you can get false matches. If you look for "UPS Next Day Air" and the ship strip has "UPS Next Day Air Saver," you'll get a false match. For this reason, I'm doing parsing and then looking for an exact match in the table.

 

- Adam

Link to comment
Share on other sites

FEATURE REQUEST

 

A feature that I'd very much like to see in the next version is the ability in product matching to display only unmatched products.  This would allow you to work on only the products that still need to be matched, without having to hunt them down.

 

Good idea. I will add it to the list of desired features. Of course, contributions are appreciated with feature requests and may move it closer to the top of the list! :rolleyes:

It would also be helpful(?) if there was an option in the product dropdown list to remove the items that are already matched.

Good idea also.

Also, an 'Auto-Match' function would be very nice, where if the model number and name match, the items are automatically matched in the table.

I'm a little confused here. If the osC model number and QB name are the same, then there is no need to match them. Unmatched products will "flow through" to QB.

Of course, if we have an sql guru watching this thread, it would also be nice to speed up the match queries for the product match page.  Of course, the slowdown could be the population of the dropdown box.....

The slowdown is in the HTML, not in the SQL. I have 3,000 products in QB, so having just 5 product match lines makes 15,000 lines of HTML.

Or the ability to match only active products (selectable to either active / all / unmatched)

Active/inactive selection is planned for a future release.

 

- Adam

Link to comment
Share on other sites

Good idea. I will add it to the list of desired features. Of course, contributions are appreciated with feature requests and may move it closer to the top of the list!  :rolleyes:

 

Good idea also.

 

I'm a little confused here. If the osC model number and QB name are the same, then there is no need to match them. Unmatched products will "flow through" to QB.

 

The slowdown is in the HTML, not in the SQL. I have 3,000 products in QB, so having just 5 product match lines makes 15,000 lines of HTML.

 

Active/inactive selection is planned for a future release.

 

- Adam

 

 

If the name and the model match, it does work, but if you want to see a list of unmatched products, these would show up as unmatched, even if they 'work' because of the match. The concept was a way to deal with these.

 

Also, in my store, probably half match and half don't. This basically means I have to match them all, or else there simply isn't anyway to tell what is matched by the model/name match vs what simply isn't matched.

 

Of course, it might be simpler to convince my wife that the part numbers should match (long reason why they don't).

 

Dan

Dan Stevens

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