NetSuite really excels in its import offering for both creating and updating records. Records of almost any type can be generated using a CSV template and current records in the system can also be updated with new data for one or many fields.
The process of mapping CSV fields against their NetSuite equivalents is reasonably straight forward and intuitive and import mappings can even be saved to save time on scheduled activities.
Not all imports are created equal though. One of the more cumbersome is the NetSuite inventory adjustment import. Most NetSuite records can be created with just one CSV file. Inventory Adjustments can take up to three separate files so warrant their own tutorial. To begin with though, what is an Inventory Adjustment?
What is an Inventory Adjustment?
An Inventory Adjustment is a posting transaction that facilitates one of the following –
- Increase of Inventory
- Decrease of Inventory
- Movement of Inventory
The key is in the name – Adjustment.
This record type allows you to adjust inventory outside of any sales or purchase process. You can also use the Inventory Adjustment to alter the value of held inventory without affecting the quantities.
As it is a posting transaction with a potential financial impact, Inventory Adjustments cannot be deleted or edited in closed periods. The relevant accounting personnel should be consulted if any changes need to be made to inventory in past periods.
|You can learn everything there is to know about Inventory Adjustments here, including how to post one manually through the UI.
The Components of an Inventory Adjustment
Most NetSuite records are made up of two key components – Header information and the sublist. The Inventory Adjustment is no different. In the header, you record the primary data such as the ledger account being adjusted, the date of the adjustment and a memo to describe its purpose. The sublist is then the item or items being adjusted. The three most simple scenarios are as follows –
- A single line increasing the held stock of one item
- A single line decreasing the held stock of one item
- Two lines increasing and decreasing the same quantity of the same item to facilitate the movement from one location to another.
The additional layer that is added when dealing with Inventory is the Inventory Detail. Each line of the record will have an Inventory Detail attached. The Inventory Detail states the quantity of each Lot/Serial Number that is being adjusted (Learn more about Serialized and Lot Numbered items). Depending on your Inventory set up this may include the bin number or expiration date of the item as well.
Preparing a NetSuite Inventory Adjustment Import
You may be used to importing records into NetSuite via the CSV Import feature. Most records, even when there is Inventory involved, can be imported using one file. The Inventory Adjustment uses three.
File 1 – The Header Information
This file will include the primary information. Every NetSuite instance is slightly different but as a minimum this file must contain the Date, Adjustment Account and, crucially, the External ID. The External ID is what will be used to link the first and second file.
File 2 – Adjustment Items
This file contains details about the actual items being adjusted. Fields should include the Item Name, the Quantity being adjusted, the Location and two External IDs. The first of these External IDs will link every line back to the first file. The second ID will link each individual line of this file to an inventory detail on the third file.
File 3 – Inventory Details
Each line of file three will state one of the second External IDs from file 2. This will link each Inventory Detail with a line item. The minimum fields included here will be Lot/Serial Number and Quantity.
Save all three files as CSV and name them intuitively. The easiest option is to number them one to three as I have done above. When uploading the files, you need to select them in the right order so numbering them this way avoids any confusion.
Importing the Inventory Adjustment CSV files
Navigate to Setup > Import/Export > Import CSV Records. Select Transactions as Import Type and then Inventory Adjustment for Record Type. At the bottom of this first screen change the One File To Upload to Multiple Files. Here you will upload your files. Do so in the order explained above.
On the File Mapping page, you will link the first and second file using the External ID. You will also link the second and third file using the secondary External ID. In the examples I have screenshot this is labelled as External ID 2.
The rest of the import process is no different to any other. On the Field mapping page, you must associate each field in the CSV files with their NetSuite counterpart. Then, lastly, on the Save Mapping page you can choose to name your import and save it prior to running.
Once the request has been made to run, you can navigate to the Job Status page to monitor the progress of the import. If the update fails an error message will be recorded in a CSV file that can be downloaded from this page. If the update is successful you can navigate to Transactions > Inventory > Adjust Inventory > List to check everything looks as it should.