How to configure the Microsoft Dynamics RMS Store Operations QuickBooks integration

Summary
Microsoft Dynamics Retail Management System (RMS) Store Operations includes an integration that works directly with Intuit QuickBooks. The only supported edition of QuickBooks is QuickBooks Pro. Other editions of QuickBooks may work. However, they have not been tested and therefore would not be supported.

The following versions of QuickBooks Pro are supported:
  • QuickBooks 2009
  • QuickBooks 2010
  • QuickBooks 2011
  • QuickBooks 2012
  • QuickBooks 2013
  • QuickBooks 2014
  • QuickBooks 2015
  • QuickBooks 2016
Notes
  • Both QuickBooks and Store Operations must be running on the same computer.
  • For operating systems that have User Account Control (UAC) enabled, you will have to make a modification to the Store Operations Manifest file.

    Follow these instructions to update the Store Operations Manager Manifest file so that the integration will work with operating systems that have UAC enabled:
    1. Locate the SOManager.exe.Manifest file in the following location:
      C:\Program Files\Microsoft Retail Management System\Store Operations
    2. Open the file in Notepad, and then make the following change to this section:
      <requestedExecutionLevel level="requireAdministrator"></requestedExecutionLevel>
      Change this section to the following:
      <requestedExecutionLevel level="AsInvoker"></requestedExecutionLevel>
The Store Operations QuickBooks integration includes the following five stages:
  1. Configure QuickBooks to interact with Store Operations.
  2. Configure Store Operations to interact with QuickBooks.
  3. Import data from QuickBooks.
  4. Assign general ledger accounts in Store Operations Manager.
  5. Export accounting and purchase order data directly to QuickBooks.
After you complete the installation, you can export accounting data directly in the QuickBooks general ledger. Additionally, you can export closed purchase orders as QuickBooks bills.

Microsoft Dynamics Retail Management Systems technical support cannot advise you about how to set up your general ledger account assignment tables or where and when to post the data to QuickBooks. Technical support strongly urges you to obtain advice from a qualified professional. This article only contains information to help you integrate Store Operations with QuickBooks.
INTRODUCTION
This article discusses how to integrate QuickBooks with Store Operations.
More information

Stage 1: Configure QuickBooks to interact with Store Operations

  1. Start QuickBooks.
  2. Click Accnt on the standard toolbar or click Lists, and then click Chart of Accounts to view the Chart of Accounts.

    Note To view the accounts that you may have to add in Store Operations, start Store Operations Manager, click Journal, and then click Assign general ledger Accounts.
  3. Click Edit, and then click New Account to add any accounts that you may want to track. For example, add accounts if you want to track sales, commissions, and cost of goods sold (COGS) for each department.

    Note There are also two accounts that you must add in QuickBooks. For more information, see step 5.
  4. Add the remaining information in the New Account screen, and then click OK.

    Note QuickBooks does not permit more than one Accounts Payable or Accounts Receivable account type per transaction. Therefore, you must use Other Current Assets and Other Current Liabilities instead of Accounts Receivable and Accounts Payable if you are setting up a new account that would ordinarily use these account types.
  5. Click Edit, and then click New Account to create a new Sales Tax Payable account. This name must be different from the default QuickBooks account. For example, name the account Sales Tax Payables.

    Note You cannot use the default Sales Tax account in QuickBooks when you assign any one of the Sales Tax accounts in Store Operations. You must create a new Sales Tax account in QuickBooks.
  6. Click Other Current Liabilities for Account Type.
  7. Add the remaining information in the New Account screen, and then click OK.
  8. Click Edit, and then click New Account to create a new Accounts Receivable account. This name must be different from the default QuickBooks account. For example, name the account AR.

    Note You cannot use the default Accounts Receivable account in QuickBooks when you assign either the Store Account: Paid On or the Store Account: Paid To accounts in Store Operations. You must create a new Accounts Receivable account in QuickBooks because the standard Accounts Receivable account type in QuickBooks requires more information than Store Operations provides.
  9. Click Other Current Assets for Account Type.
  10. Add the remaining information in the New Account screen, and then click OK.
  11. Determine the account that you want to use for the Bills account. This account offsets the Account Payable account when a QuickBooks Bill is created. You or your accountant determine this account.

    Note You cannot use the default Accounts Payable account in QuickBooks when you assign the Bills Account in Store Operations. Store Operations automatically posts to the Accounts Payable account in QuickBooks when you are posting the closed Purchase Orders. Therefore, you must select a different account.
  12. If you do not want to use any one of the accounts that are listed for the Bills account, you must create one. If an account already exists, go to the "Part 2: Set up Store Operations to interact with QuickBooks" section.
  13. Click Edit, and then click New Account to create a new account.
  14. In the Type list, click the account type.

    Note You or your accountant must determine the account type, but it cannot be Accounts Payable.
  15. Add the remaining information in the New Account screen, and then click OK.

    Note If you assign vendors or customers to this account, you may cause errors during posting.

