Requirement
Add a scheduled script to send the saved search results to the designated email address as CSV attachments.
Solution
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define(["N/email", "N/search", "N/file", "N/runtime"], /**
* @param{email} email
* @param{search} search
* @param{file} file
* @param{runtime} runtime
*/
(email, search, file, runtime) => {
/**
* Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
* @param {Object} inputContext
* @param {boolean} inputContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Object} inputContext.ObjectRef - Object that references the input data
* @typedef {Object} ObjectRef
* @property {string|number} ObjectRef.id - Internal ID of the record instance that contains the input data
* @property {string} ObjectRef.type - Type of the record instance that contains the input data
* @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use in the map/reduce process
* @since 2015.2
*/
const getInputData = (inputContext) => {
try {
let searchId = runtime.getCurrentScript().getParameter("custscript_jj_search_id");
log.debug("searchId", searchId);
if (checkForParameter(searchId)) {
let transactionSearchObject = dataSets.loadSearchFromParameter(searchId);
if (checkForParameter(transactionSearchObject)) {
let getTransactionSearchResults = dataSets.transactionSearch(
transactionSearchObject
);
log.debug(
"getTransactionSearchResults length",
getTransactionSearchResults.length
);
if (getTransactionSearchResults.length > 0) {
let convertToObjects = dataSets.convertToObject(
getTransactionSearchResults[0],
getTransactionSearchResults[1]
);
return convertToObjects;
} else {
return [];
}
} else {
return [];
}
} else {
return [];
}
} catch (err) {
log.error("error@getInputData", err);
return [];
}
};
/**
* Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
* automatically when the associated map stage is complete. This function is applied to each group in the provided context.
* @param {Object} reduceContext - Data collection containing the groups to process in the reduce stage. This parameter is
* provided automatically based on the results of the map stage.
* @param {Iterator} reduceContext.errors - Serialized errors that were thrown during previous attempts to execute the
* reduce function on the current group
* @param {number} reduceContext.executionNo - Number of times the reduce function has been executed on the current group
* @param {boolean} reduceContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} reduceContext.key - Key to be processed during the reduce stage
* @param {List<String>} reduceContext.values - All values associated with a unique key that was passed to the reduce stage
* for processing
* @since 2015.2
*/
const reduce = (reduceContext) => {
try {
let jsonValues = JSON.parse(reduceContext.values);
// log.debug("jsonValues reduce", jsonValues)
reduceContext.write({
key: reduceContext.key,
value: jsonValues,
});
} catch (err) {
log.error("error@reduce", err);
return [];
}
};
/**
* Defines the function that is executed when the summarize entry point is triggered. This entry point is triggered
* automatically when the associated reduce stage is complete. This function is applied to the entire result set.
* @param {Object} summaryContext - Statistics about the execution of a map/reduce script
* @param {number} summaryContext.concurrency - Maximum concurrency number when executing parallel tasks for the map/reduce
* script
* @param {Date} summaryContext.dateCreated - The date and time when the map/reduce script began running
* @param {boolean} summaryContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Iterator} summaryContext.output - Serialized keys and values that were saved as output during the reduce stage
* @param {number} summaryContext.seconds - Total seconds elapsed when running the map/reduce script
* @param {number} summaryContext.usage - Total number of governance usage units consumed when running the map/reduce
* script
* @param {number} summaryContext.yields - Total number of yields when running the map/reduce script
* @param {Object} summaryContext.inputSummary - Statistics about the input stage
* @param {Object} summaryContext.mapSummary - Statistics about the map stage
* @param {Object} summaryContext.reduceSummary - Statistics about the reduce stage
* @since 2015.2
*/
const summarize = (summaryContext) => {
try {
let searchId = runtime.getCurrentScript().getParameter("custscript_jj_search_id");
if (checkForParameter(searchId)) {
let transactionSearchObject = dataSets.loadSearchFromParameter(searchId);
if (checkForParameter(transactionSearchObject)) {
let titleArrays = dataSets.getTransactionSearchColumnLabels(transactionSearchObject);
let searchTitle = dataSets.fetchSearchName(searchId);
if (!checkForParameter(searchTitle)) {
searchTitle = "Saved Search ";
}
let emailContent = "Attached are the results of your search";
let csvFileData = titleArrays.toString() + "\r\n";
summaryContext.output.iterator().each((key, value) => {
for (let val = 0; val < Object.values(JSON.parse(value)).length; val++) {
csvFileData += Object.values(JSON.parse(value))[val] + ",";
}
csvFileData = csvFileData + "\n";
return true;
});
let contents = csvFileData;
if (checkForParameter(contents)) {
let fileObj = file.create({
name: searchTitle +"_"+ Math.floor(Date.now() / 1000) + ".csv",
fileType: file.Type.CSV,
contents: contents,
});
email.send({
author: 157181,
recipients: ["jesna.john@jobinandjismi.com"],
subject: "Search: " + searchTitle,
body: emailContent,
attachments: [fileObj],
});
log.debug("email sent");
}
else {
log.debug("no search results")
}
}
else {
log.error("Search Id not exist")
}
}
} catch (err) {
log.error("error@summarize", err)
}
};
const dataSets = {
loadSearchFromParameter(searchId) {
try {
let transactionSearchObj = search.load({
id: Number(searchId),
});
return transactionSearchObj;
} catch (err) {
log.error("error@loadSearchFromParameter", err);
}
},
/**
* The function is to fetch the saved search column labels
* @param {*} transactionSearchObj Search Object
* @returns column array names
*/
getTransactionSearchColumnLabels(transactionSearchObj) {
try {
let columnLabel = [];
for (
let column = 0;
column < transactionSearchObj.columns.length;
column++
) {
columnLabel.push(
transactionSearchObj.columns[column].label ||
"Column " + (column + 1)
);
}
log.debug("columnLabel", columnLabel);
return columnLabel;
} catch (err) {
log.error("error", err);
}
},
transactionSearch(transactionSearchObj) {
try {
let runSearch;
let hasResults = true;
let toRange = 0;
let transactionResults = [];
runSearch = transactionSearchObj.run();
let columnLabel = [];
let returnValue = [];
for (
let column = 0;
column < transactionSearchObj.columns.length;
column++
) {
columnLabel.push(
transactionSearchObj.columns[column].label ||
"Column " + (column + 1)
);
}
transactionResults.push(columnLabel);
while (hasResults) {
fromRange = toRange;
toRange = fromRange + 1000;
searchResults = runSearch.getRange({
start: fromRange,
end: toRange,
});
log.debug("searchResults", searchResults.length);
if (searchResults) {
if (searchResults.length < 1000) {
hasResults = false;
}
for (let i = 0; i < searchResults.length; i++) {
returnValue.push(searchResults[i]);
}
} else {
hasResults = false;
}
}
transactionResults.push(returnValue);
return transactionResults;
} catch (err) {
log.error("error@savedSearchResult", err);
return [];
}
},
convertToObject(searchColumnHead, searchResults) {
try {
let arrayObj = [];
for (let i = 0; i < searchResults.length; i++) {
let obj = {};
for (
let columnHead = 0;
columnHead < searchColumnHead.length;
columnHead++
) {
if (!obj.hasOwnProperty(searchColumnHead[columnHead])) {
obj[searchColumnHead[columnHead]] = searchResults[i].getText(searchResults[i].columns[columnHead]) ? searchResults[i].getText(searchResults[i].columns[columnHead]) : searchResults[i].getValue(searchResults[i].columns[columnHead]);
} else {
obj[searchColumnHead[columnHead] + "_" + columnHead] = searchResults[i].getText(searchResults[i].columns[columnHead]) ? searchResults[i].getText(searchResults[i].columns[columnHead]) : searchResults[i].getValue(searchResults[i].columns[columnHead]);
}
}
arrayObj.push(obj);
}
log.audit("arrayObj length", arrayObj.length);
return arrayObj;
} catch (e) {
log.error("error@convertToObject", e);
return []
}
},
fetchSearchName(searchId) {
try {
let savedsearchSearchObj = search.create({
type: "savedsearch",
filters: [["internalid", "anyof", searchId]],
columns: [search.createColumn({ name: "title", label: "Title" })],
});
let searchResults = savedsearchSearchObj
.run()
.getRange({ start: 0, end: 1 });
log.audit("searchResults name", searchResults);
return searchResults[0].getValue({ name: "title", label: "Title" });
} catch (err) { }
},
};
eturns true if parameter is valid
*/
const checkForParameter = function checkForParameter(parameter) {
if (
parameter !== "" &&
parameter !== null &&
parameter !== undefined &&
parameter !== false &&
parameter !== "null" &&
parameter !== "undefined" &&
parameter !== " " &&
parameter !== "false"
) {
return true;
}
};
return { getInputData, reduce, summarize };
});