Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

QuickBooks Contrib


VyReN

Recommended Posts

Justin,

 

I've been extremely busy, so although my intentions were good, I'm glad you've gone ahead and gotten the tax part to work. It probably would have taken me more than 10 minutes since you are already familiar with the code.

 

I am using QB Pro 2004 and I assume it will work in this version. I'll install it and let you know how it works and if I see any improvements I'd like to suggest and/or make or any incompatability with 2004.

 

Thanks,

Adam

Link to comment
Share on other sites

  • Replies 285
  • Created
  • Last Reply

Top Posters In This Topic

Not sure if it will work on QB2004 or not. Usually as long as they did not re-arrange the fields too much or add new mandatory fields (such as the case of tax codes being mandatory even for non-taxables) it will "probably" work. Hopefully, you did not download the file last night as I realized today I forgot to sum the taxes so it worked great if you only had one taxable item on the invoice, but not so well if you had more than one line item taxable. I re-arranged some stuff today and got the taxes to sum properly and re-uploaded the zip file for that zip I posted. The code in the "test" download file is a bit of a mess as I just wanted to get it to work first and then clean it up later. I believe ATM there are three loops in the file where it could/should only have two and not to mention there are all sorts of unneeded vars included in the loops. Like I said just wanted it to work for QB2003 and then I can go in and remove all of the extras that are in the file from my copy&paste hack job. :huh: It is actually a pretty simple contribution/file after you look into it since it is just a matter of creating a tab delimited file in a certain order under certain headings. I was banging my head on the wall until I realized you have to have the word "AUTOSTAX" in the "EXTRA" field for the product lines...I still have no idea as to "why" other than it tells QBs to default to your most common tax account. :blink: Maybe someday someone will get the SDK and make this an XML contribution so it can really hum (hint to some ambitious soul).

Link to comment
Share on other sites

*grins* .. yes that would be nice to have an sdk wouldn't it.

ok I have the admin set up so you can set up the preliminary vars and how you want to handle the download whether by file or email. There is something like 20 vars to setup in the older stuff I will look at what you have and will incorporate that into what you have so far.

A client gave me the qb 2002 premier so it will be nice to see if it works on that also and I have another client that has qb 2002 pro so I will test it out on that over the next week. Does anyone have a cvs we could tie this all into so we won't step on toes?

If not and you guys want I will have one setup in the next couple of days for this.

(my knowledge is so small you would think that an ant was a genius in comparison)

Link to comment
Share on other sites

Justin et al,

 

I must have downloaded the newer version, as it does have the AUTOSTAX. This is totally cool and exciting! It is going to save me SOOO much time!

 

Here's the result of my testing with QB Pro 2004:

 

1) Basically, importing customers, invoices, and products works well!

2) It chokes on groups - I have some of my items set up as groups in QB, and also my Sales Tax is a group because it is made up of various separate tax districts (this makes it easier when I do my sales tax return). With both, when I try to import, QB says that the transaction is out of balance (even if I change the item type in the iif file). It seems that it must need to have the individual line items as well as the group name. If I import non-group inventory or sales tax, there is no problem. What I've done in the interrum is set up a sales tax catagory called "Change Me" with the same tax rate as my real one. Once the invoice is imported, I change it to the proper tax item.

3) I need to set the tax status for each customer according to their state. So, new customers from California get Tax set to Yes, and all others to No (I don't have to collect sales tax for out of state customers). This might mean just adding a test for state="CA" in the qb_sales file, as I believe OSC determines this on the fly (when each order is totaled) and doesn't store a field. What may make it more complicated in the future is if the US starts requiring all Internet sales to collect tax for each state - then I'll have to allocate each customer's tax to their own state!

4) I have sales both on-line and off-line. When I import an .iif and it has product information, it overwrites or deletes (if the field isn't imported) whatever information I have entered or changed for a product in QB. Therefore, for my use, I've commented out the Inventory item section so that it doesn't import products, as all my products are already set up in QB. This would be a nice option to set in the Admin section.

5) I'd like to import credit card info. I can look at how to add this. The location of the iif file therefore needs to be secured.

