Import Balance Adjustments

Balance adjustments allow you to make adjustments to the value of a product that you currently have in stock. Balance adjustments can be made within Acctivate  or by importing an Excel spreadsheet containing the balance adjustments. Spreadsheets can contain multiple balance adjustments for multiple Product ID’s. Each row should contain information for one Product ID.

  1. Access the import data window in Acctivate by going to File > Import Data.
  2. Under the Inventory folder, select Balance Adjustments and then click Create Mapping
  3. The second page of the mapping editor allows you to select the source of your import. Select the spreadsheet you would like to import by choosing the file name. If the spreadsheet has multiple worksheets, choose the available worksheets from the drop down menu.  If the first row of the spreadsheet contains the description of the column, check the box titled First row of spreadsheet contains field names.
  4. Give the import an ID, Name, and Description for future reference so you can save for future use.
  5. Click on the Columns tab in order to view the list of fields to map.
  6. The categories Detail, Session and Transaction can be expanded to show the different fields that can be mapped. Product ID is a required field. All mapped fields will correspond to the Product ID mapped.  From the drop down menu next to each Acctivate field, select the column which corresponds to that field. The mapped Acctivate field will appear under the column name from the spreadsheet. The fields listed at the end of the article are available for import.
    Balance Adjustment map
  7. After mapping is complete, click Begin Import. You will be prompted to save this mapping. If you choose to save the mapping, you will need to give it a name. This will allow you to update the spreadsheet without requiring each field to be mapped again.
  8. The next screen (Data Editor Window) will tell you how many records passed. If any rows failed it will list them and identify why the rows failed. You will have a chance to export the failed rows to a spreadsheet for cleanup on the following screen. After reviewing the rows that passed and failed, click next.
  9. The Import Confirmation window will load all the rows for import into Acctivate. Once the rows are ready for import, the finish button will become available to select. Click Finish. This will prompt you to backup, which is recommended.
  10. Once the backup completes, the import will begin. If you had any failed rows, a window will appear giving you a chance to save a spreadsheet of all the rows that failed. Selecting Yes to this option will prompt you to select a location for saving this spreadsheet. After saving, click close to exit the Import Balance Adjustment tool.
Data Field Type(Length) Notes
Detail
Amount Number Extended Line Amount (e.g., Quantity * Unit Cost
Description Char(4095) Item Description (defaults to Product Description)
Location Char(80) Warehouse Bin/Location for inventory
Lot/Serial # Char(25) Lot/Serial Control Number for this inventory. NOTE: One row in Excel file should exist for each unique lot/serial number in the Balance Adjustment
Lot/Serial Exp Date Date Formatted according to local Windows Date/Time settings
Lot/Serial Reference Char(50) Short, one-line reference number or code for lot/serial numbered items
Lot/Serial Specification Char(4095) Longer, more detailed (potentially mutli-line) description for lot/serial numbered items
Product ID Char(40) Required. Product ID existing in Acctivate
Quantity Number Quantity on Hand at the end of the day based on Session Date
Unit Char(5) Unit of Measure, corresponds with Stocking Unit listed on Item
Unit Cost Number Unit Cost corresponding to above Unit of Measure
Session
Session Date Date Date of accounting transaction to update Acctivate inventory valuation. This is also the date for the journal entry. Defaults to today’s date. Balance Adjustments go into affect as the last transaction of the day so be sure to make this date the day you want the Quantity and Value to reflect what’s being posted.
Session ID Number Not recommended. Acctivate will assign the next available number from the Numbers section of Configuration Management.
Transaction
Description Char(50) Short, one-line description of Inventory Balance Adjustment transaction as a whole
Document Date Date Non-accounting date for receiving document.  This is typically the date from the Balance Adjustment document.
Exchange Rate Number Multi-Currency only.  This allows overrides to current exchange rate for vendor’s currency.
Receiving Document Char(15) Reference Number for Receiving Document, typically the Bill of Lading Number or Manifest/Shipment Number
Reference Char(20) An additional reference number from receiving document
Tax Included Yes/No Canada and UK editions only.  Does Unit Cost include VAT?
Transaction ID Number Not recommended.  Acctivate assigns an incremental number for each transaction within a session (i.e., 1, 2, 3…)
Warehouse Char(6) Required field