Our QuickBooks Import Export Blog is Moving

Our blog is moving to http://blog.baystateconsulting.com

Please visit us at our new blog location for the most current content.  Thanks for reading!

Importing Bills, Checks and Credit Card Transactions into QuickBooks – Required Fields

On the transaction types listed below QuickBooks gives you the option of using either expense accounts or items on the transaction.

• Bill
• Check
• Credit Card Charge
• Credit Card Credit
• Vendor Credit

On the Transaction Pro Importer for QuickBooks mapping screen required fields are denoted in red.

There are some fields however, that become required depending on whether you are using expense accounts or items.

If you are using expense accounts the following two fields are required:

• Expenses Account
• Expenses Amount

If you are using items the following two fields are required:

• Items Item
• Items Cost

If you do not follow the mapping rules above you will receive an error message such as:

“Bills o Transactions must have at least one line item-Make sure an account or item is specified on at least one transaction line.”

If you receive this error message make sure you have data mapped to the fields previously discussed below.

We also have a data dictionary that addresses this topic as well as others at http://www.baystateconsulting.com/forum/forum_posts.asp?TID=338&title=tpi-field-listing-data-dictionary

Importing Sales Tax Totals on Invoice and Sales Receipts using Transaction Pro Importer

Importing sales tax entries into QuickBooks on invoices and sales receipts can be a challenge.  This can be even more difficult if your import file does not contain the sales tax code and the sales tax item.  So what do you do if you import file only has the total sales tax amount in its own column?  If you follow the steps below you can import your transactions with Transaction Pro Importer 4.0 with no changes needed to your import file.

How to map total sales tax in TPI 4.0 if your file contains a Sales Tax Total Column

In QuickBooks Do the Following:

Create a new item of type Sales Tax. Use the picture below for the values for this item. Be sure you set the Sales Tax Rate to match 0.0% and the name as See Above.

1. In TPI click Options and select the e-Commerce tab
2. Check the box “Enable TPI e-Commerce Features”
3. Set the Import File Structure to the top selection

4. On the mapping screen – Map the field “Sales Tax” to your                             column  value that contains the total sales tax.

For the field Sales Tax Item provide the following in the Static                       Value/Formula column: See Above

Do not map the following field: Sales Tax Code

5. The e-Commerce options screen will appear later in the wizard. Set the               QuickBooks Tax Account and QuickBooks Tax Item to match                        those in  the QuickBooks file. See the Sales Tax Item you have setup to                    record  sales tax and use that name and the underlying account.                                     Do not use a Sales Tax Group item as it is not supported at the                               current  time. Specify an item of type Sales Tax Item.

Leave the other fields untouched in this screen as they require a default value for a successful import.

The result in QuickBooks will be that a separate line item will be entered on the transaction using the sales tax item and recording the amount.

Please note that there are many more scenarios of how to import transactions with sales tax depending on the structure of you import file and how your QuickBooks company file is set up.  If you have any specific questions regarding imports and sales tax please post them below.

Invoice and Sales Receipt Imports into QuickBooks – e-Commerce Features

On the options screen within Transaction Pro Importer for QuickBooks you will see an e-Commerce tab.  This blog post will address what this option can be used for.

When importing invoices or sales receipt transactions, often times shopping carts will create an export that has the amounts for shipping, handling, discounts and sales tax in separate columns instead of its own line item.  Please refer to the sample import file below.

If the e-Commerce feature is checked, when you get to the Transaction Pro Mapping screen you will see that additional fields are added in the QuickBooks Fields column.

On the last e-Commerce Options Screen you will specify the account and item that you want to be used for these e-Commerce fields.

On the e-Commerce options screen you will also notice that you can indicate that multiple items are on one line.

A sample import file that you might use with this option is pictured below.  You can see that there are three items on each line.

When the second option, separate columns is checked, on the Transaction Pro mapping screen you will see that you have up to 15 additional items that you can map to.

As you can see the file format for sales transactions provides for a variety of layouts to allow you to use Transaction Pro Importer with multiple shopping carts.

If you want to test this out you can download a free demo at http://www.baystateconsulting.com/products/01TxnWizardDemo.asp

Transaction Pro Importer – Upgrading from QuickBooks UK 2005 / 2006 to 2008 / 2010

Intuit has sent out a final reminder that all services and support for QuickBooks 2006 (United Kingdom Edition) will be discontinued as of September 30, 2010.

If you are a current user of Transaction Pro Importer from Baystate Consulting, you may benefit from these upgrade tips:

Transaction Pro Importer 4.0 Required

