In this import system, the customer record will be updated from the CSV file. Apart from other import system mentioned this import system can be used exclusively for updating customer record.
Design
A custom record is created which have an option to upload the file. The file should be comma separated values(CSV). The column headers of the CSV file should be matched and the order should not be changed to update the record.
When a record is saved user event deployed in the record will get triggered and a scheduled script will be scheduled. From the scheduled script the file will be loaded and processed.
/**
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
*/
/**
* **************************************************************************************
* MR-85 CSV import to customer record using script and UI to store import details
* **************************************************************************************
* Author: Jobin and Jismi IT services LLP
* **************************************************************************************
*
*
* Triggers a schedule script after creating a CSV import.
*
* Filename :MR-85 UE IMPORT CSV TRIGGER IMPORT PROCESS.js
*
* Revision History
******************************
* @created by santo Varghese 17/3/2018
*
*
*/
define(['N/record', 'N/task', "N/file"],
function(record, task, file) {
/**
* Function definition to be triggered before record is loaded.
*
* @param {Object}
* scriptContext
* @param {Record}
* scriptContext.newRecord - New record
* @param {Record}
* scriptContext.oldRecord - Old record
* @param {string}
* scriptContext.type - Trigger type
* @Since 2015.2
*/
function afterSubmit(scriptContext) {
try {
if (scriptContext.type == "create") {
var cofigRecordNew = scriptContext.newRecord;
log.debug({ title: "cofigRecordNew", details: cofigRecordNew });
var CSVfileIdNew = cofigRecordNew.getValue({ fieldId: "custrecord_jj_mr85_csv_import_fileq" });
var csvFile = file.load({ id: CSVfileIdNew });
var isCSV = (csvFile.fileType == file.Type.CSV);
var isHeaderMatches = true;
if (isCSV) {
var csvFileContents = csvFile.getContents();
var csvDetailsLines = csvFileContents.split("\r\n");
var header = (csvDetailsLines[0]).split(",");
log.debug({ title: "header", details: header });
var staticHeader = ["Account ID", "Account Name", "Account Status", "Sales Rep", "Parent Name", "Parent Account ID", "Previous Month Airtime Sum", "Previous Month Airtime Count", "Month to Date Airtime Sum", "Month to Date Airtime Count", "Previous Month Activation Count", "Month to Date Activation Count", "Available Sim Count", "Pre/Post Pay", "Total Available Balance", "Last Order Date", "Create Date"];
var isValidHeader = (JSON.stringify(header) == JSON.stringify(staticHeader));
}
log.debug({ title: scriptContext.type });
var recId = cofigRecordNew.getValue({ fieldId: 'id' });
var status = "";
var logERROR = "";
if (CSVfileIdNew != "" && CSVfileIdNew != null && CSVfileIdNew != undefined && isCSV && isValidHeader) {
try {
//schedule Schedule script
scheduleScrptTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscript_mr85_ss_import_qpay",
deploymentId: 'customdeploy_jj_mr85_ss_csv_processing_q',
params: {
custscript_mr80_ss_recordid_qpay: recId,
custscript_mr80_ss_start_qpay: 1,
custscript_mr85_csv_file_qpay: CSVfileIdNew
}
});
var scriptTaskId = scheduleScrptTask.submit();
//log.debug({title:"CSV IMPORT SCHEDULED",details:scriptTaskId});
status = "STARTED";
logERROR += "STARTS"
} catch (err) {
// status="FAILED";
log.debug({ title: "CANNOT TRIGGER", details: err });
logERROR += "CANNOT TRIGGER";
}
} else {
log.debug({ title: "ERROR", details: "CSV_IMPORT_FILE_ID is invalid" });
if (scriptContext.type == "create") status = "FAILED";
if (!isCSV) status = "INVALID FILE";
else if (!isValidHeader) status = "INVALID HEADERS";
logERROR += "INVALID FILE/CREATE NEW";
}
//SET STATUS
if (scriptContext.type == "create")
var recId = record.submitFields({
id: recId,
type: "customrecord_jj_mr85_csv_import_qpay",
values: {
custrecord_jj_mr85_status_qpay: status,
},
options: {
enableSourcing: false,
ignoreMandatoryFields: true
}
});
}
} catch (err) {
log.debug({ title: "ERROR_CSV_IMPORT_NOT_SCHEDULED", details: err });
}
}
return {
afterSubmit: afterSubmit
};
});
/**
* * @NApiVersion 2.x
* @NScriptType ScheduledScript
*/
/**
* **************************************************************************************
* MR-85 CSV import to customer record using script and UI to store import details
* **************************************************************************************
* Author: Jobin and Jismi IT services LLP
* **************************************************************************************
*
*
* Process the CSV file and create records.
*
* Filename :MR-85 SS IMPORT CSV PROCESS FILE.js
*
* Revision History
******************************
* @created by SV 17/3/2018
*
*
*/
define(['N/search', 'N/record', 'N/task', 'N/runtime', "N/file", 'N/format'],
function(search, record, task, runtime, file, format) {
function execute(scriptContext) {
try {
var start = runtime.getCurrentScript().getParameter("custscript_mr80_ss_start_qpay");
var recId = runtime.getCurrentScript().getParameter("custscript_mr80_ss_recordid_qpay");
var fileId = runtime.getCurrentScript().getParameter("custscript_mr85_csv_file_qpay");
//load record
var configuration = record.load({
id: recId,
type: "customrecord_jj_mr85_csv_import_qpay",
});
//GET CURREENT DETAILS
var logCompleted = configuration.getValue({ fieldId: "custrecord_jj_mr85_lines_imported_qpay" });
var logUnprocessed = configuration.getValue({ fieldId: "custrecord_jj_mr85_lines_failed_qpay" });
var logPROCESSED = configuration.getValue({ fieldId: "custrecord_jj_mr85_lines_processed_qpay" });
// var logERROR=configuration.getValue({fieldId:"custrecord_jj_mr85_log_details"});
var logERROR = "";
//load file
var csvFile = file.load({ id: fileId });
//get contents in file
var csvFileContents = csvFile.getContents();
var csvDetailsLines = csvFileContents.split("\r\n");
// log.debug({ title: "csvDetailsLines", details: (csvDetailsLines[0]).split(",") });
for (var i = start; i < csvDetailsLines.length; i++) {
var currentLine = csvDetailsLines[i];
var currentLineContents = currentLine.split(",");
var customerId = "";
if (currentLineContents[0] != "") {
var searchParams = {
type: "customer",
filters: [
["custentity1", "contains", "" + currentLineContents[0]]
],
columns: ["internalid"]
};
customerId = lookUpDestintionRecord(searchParams);
}
if (customerId != "" && customerId != undefined) {
//found record
try {
var resultrec = record.load({
type: "customer",
id: customerId
});
var fieldmap = {
custentity_jj_parentname_qpay: currentLineContents[4],
custentity_jj_accountstatus_qpay: currentLineContents[2],
custentity_jj_parentaccount_id_qpay: currentLineContents[5],
custentity_jj_previous_airtimesum_qpay: currentLineContents[6],
custentity_jj_previous_airtim_count_qpay: currentLineContents[7],
custentity_jj_monthdate_airtime_sum_qpay: currentLineContents[8],
custentity_jj_monthdate_airtimcount_qpay: currentLineContents[9],
custentity_jj_previous_mth_actcount_qpay: currentLineContents[10],
custentity_jj_monthdate_act_count_qpay: currentLineContents[11],
custentity_jj_pre_postpay_qpay: currentLineContents[13],
custentity_jj_last_order_date_qpay: formatdate(currentLineContents[15]),
custentity_jj_create_date_qpay: formatdate(currentLineContents[16]),
salesrep: getemployee(currentLineContents[3])
}
for (var key in fieldmap) {
setvalue(resultrec, key, fieldmap[key])
}
var recordId = resultrec.save({
enableSourcing: 'false',
ignoreMandatoryFields: 'false'
});
logPROCESSED++
logCompleted++;
} catch (err) {
logERROR += "\nCANNOT UPDATE CUSTOMER-" + customerId + "@LINE-" + i;
log.debug({ title: "ERROR@submitFields", details: err });
logUnprocessed++;
logPROCESSED++
}
} else {
//No record with Value
logERROR += "\nNOT FOUND CUSTOMER for " + csvDetailsLines[3] + "@line" + i;
log.debug({ title: "NOT FOUND CUSTOMER", details: csvDetailsLines[3] + "@line-" + i });
logUnprocessed++;
logPROCESSED++
}
var scriptObj = runtime.getCurrentScript();
var remainingTime = scriptObj.getRemainingUsage();
var isEnd = true;
//Check the remaining usage is too low
if (remainingTime < 1000) {
//reschedule Schedule script
scheduleScrptTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscript_mr85_ss_import_qpay",
deploymentId: 'customdeploy_jj_mr85_ss_csv_processing_q',
params: {
custscript_mr80_ss_recordid: recId,
custscript_mr80_ss_start: i + 1,
custscript_mr85_csv_file: fileId
}
});
//set flag not the end of data.
isEnd = false;
logERROR += "\nCASCADE NEXT SCHEDULE SCRIPT"
var scriptTaskId = scheduleScrptTask.submit();
//break the loop
break;
}
}
//UPDATE PROCESSING DETAILS
configuration.setValue({ fieldId: "custrecord_jj_mr85_lines_imported_qpay", value: logCompleted });
configuration.setValue({ fieldId: "custrecord_jj_mr85_lines_failed_qpay", value: logUnprocessed });
configuration.setValue({ fieldId: "custrecord_jj_mr85_lines_processed_qpay", value: logPROCESSED });
// configuration.setValue({fieldId:"custrecord_jj_mr85_log_details",value:logERROR});
//SET STATUS FINISHED IF COMPLETED
if (isEnd) configuration.setValue({ fieldId: "custrecord_jj_mr85_status_qpay", value: "FINISHED" });
//save current configuration record
configuration.save({});
} catch (err) {
log.debug({ title: "ERROR_CSV_IMPORT_PROCESS", details: err });
}
}
function getemployee(id) {
try {
var employeeSearchObj = search.create({
type: "employee",
filters: [
["entityid", "is", id]
],
columns: [
search.createColumn({
name: "internalid",
sort: search.Sort.ASC
})
]
});
var searchResultCount = employeeSearchObj.runPaged().count;
var ram;
employeeSearchObj.run().each(function(result) {
ram = result.getValue({ name: "internalid" })
});
return ram;
} catch (e) {
log.debug("e", e);
}
}
function formatdate(val) {
try {
log.debug({ title: "format val", details: val });
var date = new Date(val); // Mon Aug 24 2015 17:27:16 GMT-0700 (Pacific Daylight Time)
var value = format.format({ value: date, type: format.Type.DATETIME })
log.debug({ title: "format value", details: value });
return value
} catch (e) {
log.debug({ title: "e", details: e });
}
}
function setvalue(resultrec, field, value) {
var rambo = resultrec.setValue({
fieldId: field,
value: value
});
}
/*for getting customer ids */
function lookUpDestintionRecord(searchParams) {
var destinationId = "";
try {
var look = search.create(searchParams);
look.run().each(function(result) {
destinationId = destinationId = result.getValue("internalid");
});
} catch (err) {
log.debug({ title: "ERROR@lookUpDestintionRecord", details: err });
}
return destinationId;
}
return {
execute: execute
};
});