6) The "Description on Sales Transactions" field is blank and not importing. The "Description on Purchase Transactions" field does import. If it works for you, them maybe this is changed in 2004.

7) The selected Product Attribute for items with attributes doesn't print on the Invoice, and I don't see it in the .iif file.

8) Minor stuff: If there isn't a company name, it skips SADDR2 and leaves an empty line in the customer info. QB2004 handles addresses a little differently than previous versions, so you may not see this in yours. This would be pretty easy to fix (test for empty company and if empty move up a line). - Customer Type is the same as the Rep and doesn't get set. - Invoice numbers in QB get messed up - when I go to add an off-line invoice, it automatically increments the Invoice number of the last OSC invoice that I imported. That throws off my numbering sceme, and means that on the next import there will be a duplicate number. Haven't thought of a way around this yet though, as it is something internal to how QB functions.

9) I like the idea of pulling the info from the MySQL tables to generate an .iif instead of generating the .iif as orders are placed. An extra field could be added to the Orders, Products, and Customers tables to indicate 0 or 1 if the row had been exported to Quickbooks or not. Settings could be stored in another table. The advantages might be: No changes needed to any OSC files, easier installation of the mod, all customer info stays in one secure location, ability to reimport by date range, no need to delete .iif and no chance of loosing a transaction on a busy site when the file is deleted (due to lack of file locking). This isn't meant as a criticism of the present mod, just an idea to see what you think.

 

Anyway, I'd like to work on some of these issues (rather than just complaining about them). But don't want to work on conflicting versions of the mod or duplicate efforts - if Strider sets up a cvs that would be very helpful (then I'll have to learn how to use CVS!)!

 

Thanks,

Adam

Link to comment
Share on other sites

Hi Jason,

Good to see you back as I thought you had disappeared. :)

 

ok I have the admin set up so you can set up the preliminary vars and how you want to handle the download whether by file or email. There is something like 20 vars to setup in the older stuff I will look at what you have and will incorporate that into what you have so far.

 

Cool, I will wait for your admin integrated version rather than starting to work on my own. It should work for 2002 Pro and Premier as the iif file looks to be the same for the two versions, at least I did not notice anything right off hand. A CVS would be good at this point if multiple people are going to start working on this.

 

Hi Adam,

 

2) It chokes on groups

 

Ah, I did not add "groups" to it since I do not use them...lol. :D You can, however, add groups to it by adding a couple of lines to the iif file. A sample iif for invoices with groups and taxes is available for download HERE There are also more sample iif files on the quickbooks site HERE Then you just need to make your qb_iif_sales.php output match that of the sample one and you are all set. That is how I figured out the "secret" was AUTOSTAX when my file matched the sample exactly except for that little command and it would not work.

 

3) I need to set the tax status for each customer according to their state

 

That would be a good thing to have added in the admin section where you can set up the tax zones and have it sent to QBs for each individual customer based on their zone_id in OSC. Then in the qb_iif_sales.php just run an if statement so if the zone_id is X,Y,Z taxable if not then non tax.

 

4) I have sales both on-line and off-line.

 

That is a problem I am not sure how to handle. Have you tried setting up 2 classes one for "Web" and one for "Offline" and then set the product class up. I am not sure if it would create 2 different product names if they had different classes or if it would still over-write them. I did notice that in my current test version the products are not assigned individual classes like they should which will be a simple fix (just need to add it to the product line in the iif).

 

5) I'd like to import credit card info.

 

I plan on doing this as soon as I get my quickbooks merchant account set-up. For the moment I process the cards manually through a terminal but am planning on using "quickbooks merchant services" so that I can process the cards right out of QBs. You just need to add some extra fields onto the customer part of the set-up. Also have to make sure and password protect your qb_sales folder.

 