Stage 2: Set up Store Operations to interact with QuickBooks

In this stage, you select QuickBooks as your accounting software and connect to the QuickBooks company file.
  1. Start QuickBooks.
  2. Sign in to your company as a user with administrator credentials.
  3. Start Store Operations Manager.
  4. Click File, and then click Configuration.
  5. Click the Accounting tab.
  6. In the Accounting Information pane, click QuickBooks 2003 in the list.

    Note Click QuickBooks 2003 even if you are using a later version of QuickBooks.
  7. To connect with QuickBooks and to retrieve information about the company file, click Retrieve in the Company Information pane.

    Note If the Internet Security Levels Are Set Too High dialog box appears, you must click Make Changes or Cancel. If you click Cancel, you must click Yes to confirm your selection. If you click Cancel, you may prevent some QuickBooks features from working correctly.
  8. In the QuickBooks - Application Certificate window, use one of the following methods:
    • Click Yes, Always to grant permanent access from Store Operations to your QuickBooks company file.
    • Click Yes, This Time to grant access on this occasion only. You will be prompted for authorization on later attempts.
  9. After you connect to QuickBooks and retrieve the company information, click OK two times.
  10. For Store Operations to automatically log on to QuickBooks, follow these steps.

    Note Every time that you interact with QuickBooks in Store Operations, the QuickBooks program must be running. You can start QuickBooks manually and log on to the company file, or your QuickBooks administrator can set up the automatic logon feature. When you use the automatic logon feature, QuickBooks does not have to be running.
    1. Start QuickBooks.
    2. Click Edit, and then click Preferences.
    3. Click Integrated Applications in the left pane.
    4. Click the Company Preferences tab.
    5. Click Retail Management System, and then click Properties.
    6. Select the Allow this application to log on automatically check box.
    7. Click the user's logon name, and then click OK two times.

      Note The user must have the correct access permissions.

Stage 3: Import data from QuickBooks

The Import Data from QuickBooks Store Operations function imports the Chart of Accounts from QuickBooks. You can also import Payment Terms, Customers, Suppliers (Vendors), and Items if these terms are defined in QuickBooks.

Note The integration does not allow you to import Payment Terms, Customers, Suppliers (Vendors), and Items from Store Operations to QuickBooks.
  1. Start Store Operations Manager.
  2. Click Utilities, and then click Import QuickBooks.

    Note If Import QuickBooks is not listed, follow the instructions in the "Part 2: Set up Store Operations to interact with QuickBooks" section to select QuickBooks 2003 as your accounting software.
  3. Select the data that you want to import. You can select Accounts, Payment Terms, Suppliers (vendors), Customers, and Items if these terms are defined in QuickBooks.

    Note We recommend that you import information in the default order that appears in the Import QuickBooks window. If you change this order by re-sorting the columns, data may not be imported with the correct relationships. For additional information about importing, search for the "Import QuickBooks Option" topic in Store Operations Manager Help.

  4. Click Import. The status appears as the data is imported.

    Note If a record already exists in Store Operations, you are prompted to select an import conflict resolution. You can update Store Operations with the QuickBooks record, create a new record, or not import the record.

    Note Because the Chart of Accounts and Payment Terms is maintained in QuickBooks, Store Operations automatically replaces the Account and Term records with the QuickBooks imported records. You will not be prompted to select an import conflict resolution. If prompted, select a resolution. To do this, use one of the following methods:
    1. To apply the resolution only to the current record, click Apply.
    2. To apply the resolution to all records of the same type, such as all Customer records, click Apply to All.
    3. To stop the import, click Abort.

      Note If you abort the import, you can verify what records were imported before the interruption. To view the list of records, click Items, Customers, or Suppliers on the Database menu. To use the list to view the imported accounts, click Assign General Ledger Accounts on the Journal menu. You can view imported terms in the Supplier Properties window.

