The CSV import tool is normally a sufficient method to import data into NetSuite, however the Unit of Measure 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 (Eg: Case, Box, Piece). The smallest unit is selected as the base unit while the others are a multiple of it. A typical UOM Schedule for a packaged good could be: 64 Pieces in a Case, 8 Pieces in a Box and 1 Piece as the base unit (See Image Below).
This record would then be assigned to an Item record (Note: Once you assign a UOM Schedule to and save the item you cannot change it in the future). While the feature is useful, what do you do when you have 300 UOM Schedules sitting in a CSV file and some of the conversions aren’t in the smallest base unit? Using Excel formulas can get you clean data, but SuiteScript 2.0 steps up to the plate to save you from creating the records manually.
Let’s start off with cleaning our data using Excel formulas. As you can see in the picture above, the Unit of Measure Schedule BX/12PERCS has its base unit as BX when it should be PC. To get the true conversion factor for each line we need the following formula =(1/INDEX(A:D,MATCH(A2,A:A,1),4))*D2. Now for this formula to work we need to sort the uomschedulID from A à Z then equivanlentUOM from largest to smallest. The inside MATCH function is going to give us the last row value for the specified uomschedulID in column A, and the INDEX function is going to give us the equivanlentUOM value of that row. Since we sorted the equivanlentUOM field by largest to smallest, this will always give us the smallest conversion rate for each UOM Schedule and identify the unit type that we’ll use as the Primary Unit in NetSuite. Column I’s formula =IF(E1=1, TRUE, FALSE), serves as a reference for our code to know whether or not the row it’s populating in the UOM Schedule record is the base unit. Now that our data is clean it’s time to upload this CSV file to NetSuite now that our data is nice and clean.
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. We’re going to populate the Script Parameter for our script with this Internal ID, that way our code knows what file to use and if we need to upload a new file in the future, we can change the parameter value to the new file’s Internal ID. Using the file module, we’re going to get the contents of the CSV file and then parse the data to get all the rows into an array. Then we’re going to leverage our setIndexOfKeys function so we can get the index of all our header fields. These keys will be used to get the values we need to build a UOM Schedule Record. As we parse through the lines, we want to make sure we keep a record of the last UOMScheduleID. When we get to a line that’s current UOMScheduleID is not equal to the previous one then it’s time to save the record and create a new one. Now we can 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’s staged in the CSV file.
Want to ensure your business is getting the most out of your NetSuite usage and investment? Learn more about our NetSuite Support services.