Introduction
This writeup summarizes the details of custom import to update the customer record with the corrected Address from UPS.
Solution
The script will check the Item Fulfillment column in the file and then search for the customer. In the customer record, New address will be added on each import in the address sub-record as shown below with a label “UPS Corrected Address” so it can be generated in the report later with this filter and the latest one.
1. Create a Custom Record that mocks the behavior of Import Assistant:
User Event Script in the background of the custom record which will create a JSON file for the CSV file uploaded.
2. Import Script:
Map-reduce Script triggered by the UE script on the creation of a new Custom record (not on the edit of existing one), and the new address sub-record created for the customer with the Label “UPS Corrected Address”.
NOTE: Script will not set the default billing, Shipping, etc. Users can manually set those preferences according to their demands.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
/*************************************************************************************************************************
* Script Description : This Script is used to initiate the custom import
**************************************************************************************************************************/
define(['N/file', 'N/record', 'N/task', 'N/runtime'],
/**
* @param{file} file
* @param{record} record
* @param{task} task
* @param{runtime} runtime
*/
(file, record, task, runtime) => {
/**
* Defines the function definition that is executed after record is submitted.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.type - Trigger type; use values from the context.UserEventType enum
* @since 2015.2
*/
const afterSubmit = (scriptContext) => {
try {
if (scriptContext.type === scriptContext.UserEventType.CREATE) {
var rec = scriptContext.newRecord;
var custscript_vv_csv_chunk_cr_id = rec.id;
//Get the import file id
var fileId = rec.getValue({
fieldId: 'custrecord_vv_import_file'
})
// Type of import eg:customer address import etc.
var importType = rec.getValue({
fieldId: 'custrecord_vv_import_record_type'
})
log.debug('importType', importType);
if (importType == 1) {
var importedFile = file.load({ id: fileId });
var isCSV = (importedFile.fileType == file.Type.CSV);
if (isCSV == true) {
var processedArray = [];
importedFile.lines.iterator().each(function (line) {
var w = line.value.split(",");
processedArray.push({ ifDocNum: w[15], addrss1: w[75], addrss2: w[76], city: w[78], state: w[79], zip: w[80], country: w[81] });
return true;
});
var output = JSON.stringify(processedArray)
log.debug("processedArray", processedArray)
//create JSON file to store results of array
var nameOfFile = 'Sample Array'
var fileName = (new Date()).getTime() + nameOfFile;
var fileObj = file.create({
name: fileName,
fileType: file.Type.JSON,
contents: output
});
fileObj.folder = 60931 // sb1->2942;
var jsonfileId = fileObj.save();
log.debug("jsonfileId", jsonfileId);
// Store the script ID of the script to submit
var mapReduceScriptId = 'customscript_vv_mr_add_customer_addrss';
var deploymentId = 'customdeploy_vv_mr_add_customer_addrss';
//Set the status of the record
record.submitFields({
type: 'customrecord_vv_import_file_record',
id: custscript_vv_csv_chunk_cr_id,
values: {
'custrecord_vv_import_status': '3'
}
});
//Create a map/reduce task
var mrTask = task.create({
taskType: task.TaskType.MAP_REDUCE,
scriptId: mapReduceScriptId,
deploymentId: deploymentId,
params: {
'custscript_vv_csv_json_file_id': jsonfileId,
'custscript_vv_csv_chunk_cr_id': custscript_vv_csv_chunk_cr_id
}
});
// Submit the map/reduce task
var mrTaskId = mrTask.submit();
log.debug('mrTaskId ', mrTaskId);
}
}
var scriptObj = runtime.getCurrentScript();
log.debug('Remaining governance units: ' + scriptObj.getRemainingUsage());
}
} catch (e) {
log.debug('error@afterSubmit', e)
log.error('error@afterSubmit', e)
}
}
return { afterSubmit }
});
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/*************************************************************************************************************************
* Script Description : Script is used to add address on customer record
**************************************************************************************************************************/
define(['N/record', 'N/search', 'N/ui/serverWidget', 'N/render', 'N/email', 'N/file', 'N/encode', 'N/task', 'N/url', 'N/redirect', 'N/runtime', 'N/format'],
function (record, search, serverWidget, render, email, file, encode, task, url, redirect, runtime, format) {
function getInputData(scriptContext) {
try {
var script = runtime.getCurrentScript();
var cusRecId = script.getParameter({
name: 'custscript_vv_csv_chunk_cr_id'
});
log.debug('cusRecId', cusRecId)
// Get the JSON file with details of imported data
var fileIdNew = script.getParameter({name: 'custscript_vv_csv_json_file_id'});
log.debug('fileIdNew', fileIdNew)
if (fileIdNew) {
var fileObj = file.load({
id: fileIdNew
});
if (fileObj) {
var fileContent = fileObj.getContents()
fileContent = JSON.parse(fileContent)
//log.debug('fileContent', fileContent)
// Create the search to find the customer from Doc no: of IF in file
var itemfulfillmentSearchObj = search.create({
type: "itemfulfillment",
filters: [["type", "anyof", "ItemShip"], "AND", ["mainline", "is", "T"]],
columns: [search.createColumn({ name: "tranid", label: "Document Number"}), search.createColumn({name: "internalid", join: "customer"})]
});
var searchPageRanges, customerID, ifNumber;
var ifRecordsArray = [];
try {
searchPageRanges = itemfulfillmentSearchObj.runPaged({
pageSize: 1000
});
} catch (err) {
return [];
}
if (searchPageRanges.pageRanges.length < 1)
return [];
var pageRangeLength = searchPageRanges.pageRanges.length;
for (var pageIndex = 0; pageIndex < pageRangeLength; pageIndex++)
searchPageRanges.fetch({
index: pageIndex
}).data.forEach(function (result) {
customerID = result.getValue(result.columns[1]);
ifNumber = result.getValue(result.columns[0])
var customRecordObj = {};
customRecordObj.ifNumber = ifNumber
customRecordObj.customerID = customerID
ifRecordsArray.push(customRecordObj);
return true;
});
//log.debug("ifRecordsArray.length", ifRecordsArray.length)
// Iterate through the JSON file contents and push the details of customer
// var customerObj = {};
var customerArray = [];
for (var i = 0; i < fileContent.length; i++) {
var ifDocNumber = fileContent[i].ifDocNum
//log.debug('ifDocNumber', ifDocNumber);
if (ifDocNumber) {
var arr = ifRecordsArray;
Details = arr.filter((a) => {
if (a.ifNumber == ifDocNumber) {
return a
}
});
if (Details.length == 1 || Details.length > 1) {
customerID = Details[0].customerID;
customerArray = {customerName: customerID}
fileContent[i][Object.keys(customerArray)] = customerArray.customerName
}
}
}
//log.debug('fileContent', fileContent)
record.submitFields({
type: 'customrecord_vv_import_file_record',
id: cusRecId,
values: {
'custrecord_vv_import_status': '5'
}
});
return fileContent;
}
}
} catch (e) {
log.debug('error@getInputData', e)
log.error('error@getInputData', e)
}
}
function reduce(objRec) {
try {
var temp = [];
var objTemp = {};
var fileDetails = objRec.values;
fileDetails = JSON.parse(fileDetails)
// Load customer records and create the address subrecord
if (fileDetails.customerName) {
try {
var objRecord = record.load({
type: record.Type.CUSTOMER,
id: fileDetails.customerName,
isDynamic: true,
});
objRecord.selectNewLine({
sublistId: 'addressbook'
});
var addressSubrecord = objRecord.getCurrentSublistSubrecord({
sublistId: 'addressbook',
fieldId: 'addressbookaddress'
});
if (fileDetails.state) {
addressSubrecord.setValue({fieldId: 'dropdownstate', value: fileDetails.state})
}
if (fileDetails.country) {
addressSubrecord.setValue({fieldId: 'country', value: fileDetails.country})
}
if (fileDetails.zip) {
addressSubrecord.setValue({fieldId: 'zip', value: fileDetails.zip})
}
if (fileDetails.city) {
addressSubrecord.setValue({fieldId: 'city', value: fileDetails.city})
}
if (fileDetails.addrss1) {
addressSubrecord.setValue({fieldId: 'addr1', value: fileDetails.addrss1})
}
if (fileDetails.addrss2) {
addressSubrecord.setValue({fieldId: 'addr2', value: fileDetails.addrss2})
}
addressSubrecord.setValue({fieldId: 'defaultshipping', value: false});
addressSubrecord.setValue({fieldId: 'defaultbilling', value: false});
objRecord.setCurrentSublistValue({
sublistId: "addressbook",
fieldId: "label",
value: "UPS Corrected Address"
});
objRecord.commitLine({
sublistId: 'addressbook'
});
objRecord.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
} catch (e) {
log.debug('error@reduce', e.message)
if (e.message) {
var keyNew = (new Date()).getTime()
objTemp.ifDocNum = fileDetails.ifDocNum;
objTemp.error = e.message;
temp.push(objTemp);
objRec.write({
key: keyNew,
value: temp
});
}
}
}
} catch (e) {
log.debug('error@reduce', e)
log.error('error@reduce', e)
}
}
function summarize(summary) {
try {
//Create the CSV response file
var cusRecId = runtime.getCurrentScript().getParameter({
name: 'custscript_vv_csv_chunk_cr_id'
});
var logERROR = "";
var contents = '';
var content = [];
contents += ['IF Document Number', 'Error'].join(",") + "\r\n";
summary.output.iterator().each(function (key, value) {
logERROR = JSON.parse(value)
for (var i = 0; i < logERROR.length; i++) {
var testArray = [logERROR[i].ifDocNum, logERROR[i].error]
content.push((testArray).join(","));
contents += (testArray).join(",") + "\r\n";
}
return true;
});
if (logERROR.length > 0) {
var csvResponse = file.create({
name: 'Custom Import Response File.csv',
fileType: file.Type.CSV,
contents: contents,
folder: 60930
});
} else {
logERROR = 'All records imported successfully.'
var csvResponse = file.create({
name: 'Custom Import Response File.csv',
fileType: file.Type.CSV,
contents: logERROR,
folder: 60930
});
}
var errorFileId = csvResponse.save();
log.debug('errorFileId', errorFileId)
record.submitFields({
type: 'customrecord_vv_import_file_record',
id: cusRecId,
values: {
'custrecord_vv_import_status': '1',
'custrecord_vv_import_error_file': errorFileId
}
});
var fieldLookUp = search.lookupFields({
type: 'customrecord_vv_import_file_record',
id: cusRecId,
columns: ['owner']
});
var custrecOwner = fieldLookUp.owner[0].value;
log.debug('custrecOwner', custrecOwner)
var senderID = runtime.getCurrentUser().id;
var recipientId = custrecOwner;
var emailSubject = "The customer Address import " + cusRecId + " is Completed";
var emailBody = "<BR />Hi, <BR/> <BR/>The customer Address import " + cusRecId + " is completed. <BR/><BR/> Thank You." + " <BR/><BR/> "
email.send({
author: senderID,
recipients: recipientId,
subject: emailSubject,
body: emailBody,
attachments: [csvResponse]
});
} catch (e) {
log.debug('error@summarize', e)
log.error('error@summarize', e)
}
}
return {
getInputData: getInputData,
reduce: reduce,
summarize: summarize
}
});