6) The "Description on Sales Transactions" field is blank and not importing. The "Description on Purchase Transactions" field does import. If it works for you, them maybe this is changed in 2004.

 

This might be from commenting out the INVITEM section from the contribution as that is where those fields are populated. When you look through the file you will notice that the $memo field is called only once in the transaction section, but is called twice in the INVITEM section (once for sales and once for purchase). I am not sure how you could get it to populate both fields just using the transaction section.

 

7) The selected Product Attribute for items with attributes doesn't print on the Invoice, and I don't see it in the .iif file.

 

I think the attributes are just supposed to be appended onto the product description. Look for this in the php file "// If there are attributes, we'll simply append the values to the item description on the invoice line". From looking at my own invoices this part is not working so is something that will have to be tackled.

 

8) Minor stuff: If there isn't a company name, it skips SADDR2 and leaves an empty line in the customer info

 

This is my fault as I wanted the data in a certain way when it is sent to the FedEx Ship Manager part of QBs so I altered it compared to the way it should be. The issue was the standard way placed the company name on the shipping label twice where as I only wanted it on there once. :) I will re-arrange it back to the way it is supposed to be when it gets closer to being a "complete" contribution.

 

This isn't meant as a criticism of the present mod, just an idea to see what you think.

 

No problem and it is a good idea. Probably going to finish working on it "as is" for the moment rather than starting over though. :D LOL I am glad to see there are more people than just me that are excited about the possibilities that integrating OSC and QBs holds. If I could not get this to work I actually pondered the thought of trying out X-Cart as it already has the module built-in, gasp! :o

Link to comment
Share on other sites

Hi,

 

From what I can tell, Quickbooks doesn't allow you to put the credit card number in an .iif file. I've looked through their documentation and sample files and don't see these fields in the customer info. The credit card fields do appear in the documentation for the Excel import feature. Another possibility is using third party software, such as QODBC (which I have a copy of) to make an ODBC connection to QB, but many people don't have this. So, at this point, unless I'm missing something, we may have to use the Import Excel feature for customer info and either Excel or IIF for the transaction info (if you want the credit card fields), hence back to two different files.

 

I did download the SDK (all 56 Meg) but don't hold your breath.

 

Problem found with the current Account naming scheme: If you have two customers with the name John Smith, and the second will over-write the first in QB. It may be better to append the unique customer_id to the name to avoid this (Smith John 257).

 

- Adam

Link to comment
Share on other sites

ok I am running a little behind here but I did look at the admin settings. it is pretty close to what I did before but I also think I came up with something else that might make more sense but will be a lot more complex. getting the rcs installed on the server now so it should be done tomorrow I am hoping ... which shoe goes on what foot ....

(my knowledge is so small you would think that an ant was a genius in comparison)

Link to comment
Share on other sites

I need some troubleshooting help. My .iif file is not being created, where should I look to see what might have gone wrong during installation?

 

I've gone over and over all the instructions and I think I've done all of them correctly.

 

Thanks to anyone who can suggest where to check first...

Link to comment
Share on other sites

First make sure you created an empty directory called qb_sale which is where the file is going to be placed, next make sure you have this in application_top.php

 

  define('CREATE_SALE_QB_IIF_FILE', 1);

 

At the top of the qb_iif_sale.php find this:

 

$myfile = "/catalog/qb_sale/qb_sales.iif";

 

Make sure that is the correct path for your server. That is the most common path for those of us on Unix servers, but your may be different (especially if you are on a Windows environment, etc)

 

Last you can play around with this line in the qb_iif_sale.php file

 

$salefile = $DOCUMENT_ROOT . $myfile;

 

Instead of $DOCUMENT_ROOT you can try hardcoding your server path in there.

Link to comment
Share on other sites

Also make sure that you check that write permissions are set for the qb_sale directory (for the folder and any files) (you may need to use an FTP program to do this), and run a test purchase in QB to make sure there is no error message at the conclusion of your order (which would indicate if there is a problem writing and what directory and file it is trying to write to).

 

