When dealing with large-scale item pricing updates in NetSuite, the standard import template may not always be flexible enough to meet specific needs. In such cases, a custom script can be a powerful tool.
The standard import template for item pricing in NetSuite has limitations that may not suit all business requirements. Preparing the template file correctly can be cumbersome and time-consuming. To streamline the process and introduce more flexibility, we use a custom Map/Reduce script.
The script reads data from an Excel file, processes it, and updates item pricing details in NetSuite. The main operations include:
- Reading the Excel File: The script reads the Excel file containing item pricing information.
- Grouping Item Data: It groups the data by item and price level.
- Updating Item Records: It updates the item records in NetSuite with the new pricing details.
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
* @NModuleScope SameAccount
* @NAmdConfig ./JsLibraryConfig.json
*/
define(['N/record', 'N/search', 'N/file', 'N/runtime', 'xlsx'],
/**
* @param{record} record
* @param{search} search
* @param{file} file
* @param{runtime} runtime
* @param{XLSX} XLSX
*/
(record, search, file, runtime, XLSX) => {
const getInputData = (inputContext) => {
try {
// Define the headers of the file
const fileHeaders = ["Internal ID", "Name", "UK Item Defined Cost (GBP)", "Base Price", "Currency", "Quantity Range", "Minimum Quantity", "Maximum Quantity", "Price Level", "Unit Price"];
// Get file from script parameter using runtime module
const fildId = runtime.getCurrentScript().getParameter({ name: 'custscript_jj_file_id' });
const fileObj = file.load({ id: fildId });
const fileContents = fileObj.getContents();
const fileData = getDataFromEXCEL(fileContents);
let groupData = [];
if (JSON.stringify(fileData[0]) === JSON.stringify(fileHeaders)) {
groupData = groupItemData(fileData);
}
return groupData;
} catch (error) {
log.error('error@getInputData', error);
return [];
}
}
const reduce = (reduceContext) => {
try {
let values = JSON.parse(reduceContext.values);
// log.debug('reduce - values', values);
let itemUKInternalId = findItemId(values.itemname);
log.debug('itemUKInternalId', itemUKInternalId);
if (itemUKInternalId) {
let itemObj = record.load({ type: record.Type.ASSEMBLY_ITEM, id: itemUKInternalId, isDynamic: true });
// Set the base price to the Item record
itemObj.setValue({ fieldId: 'baseprice', value: values.baseprice });
// Set the defined cost to the Item record
itemObj.setValue({ fieldId: 'costestimate', value: values.defiendCost });
const groupedByCurrency = splitCurrencyData(values.pricelevel);
// log.debug('groupedByCurrency', groupedByCurrency);
// Loop through the keys of the JSON object
Object.keys(groupedByCurrency).forEach(function (currency) {
const groupedByPriceLevel = splitPriceLevelData(groupedByCurrency[currency]);
// log.debug('groupedByPriceLevel', groupedByPriceLevel);
Object.keys(groupedByPriceLevel).forEach(function (pricelevel) {
const sortedData = groupedByPriceLevel[pricelevel].slice().sort(function (a, b) {
return a.minimumQuantity - b.minimumQuantity;
});
for (let i = 0; i < sortedData.length; i++) {
let priceLevelData = sortedData[i];
// log.debug('priceLevelData', priceLevelData);
// find sublist id based on the curreny
const sublistId = findPriceLevelSublistId(priceLevelData.currency);
if (sublistId) {
// Find the price level quantity body field based on the price lvel minimum quantity
let quantityRange = i + 1;
let quantityFieldId = sublistId + 'quantity' + quantityRange;
let priceFieldId = 'price_' + quantityRange + '_';
// log.debug('quantityFieldId', quantityFieldId);
// log.debug('priceFieldId', priceFieldId);
// set the price level quantity body field
itemObj.setValue({ fieldId: quantityFieldId, value: priceLevelData.minimumQuantity });
// find sublist line number based on the price level
const line = itemObj.findSublistLineWithValue({ sublistId, fieldId: 'pricelevelname', value: priceLevelData.pricelevel });
if (line >= 0) {
itemObj.selectLine({ sublistId, line });
itemObj.setCurrentSublistValue({ sublistId, fieldId: priceFieldId, value: priceLevelData.unitPrice });
itemObj.commitLine({ sublistId });
} else {
log.error('Pricelevel Sublist line number not found', 'Item: ' + values.itemname + ' Price Level: ' + priceLevelData.pricelevel + ' Currency: ' + priceLevelData.currency);
}
} else {
log.error('Pricelevel Sublist not found', 'Item: ' + values.itemname + ' Currency: ' + priceLevelData.currency);
}
}
});
});
itemObj.save({ ignoreMandatoryFields: true });
} else {
log.error('Item not found', values.itemname);
}
} catch (error) {
log.error('error@reduce', error);
}
}
const summarize = (summaryContext) => {
// Summarize added to find the Map/Reduce script excution end time.
}
/**
* Reads the contents of an Excel file and returns the data as a 2D array.
* @param {string} fileContents - The base64-encoded contents of the Excel file.
* @returns {Array<Array<any>>} - The data from the Excel file as a 2D array.
*/
function getDataFromEXCEL(fileContents) {
try {
let data = [];
let workbook = XLSX.read(fileContents, { type: 'base64' });
let sheetName = workbook.SheetNames[0]; // Assuming the first sheet
let sheet = workbook.Sheets[sheetName];
let range = XLSX.utils.decode_range(sheet['!ref']);
for (let rowIndex = range.s.r; rowIndex <= range.e.r; rowIndex++) {
let row = [];
for (let colIndex = range.s.c; colIndex <= range.e.c; colIndex++) {
let cellAddress = XLSX.utils.encode_cell({ r: rowIndex, c: colIndex });
let cellValue = sheet[cellAddress] ? sheet[cellAddress].v : '';
row.push(cellValue);
}
data.push(row);
}
return data;
} catch (error) {
log.error('error@getDataFromEXCEL', error);
}
}
/**
* Groups the data based on internalId and returns an array of items with their price level details.
* @param {Array<Array<any>>} data - The data to be grouped.
* @returns {Array<Object>} - An array of items with their price level details.
*/
function groupItemData(data) {
try {
let items = {};
data.shift(); // Remove the header row
data.forEach(function (row) {
const internalId = row[0];
const itemName = row[1];
const defiendCost = row[2];
const basePrice = row[3];
const currency = row[4];
const quantityRange = row[5];
const minimumQuantity = row[6];
const maximumQuantity = row[7];
const priceLevel = row[8];
const unitPrice = row[9];
// If the item does not exist in the items object, create it
if (!items[internalId]) {
items[internalId] = {
internalid: internalId,
itemname: itemName,
defiendCost: defiendCost,
baseprice: basePrice,
pricelevel: []
};
}
// Push the price level details to the item's price level array
items[internalId].pricelevel.push({
currency: currency,
quantityRange: quantityRange,
minimumQuantity: minimumQuantity,
maximumQuantity: maximumQuantity,
pricelevel: priceLevel,
unitPrice: unitPrice
});
});
return Object.values(items);
} catch (error) {
log.error('error@groupItemData', error);
return [];
}
}
/**
* Finds the internal ID of an inventory item by its name.
* @param {string} itemname - The name of the inventory item.
* @returns {string|null} - The internal ID of the inventory item, or null if not found.
*/
function findItemId(itemname) {
try {
const inventoryitemSearchObj = search.create({
type: "assemblyitem",
filters: [
["type", "anyof", "Assembly"],
"AND", ["name", "is", itemname]
],
columns: [search.createColumn({ name: "internalid" })]
});
let internalId = null;
if (inventoryitemSearchObj.runPaged().count > 0) {
inventoryitemSearchObj.run().each(function (result) {
internalId = result.id;
});
}
return internalId;
} catch (e) {
log.error('error@findItemId', error);
return null;
}
}
/**
* Finds the sublist ID based on the given currency.
*
* @param {string} currency - The currency to find the sublist ID for.
* @returns {string|null} - The sublist ID if found, otherwise null.
*/
function findPriceLevelSublistId(currency) {
try {
let sublistId = '';
switch (currency) {
case "British pound":
sublistId = 'price1';
break;
case 'USA':
sublistId = 'price2';
break;
case 'Canadian Dollar':
sublistId = 'price3';
break;
case 'Euro':
sublistId = 'price4';
break;
case 'AUD':
sublistId = 'price5';
break;
default:
sublistId = null;
break;
}
return sublistId;
} catch (e) {
log.error('error@findPriceLevelSublistId', error);
return null;
}
}
/**
* Splits the given data array into groups based on currency.
*
* @param {Array} data - The data array to be split.
* @returns {Object} - An object containing the data grouped by currency.
*/
function splitCurrencyData(data) {
try {
let groupedByCurrency = {};
// Iterate through the data array
data.forEach(function (item) {
let currency = item.currency;
if (!groupedByCurrency[currency]) {
groupedByCurrency[currency] = [];
}
groupedByCurrency[currency].push(item);
});
return groupedByCurrency;
} catch (error) {
log.error('error@splitCurrencyData', error);
return {};
}
}
/**
* Splits the given data array into an object grouped by price level.
*
* @param {Array} data - The data array to be split.
* @returns {Object} - An object containing the data grouped by price level.
*/
function splitPriceLevelData(data) {
try {
// Initialize an empty object to store the result
let groupedByPriceLevel = {};
// Iterate through the data array
data.forEach(function (item) {
let priceLevel = item.pricelevel;
if (!groupedByPriceLevel[priceLevel]) {
groupedByPriceLevel[priceLevel] = [];
}
groupedByPriceLevel[priceLevel].push(item);
});
return groupedByPriceLevel;
} catch (error) {
log.error('error@splitPriceLevelData', error);
return {};
}
}
return { getInputData, reduce, summarize }
});