Channergy QuickBooks Integration help

Introduction

The purpose of this document is to show the setup and use of the QuickBooks Integration Export tool. Quickbooks Integration is downloadable from here.

Background

The Channergy QuickBooks Integration export tool is designed to export an .iif formatted file containing the daily sales (using either the Cash or Accrual accounting method) or bills.   This file can be imported into QuickBooks and used for managing your company finances.

Setup

To set up the application do the following:

1. Copy the Quickbooks Integration.exe to your Channergy database folder.
2. From a client workstation right click on the Quickbooks Integration.exe file and create a shortcut from the context menu. Copy the shortcut to the Tools folder in your Channergy database folder.
3. You can then launch the application from the Tools menu from Channergy.
4. Test your connection to the Channergy database by selecting the Test Connection option from the Setup menu in the application. If the ODBC connection has been set up on the client system you will get an ODBC Connection Successful dialog box.
   1. If not go to the ODBC connection setup.

Using the Application

Sales Export

1. Select the date range that you want to export the data for. The export utility uses the following criteria.
   1. The application will use the Ship date for the date range.
   2. The application will not export any transactions that have been previously exported.
2. Select the basis of accounting that your QuickBooks file is set up to use.
3. If you select the Cash basis you will also have the option to include order details by checking the check box.
4. Select whether or not you want to export the data based on Company or Operator
   1. Note that the Company option is only used if you have set up multiple companies in Channergy.