Stage 4: Assign general ledger accounts in Store Operations Manager

You must set up the account assignment table in Store Operations Manager. Microsoft strongly recommends that an accountant set up this table. QuickBooks does not accept a general journal import file if the debits and credits do not balance.

Every time that you add a new tender or tax type, you must update the general ledger account assignments to incorporate these additions. Use the following table to guide you through the types of general ledger accounts that you must enter for each account type listed. The table provides some additional information about these accounts. The table assumes that you are using accounts for Tender Received, Tender Opening, and Tender Closing.

Table: QuickBooks general ledger account setup in Store Operations
DescriptionType of debit accountType of credit accountAdditional Details
Inventory: Cost of Goods SoldCost of Goods SoldInventoryThe total cost of items sold. Enter account numbers in these accounts if you want to track this value as a total for all departments. If you want to track this value per department, leave these accounts blank and enter your account numbers in the Dept xxxx: Cost Account.
CommissionSelling ExpenseOther Current LiabilitiesThe commission payable to sales reps. Set up this account if you want to track this value for every department. If you want to track this value per department, leave these accounts blank and enter your account numbers in the Dept xxxx: Commission Account.
Customer Deposit: MadeOther Current LiabilitiesThe total deposits that are made from work orders on customer accounts.
Customer Deposit: RedeemedOther Current LiabilitiesThe total deposits that are redeemed when work orders are completed.
Layaway: ClosedOther Current LiabilitiesThe layaway amount that is closed when the customer received the goods. Store Operations automatically credits the Sales and Tax accounts to balance the posting.
Layaway: PaidOther Current LiabilitiesThe amount paid by customers on new or existing layaway items. Store Operations automatically debits the Payment Received accounts to balance the posting.
Sales: TotalSalesThe total sales amount not including tax. If you post this account, you must not post the Sales: Total + Tax account to avoid redundant posting. Set up this account if you want to track this value for all departments and track taxes separately. If you want to track this value per department, leave these Accounts blank and enter your account numbers in the Dept xxxx: Sales account.
Sales: Total + TaxSalesThe total sales amount plus tax. If you post this account, do not post the Sales: Total or any Tax Collected accounts to avoid redundant posting. Only use this account if you do not track sales by department or sales taxes separately.
Shipping Charge: TotalOther IncomeThe amount that is collected for shipping charges. Expense it out through the general ledger program.
Store Account: Paid OnAccounts ReceivableCharges made to Customer Accounts. The offset is to Sales and Tax Collected.
Store Account: Paid To Accounts ReceivablePayments from customers to reduce their customer balance. The offset is to Tender Received.
Surcharges: Cash BackOther IncomeThe amount collected for fees that are charged to customers when they receive cash back on their debit purchases. Expense out any fees that are charged to you through the general ledger program.
Surcharges: DebitOther IncomeThe amount collected for fees that are charged to customers when they use their debit card for purchases. Expense out any fees that are charged to you through the general ledger program.
Tax Collected: Total Taxes PayableThe total amount that is collected for sales taxes for all departments. You can post Tax Collected for the individual tax types or for the total tax that is collected. If you post Tax Collected: Total Account, do not post the individual Tax Collected: xxxx accounts to avoid redundant posting. A Tax Collected: xxxx entry is created for each type of tax in Store Operations. Therefore, every time that you add a new tax type, you must update this account to make sure that the posting is correct.
Cash DroppedCashCashThe cash amount that is taken out of the cash drawers for deposit during the cashiers' shifts.
Cash Paid OutExpensesCashThe cash amount that is paid out from the cash drawers (for minor miscellaneous expenses).
Tender Rounding ErrorCashThis is the rounding error that may occur when you accept multiple currencies. For example U.S. and Canadian dollars. This error may also occur if pennies are not included in transactions.
Tender Closing Amounts: xxxxCash or Other AssetsCash or Other AssetsThe total amount that is available when you close the cash drawers for a particular tender type. A Tender Closing Amounts entry is created for each type of tender in Store Operations. Therefore, every time that you add a new tender type, you must update this account to make sure that the posting is correct. For more information, see the notes that appear after this table.
Tender Opening Amounts: xxxxCash or Other AssetsCash or Other AssetsThe total amount that is available when you open the cash drawers for a particular tender type. A Tender Opening Amounts entry is created for each type of tender in Store Operations. Therefore, every time that you add a new tender type, you must update this account to make sure that the posting is correct. For more information, see the notes that appear after this table.
Tender Over/Short: xxxxCash or Other AssetsThe amount that the tender type is over or short at the end of the batch. It reflects the errors that the cashiers made during tender transactions. A Tender Over/Short entry is created for each type of tender in Store Operations. Therefore, every time that you add a new tender type, you must update this account to make sure that the posting is correct. If you are using this account, leave the Tender Over or Tender Short accounts blank to avoid redundant posting.
Tender Over: xxxxCash or Other AssetsThe amount that the tender type is over at the end of the batch. It reflects the errors that the cashiers made during tender transactions. A Tender Over entry is created for each type of tender in Store Operations. Therefore, every time that you add a new tender type, you must update this account to make sure that the posting is correct. If you are using this account, leave the Tender Over or Tender Short accounts blank to avoid redundant posting.
Tender Received: xxxxCashThe total amount that is received for a particular tender type. This value is calculated by taking the Closing Amount - Opening Amount - Tender Dropped - Tender Paid. A Tender Received entry is created for each type of tender in Store Operations. Therefore, every time that you add a new tender type, you must update this account to make sure that the posting is correct. For more information, see the notes that appear after this table.
Tender Short: xxxxCash or Other AssetsThe amount that the tender type is short at the end of the batch. It reflects the errors that the cashiers made during tender transactions. A Tender Short entry is created for each type of tender in Store Operations. Therefore, every time that you add a new tender type, you must update this account to make sure that the posting is correct. If you are using this Account, leave the Tender Over or Tender Short Accounts blank to avoid redundant posting.
Tax Collected: xxxxTaxes PayableThe total amount that is collected for sales taxes for each department. You can post Tax Collected for the individual tax types or for the total tax collected. Enter account numbers in these accounts if you want to track value this separately for each sales tax. If you want to track taxes as a total, leave these accounts blank and enter your account numbers in the Tax Collected: Total. A Tax Collected: xxxx entry is created for each type of tax in Store Operations. Therefore, every time that you add a new sales tax, you must update this account to make sure that the posting is correct.
Dept. xxxx: CostCost of Goods SoldInventoryThe cost of the items that are sold per department. Enter account numbers in these accounts if you want to track this value separately for all departments. If you want to track this value as a total, leave these accounts blank and enter your account numbers in the Inventory: Cost of Goods Sold.
Dept. xxxx: CommissionSelling ExpenseOther Current LiabilitiesThe commission payable to sales reps by department. Set up this account if you want to track this value separately for every department. If you want to track this value as a total, leave these accounts blank and enter your account numbers in the Commission Account.
Dept. xxxx: SalesSalesThe sales amount not including taxes for the department. If you post this account, do not post Sales: Total + Tax or Sales: Total Account to avoid redundant posting. Set up this account if you want to track sales and taxes for each department separately. If you want to track this value as a total, leave these accounts blank and enter your account numbers in the Sales: Total + Tax or Sales: Total.
Department Tax RoundingCashA rounding error may occur when you calculate the tax per department in a VAT system. Use this option if you use VAT.
Note For the tender amounts to post correctly to your accounting system, type opening and closing amounts in the point of sale for each batch.

