The comma-separated value (CSV) import tool is normally a sufficient method to import data into NetSuite. However, the Unit of Measure (UOM) Schedule does not fall under the supported CSV import record types. The UOM record gives an organization the ability to stock, purchase, and sell an item in multiple units. For example, Case, Box, and Piece.

Selecting the base unit for a NetSuite Unit of Measure Schedule

The smallest unit is selected as the base unit, and the others are measured as a multiple of that unit. A typical UOM Schedule for a packaged good could be 64 Pieces in a Case, eight Pieces in a Box, and one Piece as the base unit as shown in the image below.

Using SuiteScript 2.0 to simplify the process

This record would then be assigned to an Item record. Users should note that once they assign a UOM Schedule to an item and saves, it cannot be changed. While the feature is useful, users can run into challenges. One example is if they have 300 UOM Schedules within a CSV file and some of the conversions are not in the smallest base unit. Using Excel formulas can clean the data, but SuiteScript 2.0 provides added benefits, saving the time of creating records manually.

How to prepare to import a NetSuite Unit of Measure Schedule

Start with cleaning the data using Excel formulas. In the picture above, the Unit of Measure Schedule BX/12PERCS has its base unit as BX but should be PC. To get the true conversion factor for each line, the following formula is necessary:

=(1/INDEX(A:D,MATCH(A2,A:A,1),4))*D2.

For this formula to work, the uomschedulID column needs to be sorted from A to Z. Then, the equivanlentUOM column must be sorted from largest to smallest. The inside MATCH function gives the last row value for the specified uomschedulID in column A. The INDEX function gives the equivanlentUOM value of that row. Since the equivanlentUOM field was sorted by largest to smallest, this will always identify the smallest conversion rate for each UOM Schedule and identify the unit type that can be used as the Primary Unit in NetSuite. Column I’s formula, =IF(E1=1, TRUE, FALSE), serves as a reference for the code. This helps it know whether or not the row it is populating in the UOM Schedule record is the base unit. Now that the data is clean, it is time to upload this CSV file to NetSuite.

Understanding SuiteScript 2.0’s code when importing NetSuite Unit of Measure Schedules

The code below is part of a Scheduled Script run on demand.

A file receives an Internal ID when it is uploaded to the File Cabinet. Populate the Script Parameter for the script with this Internal ID, this way the code knows what file to use. Additionally, if a new file needs to be uploaded in the future, the parameter value can be changed to the new file’s Internal ID. Using the file module will help pull the contents of the CSV file and parse the data to get all the rows into an array. Then, leverage the setIndexOfKeys function to get the index of all the header fields.

These keys will be used to get the values necessary to build a UOM Schedule Record. Make sure to keep a record of the last UOMScheduleID. When encountering a line where the current UOMScheduleID is not equal to the previous one, then it is time to save the record and create a new one. Once these steps are complete, Save and Execute the Script Deployment record, and when the code is finished running, all the UOM Schedule Records will be in the system exactly the way it is staged in the CSV file.

NetSuite support provided by Citrin Cooperman

Citrin Cooperman’s Digital Services Practice is skilled in helping business increase efficiency to get the most out of their NetSuite investment. To learn more about our NetSuite support and optimization services and how they can help your business achieve its strategic objectives, reach out to your Citrin Cooperman advisor or sales@citrincooperman.com.