Jump to content
Latest News: (loading..)
adam5532

QBI Quickbooks Import

Recommended Posts

OK now I am bummed...

 

I was excited for this release mainly so I can support Coupons /Gift Certificates, COD fees all that other stuff - and YIKES!!!!! it doesnt... anyone got any ideas how to get this in? (Adam)?

 

 

Unless I missed something?????

 

C-


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 this masterpiece!

I would like to change the format of the customer's name as it is created in the .iif file.

Your way:

Doe-89

 

I would like

(89)Doe,Jane

 

How would I do this?


"There is no doubt about precisely when folks began racing each other in automobiles. It was the day they built the second automobile." - Richard Petty

Share this post


Link to post
Share on other sites
Adam,

Thanks for this masterpiece!

I would like to change the format of the customer's name as it is created in the .iif file.

Your way:

Doe-89

 

I would like

(89)Doe,Jane

 

How would I do this?

The customer account number ("name" in Quickbooks) is created in the qbi_engine_orders.php file and starts at 120

 

To do what you want above, replace lines 125 and 126 with the following:

 

$customers_lastname=preg_replace("/\W+/","",$myrowc["customers_lastname"]);

$customers_firstname=preg_replace("/\W+/","",$myrowc["customers_firstname"]);

$custname="(".$myrowo["customers_id"].")".substr($customers_lastname,0,10).",".substr($customers_firstname,0,10);

$custname=substr($custname,0,32);

 

The numbers 10 and 32 above are to restrict the maximum length, to prevent abnormally long account numbers. Even if you don't mind this, the 32 should be set to the maximum account number (name) length for Quickbooks, which I don't know off-hand at the moment.

 

The preg_replace part gets rid of bad stuff that could upset the formating of the iif file, so if anyone else makes their own custom changes, make sure that you clean the data as above.

 

If you want customers with a company name to have the same format, then also replace lines 122 and 123 with the same code above.

 

I will add an option in the config in a future version to provide different customer account number (name) formats. If people let me know what they would like, I will incorporate the most asked for ones.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
OK now I am bummed...

 

I was excited for this release mainly so I can support Coupons /Gift Certificates, COD fees all that other stuff - and YIKES!!!!! it doesnt... anyone got any ideas how to get this in? (Adam)?

Unless I missed something?????

 

C-

(Chris has gotten this to work now).

 

I will add coupons, gift certificates, COD, and low order fees, probably in the next revision. Currently these things will not work correctly.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

hi adam,

 

gr8 effort , its really gr8

 

i have got few questions

 

1. What is the format of orders .iif , what is the theory of TRNS, SPL, TRSID etc. so that we can manipulate it ourselves

 

2. Does the IIF file exported depends upon the "various company" types in quickbooks.

 

thanks

Edited by ankitvaid

Share this post


Link to post
Share on other sites

I have just uploaded v1.01.

 

The following issues have been addressed:

1.01 12/30/04

a) Adds a missing line in the database table installation to set the default for orders.qbi_imported from 2 back to zero.

b ) Adds more UPS and USPS shipping services to the qbi_shiposc table.

c) Minor html tag fix in qbi_functions.php item_menu.

d) Added explanation of configuration options to manual.

e) A few other very minor changes in qbi_config.php and in the qbi_config table defaults.

 

More significant improvements will come later. Mainly, I wanted to get this up because it fixes a bug ("a" above) that prevents proper functioning. If you have already downloaded version 1.00, you can just make the fix in "a", which I already have posted in this forum. The manual also has been expanded to explain most of the configuration options.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
hi adam,

 

gr8 effort , its really gr8

 

i have got few questions

 

1. What is the format of orders .iif , what is the theory of TRNS, SPL, TRSID etc. so that we can manipulate it ourselves

That's a big question. You can learn about the iif file format by going to the help section in Quickbooks and searching for iif. You can also get additional information at www.quickbooks.com support by searching for iif.

2. Does the IIF file exported depends upon the "various company"  types in quickbooks.

The types you are referring to I believe are just the default account set ups for various types of businesses and this would have no effect on QBI. The various special versions of Quickbooks Premier are a different thing -- I think it would still work fine, but I haven't tested it with Premier.

- Adam

Share this post


Link to post
Share on other sites

Hi All

 

Does this mod have any problems with case related to account and customer names?

 

What I've heard about some qb modules is that if the customer name is in the wrong case, then QB just makes a new account.

 

Instead, any import should not be case-sensitive.

 

Many thanks :)

Share this post


Link to post
Share on other sites

Adam,

 

Great Job, I have tested been testing it this morning on my test site and I'm very impressed with what you created. Now I'm working on matching products, it's going a bit slow scrolling through so many items. I would like to make the pull down list longer than 11 rows, where do I make this change in the code? I've been looking at the qbi_productsmatch.php file and can't find anything to change there. I would also like to pulldown column wider; where would I set this?

 

Thanks again for the great job.

 

Mark

Share this post


Link to post
Share on other sites

It the version 1.01 absolutely neccessary? Are any bugs in 1.0 that stop it from functioning? I just installed the first version last ngiht, and didn't know if I should go back to the newest version..

 

 