- Adam

Link to comment
Share on other sites

Thanks, Justin and Adam...you are very kind.

 

I tried all of those suggestions and still no file is being generated, so it must be something I've done (or not done). ;)

 

At least it's not preventing orders from going through...back to go over the installation instructions again and see if I've missed something--one more time (maybe several).

 

I just can't wait, this was/is the best contribution ever. The cut/paste into Quickbooks is so painful. :D

 

Thanks again for the hints.

Link to comment
Share on other sites

Okay, I got it to work!!! Yippee!!

 

For all those that are struggling, my solution was that I had to hard-code the ROOT DOCUMENT as was suggested. Thanks. However, I had included an extra 'catalog' at the end the first time, so it wasn't recognizing it. But that was the solution!!!

 

I know enough to be dangerous, but maybe for those like me, perhaps the installation instructions could include the 'define' or call it differently???

 

I LOVE THIS!!!! It works with tax, too!!!! Of course, now I have to go back and input all my particulars and get rid of the Michigan tax, but IT WORKS!!!! I'm sooooo excited, can you tell? :D

 

This is such a great contribution and should definitely be included as standard.

 

Gets 5 stars!!! If I only had Ben Stein's money...I'd be so happy to share.

Link to comment
Share on other sites

This is such a great contribution and should definitely be included as standard.

 

It is not quite ready for "Prime Time" yet :D but hopefully over the next couple of weeks/month some of the particulars will be worked out and it will be cleaned up a bit to make it more of a complete module. Still lots to be done on it...

Link to comment
Share on other sites

Hi,

 

I've made the following improvements and fixes to the 'tax' version:

 

1) Addresses without a company name don?t skip a line

(now checks if company exists and if not moves everything up a line)

 

2) Shipping methods now import properly

(field was blank so it always gave the default)

 

3) QB customer 'Name' now contains the unique cust id to avoid overwriting another customer with the same name

(is now in the form "last-firstcustid" and is easier to change to alternative formats)

 

4) Customer is set as taxable or non-taxable according to the shipping zone

(all customers added to the customer list will now be added as taxable if their shipping address is in your tax zone. Can also be defeated if desired)

 

5) Customer's QB 'tax item' now imports

(the tax name was blank in the customer list)

 

6) Terms now follows the payment method chosen by the customer on each invoice

(terms was static and wasn't able to follow the chosen payment method [if you gave the customer the option to send a check or pay by credit card])

 

There are of course a lot of improvements left to make, but these cover the main problems at least I was having. The only really important thing lacking for me is the ability to import group product and group sales tax items. I didn't have time yet to work on adding the groups.

 

Shall I go ahead and upload the updated file to the package? Or Justin etc, are you concurrently making changes? Also, since this now has tax capability and also works with versions besides 2003 (I use 2004), shall I start a new package with a more appropriate name than " ... 2003 No Tax"?

 

Thanks,

Adam

Link to comment
Share on other sites

Hey Adam,

 

Go ahead and contribute that bad boy! :D I would do it as a new contribution with a relatively similar name so that it appears right next to the no-tax version in the contribution list. It should also work fine with QBs2002 as far as I can tell. You should probably go through the install instructions as well and remove the parts about the qb_iif_customer.php along with a few of the instructions that are no longer needed. I have not had any time to work on it as of late as I am having host problems and am in the process of moving my site to a different host, big fun. I did discover that my alterations to the address format left the street address field in the QBs CC module blank rather than auto-filling them as it should, so I am looking forward to just loading your file up which will fix that problem and I will no longer have to enter in the street addy before submitting the CC payment.

 

Lastly, you mentioned a page or so back about finding something about being able to import the CC info from excel...did you find something that shows the field headings in excel on how to do this? I am thinking if I know the field headings I can alter it a bit to work so I do not have to manually enter in the CC numbers for each customer. Once the address format is taken care of by using your version, the CC number will be the only thing remaining I have to manually enter to process and ship an order besides package dimensions etc.. Call me lazy, but I am hoping to get the whole process taken care of with a few clicks and almost zero data entry. B)

