Go to Bottom

Export Accounting Data

Users that are upgrading from version 4.4 (including 4.3 Beta), 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 a mark-up language similar to HTML, except that it is used to structure data, not display it. XML is used to order data in such a way that it can be passed between applications with relative ease. makes this is a requirement; two new accounts must be created prior to export or else the system will not allow the export to occur.

This article contains the following sections. Click on a link to jump to that section.

• To Export Data to QuickBooks

• Performing an Export via the .IIF (Intuit Information File) Transfer Method

• Basic Example of How Funds Flow between the Order Manager and QuickBooks

• Troubleshooting QuickBooks Discrepancies

Overview

This article assumes that QuickBooks has been installed on a workstation where the Order Manager is installed and that a QuickBooks company file has been created and the Chart of Accounts has been established.

Be advised that at this time, the export to QuickBooks does not contain any information about manual changes to inventory QOH. You need to use the Inventory Adjustments report and manually add a journal entry to QuickBooks. We hope to address this in the near future.

There are four broad categories of information that can be exported from the Order Manager 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. The Order Manager 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 the Order Manager 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 the Order Manager 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", the Order Manager 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 the Order Manager’s PO History table when purchase orders are received/posted in the Order Manager. 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 the Order Manager. 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!

After the instructions in 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 the merchant can choose from 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. (Available 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. (Available 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.

To Export Data to QuickBooks

1. Navigate to the Main Menu> Maintenance Menu> Import/Export tab> Export Accounting Data (to QuickBooks, etc.) >GO.

The Accounting Data Export dialog box opens:

An Explanation of the Form:

1. 1. New batches include the following information - check the box or boxes to determine what type of data will be included in the new batch of data to be exported to QuickBooks. The choices are Sales, Deposits, Receiving or Invoicing.

   2. Preview Any New QuickBooks Data -  click this button to preview any new data through a date specified by the user, prior to it being exported via the chosen transfer method.

   3. Preview Selected Batch of Data - click on the row of a batch ID in the Previous Export to QuickBooks (G) section of the form and then click this button to preview the details of that batch of data.

Sample preview window:

4. Export Any New QuickBooks Data to File - after previewing and verifying the data to be exported, click this button to execute the transfer of data via the chosen transfer method.

5. Re-Export Selected Batch of Data to File - click this button to export the selected batch ID (highlighted in the Previous Exports to QuickBooks (G.) section of the form) to QuickBooks again. Use this button with caution as data will be duplicated in QuickBooks if it is not removed from the accounting software prior to being re-exported. If errors occurred during an export, click on the Export Any New QuickBooks Data to File button, to resume the export instead.

Tip! Make a backup of the QuickBooks company file prior to performing exports to allow an easy way to back out changes. Otherwise, manually remove the data before re-exporting data to QuickBooks.

6. Filter Export List - filters the data that appears in the Previous Exports to QuickBooks (G.) list. Click on a radio button to display either the last 10 exports or all exports.

7. Previous Exports to QuickBooks  - displays a list of previous exports. Column headings are: ID (ExportID), Date (Exported) and Thru date range for each type of data (Sales, Deposits, Receiving or Invoices).

2. Choose the type of data to be exported (A) and click the appropriate action button (B-E). 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.

Performing an Export via the .IIF (Intuit Information File )Transfer Method:

1. Navigate to the Main Menu> Maintenance Menu> Import/Export tab> Export Accounting Data (to QuickBooks, etc.) >GO.  

2. Choose the type of data that should be exported: Sales, Deposits, Receiving or Invoicing.

3. If it is not necessary to preview the data that is selected for export, click the Export Any New QuickBooks Data to File button on the QuickBooks Export form pictured earlier in this article. Skip to step 5.

4. To preview the data before it is exported, click the Preview Any New QuickBooks Data button. Click the Print button to send the data selected for export to the workstation's default printer. Click the Run Export button to start the export process.

5. When any of the Export buttons are clicked, the user will be prompted to choose an action if the .iif file already exists. If Yes or No are selected, the export will proceed after the file has been archived or deleted.

6. A calendar with the heading Export all new history and transactions thru: opens. Select to mouse "left-click" on an object, usually a row in a table, in the context of Order Manager documentation a date on the calendar and click OK. Data is exported for approved transactions up to and including the date selected.

   7. If it is the first time Deposit information is being exported, the calendar form will appear a second time with the heading "Select starting date for exporting Transaction". Select a date on the calendar and click OK.

   8. The Order Manager exports the data to the text file specified on the Accounting Export Setup form.

   9. When ready to import the data in the text 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.

   10. If an error occurs during an IIF transfer, the error message will display the line number of the failing transaction. This transaction will not be imported to QuickBooks, it will be 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.  

Perform a Simple Text File Transfer

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.  

Basic Example of How Funds Flow between the Order Manager and QuickBooks Accounts

When an item is "sold", the AR account is debited with the price of the item sold and the Sales account is credited with the price of the item sold.

Example:

A $100.00 widget is sold.

AR     = $100.00

Sales = -$100.00

When the payment is recorded for the widget,the Deposit account is debited and the AR account is credited.

Example:

Payment is received for the $100.00 widget

Sales   = -$100.00

AR       = $0.00

Deposit = $100.00

The changes described above take place as two separate transactions in QuickBooks – one as a General Journal entry (Sale) and one as a Deposit entry (Payment).

When a return occurs, the reverse is applied and the AR account is credited, and the Sales account is debited.

Sales   = $0.00

AR       = -$100.00

Deposit = $100.00

The line below indicates this is occurring – For the return on order 81845, -$152.95 is being debited (to offset earlier credit) to the sales account “OM Refund”.

And the offset (credit) is applied to the “OM SALES ACCT REC” (AR - rolled into the daily journal entry)

Once the credit is issued to the customer (they are given a refund) the AR account is debited, and the Deposit account is credited.

Sales    = $0.00

AR        = $0.00

Deposit = $0.00

Notice that the refund issued to order 81845 shows the deposit account (OM PayPal) as being credited and the sales account (OM SALES ACCT REC) being debited for the amount of the return.

Additional Information

Keep in mind that at any given time the AR could show a balance/credit situation for one or more of the following reasons:

1. An order has been approved but not paid (shows Balance Due) – AR would show positive.

2. An order has been paid for but not approved (order shows paid in full but no sales data has been recorded – AR would show negative).

3. A Return has been processed but the credit has not been issued (order shows credit due) – AR would show negative.

4. An error condition could have caused incorrect data to be written or could have omitted data from the sales history system leaving an imbalance.

A review of the current state of the order versus sales history system versus deposit history can usually identify these problems.

Created: 1/19/12

Modified: 2/25/13

Go to Top