This article is relevant if you need to import data into NetSuite and the native CSV file tools are not supported.
Background
For a recent NetSuite implementation for a client that had over 70 subsidiaries, the client asked us if there was a way to import the subsidiary into NetSuite. Like many objects and tables in NetSuite, out-of-the-box, NetSuite does not support a subsidiary import. However, the subsidiary object is exposed to the SuiteScript environment. This is good news.
Fortunately, we have invented an import tool that we give to all of our clients, license-free, that can get this job done. See my 2020 article, Fully Automate Complex NetSuite Data Imports. While this tool was designed to produce a hands-free mechanism to create and update information in NetSuite (e.g., automating item fulfillment shipments or item receipts), it can also be used for infrequent jobs; like this one — to update subsidiary records.
Basic Mechanics of the NetSuite Import
The tool basically requires the following:
- File Layout: CSV, FlatFile, XML, and JSON is supported; make life easy, and get the data in an easy to understand format.
- Data Mapping: the file is going to be mapped to the NetSuite target object. A small script is written to give you full control (see below).
- Job Definition: describe the nature of the import job to the tool so that it can act.
The tool handles all the “plumbing” that helps get the data to the most important function: the mapping layer. Most NetSuite SuiteScript Developers will be comfortable with the task at hand.
Example Subsdiary SuiteScript Data Mapping Import Logic
Below is the actual plug-in we crafted to map the contents of a CSV file to create and update NetSuite subsidiaries. Notice the function is only focused on the actual data mapping work — all the other mechanics of getting the file to be read and set up are handled by the infrastructure.
/**
* @NApiVersion 2.0
* @NModuleScope Public
* @NScriptType plugintypeimpl
*/
define(['N/runtime','N/log','N/record','N/search', '/.bundle/132118/PRI_ServerLibrary'],
function(runtime,log,record,search, priLibrary) {
var scriptName = "ac_pl_Subsidiary";
const STATUS_FAILED = 4;
const STATUS_PROCESSED = 2;
const STATUS_SKIPPED = 3;
// develop a map of the fields by cateagory. Use metadata about the fields to help the
// tool understand how to process.
var MAP = {
Header:{
'inactive':{name: 'inactive', text: false, checkbox: true},
'elimination':{name: 'inactive', text: false, checkbox: true},
'externalid':{name: 'externalid', text: false},
'name':{name: 'name', text: false},
'legalname':{name: 'legalname', text: false},
'country':{name:'country', text: true},
'stateprovince':{name:'state', text: true},
'parentsubsidiary': {name: 'parent', text: false},
'taxid': {name: 'ssnortin', text: false},
'currency': {name: 'currency', text: true},
'TransactionPrefix': {name: 'tranprefix', text: false},
'vendorbillpurchaseorderamounttolerance': {name: 'purchaseorderamount', text: false},
'vendorbillpurchaseorderquantitydifference': {name: 'purchaseorderquantitydiff', text: false},
'vendorbillpurchaseorderquantitytolerance': {name: 'purchaseorderquantity', text: false}
},
Address:{
'country': {name: 'country', text: true},
'address1': {name: 'address1', text: false},
'address2': {name: 'address2', text: false},
'address3': {name: 'address3', text: false},
'city': {name: 'city', text: false},
'stateprovince': {name: 'state', text: false},
'zip': {name: 'zip', text: false}
}
};
function createRecord(importData, externalId, context) {
var funcName = scriptName + "createRecord ";
var obj = JSON.parse(importData)[0];
var internalid = (obj.externalid) ? searchRecord(obj.externalid) : null;
var msg = '';
var id;
log.debug('Payload', JSON.stringify(obj));
try{
var nsrecord;
//insert or update?
if(!internalid){
nsrecord = record.create({
type: search.Type.SUBSIDIARY,
isDynamic: true
});
}else{
nsrecord = record.load({
type: search.Type.SUBSIDIARY,
isDynamic: true
});
}
//must connect to a parent subsidiary in the model
if(obj['parentsubsidiary'] && obj['parentsubsidiary'] != ''){
var subid = searchRecord(obj.parentsubsidiary);
if(!subid){
throw new Error('Cannot find parent subsidiary with externalid: '+ obj.parentsubsidiary)
}else{
obj.parentsubsidiary = subid;
}
}
//perform the data mapping work.
var d = {};
for(var f in MAP.Header){
if(obj[f] == null) continue;
if(MAP.Header[f].checkbox){
d[f] = (obj[f] == 'T' || obj[f] == 'true' || obj[f].toLowerCase() == 'yes')
nsrecord.setValue({
fieldId: MAP.Header[f].name,
value: (obj[f] == 'T' || obj[f] == 'true' || obj[f].toLowerCase() == 'yes')
});
continue;
}
if(MAP.Header[f].text){
d[f] = obj[f]
nsrecord.setText({
fieldId: MAP.Header[f].name,
text: obj[f]
});
}else{
d[f] = obj[f]
nsrecord.setValue({
fieldId: MAP.Header[f].name,
value: obj[f]
});
}
}
var mainAddress = nsrecord.getSubrecord({
fieldId: 'mainaddress',
isDynamic: true
});
for(var f in MAP.Address){
if(obj[f] == null) continue;
if(MAP.Address[f].text){
d[f] = obj[f]
mainAddress.setText({
fieldId: MAP.Address[f].name,
text: obj[f]
});
}else{
d[f] = obj[f]
mainAddress.setValue({
fieldId: MAP.Address[f].name,
value: obj[f]
});
}
if(f == 'stateprovince'){
log.debug('State', obj[f])
}
}
log.debug('Values', JSON.stringify(d));
id =nsrecord.save({
ignoreMandatoryFields: true
})
}catch(e){
msg = e.toString();
}
if(id){
return {
status: STATUS_PROCESSED,
message: '',
recordId: id
};
}else{
return {
status: STATUS_FAILED,
recordId: id,
message:msg
}
}
}
function searchRecord(externalId){
var s = search.create({
type: search.Type.SUBSIDIARY,
filters: [
['externalid', 'is', externalId]
]
}).run().getRange(0,1);
return (s && s.length > 0) ? s[0].id : null;
}
return {
createRecord:createRecord
}
});
Get Help Importing NetSuite Data
I would like to thank Vanessa S., one of our Technical Analysts on our team, for crafting the SuiteScript code above. I am fortunate to work with a talented team of professionals that hold our high standards for care.
Should you find this article meaningful, feel free to receive notifications to future posts as I publish them. If you have a NetSuite data import job and are told, “NetSuite does not support it”, don’t accept that; instead, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