Link to comment
Share on other sites

Hi,

 

Ok, I've uploaded the latest and given it a new name "Quickbooks IIF Import with Tax Option." This should avoid any confusion since it works with more than Pro 2003 and now has Sales Tax.

 

It can be found here:

http://www.oscommerce.com/community/contributions,1861

 

It incorporates Justin's Sales Tax mods he did earlier (but hadn't uploaded as a contribution) as well as the improvements I made and listed in my previous post.

 

This is my first time contributing!

 

Note the following:

 

Known bugs:

The country name is not importing (I found one problem, but if I import the field it prints "Array" instead of the country).

Invoice numbering between on-line and off-line can interfere and get reset by the other.

 

Compatibility with non-tax stores:

Not tested yet, but it makes sense to me that we only have one version for both tax and non-tax people. We may need to add some configuration variables, as I imagine QB might choke if you import an IIF with tax items when tax is turned off.

 

Wish list:

Ability to import products that are group items

Ability to import sales tax that is a group item (there is a work-around noted in the code and installation docs)

Credit card number import

Admin panel

 

Installation Documentation:

I made some notes at the top about the changes, but I think the docs need improvement and re-writing. I didn't have time to do that (yet).

 

JB, in response to your post:

Yes, now that missing address line in the credit card tab in Quickbooks fills in. However, as you probably already know, QB has a little bug (did it in 2002 and I would have thought they'd have fixed it by 2004) that it isn't too smart and just fills in the second line in your address. So, if the company name is the second line in your address, that's what it fills in for the CC billing address and you have to cut and paste the address line in to fix it! I don't see a way around this (except with the Excel import), as the IIF file doesn't import those CC fields.

 

It looks like the Excel file import is a new feature in QB 2004. The allowable fields are listed in the QB Pro 2004 help and probably on-line also. There is a menu choice FILE | Import | Excel Files. What you do is create an Excel file (I don't know if a plain CSV file will work the same) with the desired column headings. QB allows you to set up "Mappings" so that you can map the column name to the fields in QB.

 

Anyway, it looks like the Excel import feature adds the capability to import CC numbers, expiration, address etc while these fields are lacking in the documentation for the IIF files.

 

So, it looks like we'd need to import a separate Excel or CSV file for the customers and then import the IIF for the orders. I'm contemplating whether this is the way to go, or if an on-line gateway like Echo makes more sense. I hate to have to pay for two merchant accounts (why Chase or Wells Fargo doesn't have a gateway option with their Quickbooks merchant accounts is beyond me but I guess they aren't quite there yet). Don't know what kind of volume you do, but I need both on-line and off-line CC processing.

 

Thanks,

Adam

Link to comment
Share on other sites

Adam, you and JB are 'da men! Thanks for your efforts. I guess from your very last post I can infer it makes sense for me to get off my wallet and upgrade to 2004, right?

 

JB, we use www.addaction.net for hosting and are quite satisfied . . . for what it's worth, we have zero association with them - other than being satisfied customers.

Edited by jbeech

John Beech - GM (and janitor)

Link to comment
Share on other sites

I like the customer part for several reasons.

 

The main reason is because I have many different sizes, product IDs and other things built into quickbooks for each specific size. The problem i ran into with the sales file was this:

 

Pretend I import 3 orders, all happen to be the same shoe, with the item ID of JY301.

 

In quickbooks, JY301 does not exist alone. JY301 is a type of shoe, and inventory is needed for each specific size, i.e. JY301-B/8 , JY301-W/7H , and JY301-B/7.

 

so when the import would take place, Id show negative 3 JY301, because it isnt even technically a product, the product is, for instance, JY301-B/8 a black size 8 shoe.

 

I dont know if this explains the problem very well, but it is necessary to have just the customer information in my business, and have the sales manager manually type in the orders.

 

I'd like to be able to do two things with the contribution:

 

1.) Have the casing set for a Capital letter only on the first letter on import.

 

2.) Remove the organization line which is blank 95% of the time.

 