Note If you are using Tender Received Amount but not Tender Opening Amount or Tender Closing Amount, you must take the following actions:
  • Set credit and debit accounts for Tender Dropped and for Tender Paid Out.
  • Leave each Tender Opening and Tender Closing amount blank.
  • Set the debit amount for Tender Received.
Note If you are using the Tender Opening and Tender Closing Amounts, but not the Tender Received Account, you must take the following actions:
  • Set debit accounts for both Tender Dropped and Tender Paid Out and leave their credit accounts blank.
  • Leave Tender Received blank.
To assign general ledger Accounts, follow these steps:
  1. Start Store Operations Manager.
  2. Click Journal, and then click Assign general ledger Accounts.
  3. Click the Debit Account # box or the Credit Account # box of the general ledger account that you want to assign.
  4. Click the QuickBooks account from the list.
  5. Repeat steps 3 and 4 until all the required accounts are assigned.

    Note Not all general ledger accounts have an account that is assigned in the Debit Account # box or the Credit Account # box.
  6. Click the QuickBooks account that you are using for the Bills Account from the list if you want to post your closed purchase orders to QuickBooks as bills.
  7. Click OK to save your changes, and then close the Assign general ledger Accounts window.

Stage 5: Export accounting and Purchase Order data directly to QuickBooks

