How to Export RetailEdge Data to QuickBooks

RetailEdge can export closing summary information to QuickBooks Desktop or Quickbooks Online using QuickBook’s API.  This is how you t export RetailEdge Data to QuickBooks Desktop or QuickBooks Online.

RetailEdge to QuickBooks Export Overview #

RetailEdge exports closing summary information to QuickBooks. There are two primary components that get exported to QuickBooks.

  1. Sales Receipts.  For each active payment method used in RetailEdge, the export creates a Sales Receipt in QuickBooks. Each Sales Receipt will be added to the Undeposited Funds account. You can then deposit the funds to the bank account of your choice. This helps when reconciling deposits.
  2. General Journal Entry.  The export also creates a single General Journal Entry in QuickBooks. The General Journal Entry is used to balance out the imported data and allocate funds to certain QuickBooks accounts like Cost of Goods Sold. It is also used to map RetailEdge department sales to QuickBooks income accounts and/or sub accounts.

Export Example #

Let’s say you have a sales session with 10 transactions. There are 3 cash transactions which total $100 and 7 check-based transactions that total $200. The export will create two Sales Receipts in QuickBooks. One Sales Receipt will be for $100 and associated with Cash and the other will be for $200 and will be associated with Check. The export will also create a single General Journal Entry record.

There is one exception to the standard export plan. If a RetailEdge sales session has a payment method with a negative total value, the export will create a Credit Memo in QuickBooks instead of a Sales Receipt.

Setting Up The Export To QuickBooks #

Setting up all the correct information is a bit tedious, but it does work. It is very important that the settings in RetailEdge and QuickBooks match precisely. The setup is the same for both Desktop and Online, with the exception of Step 2 below.

Note: With QuickBooks Online, you need to be using the Plus or Advanced Versions (unless you have converted to Online from Desktop). If you are using Simple Start or Essentials, you will need to leave need to leave the Class Field below blank.

STEP 1 – Setting up The QuickBooks Account Mapping
#

In RetailEdge, go to Tools | Settings | Locations from the main menu.

Modify the appropriate Location and go to the QuickBooks sidebar option. Each line on this form needs to match a corresponding account in QuickBooks. Just the Account Name, not the Account Number . (e.g., 1300 · Inventory Asset would be just Inventory Asset)

Field Map Explanations #

Here’s an explanation of each field:

Checking #

This is the name of a QB checking account. The QB account should be type “Bank”. An example name might be “Checking”.

Accounts Receivable Acct #

This field maps A/R (House Charges) to QB. The QB account should be type “Other Current Asset”. An example name might be “Acct Receivable”.

Customer Name (RE Sales) #

In order to track deposits to QB and reference RetailEdge sales, we need a “dummy” customer in QB. In QB, add a customer to the Customer List and make sure it matches the RetailEdge Customer Name value in the RetailEdge QB Export mappings. An example name might be “RetailEdge-Customer”. Also, in QuickBooks, make sure that the customer’s Tax Code value is set to non taxable value.

Class Name (RE Activity) #

The data exported into QB is associated with a QB Class. This allows for better report filtering. Create a Class in QB to match the RetailEdge value (e.g. Class Name = RetailEdge). It’s possible that you might have to turn on Class tracking in QB. To do so, edit the preferences and check the “Use Class Tracking” field on the Accounting tab.

Sales Income Account #

This is the QB Sales account where sales will be posted. This RetailEdge value and the QB account name need to match. The QB account type should be type “Income”.

Inventory Asset Account #

This account tracks changes in inventory value. The QB account type should be “Other Asset”. An example name might be “Inventory Asset”.

COGS Acct: This account tracks the Cost of Goods sold. The QB account type should be “Cost of Goods Sold”.

Item Name (from Items list) #

Like the “dummy” customer, we need a “dummy” item to sell within QB. Go to the Item List in QB and add a “Non-Inventory Part”. An example name might be “RetailEdge-ItemsSold”.  Also, in QuickBooks, set the item’s Income Account to match the account used for “Sales Income Account” from above.

House Charge Asset Acct #

This account tracks house charge totals within QB. Add a new QB account of type “Other Asset”. An example name might be “RetailEdge-House Charge”.

Store Credit Liability Account:

This account tracks store credit totals within QB. Add a new QB account of type “Other Current Liability”. An example name might be “RetailEdge-Store Credit”.

Layaway/Open Order Liability Account #

This account tracks layaway totals within QB. Add a new QB account of type “Other Current Liability”. An example name might be “RetailEdge-Layaway”.

Loyalty $$$ Liability Account #

This account tracks Loyalty Dollars totals within QB. Add a new QB account of type “Other Current Liability”. An example name might be “RetailEdge-Loyalty”.

Gift Card Liability Account #

This account tracks Gift Card totals within QB. Add a new QB account of type “Other Current Liability”. An example name might be “RetailEdge-Gift Card”.

Payout Expense Account #