Any tips would be greatly appreciated.

Link to comment
Share on other sites

Jacob,

 

The qb_iif_sale.php file actually contains customers, orders, and products. The customers haven't been left out but have been combined with the orders and products because you really only need one file to do everything. Then you only have to import one file into Quickbooks. Maybe the file should be renamed to something more appropriate, but right now it is that way because this contribution has gone through many revisions. Hopefully I'll find another block of time soon to make more improvements (or maybe someone else will).

 

If you only want to import new customers, then you can manually comment out the parts in qb_iif_sale.php where it creates the orders and products part of the IIF file. You're right -- this should be easier, and when an admin interface is made for this contrib. then you should be able to easily select this.

 

But your question brings up another alternative -- wouldn't it be nice if it could append the product attribute to the product number? That way, you could have one product (with attributes) in osC, and multiple corresponding products in Quickbooks (since it doesn't support multiple attributes for one product). I could use this myself! Then you wouldn't have to make all the invoices by hand. Right now, due to a bug, the contrib is not importing the attributes at all! I'll put this on my list of things to add when I have a chance to work on it next!

 

As far as your other comments

1) The casing for the customer is whatever they typed into osC. I read a discussion on this a while back in the forum -- the conclusion was that because some surnames do start with a lower case letter or have an uppercase letter in the middle, you can't assume that all names should have the initial letter capitalized. For that reason, I at least don't plan on adding this to the contribution. But if you want your copy to do this, you just need to add a line in to process the variables for first and last name in the form:

$foo = ucfirst($foo);

It might make sense to convert the customer "name" that QB uses to identify a customer to all caps, just for consistency, since some customers insist on typing in all lower-case!

 

2) Make sure you have the latest version, the one I uploaded under the contribution name "Quickbooks IIF Creation w Tax Option." This is already modified to get rid of the blank line if the customer has no company or organization.

 

Thanks,

Adam

Link to comment
Share on other sites

If you only want the customer info with no order details and invoice just use one of the old versions and skip the sale_iif.php parts. The customer portion will work tax/no tax it does not matter since it is just the customer fields. HTH

Link to comment
Share on other sites

Would it be possible for someone to lend a helping hand in editing the original customer sales code so that there is no space for the company name, and adding the first letter caps code also?

 

I think many people would find this useful at this time, I just can't seem to make everything work perfectly... It keeps adding 't' as the rep.

Link to comment
Share on other sites

To get rid of the company name in the address lines find this in the qb_iif_customer.php file:

 

// Create the IIF line & write it to the file. If you changed the header line, you
// will also need to change these lines to match - read the QB IIF docs for more info
$line ="CUST\t$lastname $firstname\t$firstname $lastname\t$company\t$street\t$city, $state $pcode\t\t";
$line.="$firstname $lastname\t$company\t$street\t$city, $state $pcode\t\t$phone\t\t$fax\t";
$line.="$emailto\t\t$firstname $lastname\t\t$salesman\t$terms\t$taxable\t$creditlimit\t\t$rep\t$company\t\t$firstname\t";
$line.="$lastname\t\n";
fputs($fp,$line);

 

Change it to this:

 

  $line ="CUST\t$lastname $firstname\t$firstname $lastname\t$street\t$city, $state $pcode\t\t\t";
 $line.="$firstname $lastname\t$street\t$city, $state $pcode\t\t\t$phone\t\t$fax\t";
 $line.="$emailto\t\t$firstname $lastname\t\t$salesman\t$terms\t$taxable\t$creditlimit\t\t$rep\t$company\t\t$firstname\t";
 $line.="$lastname\t\n";
 fputs($fp,$line);

 

HTH

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