Quickbooks Import QBI Configuration and User Manual V2.10 May 8, 2005 © 2005 by Adam Liberman The QBI manual is split into two documents, the Installation Manual and the Configuration and User Manual. Please read the Installation Manual to install QBI, and then proceed to this manual to complete the set up before using QBI. Contents: 1 Introduction 2 License 3 Support 4 Donations 5 Purpose 6 Features 7 Security 8 Revision History 9 Upgrading 10 Configuration 11 Importing Quickbooks Lists to QBI 12 Setting Up Products and Shipping 13 Understanding Matching 14 Matching shopping cart to Quickbooks 15 Preparing Quickbooks Using the Product Import Utility 16 Importing Customers 17 Using QBI to Import New Orders 18 Importing the Previous Batch of Orders 19 Future Plans 20 Known Limitations 21 Known Bugs 22 FAQ‟s 23 Troubleshooting 1) Introduction: Please read the Installation Manual and the Config and User Manual (this document) entirely one time through before starting the installation. QBI is very easy to use and very easy to install. However, due to the complexities of accounting systems, there is a bit of configuration and preparation that needs to be done for it to work smoothly. Note that if you have installed a previous iif file creation contribution, you do not need to remove it for QBI to work properly. It is suggested that you leave the previous contribution in place until you are certain that QBI has been installed and is working properly. 2) License: Quickbooks Import QBI is copyrighted by Adam Liberman and released under the GNU General Public License. By downloading and/or using this software, you must agree to the following terms: Quickbooks Import QBI is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. Quickbooks Import QBI is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with Quickbooks Import QBI; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 3) Support: For support, please post a message in the appropriate shopping cart forum so all users can benefit. You may also send a private message to me through user id adam5532. 4) Donations: It has taken many hours (over 250 so far) to develop this contribution, and your donation helps make it viable for me to support it, continue to improve it, and add features. If you appreciate this contribution, it saves you time, and it helps your business to be more successful, feel welcome to express your gratitude with a donation of any size (nothing is too small) using one of the following methods: Paypal (email firstname.lastname@example.org) Credit card (contact me) A gift from my Amazon.com wish list (Adam Liberman, California USA). 5) Purpose: QBI allows you to import sales invoices, customers, products, and payments from your shopping cart directly into Quickbooks. The standard import includes new invoices and new customers, with options available to configure it as needed. Sales tax is supported, as well as shopping cart product attributes, Quickbooks item groups, and conversion between shopping cart and Quickbooks product names. 6) Features: a) Imports new invoices and new customers in one iif file b) Imports payments with invoices or as sales receipts c) Can go back and reimport previous invoices d) Can import all customers or products e) Converts shopping cart products to Quickbooks item groups f) Converts shopping cart products with attributes to separate Quickbooks items g) Converts shopping cart products with attributes to Quickbooks sub-items h) Converts any shopping cart product to a Quickbooks item with a different name i) Converts shopping cart shipping methods to Quickbooks shipping names j) Converts shopping cart payment methods to Quickbooks payment types. k) Configurable for Quickbooks with sales tax turned on or off l) Creates unique Quickbooks customer names m) Supports discounts, coupons, and fees n) Supports multiple payment methods, including Authorize.net and PayPal o) Almost no alteration of shopping cart files p) Multilingual interface q) Invoice description language options r) Converts essential data to default store language s) No file editing needed – all settings done in a graphical interface t) Configuration saved in a database table making upgrades easy u) Table creation is automatic after installation v) Table updates are automatic when new versions are installed w) Sanitizes data to avoid iif file errors x) Strips blank lines and line feeds in addresses for proper importing to different Quickbooks versions y) Shows country name only for foreign orders (option) z) Changes state/province names to postal codes (option) aa) Adds customer comments to invoices (option) bb) Updates status and sends emails to customers (option) cc) Deletes credit card numbers (option) dd) Support for discounts, coupons, and fees ee) Automatic detection of installed payment, shipping, and discount modules in shopping cart ff) Automatic database installation and upgrading gg) Support for products with multiple attributes (pass-through only, no matching yet) hh) Option to only import orders with a specific status (to support PayPal IPN) ii) Payment method matching to Quickbooks jj) Indication of which payment methods are prepaid and which aren‟t kk) User configurable customer numbers ll) User configurable Invoice and PO numbers 7) Security: Because the iif files contain customer information, make sure that the qbi_export and qbi_import directories that you create below are password protected so that they can not be viewed. Ideally, your entire catalog/admin directory and all directories and files below it should be password protected. Also, it is recommended that you access the Admin area of the shopping cart in secure mode (SSL) to protect data during upload and download. 8) Revision history: 2.10 May 8, 2005: New Features: 1. Product export can be set as inventory, non-inventory, or services. 2. Product export supports up to 2 levels of attributes. 3. Products export includes inventory quantity. 4. Added products export for all, previous, or new products. 5. Option added to not export inactive products. 6. Non-inventory and services matching added. 7. Discounts and other charges can now be matched. 8. Discounts and other charges can now be included in Quickbooks groups. 9. Default item option. 10. Setup screens now give name of iif file found. 11. Added customer's fax number and option to import as Alt Phone. 12. Added logging option. 13. Moved menu system to separate file. 14. Added css classes and improved interface styling. 15. Converted some parts to object oriented design. Fixes: 16. Discounts were being added instead of subtracted. 17. Rounding errors corrected that occasionally caused out-of-balance condition. 18. Top level of a product with attributes would import as a service. 19. Eliminated "not an array" errors on set-up pages. 20. If setup fails to find items in the iif, a failure instead of a success message is now given. 21. Apostrophes in product attribute values no longer cause an SQL error. 22. "All Previous Orders" was erroneously including "Previous Batch" in the count. 23. Hidden Quickbooks items are no longer imported. 24. Improved handling of Quickbooks groups. 25. Customer iif was repeating headers for each customer. 2.02b Feb 24, 2005: 1. Fixed command in SQL file that was deleting the qbi_disc table when upgrading. 2.02 Jan 27, 2005: 1. Added missing semicolon in documentation for qbi_paycart table definition. 2. Removed incorrect quotations in SQL file for v1 to v2 upgrade. 3. Array error for product model and name was generated under some conditions, fixed. 4. Incorrect field name for a discount table field fixed. 5. Negative sign was missing on price for non-group items. 2.01 Jan 24, 2005: 1. Fixed problem in database installation function. 2.00 Jan 23, 2005: New Features: 1. Support for discounts, coupons, and fees 2. Automatic detection of installed payment, shipping, and discount modules in shopping cart 3. Automatic database installation and upgrading 4. Support for products with multiple attributes (pass-through only, no matching yet) 5. Option to only import orders with a specific status (to support PayPal IPN) 6. Payment method matching to Quickbooks 7. Indicate which payment methods are prepaid and which aren‟t 8. User configurable customer numbers 9. User configurable Invoice and PO numbers 10. Adjustable products dropdown width 11. Create and Utilities pages combined 12. New menu system 13. Added blank line before shipping charges on invoice 14. Confirmation message when updating shipping and product matches 15. Option to maintain proper address label creation with ShipRush 16. Iif creation 5 – 10 times faster 17. Simplified payment import options 18. Numerous new and clearer configuration options Fixes: 19. All customers were sent a status email when importing old orders if “send email” was checked 20. The last setup iif input file would be processed instead of the first that was found 21. Product descriptions could be blank in some configurations 22. Setting customer limit to zero would set limit to $0 instead of no limit 23. Corrected directory names in manual from qbi_import/qbi_export to qbi_input/qbi_output, added permissions 24. Products match would go back page one after updating instead of staying on the same page 25. Previous and Next weren‟t translating on matching pages 26. A few other text items weren‟t translating 27. Expanded manual with more information and explanations of all options 28. General code cleanup and improvement, moved all page headers and footers into includes 1.01 Dec 30, 2004: 1. Added a missing line in the database table installation to reset the default for orders.qbi_imported from 2 back to zero. 2. Added more UPS and USPS shipping services to the qbi_shipcart table. 3. Minor html tag fix in qbi_functions.php item_menu. 4. Added explanation of configuration options to manual. 5. A few other very minor changes in qbi_config.php and in the qbi_config table defaults. 1.00 Dec 29, 2004: 1. Original version. 9) Upgrading: a) No settings or data are stored in any of the QBI files. Everything is in the database. b) However, please do a backup of your database before proceeding. c) Remove no longer used files. See the separate upgrade document for further details. d) Add definitions for new tables (see the upgrade document). e) Replace all the older files with the new ones. f) When you start QBI for the first time after an upgrade, you will be prompted to upgrade the database tables. g) Click the button to do update the tables automatically. h) Then, follow the link to the configuration page. i) Check that your settings are still correct. Also, the upgrade may have added additional options to set. j) Note that even if you don‟t change any configuration settings, you have to click “Submit” to complete the upgrade. 10) Configuration: a) After installing the database tables, you will be prompted to continue to the configuration. b) Check and update all the configuration options explained below as needed for your store. Note that various Quickbooks account names are required and must match exactly what you already use in Quickbooks. c) You must click “Submit” to continue, even if you did not make any changes. Option Default Description Quickbooks Import Options Quickbooks version 2003 and up Set this correctly to determine the maximum number of address lines. 2003 and up is 5 lines, earlier versions are 3 lines. Download iif file Checked This should normally be checked. If it is unchecked, the iif file will be created but it will remain in the qbi_output directory and you will not be prompted to download it. Product rows displayed 5 In the Match Products screen, each product or product/attribute combination will have a drop-down menu that contains all your items and groups from Quickbooks. If you have 1,000 items in Quickbooks, and display 5 rows, you would have 5,000 lines in the dropdown menus. The row limit is to prevent the page from becoming excessively large and downloading too slowly. Products dropdown Name Determines if the Quickbooks item Name or Description appears menu sort order first in the dropdown. The list will be alphabetized by what appears first. Products dropdown 48 Determines the width of the dropdown. A shorter width will width prevent a horizontal scroll-bar from appearing in the browser. Use a longer width if your product descriptions are long and you need to see more text to distinguish them. Create log file Unchecked When checked, QBI will create a log file every time an iif file is downloaded. This file includes all the configuration settings, additional diagnostic information, and any errors encountered processing each order. The errorlog.txt file is located in the qbi_output directory and is overwritten each time. This file will help you in troubleshooting, and may be requested when you seek technical support. Orders Import orders with Pending Setting to “Pending” will only import orders marked as Pending. status This will prevent import of orders that have been placed but where payment has not been completed. This can occur with the PayPal IPN payment module, among others. Setting to “(any)” will import all new orders without regard to payment status. If you are using PayPal or another payment module where payment is done outside of your store, you should not use “(any)” because you will not be able to distinguish between paid and unpaid orders. Update status Unchecked If this is checked, then the following three options become active. Change CC order status Processing When you create the iif file from new orders, the status for all to credit card orders will be changed to this setting. If you ship the same day, you may want all orders to update to shipped. Change check/mo status Processing When you create the iif file from new orders, the status for all to check / money order orders will be changed to this setting. Because you will have to wait to receive payment, you may wish these orders to update to pending. Send status email Unchecked When you create the iif file from new orders, and the status has been changed, an email will be sent to the customer. This email is nearly the same as the one the shopping cart sends when you manually update an order, but does not include extra comments. Delete credit card Unchecked Select this option if you use a payment gateway and the payment number module leaves credit card numbers in the shopping cart database. This is an added security measure. It is not necessary to check this if using Authorize.net Consolidated. If you retrieve credit card numbers from the admin panel and charge each one individually, then do not check this box or the numbers will be deleted as soon as the order is placed and before you are able to retrieve them. Customers Customer number %C10W-%I The customer account number format for any customer that has (business) a company name. You can configure the customer account number (called “name” in Quickbooks) any way you desire by constructing your own template. As an example, the default would create an account number like this: AcmeProduc-4872. 1) It is strongly suggested that you include the shopping cart customer number as part of your customer account number, as this number is always unique to each customer. If you don‟t, and you have two customers called Acme Products, the second customer will overwrite all the customer data for the first in Quickbooks, as Quickbooks will think they are the same customer. 2) Once you establish a format that you like, try not to change it, because if the same customer orders again, then Quickbooks will create a second account (e.i. AcmeProduc-4872 and AcmeProduc – 4872 are different customers to Quickbooks). 3) If you do not use individual customers in Quickbooks, and all your sales are under the same customer name, just type in the customer name without any of the special content codes. Content Codes: All content codes must start with % and contain 5 characters, except for %I which only has 2. %Xnnx The first letter indicates what data to use. C = Company F = First name L = Last name I = shopping cart customer number %xNNx (C, F, and L only) The maximum number of characters. If less than 10, be sure to add a leading zero, e.i. 08. %xnnX (C, F, and L only) The format: U = All uppercase L = All lowercase W = Capitalize first letter of each word (spaces are removed after capitalization) M = Mixed, leave as is Other characters: Any other characters or spaces will be printed exactly as- is. For example, the “-” in the default produces the dash in AcmeProduc-4872 Customer number %L10W-%I The customer account number format for any customer that does (residence) not have a company name. See format above. An example of the default would be Smith-593. Customer limit 0 The $ limit that you want to enter in Quickbooks for each new customer. This appears in the “payment info” tab in Quickbooks. If you do not want a limit, set to zero. Customer type The customer type that you want to enter in Quickbooks for each new customer. This appears in the “additional info” tab in Quickbooks. This should match a type already set up in Quickbooks. You may leave this blank if you don‟t use customer types. Use state codes Checked Changes state and province names in the customer‟s addresses to postal codes (ie. California changes to CA). Proper formatting of mailing addresses in the US requires the use of state codes instead of their names. The shopping cart has some problems with this, and sometimes puts in the names instead of the codes. Include local country Unchecked If checked, the country is always included in the address. If unchecked, the country is removed if it is the same as the country where the store is located. Include company and Checked Uncheck this if you do not wish the Company and Contact contact Name fields to be filled in Quickbooks. Invoices Invoice Account Accounts Must match Quickbooks, normally Accounts Receivable. Receivable Sales Receipt Account Undeposited Must match Quickbooks, normally Undeposited Funds. Funds Invoice number %I The format for the Invoice number. %I represents the shopping cart order number. Any other characters, including spaces, will print as-is. For example, the format “RR %I-web” would print “RR 9382-web” for order number 9382. Invoice/Sales Receipt %I The format for the „PO Number‟ field on both Invoices and 'PO Number' Sales Receipts. If you use the PO Number, and leave Invoice number blank, Quickbooks will not reset your Invoice numbers. This may be desirable if you have both on-line and off-line sales. On Sales Receipts, Quickbooks does not import the Sales Receipt number from shopping cart, so the PO Number field is a convenient way to have the shopping cart order number show on your Sales Receipts. In Quickbooks, you can search for PO Numbers using advanced search, and in the templates you can rename PO number to “shopping cart Order No. ” if you wish. Note that the default label for “PO Number” is “Check No.” on Sales Receipts (confusing?). %I represents the shopping cart order number. Any other characters, including spaces, will print as-is. Invoice to print Checked All imported orders will be marked “to be printed.” This makes it convenient to print out all the new order invoices or sales receipts as a batch in Quickbooks. Invoice terms paid The Terms name you use in Quickbooks for credit card or other online purchases already paid for online (such as PayPal). Follows the payment Type setting made in Setup:Payments. This should match a Terms name you have already set up in Quickbooks. Invoice terms not The Terms name you use in Quickbooks for check/money order prepaid and other purchases not already paid online. Follows the payment Type setting made in Setup:Payments. This should match a Terms name you have already set up in Quickbooks. Class The invoice class. Not needed if you don‟t use classes for invoices. Rep The Rep field on the Invoice. Must match a Rep that you have already set up in Quickbooks. You might choose to use “Web” for all website orders. This may be left blank if you don‟t use Reps or don‟t wish to assign one to online orders. FOB This is the text that will appear in the FOB field on the Invoice in Quickbooks. It can be any text and does not have to match something already in Quickbooks. It may be left blank if you don‟t use it. Include customer Checked This adds the customer‟s order comments as a line on your comments invoice after all the sales items. This is convenient because you will not have to individually open invoices in the shopping cart to see the comments. Customer message This appears in the customer message field on the invoices in Quickbooks. If you already have a message in Quickbooks, copy the text exactly into this field. Invoice memo This appears in the memo field on the invoices in Quickbooks. Import fax as Alt Phone Unchecked When checked, the customer's fax number in the shopping cart will be imported to the Quickbooks Alt Phone field instead of the fax field. This is useful if you use the fax field for a cellular or alternate number instead of for fax. Items Item income account The income account you normally use with items. This account will be assigned to all products that you haven‟t matched in the matching table. Shopping cart products that are matched to a Quickbooks item will use the income account for that item. Asset account Inventory Asset The inventory asset account you normally use with items. The defaults are the usually settings in Quickbooks. COGS account Cost of Goods The COGS account you normally use with items. The defaults Sold are the usually settings in Quickbooks. Class The class for each item on the invoice. Not needed if you don‟t use classes invoice items. Description language Default If you have a multilingual enabled store, and you choose "Customer's," the item description (name in shopping cart) flows through to the invoice in whatever language the customer had the store set to when they ordered. That lets you print your invoices with descriptions that the customer can read them in their own language. If you set it to "Default," then the descriptions on the invoices are always converted into the shopping cart default language. All other fields, such as payment methods, are always converted to the store default language so that they will match correctly in Quickbooks. Note that this option only has effect if you have not "matched" the item. If it is matched, then the description is always whatever the sales description is for the item in Quickbooks, since Quickbooks descriptions aren't multilingual. Match types Inventory Determines which QB item types (inventory, non-inventory, and checked service) will be displayed in the matching drop-down menu. If none are selected, then all types will be displayed. Use default item Unchecked When unchecked, any items that are not matched will flow through into QB (make sure that these items already exist in QB with the exact same name). When checked, any items that are not matched will be replaced with the default name entered in the box below (make sure this item already exists in QB with the same exact name). This is useful if you do not track inventory and/or your store item count exceeds the maximum allowed by QB and you wish all store sale items to import as one non- inventory item. Note that the description will always flow through, so that you can still see on the invoice what products where purchased. Default name The QB item name that will be used when "Use default item" above is checked. Export type Inventory In the Products Export, sets the Quickbooks item type that osC products will be exported as. Only export active items Unchecked When checked, inactive items in the shopping cart will not be included in the product export to Quickbooks. Shipping Name The Name for the shipping charge item in Quickbooks. Must exactly match what is in Quickbooks. This is usually an “Other Charge” item. Description The Description for the shipping charge item in Quickbooks. Must exactly match what is in Quickbooks for the shipping item named above. Account The Description for the shipping charge item in Quickbooks. Must exactly match what is in Quickbooks for the shipping item named above. Class The shipping class. Not needed if you don‟t use classes for shipping charges. Shipping taxable Unchecked If shipping is taxed in both Quickbooks and the shopping cart, then check here. The settings for Quickbooks and the shopping cart must match. Taxes Tax turned on Unchecked This must match your setting in Quickbooks. Tax name The Tax Name for the “Sales tax item” in Quickbooks. Must exactly match the name in Quickbooks. Description The Description for the “Sales tax item” in Quickbooks. Must exactly match the description in Quickbooks for the “Sales tax item” named above. Tax agency The Tax Agency for the “Sales tax item” in Quickbooks. Must exactly match the Tax Agency in Quickbooks for the “Sales tax item” named above. Tax rate 0 The Tax Rate for the “Sales tax item” in Quickbooks. Must exactly match the Tax Rate in Quickbooks for the “Sales tax item” named above. Payments Import payments No Applies only to orders that have been paid with a credit card. All orders that are to be paid by check or money order will import as Invoices and are not affected by this option. No: If you do not use an online payment gateway, and you will be processing the credit card number later, choose this option. You will want to enter your payments later in Quickbooks. As Invoice and Payment: For orders already paid online through a gateway such as Authorize.net. The order will import as an Invoice, and the payment will import as a Payment. Note, however, that Quickbooks does not automatically associate the payment with the Invoice. You will need to review the payments in the Receive Payments part of the Customer Navigator in Quickbooks and check off each paid Invoice. As Sales Receipt: For orders already paid online through a gateway such as Authorize.net. The order will import as a paid Sales Receipt, and no further steps are necessary. Payment memo If “As Invoice and Payment” is selected above, this will be the text that appears in the Memo field on each Payment. 11) Importing Your Quickbooks Lists to QBI: a) Open Quickbooks. b) Go to File|Export|Lists to iif Files (may vary slightly with the version). c) Select “Item List,” “Shipping Method List,” and “Payment Method List.” d) Name the file lists.iif. e) Alternately, you can separately save items to items.iif, shipping methods to ship.iif, and payments to payments.iif. f) Upload the file or files to the admin/qbi_import directory. g) Continue following the instructions in the next section for setting up products, discounts/fees, shipping, and payments. 12) Setting Up Products, Shipping, Discounts/Fees, and Payments: a) Products i) Go to the QBI menu item “Set up Products.” ii) If you have properly uploaded the items.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into QBI. The items.iif file will have priority if both are found. iii) Click “Import iif Items File.” iv) Your Quickbooks items (products) will import and be listed on the screen. b) Shipping i) Now go to “Set up Shipping.” ii) If you have properly uploaded the ship.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into QBI. The ship.iif file will have priority if both are found. iii) Click “Import iif Shipping Methods File.” iv) Your Quickbooks shipping methods will import and be listed on the screen. v) If you later find that you need to add, change, or delete items or shipping methods in Quickbooks, you may import an updated iif file to QBI at any time. The lists will be updated, and any matches previously made (explained in the section below) will be preserved. c) Discounts/Fees i) Now go to “Set up Discounts/Fees.” ii) If you have properly uploaded the items.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into QBI. The items.iif file will have priority if both are found. iii) Click “Import iif Discounts/Fees File.” iv) Your Quickbooks discounts and fees will import and be listed on the screen. v) If you later find that you need to add, change, or delete items or shipping methods in Quickbooks, you may import an updated iif file to QBI at any time. The lists will be updated, and any matches previously made (explained in the section below) will be preserved. d) Payments i) Now go to “Set up Payments.” ii) If you have properly uploaded the payment.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into QBI. The payment.iif file will have priority if both are found. iii) Click “Import iif Payment Methods File.” iv) Your Quickbooks payment methods will import and be listed on the screen. At the same time, QBI will automatically detect all your shopping cart payment modules and will add or update this data as well in the database. v) Each payment method will have a select box with the options “Not Paid” and “Pre-Paid.” Methods that are paid on-line, that you may wish to import as Invoices/Payments or Sales Receipts, should be changed to “Pre-Paid.” Click on “Update Types” to save your changes. vi) If you later find that you need to add, change, or delete items or shipping methods in Quickbooks, you may import an updated iif file to QBI at any time. The lists will be updated, and any matches previously made (explained in the section below) will be preserved. 13) Understanding Matching: a) In the next section after this, you will match your shopping cart and Quickbooks items. Read this section to understand how this procedure works before proceeding to the next. Matching products, discounts/fees, shipping methods, and payment methods are necessary for most stores. b) Quickbooks can not create items on the fly from shopping cart orders – the items in the iif file you import into Quickbooks must be identical to items that already exists in Quickbooks. To give you greatly enhanced flexibility, QBI has matching tables that allow it to change the name of any shopping cart product or product/attribute combination to any Quickbooks item or item group. c) Every shopping cart product and product/attribute combination must either exist in Quickbooks, or be matched in the matching tables. To set this up, you can use the Matching utility and/or the Product Import utility as described below. If you already have shopping cart and Quickbooks product names that match identically, then you do not have to do either. d) The item description that appears on the Quickbooks invoice does not have to match. In fact, one QBI configuration item gives you the option to leave the description in the customer‟s native language (for stores where customers may use a variety of different languages when ordering). e) Unless they are matched to something else in the matching tables, shopping cart products with attributes will appear as Quickbooks items/subitems when imported. For example, the shopping cart product “Running Shoe Size 10” will import as “Running Shoe:10”. f) Shopping cart products with multiple attributes can not be matched at this time. But, they can be imported and will appear as a Quickbooks item:subitem:subitem… . You must make sure that the item and subitems already exist in Quickbooks before importing these orders. g) Shipping methods also need to be matched. As the shopping cart‟s shipping entries are much too long for Quickbooks, and include the package weight, they need to be parsed and converted to Quickbooks shipping methods. If a shopping cart method is not in the matching table, a default type of “Method not set” will appear on the invoice. h) Note that the terminology used by the shopping cart and Quickbooks differs as follows (shopping cart/Quickbooks): Things for sale: products/items Product code: model/name Product name: name/description Paragraph describing the product: description/(n/a) 14) Matching shopping cart to Quickbooks: a) In QBI, select the menu item “Match” and the submenu for Products, Discounts/Fees, Shipping, or Payments as desired. b) You only need to match products where the product code will not be exactly the same in both the shopping cart and Quickbooks (shopping cart model/Quickbooks name). c) All shopping cart shipping methods must be matched to Quickbooks shipping items, or else your invoices will not show the shipping method chosen by the customer. d) Note that because some contributed shipping modules do not properly include the shopping cart language files which QBI scans to find all the shipping methods, some shipping methods may be missing or incorrectly worded. In this case, you may need to either make corrections in the shipping module language files, or manually add missing shipping methods in the qbi_shipcart database. e) If you add or delete any products, discounts/fees, shipping methods, or payment methods in Quickbooks, you can upload a new iif file and load it into QBI (the match screens will prompt you if a file with the correct name is found in the qbi_import directory). Only the changes, deletions, and additions will be made and your previous matches will be preserved. f) The match utility creates a drop-down menu for each shopping cart store product that lists every Quickbooks item and item group. If you have thousands of items in your store, it can take some time to load the page. In this case, the “product rows displayed” option in the configuration should be set to a low number. g) Note that you must click the button to save your changes before going to the next page when the list spans multiple pages. Otherwise your changes will not be saved. 15) Preparing Quickbooks Using the Product Import Utility: If you have never used Quickbooks with your on-line store, and you wish to import all your store items into Quickbooks, click the “Create iif file from all shopping cart products” button in the QBI Utilities to make an iif file that you can import into Quickbooks. This will work properly with shopping cart products that have attributes, but will not work for shopping cart products that you wish to match to Quickbooks item groups. Unless you have a large number of items that are not already in Quickbooks, and you will not wish to change the names or use item groups, this method is not recommended. It is preferable to add your items directly in Quickbooks, and then match them to the items in the shopping cart using the “Match|Products” page as explained above. Note that the item import, if you choose to do it, only needs to be done one time. 16) Importing Customers: You may use the Import Customers feature on the Create page to make an iif file for importing all your shopping cart customers (only those who have actually placed successful orders) into Quickbooks. It is not necessary to do this, as customers are always imported with their orders. Use this feature only if you wish to have all your prior customers in Quickbooks for historical reasons. 17) Using QBI to Import New Orders: a) Go to the QBI menu item “Create iif” b) Under New Orders, click “Download iif File.” c) In the dialog box that pops up, select “Save to Disk” and choose the location. d) In Quickbooks, go to File|Import|iif Files. e) In the file box that pops up, select the file that you just saved and click “open.” f) Quickbooks will confirm that the file was imported successfully! Note: Sometimes Quickbooks will confirm success even though the import has failed. The first few times you import the iif files, make sure that the invoices and customers have imported correctly. If they haven‟t and/or you receive any errors messages, please save the iif file for troubleshooting, and write down the exact text of any error messages. QBI has been thoroughly tested to avoid any errors, but… 18) Importing the Previous Batch of Orders: Every time you import new orders, they then become the “Previous Batch.” If you need to reimport them for some reason, for example if you change an option in QBI and wish to try again, use the “Previous Batch of Order” import feature on the Create page. 19) Future plans: a) Further improvement to the user interface with full CSS. b) Validation to XHTML and WAI. c) Matching support for products with multiple attributes d) Support for QB groups with hidden sub-items e) Utility to selectively update previous order group to shipped f) Multiple sales tax rates g) Support for Canadian, UK, Australian, and other international QB versions. h) Your suggestions are welcome! 20) Known limitations: a) Sales tax groups are not currently supported. Quickbooks has a serious bug where sales tax groups may incorrectly calculate sales tax. This is because Quickbooks rounds each tax item to the nearest penny, instead of rounding just the tax group. Intuit is aware of this bug but has not corrected it for many years! For this reason, do not use sales tax groups to avoid input errors. If you still wish to use sales tax groups, use a single tax item that reflects the total tax rate, import the invoices, and then change the tax to a group on the invoice afterwards. Note that this work-around could cause problems with prepaid orders, because the invoice total may change by one penny after switching from a single sales tax item to a group. b) Credit card numbers can not be included as iif files do not support this. However, payments are supported, so if your store uses a credit card payment gateway, customer payments can be imported into Quickbooks as invoices/payments or sales receipts. c) Sales receipts are numbered sequentially in Quickbooks and do not reflect the shopping cart order number. To partially work around this, the shopping cart order number is imported into the “PO Number” field. In the Quickbooks templates, you can change this to read “Shopping cart Order Number.” d) Products with multiple attributes will pass through to Quickbooks but can not yet be matched. e) Non-US versions of Quickbooks are not yet supported. f) The product export iif will only show quantities for master items. Items with attributes will show as zero inventory. This is due to a limitation of the shopping cart program, as there is no inventory tracking for individual attributes. This is due to be addressed in future versions of the shopping cart. 21) Known bugs: a) None 22) FAQ’s: If I add, delete, or change my items or groups in Yes. QBI will make the changes and preserve all your Quickbooks, can I reimport the new iif file into QBI previous work, including your matches. without loosing my previous work? If sales tax is turned on in Quickbooks, but I don‟t QBI must be set to match your setting in Quickbooks. charge tax, how should QBI be set? It doesn‟t matter if you are actually charging tax or not. If I change options in QBI, will this be reflected in the Yes. QBI always generates a new iif file every time iif file if I reimport it? you download it. 23) Troubleshooting: Create File permission errors when creating iif files. Check that directory qbi_input has read/write/execute permission. On unix servers, set to chmod 777. Created iif file dumps to browser instead of Choose “save as” instead of “open.” If your browser is downloading. set to automatically take a certain action with specific file types, you may need to adjust your settings for files with the iif extension. Set Up File permission errors when doing Product or Shipping Check that directory qbi_input has read permission. Set Up On unix servers, set to chmod 755. Match Config iif File Import Into Quickbooks Transaction out of balance error Tax settings in QBI do not match Quickbooks. Low order fee, gift certificates, or coupons haven‟t been properly set up. Shopping cart product is matched to a Quickbooks group and the price is not the same in both the shopping cart and Quickbooks. Transaction out of balance errors with Authorize.net Add: $order->info['total']+=0.000001; Consolidated 1.7 contribution installed Before: $form_data = array( In: authorizenet_direct.php Also, make sure that all other causes are corrected as well.