Export Accounting Data

Users that are upgrading from version 4.4 MUST revisit the Accounting Export Setup process prior to exporting any data - EVEN IF YOU STILL INTEND TO USE THE .IIF FILE INTERFACE.

The changes to the Purchase Orders system and the added support for QuickBooks' XML makes this is a requirement; two new accounts must be created before the system allows the export to occur.

Contents of this Topic: ShowHide

Overview

This article assumes that QuickBooks is installed on a workstation where OMS is installed, a QuickBooks company file is already created, and the Chart of Accounts is established.

There are four broad categories of information that can be exported from OMS and imported into QuickBooks: Sales, Deposits, Purchasing (Receiving) and Invoicing. They can be exported individually or all at the same time.

Sales - Sales are exported as General Journal Entries that can be summarized by day and account. Included are sales, sales tax, shipping charges, discounts, surcharges, coupons and the costs of goods sold. You can use one or more sales accounts. Items can be assigned to sales accounts individually, by inventory category, by a system-wide default sales account or any combination of these. OMS collects sales data from the time that an order is approved, therefore sales data for unapproved orders is not exported.
Deposits - Deposit data refers to transactions in OMS which are created each time a payment is received or a credit is issued. Payments received are imported into QuickBooks as deposits and are summarized by date and payment method with an option to combine Visa, MasterCard and Discover payments into one journal entry. Credits issued are imported as individual checks, unless they are rolled up into the Daily Deposit. You can specify which payment methods are exported and to which QuickBooks accounts. Each payment method can have a number of "delay days", i.e. the number of days it takes for a credit card payment to be deposited into your account. For example, Visa and MasterCard payments are deposited into your account the next business day but American Express and Discover charges are deposited in five business days. You can also specify a cutoff time so that payments received after a given time of day are assumed to have been received the following day. The user can specify the starting and ending dates (inclusive) that OMS will export. When it exports deposit data or transactions, the program looks at the "type" field of the transaction record. If the field contains a value, i.e. the value is "not null", OMS looks up the value in the Payment Methods table to determine which credit and debit accounts are assigned to it. If either account is missing OR the payment method does not exist, the transaction is not exported, allowing the user to limit the types of transactions that are exported. If you wish to export store credit and gift certificate transactions, you must create corresponding payment methods on the List Maintenance screen.
Receiving (Purchasing) - Receiving data refers to records written in OMS ’s PO History table when purchase orders are received/posted in OMS . General Journal entries are created in QuickBooks, which debits the “Inventory Value” account and credits the “PO Liability” account.
Invoices - Invoice data refers to records written into POInvoice & POInvoiceDetails tables when a vendor invoice is posted in OMS . Bill entries are created in QuickBooks, which debits the “PO Liability Account” and credits the “Accounts Payable” account. Invoices are NOT edit-able at this time!!! Be sure data is correct prior to posting!

Please note that at this time manual adjustments to inventory QOH levels are not automatically exported to QuickBooks. Instead, you must use the Inventory Adjustments report to identify the changes and manually create a journal entry in QuickBooks. We hope to add this functionality in the future.

After the instructions in the Knowledge Base topic Accounting Export Setup are completed, data can be exported to QuickBooks as often as the merchant wishes; daily, weekly, or monthly.

There are several methods from which the merchant can choose to export the data:

Export the data to an .IIF file from the Accounting Data Export dialog box. The .IIF file is saved to your Stone Edge directory.
Export the data to a simple text file from the Accounting Data Export dialog box. The text file is saved to your Stone Edge directory. (starting in version 4.3 Beta).
Export the data via the XML API which writes the data directly to the QuickBooks company file. It is possible to preview or print the data that has been selected before it is exported via the Export Data to QuickBooks dialog. (starting in version 4.3 Beta).

The process for exporting data to QuickBooks is essentially the same for all methods once the Accounting Export Setup is complete. Direct XML transfers will begin immediately without any additional action on the part of the user. Simple text files and .IIF file transfers require an additional, manual step to import the data from the newly created text file into QuickBooks.

Export Data to QuickBooks via the Direct XML Transfer Method

Go to Main Menu>Settings>Data Functions>Export Accounting Data.

The Accounting Data Export dialog box opens:

Accounting Data Export screen

Choose the type of data that should be exported: Sales, Deposits, Receiving or Invoicing.
If it is not necessary to preview the data that is selected for export, select Export any new accounting data to file from I would like to. Skip to step 6.
To preview the data before it is exported, select Preview any new accounting data from I would like to.

Accounting Export Preview screen
Optional: Select Print to send the data selected for export to the workstation's default printer.
Select Run Export to start the process.
A calendar with the heading Export all new history and transactions thru: opens. Select a date on the calendar and select OK. Data is exported for approved transactions up to and including the date selected.
If it is the first time Deposit information is being exported, the calendar appears a second time with the heading "Select starting date for exporting Transaction". Select a date on the calendar and select OK.
OMS exports the data directly to QuickBooks.

Export Data to QuickBooks via the .IIF (Intuit Information File )Transfer Method

Go to Main Menu>Settings>Data Functions>Export Accounting Data.

The Accounting Data Export dialog box opens:

Accounting Data Export screen

Choose the type of data that should be exported: Sales, Deposits, Receiving or Invoicing.
If it is not necessary to preview the data that is selected for export, select Export any new accounting data to file from I would like to. Skip to step 6.
To preview the data before it is exported, select Preview any new accounting data from I would like to.

Accounting Export Preview screen
Optional: Select Print to send the data selected for export to the workstation's default printer.
Select Run Export to start the process.
If the .iif file already exists the program prompts for the action to take. If Yes or No are selected, the export proceeds after the file has been archived or deleted.
A calendar with the heading Export all new history and transactions thru: opens. Select a date on the calendar and click OK. Data is exported for approved transactions up to and including the date selected.
If it is the first time Deposit information is being exported, the calendar appears a second time with the heading "Select starting date for exporting Transaction". Select a date on the calendar and select OK.
OMS exports the data to the file specified in the Accounting Export Setup.
When ready to import the data from the file into QuickBooks, go to File>Utilities>Import>Import IIF Files from the QuickBooks menu bar and navigate to the file that contains the exported sales or deposit data.
If an error occurs during an IIF transfer, the error message will display the line number of the failing transaction. This transaction is not imported to QuickBooks, it is skipped and the remaining transactions are imported. The easiest way to correct such an error condition is to revert to the backup of the company file made before the import was started, locate the failing transaction in the IIF file (using a text editor other than MS Excel), correct it and perform the import of the IIF file again.

Export Data to QuickBooks by the Simple Text File Transfer Method

Performing a simple text file transfer is similar to the .iif file transfer, except that a regular text file (.txt) is the recipient of the exported data, as designated in the Account Export Setup screens. Refer to the previous section for specific instructions.

Additional Information

Accounting Export Setup

Accounting Data Export

Accounting Export Preview

How Funds Flow Between Stone Edge and QuickBooks Accounts

Created: 7/13/12

Revised: 9/23/14

Published: 04/14/16