1. Be sure you are running the latest release of Transaction Pro Importer 4.0. You can do this by clicking About/Check for Updates. If you are on an earlier major version of Transaction Pro Importer such as 2.0 or 3.0 you need to go to our upgrade page here:   http://www.baystateconsulting.com/register/upgrade.asp. Also note that if you plan to run QuickBooks UK 2010 under Windows 7 you will need to upgrade to TPI 4.0 as that is the only version that will work with Windows 7.
2. VAT Considerations

   With UK 2005/2006 there was the ability to set the total VAT via the TPI field “Tax1Total” With the release of UK 2008 Intuit removed this field from the QBSDK and TPI lost the ability to set VAT directly.

   Note: This only impacts users that have the total VAT appearing in a column rather than a separate line item row in the data in their data file. If you are currently importing VAT from a separate line item row than you can ignore this section.

   Workaround 1 (Preferred): Use the “Sales Tax Code” field in TPI to set the VAT code for each line and let QuickBooks calculate the VAT for you.

   Workaround 2: Use the eCommerce option and map the VAT column to the “Sales Tax” field in TPI. Be sure to set your VAT item account and name to match on the eCommerce settings screen that appears in TPI. TPI will create a new line item with the VAT.

   Workaround 3: Check the options “Do not add new Customers” and “Do not add new Items” on the TPI basic options screen. Then make sure your customers and items are setup with the VAT codes you want to use in your import file. This option is for users that want to let QuickBooks calculate the VAT but currently do not know or have VAT codes on each line of their import file. When importing the file with existing customers and items, QuickBooks will correctly calculate the VAT based on the settings for those entities.

3. Multi-currency considerations

   When importing transactions that contain multiple currencies, QuickBooks will determine the currency to use based on the currency set on the Customer or Vendor record, it is not necessary to specify the currency however it is possible to specify the currency if the Customer or Vendor record does not exist. You can also specify the Exchange Rate that should be used for that particular transaction.  The exchange rate is the market price for which this currency can be exchanged for the currency used by the QuickBooks company file as the “home” currency

Importing Deposits into QuickBooks Online

All third party applications that work with QuickBooks online are required to use the Software Development Kit (“SDK”) which is included in the QuickBooks software.  Unfortunately, for whatever reason Intuit does not provide support for all transaction type and features.

One request we see a lot is how do I import deposits into QuickBooks via a third party application if the QuickBooks SDK does not support it?  We suggest that you import these transactions as sales receipts or journal entries.  You will probably need to either make changes to your QuickBooks company file and/or the import file.

Importing Deposits as Sales Receipts

A sales receipt in QuickBooks is a cash sale which increases your cash balance and most times an income account.  However, the difference between a deposit and a sales receipt is that deposits use accounts and sales receipts use items.

So what you need to do in QuickBooks Online is to set up items that correspond to the income accounts that you want to post to.  Lets say all your deposits need to be posted to the income account, Services, in QuickBooks Online.  Access your product and services list and create a new service called, Services, and then map it to the income account, Services.

Then if all of your deposits will be going to the same income account on the Transaction Pro Importer mapping screen you can type in the word Services next to the QuickBooks field, Item and in the static value column.

If you have multiple income accounts you can either do separate imports for each income account or modify your import file and your product and services list to accomodate the multiple income accounts.

Import Deposits as Journal Entries

Before you import your file you will need to modify two things within the import file.  First you will need to convert the deposit amounts to a negative amount (see the blue area in the screen shot below) so that they will be reflected as a credit in the journal entry.  The other thing that you will need to do is to provide a line in the journal entry for the debit to the cash account (see the yellow area in the screen shot below).

You can test this out by downloading a free trial of the Transaction Pro Importer for QuickBooks Online at http://www.baystateconsulting.com/products/01TxnWizardOEDemo.asp

If you have any questions on this post or have any other transactions that are creating import challenges please post below or email us.

Receive Payment Transaction Import into QuickBooks (many options available)

Often times third party vendors that provide QuickBooks users with files that contain payment information that is either  incomplete or inaccurate making it difficult to import the receive payment transactions into QuickBooks.

This post will point out many options that you have available within Transaction Pro Importer (“TPI”) to overcome these hurdles.

The options we will be discussing are as follows:

1. Customer account number cross reference
2. Command line to allow a payment to be applied to multiple invoices
3. Command line to use if your import file contains only the apply to invoice number and does not contain the customer

Customer Account Number Cross Reference

This option is used if there is a concern that the customer name in the import file may not match the customer name in QuickBooks exactly or if the import file only contains an account number.

First make sure that you have updated your account no. field on the payment tab in QuickBooks for all customers involved.  Don’t worry if you forget to update some of the customers.   If TPI does not find a matching account no. you will get a list of these pre-import so you can fix these customers in your QuickBooks company file.

Then on the TPI Basic option tab check the option to lookup customer name in QuickBooks when account number is mapped to customer field.

One payment to be applied to multiple invoices

If you have one payment to be applied to multiple invoices your import file should contain multiple lines which contain the same check number and customer name but the apply to invoice number and amount will change on each line.

You need to edit the properties (right mouse click on the TPI shortcut and select properties) in the TPI shortcut to add -RECPAY2 at the end of the target.

More details on command line parameters and how they can be enabled can be found at:  http://bscdownload.com/downloads/Command_Line_Parameters.htm

Import File only contains an invoice number to apply the payment to but does not contain the customer name

If your import file does not contain the customer name but does contain the apply to invoice number you can enable the -RECPAY3 command line parameter.  Instructions on how to do this were discussed above.

If you want to try any of these options out you can obtain a free demo of TPI at http://www.baystateconsulting.com/products/01TxnWizardDemo.asp#demo