To export your accounting data directly in the QuickBooks general ledger and to export closed Purchase Orders as QuickBooks Bills, follow these steps:
  1. Start Store Operations Manager.
  2. Click Journal, and then click Post Closed Batches.

    Note Only closed batches are available to be posted.
  3. Click the batches that you want to post. Or, click Dates to open the Select Batch Range window to type a start date and an end date. When you click OK, all the batches that are closed and that are in this date range are selected, even if they have been previously posted.

    Note If you repost a batch that has already been posted, you may have duplicate general ledger and Bill entries in QuickBooks. Make sure that you delete duplicate records from QuickBooks.
  4. If you have set up a Bills Account in QuickBooks, select the Include Purchase Orders check box to import closed purchase orders that are included in the selected batches.

    Note Closed Purchase Orders are included in the closed batches. You can import Closed Purchase Orders into QuickBooks as bills when the batch is posted. Before you import Purchase Orders into QuickBooks, you must first select a Bills account in the account assignment table. The Bills account that you select will offset the Account Payables account when the bill is created in QuickBooks.

    The Store Operations Purchase Order exports the following information to the QuickBooks bill:
    Store Operations PO FieldQuick Books Bill Field
    Purchase Order NumberReference Number
    Supplier NameVendor
    Purchase Order Creation DateTransaction Date
    Total of the Purchase OrderAmount Due
    TermsPayment Terms
    One expense line for posting to the Account specified in the Assign Accounts Bill Posting Account
  5. Click Post to export the account data and the closed Purchase Orders.

    Note If you receive an error message when you post the closed batch, you must view the QBPost.log file that the error message indicates. Compare the QBPost.log file to the error message articles list in the "References" section. By default, the log file is in the C:\Program Files\Microsoft Retail Management System\Store Operations folder.
  6. Click OK to close the Post Closed Batches window that tells you how many batches were posted.
  7. The information that was posted is now available in QuickBooks.

    To create a General Journal report in QuickBooks, follow these steps:
    1. Click Reports, click Custom Transactions Detail Report, and then click the Filters tab.
    2. Click Transaction Type in the Filter list.
    3. Click Journal in the Transaction Type list.
    4. Click to select a date range, and then click OK.
    To view the new QuickBooks Bills, click Bill on the standard toolbar or click Vendors, and then click Pay Bills.

    Note The bills that you post may not be the last bills that are listed in QuickBooks.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
837803 A "Transaction split lines to Accounts Receivable must include a customer on that split line." event is logged in the QBPost.log event log when you post to Intuit QuickBooks
837804 Error message in the QBPost.log file after you try to post closed batches to QuickBooks from Microsoft Dynamics Retail Management System Store Operations: "Sales Tax Detail Line must have a Vendor"
838322 You receive an "Only one A/R or A/P account allowed per transaction" error message when you post a batch from Microsoft Retail Management System, Store Operations, to QuickBooks
838324 You receive a "Purchase order 'xxxxxx' in batch yy does not have a supplier" QuickBooks error message when you try to post a closed batch to QuickBooks
838323 You receive a "Transaction not in balance" error message in the QBPost.log file when you post to Intuit QuickBooks
918268 Error message when you try to post a batch from Store Operations to QuickBooks: "There is an invalid reference to QuickBooks Vendor <XXXXXX> in the Bill"
TK 32018
Properties

Article ID: 836444 - Last Review: 04/01/2016 14:55:00 - Revision: 11.0

Microsoft Dynamics Retail Management System Store Operations 2.0, Microsoft Retail Management System Store Operations 1.3, Microsoft Retail Management System Store Operations 1.2

  • kbexpertiseinter kbexpertisebeginner kbhowto kbmbsmigrate KB836444
Feedback