This account tracks Payout totals within QB. Add a new QB account of type “Expense”. An example name might be “RetailEdge-Payout”. The payouts would be an drawer/deposit adjustment caused by a payout. Payouts can be performed by choosing Payout from the Register menu. Payouts might be things like someone taking money out of the drawer to pay the UPS man or to buy something from the hardware store.

Sales Tax Liability Account #

This account tracks Sales Tax within QB. Add a new QB account of type “Other Current Liability”. An example name might be “RetailEdge-Sales Tax”.

Note: The default Sales Tax account in QuickBooks does NOT have a type of “Other Current Liability”. Most customers will need to create a new account to be used for mapping with RetailEdge.

Cash Drawer Adjust Acct #

This account tracks Cash Drawer Adjustment totals within QB. Add a new QB account of type “Expense”. An example name might be “RetailEdge-DrawerAdjustment”.

NonSales Adjustment Acct #

This account tracks special adjustments. Add a new QB account of type “Expense”. An example name might be “RetailEdge-NonSalesAdjustments”. Non Sales Adjustments are adjustments are for actions that are not a part of the sales process and include:

1. Gift cards manually issued.
2. Gift cards manually voided (with no refund).
3. Loyalty dollars issued.
4. Loyalty dollars voided.
5. Store credits manually issued.
6. Layaways canceled (with no refund)
7. Open Orders canceled (with no refund)

STEP 2 – Setting Up Department QuickBooks Categories #

For each Department in RetailEdge, you can optionally associate a QB sales account. This can be a top level income account or a subcategory (which is also an income account). If you use a top level income account, enter the sales account name into the Department’s “QuickBooks Category” field (e.g. Sales).

Sales Subcategories #

If you are using subcategories, then you need to enter a special value into the Department’s “QuickBooks Category”.

Quickbooks Desktop: The value needs to be a combination of the top level income account and the subcategory. For example, if you had a top level account called “Sales” and a subcategory called “RetailEdge-MISC”, then the correct value would be “Sales:RetailEdge-MISC”.

QuickBooks Online:  Do not include the top level account information. Instead, use the name of the subcategory itself. Example: RetailEdge-MISC.

STEP 3 – Entering the QuickBooks Data File Location #

You need to tell RetailEdge where to find the QB data file. In RetailEdge, go to Tools | Settings | Workstations. Modify the appropriate workstation.

Go to the Misc Option and enter the “QuickBooks File Name:”.

STEP 4 – Entering Payment Methods In QuickBooks #

In QuickBooks, you need to make sure you have Payment Methods that match all the payment methods in RetailEdge. If you are taking Debit Cards through integrated credit card processing you will want to add one additional Payment Method for “Debit Card” to account for this reserved payment method.

In QB, go to Lists | Customer & Vendor Profile lists | Payment Method List. For each RetailEdge payment method, add a QB Payment Method. The name needs to match the RetailEdge value exactly.

STEP 5 – Performing a Sample Export #

Try running the export on a sample QB file (use a sample file so that “real” data can’t get messed up).

Please Note: If you are using QuickBooks Desktop, QuickBooks needs to be installed on the same computer where you are running RetailEdge.

Also, QuickBooks may issue a security alert, when a third-party application such as RetailEdge attempts to touch its data. This is normal and you should instruct QuickBooks to allow this operation.

Testing and Troubleshooting Suggestions #

Testing the Export #

When testing the export configuration, we recommend that you future-date the exports. This will make things easy to locate in QuickBooks and will make it easier to remove the test data.

Unwinding the Export #

To unwind an export, you need to delete each generated Sales Receipt and/or Credit Memo and also delete the single General Journal Entry.

Locating the Receipts and Entries #

There are many ways to locate Sales Receipts and General Journal Entries but one easy way is to generate a Profit and Loss report, filtered for the future-dated export date. From this report, you can drill into the Net Income line and see the contributing components. You can delete a Sales Receipt by drilling into it and then select Delete.

Mapping Errors #

If you encounter a mapping error, it’s possible the Sales Receipts will be exported to QuickBooks but the General Journal Entry will not. In this case, you only need to remove each Sales Receipt associated with the export.

One note: It’s easier to remove test data while items are still in the Undeposited Funds account. If funds have been deposited, you will need to delete the deposits before you can remove the Sales Receipts.

Problems with Exporting Sales Tax #

We had customers have trouble with the sales tax portion of the export.   Sometimes this is displayed as QB Error Code 3180: Sales Tax Detail Line must have a Vendor

It might be necessary to create a new sales tax account (Other Current Liability) instead of using the default, built-in, one. We tried this on the customer’s QB data – we added a new account called “Sales Tax2” – and the export worked successfully.

Be sure to map RetailEdge over to the new account as well.

How to Know The Last Deposit from RetailEdge #

RetailEdge does not list whether a deposit was exported to QuickBooks. The best way to make sure that you don’t duplicate deposit exports from RetailEdge is to open QuickBooks (QuickBooks must be loaded on the machine where the export takes place) and look at the checking account register. This will quickly let you see what the last deposit taken to QuickBooks was and you can then match this to the RetailEdge closing report dates.