Note to users of version 4.3 (Beta): After installing the new version, all users 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 make this is a requirement; two new accounts must be created prior to export or else the system will not allow the export to occur.
Background
With version 4.3 Beta, there two additional methods for exporting accounting data to QuickBooks. The three methods that you can use are:
Export the data via XML API which writes the data to QuickBooks almost immediately. (Available in version 4.3 Beta). (recommended)
Export the data to an .IIF file from the QuickBooks Export dialog box. The .IIF file is saved to your Stone Edge directory.
Export the data to a text file from the QuickBooks Export dialog box. The text file is saved to your Stone Edge directory. (Available in version 4.3 Beta).
To indicate which type of export you wish to use, go to the AccountingApplication system parameter (Program group). If you have already performed an export to QuickBooks, the system defaults to "QuickBooks .iif" files; otherwise the system defaults to "None"). If set to None, you cannot open the Accounting Export Setup dialog box. Instead, you are prompted with the following message box:
"You have not defined an accounting program to use with the Order Manager. Go to the Set System Parameters screen, select the Program Group and set the AccountingApplication parameter to the accounting application and format you intend to use before using the Accounting Export Setup."
AccountingApplication = QuickBooks Direct Transfer (xml)
(recommended)
Accounting Import tab
Path to QuickBooks Company Data File (NEW) - User must enter the path and file name of the QuickBooks data file. File name must have a ".qbw" extension to be accepted. File can be anywhere on your local network.
Import Chart of Accounts - Prior to importing your chart of accounts directly from QuickBooks, you MUST have QuickBooks open on the PC Where you intend to do the import. Once you click the Import Chart of Accounts button, you must switch over to QuickBooks and assign "data file access permissions" to the Order Manager (permissions are assigned one time only). We highly recommend that you set up the permissions to only allow the Order Manager access to the QuickBooks Data File while QuickBooks is open. This requires the user to "log in" to QuickBooks before the Order Manager can read from or write to QuickBooks (prevents accidental exports and invalid access by other applications). It is at the user's discretion as to what permissions are given. If you do select the option to require QuickBooks to be open then any time you perform an export, you must ensure QuickBooks is running prior to the export.
Export Setup tab - File Location and Archive Location are not available (i.e. hidden).
All other tabs - Same as .IIF and simple text (see below).
AccountingApplication = QuickBooks File Transfer (.iif)
If set to QuickBooks File Transfer (.iif), you see the following:
Account Import tab (NEW)
Here you can import your Chart of Accounts from an .iif file. Click on the Import Chart of Accounts button and the system will prompt you for the location of the .iif file. At the completion of the import, you see the accounts in the list at the bottom of the screen.
Export Setup tab
Here you can establish the following information:
Field Name |
Comments |
Start Date for Accounting Exports (NEW) |
Allows the user to set a start date for the beginning of all exports. This is designed for users who already have some historical data that they may not want to export. This date restricts the system to only export information from this date forward (this date is included in the export!) |
Order Manager Accounts Receivable Account (MODIFIED) |
Select an "Other Current Asset" account to use as the "Accounts Receivable" account used by the Order Manager. For QuickBooks users, this list is restricted to an asset account you can no longer select a QuickBooks AR account. |
Output file name and path |
Specify the path and file name where you want the Order Manager to write the accounting data. At time of export if this file exists, the Order Manager will prompt the user to either "Archive" or "Delete" the existing file (system does not support appending new data to the file). If the option to archive is selected, the file will be moved to the "Archive Path" location. The file name MUST end with a ".iif" extension. |
Archive path |
Set the path where old .iif files will be moved. |
Name of report for Printing Previews |
Default set to "QBPreviewReport" this setting allows the user to substitute a different custom report when printing data from an Export Preview. |
Export Sales Data to Accounting |
Check this option to include sales data in the accounting export. Sales data includes product retail sales amounts, cost of goods, taxes, shipping, discounts, surcharges coupons and returns. |
Summarize Sales Data |
check this option if you want the sales data to be summarized by day and account. Leave this option unchecked to show sale details. |
Rounding Errors Credit Account (NEW) Required |
It is inevitable that there will be rounding errors when summarizing data for export. Should there be differences, this account will be used to record the "credit" side of the transaction. The Accounts Receivable will represent the Debit side. Older versions of the Order Manager created an account automatically called "Order Manager Rounding". This will remain as the default account but can be changed by the user if needed. |
Misc. Sales Debit account (NEW) Required |
This account is only used in the absence of all other "sales" accounts for recording product retails sales. In reality it should never be used because the system requires the seletion of a "default" sales account at a minimum. Should be set to an Asset account or the Order Manager Accounts Receivable. Older versions of the Order Manager automatically create this account with the name "Misc Order Manager Debit" which will remain the default for this account. User can change this if needed. |
Misc. Sales Credit account (NEW) Required |
This account is only used in the absence of all other "sales" accounts for recording product retails sales. In reality it should never be used because the system requires the selection of a "default" sales account. Should be set to an Asset account. Older versions of the Order Manager automatically create this account with the name "Misc Order Manager Credit" which will remain the default for this account. User can change this if needed. |
Export Deposits to Accounting |
Select this option to export all payment transactions to QuickBooks. |
Summarize Deposit Data |
Select this option to summarize the deposits by day and payment method otherwise the system will show each payment separately. |
Combine Visa & Mastercard |
(Only available when "Summarize Deposit Data" is selected.) Select this option to summarize Visa and Mastercard charges together into a single deposit. |
Show Split Detail in Summary |
(Only available when "Summarize Deposit Data" is selected.) Select this option to have the system break out each deposit as a "split" against the daily summary deposit. |
Rollup Credits into Deposit |
(Only available when "Summarize Deposit Data" is selected.) Some accounting applications do not permit "negative deposits" when you issue a credit to a customer. This option allows you to include "credits" in a daily deposit provided the total credits to not allow the deposit to equal or fall below zero. Should credits cause the deposit to reach or fall below zero, they will be recorded separately as "checks" (QuickBooks). If not then they will be included as splits against the daily deposit. |
Credit Card Deposit Cutoff Time |
Transactions recorded in the Order Manager after this time will be recorded with the following day's business. |
Sales Accounts tab
-Users no longer see the "POST COG Data as..." option. This was removed and the system was re-designed to ensure that a "Cost of Goods Sold" (QuickBooks) or "Expense" (text) account is set up for the debit account and that an "Inventory Asset" (QuickBooks) or "Asset" (text) account is set up for the Credit account.
Inventory Categories tab - No changes
Deposits tab - No changes
POS Deposits tab - No changes
Purchase Orders tab
Field Name |
Comments |
"Export PO Receiving Data?" and "Export PO Invoicing Data?" |
Replaced "Export PO Data to QB" checkbox. |
Path to .iif file for exporting |
Removed, now use single file specified on Export Setup tab. |
Archive Path |
Removed, now use single location specified on Export Setup tab. |
Export PO Receiving Data |
Check this option to export data regarding items received using the Purchase Orders system. |
|
IMPORTANT NOTE: The Order Manager DOES NOT yet record receiving history at the Inventory Screen or at the Scan Non PO Items into Inventory screen. Any receiving recorded here WILL NOT be exported to your Accounting System!!! |
|
IMPORTANT NOTE: The Order Manager DOES NOT yet record inventory valuation changes for "Inventory Adjustments". Adjustments WILL NOT be exported to your accounting system. |
Export PO Invoicing Data |
Check this option to include invoices (bills) entered at the Purchase Orders system in the export to your accounting system. |
Purchase Order Payable Account |
Set this to your "Accounts Payable" account (QuickBooks) or an "Expense" account (text). This will be the account that gets credited when invoicing data is exported. |
Purchase Order Liability Account |
Set this option to an "Other Current Liability" (QuickBooks) or to a "Liability" or "Expense" (text) account. This account maintains the value of received goods (as a negative) that have been received (or drop- shipped) but have not been paid for (invoiced). This account is credited at time of receiving or, for drop-ships, at the time the item is marked as "shipped" (post order approval). This account is Debited by the product totals recorded on an invoice. |
Purchase Order Asset Account |
Set this account to "Inventory Asset" (QuickBooks) or "Asset" (text) account that tracks your total inventory value. This should be the same account that is set as the Credit Account for Cost of Goods Tracking on the Sales Accounts tab. |
Purchase Order Freight Charges |
Set this to an "Other Current Expense" (QuickBooks) or an "Expense" (text) account for maintaining the Debit data for shipping charges incurred against Purchase Orders and Drop-Ships. Credit Account is the Purchase Orders Accounts Payable account. |
Purchase Order Discounts |
Set this to an "Other Current Expense" (QuickBooks) or an "Expense" (text) account for maintaining the Debit data for discounts applied to Purchase Orders and Drop-Ships. Credit Account is the Purchase Orders Accounts Payable account. |
Purchase Order Surcharges |
Set this to an "Other Current Expense" (QuickBooks) or an "Expense" (text) account for maintaining the Debit data for surcharges applied to Purchase Orders and Drop-Ships. Credit Account is the Purchase Orders Accounts Payable account. |
AccountingApplication = Simple Text
Accounting Import - Same as .IIF EXCEPT that when you click the Import Chart of Accounts button, you are prompted for a ".txt" file which must be a tab delimited file of 4 columns of data containing the following information in the order specified with no field name header:
Field 1 - Account Name - Name of the account
Field 2 - Account Number - Must be numeric
Field 3 - Account Type - Must be one of the following values: "Revenue", "Expense", "Asset", "Liability"
Field 4 - Description - A short description of the account
(later revisions of the Order Manager will make this a more flexible import/export system)
Export Setup - Same as .IIF EXCEPT that the Export File Name must end with a ".txt" extension
All other tabs - Same as .IIF.
The text file is exported in two sections. Each section includes a header record in the following tab delimited format:
Section 1: Account information - List of accounts included in the export for verification that accounts exist in your accounting application
Field 1: AccountName
Field 2: AccountNumber
Field 3: AccountType
Field 4: AccountDescription
Section 2: Transactions - Actual accounting data
Field 1: AccountName - Name of the account affected
Field 2: AccountNumber - Account Number affected
Field 3: Amount - positive dollar amount of transaction
Field 4: Date - Transaction date
Field 5: TransactionType - "Debit" or "Credit" - indicates whether the "Amount" to apply to the account is positive (Debit) or Negative (Credit)
Field 6: Memo - Notes regarding the transaction
Purchase Orders
Receiving is now separated from billing. In the past, when you "Posted" your receiving, the program exported a Bill to a QuickBooks .IIF file. This is no longer the case.
Receiving
Now, when you Post Receiving, the data is maintained in a history table (similar to the sales history table). The data can be exported to QuickBooks on demand. Receiving regular inventory items (i.e. not drop-ships) increases (debits) your Inventory Value and decreases (credits) an "Other Current Liability" account (same as drop-ships) indicating the items have been received but not yet "Invoiced" in the PO System.
Invoicing
When you enter your invoicing in the Order Manager, the Other Current Liability account is increased (debited) and your Accounts Payable is credited with the Bill now due.
Exporting Data to QuickBooks
A new single export point for all Accounting application data is now available. The Export Data to Quickbooks screen now includes all Sales, Deposit, Receiving and Invoicing exports - which can be run independently. The Export screen now has checkboxes allowing you to define what data is exported.
If you selected the "Export Sales Data to Accounting?" option on the Export Setup tab of the "Accounting Export Setup" screen, the "Sales" checkbox is enabled on the "Export Setup" screen.
If you selected the "Export Deposits to Accounting?" option on the Export Setup tab of the "Accounting Export Setup" screen, the "Deposits" checkbox is enabled on the "Export Setup" screen.
If you selected the "Export PO Receiving Data?" option on the Purchase Orders tab of the "Accounting Export Setup" screen, the "Receiving" checkbox is enabled on the "Export Setup" screen.
If you selected the "Export PO Invoicing Data?" option on the Purchase Orders tab of the "Accounting Export Setup" screen, the "Invoicing" checkbox is enabled on the "Export Setup" screen.
If the controls above are "enabled", they are selected by default. You can un-check or de-select the items you do not wish to be exported.
You can still "preview" the data prior to exporting it (critical for QuickBooks XML users). Each export is recorded with the time and date the export took place and the data that was exported (sales, deposits, receiving & invoicing).
QuickBooks XML API
The QuickBooks XML API (SDK) allows the Order Manager to export your data directly to the QuickBooks data file rather than to an .iif file. This requires that the Order Manager performing the export be on a computer that has the QuickBooks Client installed. The versions supported are 2003-2006 for US - Pro, Premier and Enterprise. It is highly recommended that you use the Export Preview to review the data being sent prior to running the actual export.
IMPORTANT NOTE: It is possible to re-export previous batches of accounting data, however, if you do this while using the QuickBooks XML API, you may end up with duplicate data in QuickBooks. There is no way of knowing what historical data remains in QuickBooks as a result of the original export so all data is exported and is accepted again by QuickBooks. Please use the re-export function with care.
Notes
Bug: When a drop-ship item is sold, a "cost of goods" entry is made in History. When the entry was exported to QuickBooks, the "Cost of Goods" account was debited and the "Inventory Asset" account was credited which was incorrect since drop-ships are never received into inventory.
Fix: The system has been changed to record a new history record called "Cost of DropShips" so that when the export to QuickBooks occurs the "Cost of Goods" account is debited and an "Other Current Liability" account (designated by the user) is credited for items that have been "sold" but not "invoiced" in the PO System. Once a drop-ship PO has been "Invoiced", the "Other Current Liability" account is debited and the "Accounts Payable" is credited thus creating a "Bill" for QuickBooks. The "Other Current Liability" account should balance to zero assuming that all drop-ships ordered have been invoiced. This same Liability account is also used for inventory items "received" and not yet invoiced.
Bug: Users had to list the "Cost of Goods Sold" account as the Credit account and the "Inventory Asset" account as the Debit account for the Cost of Goods Sales category because costs were initially being reported as a negative value when debited.
Fix: The user can now "post COG as a positive" rather than a negative (correcting the error). The system corrects this account assignment automatically and lists the Cost of Goods Sold as the debit account and the Inventory Asset account as the credit account for Cost of Goods reporting.
See Also
Accounting Export Setup - v4.2 & Below
Accounting Export Setup - V4.4 & Above