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.
- Access the import data window in Acctivate by going to File > Import Data.
- Under the Inventory folder, select Balance Adjustments and then click Create Mapping
- 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.
- Give the import an ID, Name, and Description for future reference so you can save for future use.
- Click on the Columns tab in order to view the list of fields to map.
- 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.
- 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.
- 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.
- 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.
- 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.
|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 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.|
|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…)|
Import Data Specs
• Import Customer Information
• Import Customer Product IDs
• Import Inventory Receipts
• Import Inventory Transfers
• Import Lot/Serial Numbers
• Import Products
• Import Product Class
• Import Product Components
• Import Product Prices
• Import Product Type
• Import Alternate Product IDs
• Import Shipments
• Import Warehouse Locations
• Sales Order Import Mapped Fields