Thanks!

 

B.

Share this post


Link to post
Share on other sites

bamboo, is you dont want to download the latest version.

 

Just make these change:

After running the database installer qbi_install_db.php, go into your database manager (such as phpMyAdmin), go to the table "orders," and change the default for the field "qbi_imported" from 2 to 0 (zero),

Share this post


Link to post
Share on other sites

I was able to make the pull down wider in the match products page by editing function item_menu in the the qbi_functions.php file. Prior to making this field wider I wasn't able to match items because similar items all looked the same (information to the right was trunkated).

 

I still don't see where I can increase the number of items in the pulldown. I increased the number of items on the page to 20, but still only seeing 10 at a time in the pulldown. If anyone knows where to change this, I would appriciate hearing back.

 

Thanks,

Mark

Share this post


Link to post
Share on other sites

go to

 

http://www.domain.com/admin/qbi_config.php

 

Increase this field

Product rows dispayed:   20

 

 

this is working for me. It was initially set to 5 and i increased it to 20.

Share this post


Link to post
Share on other sites
I was able to make the pull down wider in the match products page by editing function item_menu in the the qbi_functions.php file.  Prior to making this field wider I wasn't able to match items because similar items all looked the same (information to the right was trunkated).

I will add an option for this in a future version. The reason for the length limit was to prevent the drop-down from being unusually wide so that you wouldn't have to constantly be horizontally scrolling.

I still don't see where I can increase the number of items in the pulldown.  I increased the number of items on the page to 20, but still only seeing 10 at a time in the pulldown.  If anyone knows where to change this, I would appriciate hearing back. 

 

Thanks,

Mark

This is an html issue, and there's no way that I'm aware of to control this. The only attribute that controls the number of lines is "size," but this turns the drop-down into a list box. The number of lines is controlled automatically by your browser, and will depend on your screen resolution and font size. On my screen at 1024 x 768 and normal text size, I get 20 rows in the drop-down menu.

Thanks,

- Adam

Share this post


Link to post
Share on other sites
Hi All

 

Does this mod have any problems with case related to account and customer names?

 

What I've heard about some qb modules is that if the customer name is in the wrong case, then QB just makes a new account.

 

Instead, any import should not be case-sensitive.

 

Many thanks :)

Right now the customer account (name) does not change case, but in the future I will be adding options in the config for different account naming methods, and possibly a method using code letters so that you can construct your own format in a text box without having to edit any of the html code.

 

If you want the account name in all uppercase, then add this in qbi_engine_orders.php after line 123 and 126.

$custname=strtoupper($custname);

 

But there should be no problem with case however. Once the customer creates an account in osC, they shouldn't be changing their last name or company name that is stored in their customer address book, so their customer account (name) will be the same for every order and won't create a duplicate in QB, regardless of the case they use. If they do change their last name or company name, then yes it will create a new account in QB. The only way to avoid this would be to only use their osC account number as the customer account (name) in QB. Personally, I find their last name (and the unique osC account number, so that two people named Joe Smith have unique accounts) a better system for finding customers than having to ask them for an arbitrary number, but I will make number only an option in the future.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites

I'm at 1680x1050 and only get the 11 rows in the pull down. So the next best solution is to have my wife to match the products. Problem fixed, just have to break the news to her... :)

 

Have you thought about adding something to clean up the name/phone/address before creating the iif file? I had a little routine I created to export users account to input to QB's, in it I included a clean up of the phone number (make into a 999-999-9999 format) and change names to upper/lower case (using the ucwords()). I'm going to add these to my installation of your QBI over the next day or two, I really hate the look of names and addresses in all lower case or caps.

Share this post


Link to post
Share on other sites
I'm at 1680x1050 and only get the 11 rows in the pull down.  So the next best solution is to have my wife to match the products.  Problem fixed, just have to break the news to her...  :)

 

Have you thought about adding something to clean up the name/phone/address before creating the iif file?  I had a little routine I created to export users account to input to QB's, in it I included a clean up of the phone number (make into a 999-999-9999 format) and change names to upper/lower case (using the ucwords()).  I'm going to add these to my installation of your QBI over the next day or two, I really hate the look of names and addresses in all lower case or caps.

The name and address are stripped of blank lines and line returns. The company name is also stripped of "none," "n/a," and other variants.

 

Then, before they are used in the account name, they are stripped of characters other than letters, spaces, and numbers.

 

The phone number is not presently altered. If that was formatted, it would have to accomodate the format of different countries, as well as phone extensions like "ext," but you could write a regular expression to do this (if you have one already, please send it to me!)

 

As far as ucwords, there was a whole discussion of whether this should be added to osC, and the feeling eventually was that it was a problem because sometimes names don't begin with an uppercase letter, especially last names.

 

But for the account number (name), yes this is a good idea to make it consistent. I will do some more work on the account naming for a future version. I knew that there would be many different ideas on how to do this! For those who know some php, it is fairly easy to modify it to your likings in the meantime.

 

Thanks,

Adam

Share this post


Link to post
Share on other sites
Hi there,

 

Just a quick question.? In this thread on the QB dev support forums they basically advise against using IIF:

 

