Jump to content



Photo
* * * * * 4 votes

QBI Quickbooks Import


  • Please log in to reply
1420 replies to this topic

#21   knipper

knipper
  • Members
  • 130 posts
  • Real Name:Steve

Posted 29 December 2004 - 21:09

Ahhhh...

Thanks for the explaination. That clears things up for me. ( at least for now! /thumbsup.gif' class='bbc_emoticon' alt=':thumbsup:' /> ) Great job, I know you've made many people happy with this contribution!

Yes. Inventory is tracked normally in Quickbooks for sales of Inventory items. It doesn't matter if the invoice is paid or not.

If you have inventory turned on in osC, you'll need to set the stock level to match what's in Quickbooks. So, if stock is 50 units in osC and QB, when you make a sale of two pieces and import it with QBI, then both will show 48 pieces. But if the count starts with 40 in osC and 50 in Quickbooks, then you'll end up with 38 and 48. So, the number of units sold stays on track, but you need to make sure that the starting inventory count is the same in both osC and QBI, because QBI does not do any transfer of the total inventory information.

Just to clarify the payments options (I will explain all the options in a future revision of the manual, but a lot of people were clamouring for me to get version 1.00 released, so I didn't get this part explained fully yet).

Import payments with Invoice: If you do credit card sales with an online gateway, like Authorize.net, turn this on. If you do credit card sales, but process the credit card manually later, then turn it off and record your payments in Quickbooks later when you process the card or use the QB Merchant account.

Import CC payments as Sales Receipt: If you use a gateway, check this and any paid CC sales will import as sales receipts instead of invoices and payments. This is better, because you don't have to manually check off which invoice is paid. Note, if you choose this feature, then "Import Payments with Invoice" is over-ridden.
No questions are dumb.

Thanks,
Adam

<{POST_SNAPBACK}>


Steve K AKA - Knipper -

#22   clarocque

clarocque
  • Members
  • 559 posts
  • Real Name:Chris
  • Location:VT/USA

Posted 29 December 2004 - 21:43

Am I missing something obvious? My orders do not seem tto be chnging the QBI status, by default they are setting to 2 and showing in previous orders. If I manually set it to 1 in DB then it shows in Previous but does not update - I must be missing something????


C-
osC Contributions I have published.

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

#23   clarocque

clarocque
  • Members
  • 559 posts
  • Real Name:Chris
  • Location:VT/USA

Posted 29 December 2004 - 21:45

Alos,

Is anyone else having problems save the iif file w/ Firefox?
osC Contributions I have published.

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

#24   adam5532

adam5532
  • Members
  • 678 posts
  • Real Name:Adam Liberman
  • Location:California, USA

Posted 29 December 2004 - 22:10

Am I missing something obvious?  My orders do not seem tto be chnging the QBI status, by default they are setting to 2 and showing in previous orders.  If I manually set it to 1 in DB then it shows in Previous but does not update - I must be missing something????
C-

<{POST_SNAPBACK}>


Ok, slight bug discovered!

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),

This will be corrected in the next revision.

Thanks,
Adam

#25   adam5532

adam5532
  • Members
  • 678 posts
  • Real Name:Adam Liberman
  • Location:California, USA

Posted 29 December 2004 - 22:14

Alos,

Is anyone else having problems save the iif file w/ Firefox?

<{POST_SNAPBACK}>


I've tested it with both IE 6.0 and Firefox 1.0. In fact, I'm using Firefox now as my default browser. I haven't had problems myself, but let me know what kind of problem you are having.

Thanks,
Adam

#26   Farrukh

Farrukh
  • Members
  • 480 posts
  • Real Name:Farrukh Saeed

Posted 29 December 2004 - 23:24

I am also using Firefox as default and no problems /smile.gif' class='bbc_emoticon' alt=':)' />

#27   clarocque

clarocque
  • Members
  • 559 posts
  • Real Name:Chris
  • Location:VT/USA

Posted 30 December 2004 - 01:07

I am also using Firefox as default and no problems /smile.gif' class='bbc_emoticon' alt=':)' />

<{POST_SNAPBACK}>



OK thanks for the feedback - it is clear I didnt sleep enough - local setting - all set now /wink.gif' class='bbc_emoticon' alt=';)' />
osC Contributions I have published.

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

#28   clarocque

clarocque
  • Members
  • 559 posts
  • Real Name:Chris
  • Location:VT/USA

Posted 30 December 2004 - 01:43

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!

#29   sgreiner

sgreiner
  • Members
  • 77 posts
  • Real Name:Scott

Posted 30 December 2004 - 01:44

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

#30   adam5532

adam5532
  • Members
  • 678 posts
  • Real Name:Adam Liberman
  • Location:California, USA

Posted 30 December 2004 - 02:55

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?

<{POST_SNAPBACK}>

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

#31   adam5532

adam5532
  • Members
  • 678 posts
  • Real Name:Adam Liberman
  • Location:California, USA

Posted 30 December 2004 - 02:59

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-

<{POST_SNAPBACK}>

(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

#32   ankitvaid

ankitvaid
  • Members
  • 1 posts
  • Real Name:Ankit Vaid

Posted 30 December 2004 - 08:25

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, 30 December 2004 - 08:26.


#33   adam5532

adam5532
  • Members
  • 678 posts
  • Real Name:Adam Liberman
  • Location:California, USA

Posted 30 December 2004 - 11:39

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

#34   adam5532

adam5532
  • Members
  • 678 posts
  • Real Name:Adam Liberman
  • Location:California, USA

Posted 30 December 2004 - 11:46

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

#35   DogTags

DogTags
  • Members
  • 30 posts
  • Real Name:DogTags

Posted 30 December 2004 - 14:13

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 /smile.gif' class='bbc_emoticon' alt=':)' />

#36   mpiscopo

mpiscopo
  • Members
  • 413 posts
  • Real Name:Mark
  • Location:Rockville, MD

Posted 30 December 2004 - 15:36

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

#37   bamboo

bamboo
  • Members
  • 17 posts
  • Real Name:Brian Curcio

Posted 30 December 2004 - 16:00

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.

#38   Farrukh

Farrukh
  • Members
  • 480 posts
  • Real Name:Farrukh Saeed

Posted 30 December 2004 - 16:20

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),


#39   1quicksi

1quicksi
  • Members
  • 609 posts
  • Real Name:Steven
  • Location:Connecticut

Posted 30 December 2004 - 16:21

Darn, beat me by 1 minute...

Edited by 1quicksi, 30 December 2004 - 16:22.


#40   mpiscopo

mpiscopo
  • Members
  • 413 posts
  • Real Name:Mark
  • Location:Rockville, MD

Posted 30 December 2004 - 17:39

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