5. In the Operator/Company list box select the Operator(s) or Company(ies) that you want to export.
6. Click on the Process button.
7. The status screen below the Operator/Company list box will show the status of the export.
   1. The application will create a folder in the Channergy database folder called QBExport.
   2. The file will be named QBCashyyyymmdd-yyyymmdd-timestamp.iif (for using the Cash accounting method or QBInvoiceExport for Accrual basis). The file naming convention is as follows:
      1. The yyyymmdd is the date range in year month day format.
      2. The timestamp is in the format of yyyymmddhhmmss (year month day hours (24 hour format) minutes and seconds.
   3. If you get a “No records to export” message in the status screen this could mean two things.
      1. There were no shipped orders in the date range for the Operator(s) selected.
      2. The records that you selected to export have already been exported. If you want to re-export the data, empty the QB tracking tables by scrolling down to bottom of this page.

Bill Export

The bill export utility will export out an .iif file that can be used to import received purchase orders into the Channergy database.  The tool will only export out purchase orders that have an Invoice number assigned to them.

1. Select the date range you want to export bill information for.
   1. The tool will not export out any bills that have been previously exported regardless of the date range selected.
2. Click on the Process button.
3. The status screen will show you the progress of the export similar to below.
   1. The application will create a folder in the Channergy database folder called QBExport.
   2. The file will be named QBBillyyyymmdd-yyyymmdd-timestamp.iif. The file naming convention is as follows:
      1. The yyyymmdd is the date range in year month day format.
      2. The timestamp is in the format of yyyymmddhhmmss (year month day hours (24 hour format) minutes and seconds.
   3. If you get a “No records to export” message in the status screen this could mean two things.
      1. There were no shipped orders in the date range for the Operator(s) selected.
      2. The records that you selected to export have already been exported. If you want to re-export the data, empty the QB tracking tables by scrolling down to bottom of this page.

Importing the data into QuickBooks

Importing the data into QuickBooks involves opening the QB application and importing the data into QuickBooks.

The details are as follows:

1. Open QuickBooks
2. Go to File -> Utilities -> Import -> IIF Files… from the file menu.
3. Navigate to the QBExport folder in your data directory and select the .iif file that you want to import and click on open.
4. Once the import is complete you will get a Your data has been imported dialog box.

 

Reconciling the sales data in QuickBooks

Reconciling the Channergy sales in QuickBooks depends on what accounting system you chose for your business.  The QB export file creates the following accounts for both Cash and Accrual accounting:  Mailware Sales (Income), Mailware Sales Tax Payable (Other Current Liability) and Mailware Shipping.  For accrual based accounting an additional account (Mailware Payments) is created.

Cash Accounting

The iif file created by the application creates Sales Receipts grouped date and Operator (or Company).

If “Include Order Details” option is turned on the sales receipt details will look similar to the following:

There will be a cash receipt generated for each operator on each day that the operator had sales recorded (shipped).  The item detail will include Mailware Sales, Mailware Shipping and Mailware Sales Tax Collected for each day.  The operator and product information is contained in the description field.

If the Order Details option is turned off, the Sales Receipts screen will look similar to the following:

A cash receipt will be generated for each operator in the date range containing the Mailware Sales, Mailware Shipping and Mailware Sales Tax Collected for all sales in the date range.  The Description field contains the Operator information and the date range.

Accrual Accounting

By its very nature accrual accounting is much more involved when it comes to reconciling the accounts.  The iif file exported from the application generates invoices for order, shipping and tax collected for each operator (or company).

The Invoices screen for items will look similar to below:

The invoice number will be of the form ###-O-yyyymmdd where ### is the operator id (or CompanyNo) O is for orders and yyyymmdd is the export date.  The Quantity contains the summarized number of each product sold, the Item Code is the Channergy product number and the Description contains the Channergy order number.

The invoice screen for shipping collected looks similar to below:

The invoice number will be of the form ###-S-yyyymmdd where ### is the operator id (or CompanyNo)  S is for shipping and yyyymmdd is the export date.  The Code is the Channergy Shipping collected for each order as defined in the Description.

The invoice screen for sales tax collected looks similar to below:

The invoice number will be of the form ###-T-yyyymmdd where ### is the operator id (or CompanyNo)  T is for sales tax and yyyymmdd is the export date.  The Code is the Mailware Sales Tax collected for each order as defined in the Description.

To reconcile payments to each invoice click on the  button this will open up the Apply Credits screen similar to below:

The Credit No. field contains the information for linking the payment to the operator (or company) that invoices were imported for.  They are in the form of ###-P-yyyymmdd where ### is the operator id (or Company No), P is for payment and yyyymmdd is the import date.  To apply the payment to an invoice click on the check next to the associated Credit No for the invoice and click on done.

Repeat this process for the Shipping and Tax invoices for the Operator (or Company No).

Processing Billing Export

After the QBBill file has been imported into Quickbooks, you can view and pay the bills in the Pay Bills screen.

If the vendor does not exist then the vendor is created in the Quickbooks database.

Trouble Shooting

• ODBC connection setupThe following instructions are for setting up the ODBC connection to the Mailware database if you get a connection failed error message.  This error is caused by either not having the Mailware ODBC drivers installed on the client machine or the ODBC connection is not set up.This process will require that the user logged into the computer have administrative privileges.
  1. Download the ODBC drivers: Go to Setup -> ODBC -> Download Drivers
  2. Open the downloaded MailwareODBC.exe file and install it with all the standard options.
  3. Setup the ODBC connection: Go to Setup -> ODBC -> Setup Connection
  4. The ODBC Data Source Administrator will open.
     1. Click on the System DSN tab.
     2. Click on the Add button
     3. Select the DBISAM 4 ODBC Driver from the list and click on the Finish button
     4. In the Data Source Name field in the Configure Data Source screen type in the word Mailware.
     5. Click on the Next button several times and accept all of the default values and then the Finish button to complete the process.
• Emptying the QB Export Tracking TablesUse the following procedure ONLY if you have not imported any .IIF files into your QuickBooks company file.  If you have already imported Channergy .IIF files into your QuickBooks company file you will create duplicate records in QuickBooks.  If you want to re-export a specific date range contact technical support (866-624-5927×2 or support@core-tech.com) for assistance.The following process requires some limited knowledge of SQL and the Database System Utility that is installed with Channergy (versions 2011 and later).
  1. Go to Start -> Channergy 20XX (where XX is the version that you are using) -> Database Utility 
  2. Open a SQL Query window by clicking on the button on the tool bar.
  3. In the Select database to query window navigate to the Channergy database folder.
     1. If you run this query on your Channergy server it is usually C:\Mailware\Data
     2. If you do not know the path to your Channergy database folder, do the following:
        1. Open Channergy and go to File -> Open Database
        2. Select your Channergy database from the Network Database window.
  • Click on Edit…
  1. Copy the directory from the directory field
  2. Paste the directory into the Available Databases field in the Select database to query screen.
  4. Type the following into the SQL Query window

  DELETE FROM QB_ExportedItems; DELETE FROM QB_ExportedPayments; DELETE FROM QB_TaxAndShippingAmounts; DELETE FROM QB_BillExport;

  5. Click on the Run SQL button.