http://idnforums.intuit.com/topic.asp?TOPIC_ID=3061ᶸ

 

Is there any reason you chose to go the IIF route rather than using the new sdk with qbXML?

 

Thanks,

 

Anthony

Hi Anthony,

 

1) Using the sdk would essentially mean building a whole application, so I anticipate this would be a much larger and time consuming project to do.

 

2) Many people that are using osC on a shared hosting plan do not have permissions to make a direct connection to the MySQL database. So, you'd still need a utility like QBI as a front-end to download the order data from osC onto your computer, even if you made a Windows program with the sdk. Then, you could use the Windows program to read the iif file and import the data into Quickbooks. In this case, I would add a configuration option in QBI to allow it to use a modified iif file format -- this would allow the file to include data, like credit card numbers, that iif doesn't support. You'd still have the advantage of the superior importing of the sdk, because you'd have the full error checking, more options and flexibility, and possibly more features like automatic shipping integration.

 

3) Despite the technical advantages of the sdk, QBI works really well! It has routines in it to sanitize the data to avoid any errors in creating the iif file. If any errors are discovered by users, I will fix them as soon as possible!

 

4) Intuit has said that they will drop iif from QB at some time in the future, but as far as I know QB 2005 still has iif. When it is dropped, then the sdk Windows program will definately be needed.

 

Thanks,

Adam

Edited by Johnson

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.

 

I did have a problem though. I did my first import and QB2004 had a problem with the taxes. I think I didn't configure the fields right for that. I wasn't sure what to do because the instruction manual doesn't say much about it. I also want to make sure that I have my item classes and other item info correct.

 

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.

 

Great job and thanks again!

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"

 

The this scree pops up:

 

"Error on line 14: cannot record invalid transaction"

 

The the progress bar finishes, as if the orders were still imported, but I cant find them anywhere.

 

The iif file consisted of 2 orders: 1 credit card order from a customer and 1 check/money order that I created to practice. I have the option set to import as sales receipts, so the first order should come in as a receipt and the 2nd order as an invoice.

 

Relevant settings in my config screen are as follows:

 

Delete credit card number: [not checked]

Product rows dispayed:

 

Customer type: [WEB]

Use state codes: [checked]

Include local country: [not checked]

 

Invoices:

Invoice account: [Accounts receivable]

Sales Receipt account: [undeposited funds]

Invoice to print: [checked]

Use Sales Receipts for paid orders: [checked]

Invoice terms: [Credit Card]

Invoice rep: ? -- what is this??

Invoice fob: [MyFOB point *not shown here]

Include customer comments: [checked]

Customer message: [Thanks for business!]

Invoice memo: *left blank*

 

Items:

Item account: [sales: Merchandise]

Item asset account: *left blank*

Item class: [inventory Part]

COG account: [Cost of Goods Sold]

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

 

Shipping: *I'm positive the shipping fields are correct

 

Taxes:

Tax turned on: [checked]

Tax name: [GA]

Tax agency: [GA Sales Tax]

Tax class: [sales Tax Item]

Tax rate: [7%]

 

Payments:

Import payments with invoice: [checked]

Payment memo: [online cc payment]

 

--------------------------------------------------

Thanks for your help!

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"

 

The this scree pops up:

 

"Error on line 14: cannot record invalid transaction"

 

The the progress bar finishes, as if the orders were still imported, but I cant find them anywhere.

 

The iif file consisted of 2 orders: 1 credit card order from a customer and 1 check/money order that I created to practice.  I have the option set to import as sales receipts, so the first order should come in as a receipt and the 2nd order as an invoice.

 

Relevant settings in my config screen are as follows:

 

Delete credit card number:    [not checked]

Product rows dispayed: 

     

Customer type:  [WEB]

Use state codes: [checked]

Include local country:  [not checked]

   

Invoices: 

Invoice account:  [Accounts receivable]

Sales Receipt account:      [undeposited funds]

Invoice to print:    [checked]

Use Sales Receipts for paid orders:    [checked]

Invoice terms:    [Credit Card]

Invoice rep:      ? -- what is this??

Invoice fob:      [MyFOB point  *not shown here]

Include customer comments: [checked]

Customer message:  [Thanks for business!]

Invoice memo:  *left blank*

   

Items: 

Item account:  [sales: Merchandise]

Item asset account: *left blank*

Item class:    [inventory Part]

COG account:  [Cost of Goods Sold]

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

   

Shipping: *I'm positive the shipping fields are correct

   

Taxes: 

Tax turned on:  [checked]

  Tax name:  [GA]

  Tax agency:  [GA Sales Tax]

  Tax class:      [sales Tax Item]

  Tax rate:  [7%]

   

Payments: 

Import payments with invoice:  [checked]

Payment memo:  [online cc payment]

 

--------------------------------------------------

Thanks for your help!

 

 

do you have anything else like, coupon, GV or COD fee etc?

 

The best thing to do is open in like Excel and look at the match make sure it matches. If your tax is wrong (like you thought) it wont work.

 

Look ok to me - if it is 7%

 

By the way REP is sales rep I believe.


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

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

×