Jira Code: HTL-33
The script is used for sales page forecast report, which runs every morning and update the custom record which is updated in customer record newly created tab. The information from the invoices will be categorized with the category and with customer and it will be stored in the above stated custom record. The calculations and comparisons will be performed in the script and will be updated the corresponding fields of custom record. The scheduled script will update the custom record. The client script will do on the calculations on field change.
Scheduled Script:
/**
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
/**
* Script Description
* Sales page forecast - Sales page forecast report
*/
/*******************************************************************************
* Hygiene Technologies Ltd
* **************************************************************************
*
* Date: 22/02/2019
*
* Author: Jobin & Jismi IT Services LLP
*
*
* REVISION HISTORY
*
* Revision 1 $ 22/02/2019 Maria: Create
*
******************************************************************************/
define(['N/runtime', 'N/search', 'N/task', 'N/record'],
function(runtime, search, task, record) {
/**
* Definition of the Scheduled script trigger point.
*
* @param {Object} scriptContext
* @param {Object} scriptContext
* @param {string} scriptContext.type - The context in which the script is executed. It is one of the values from the scriptContext.InvocationType enum.
* @Since 2015.2
*/
function execute(scriptContext) {
try {
logme("ScheduledScript", "Scheduled script started");
var scriptObj = runtime.getCurrentScript();
////get the script parameter startRange
var startRange = scriptObj.getParameter({ name: 'custscript_jj_startrange' });
var endRange = scriptObj.getParameter({ name: 'custscript_jj_endrange' });
var n = scriptObj.getParameter({ name: 'custscript_jj_total_count' });
if (startRange == '' || startRange == null) {
n = 0;
}
var temObject = {};
var Object = {};
//search 1 to get customers
var customerSearchObj = search.create({
type: "customer",
filters: [
["stage", "anyof", "CUSTOMER"],
"AND",
["isinactive", "is", "F"]
],
columns: [
search.createColumn({ name: "internalid", sort: search.Sort.ASC, label: "Internal ID" })
//search.createColumn({name: "altname", label: "Name"})
]
});
var searchResultCount1 = customerSearchObj.runPaged().count;
var end = 0;
//when script initially run startrange will be blank so set startrange as 0.
if (startRange == '' || startRange == null) {
if (searchResultCount1 < 50) {
startRange = 0;
endRange = searchResultCount1;
end = 1;
} else {
startRange = 0;
endRange = 50;
}
}
startRange = parseInt(startRange);
endRange = parseInt(endRange);
var custsearchResult = customerSearchObj.run().getRange({
start: startRange,
end: endRange
});
var customerarray = [],
customer = [];
for (var j = 0; j < custsearchResult.length; j++) {
var custsingleResult = custsearchResult[j];
var customer = custsingleResult.getValue({
name: "internalid",
label: "Internal ID"
});
customerarray.push(customer);
}
// Function to get category list
//var category = categorylist();
var category = categorylist();
for (var w = 0; w < customerarray.length; w++) {
for (var e = 0; e < category.length; e++) {
var str = customerarray[w] + "_" + category[e];
Object[str] = [customerarray[w], category[e], "0", "0"];
}
}
// Function to get Actual YTD sales
var actualytdsales = actualytdsearch(customerarray, category, Object);
// Function to get Last YTD sales
var lastytdsales = lastytdsearch(customerarray, category, actualytdsales);
//logme("lastytdsales",lastytdsales);
// Function to delete custom record.
deleterecord(customerarray,category);
//var lastytdsales_length = Object.keys(lastytdsales).length;
var key;
// Set values to the Sales Page Forcast custom record.
for (key in lastytdsales) {
if (lastytdsales.hasOwnProperty(key)) {
var value = lastytdsales[key];
//logme("value",value);
var customrecord_htl33_jj_sales_page_forcastSearchObj = search.create({
type: "customrecord_htl33_jj_sales_page_forcast",
filters: [
["custrecord_htl33_jj_customer", "anyof", lastytdsales[key][0]],
"AND",
["custrecord_htl33_jj_category", "is", lastytdsales[key][1]]
],
columns: [
search.createColumn({
name: "scriptid",
sort: search.Sort.ASC,
label: "Script ID"
}),
search.createColumn({ name: "custrecord_htl33_jj_category", label: "Category" }),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
var searchResultCountsales = customrecord_htl33_jj_sales_page_forcastSearchObj.runPaged().count;
// If no record exits , create a new sales page forcast record.
if (searchResultCountsales == 0) {
var customRecord = record.create({
type: 'customrecord_htl33_jj_sales_page_forcast',
isDynamic: true
});
customRecord.setValue({
fieldId: 'custrecord_htl33_jj_customer',
value: lastytdsales[key][0]
});
//set category
customRecord.setValue({
fieldId: 'custrecord_htl33_jj_category',
value: lastytdsales[key][1]
});
//set Actual YTD
customRecord.setValue({
fieldId: 'custrecord_htl33_jj_actual_ytd_sales',
value: lastytdsales[key][2]
});
if (lastytdsales[key][3]) {
customRecord.setValue({
fieldId: 'custrecord_htl33_jj_last_ytd_sales',
value: lastytdsales[key][3]
});
} else {
customRecord.setValue({
fieldId: 'custrecord_htl33_jj_last_ytd_sales',
value: 0
});
}
var recordId = customRecord.save({
enableSourcing: true,
ignoreMandatoryFields: false
});
}
// If record exits , update existing sales page forcast record.
else {
var searchResultsales = customrecord_htl33_jj_sales_page_forcastSearchObj.run().getRange({
start: 0,
end: 5
});
for (var j = 0; j < searchResultCountsales; j++) {
var SalessingleResult = searchResultsales[j];
var recordId = SalessingleResult.getValue({
name: "internalid",
label: "Internal ID"
});
}
// Load the sales page forcast record
var salespageforcast = record.load({
type: 'customrecord_htl33_jj_sales_page_forcast',
id: recordId,
isDynamic: true
});
//set customer
salespageforcast.setValue({
fieldId: 'custrecord_htl33_jj_customer',
value: lastytdsales[key][0]
});
//set category
salespageforcast.setValue({
fieldId: 'custrecord_htl33_jj_category',
value: lastytdsales[key][1]
});
//set Actual YTD sales
salespageforcast.setValue({
fieldId: 'custrecord_htl33_jj_actual_ytd_sales',
value: lastytdsales[key][2]
});
//set Last YTD sales
if (lastytdsales[key][3]) {
salespageforcast.setValue({
fieldId: 'custrecord_htl33_jj_last_ytd_sales',
value: lastytdsales[key][3]
});
} else {
salespageforcast.setValue({
fieldId: 'custrecord_htl33_jj_last_ytd_sales',
value: 0
});
}
//Get forcast value
var forcastvalue = salespageforcast.getValue({
fieldId: 'custrecord_htl33_jj_forecast',
});
if (forcastvalue) {
// calculate number of days
var numbrofdays = ((Math.round(Math.abs((new Date("4/1/" + (new Date().getFullYear() - 1)) - new Date()) / (24 * 60 * 60 * 1000)))) - 1);
// Calculate target value
Targetytdvalue = (parseFloat((forcastvalue / 365) * numbrofdays).toFixed(2));
// Calcuated for showing color Red
newtarget = 0.8 * Targetytdvalue;
salespageforcast.setValue({
fieldId: 'custrecord_htl33_jj_target_ytd_sales',
value: Targetytdvalue
});
// Condition to show green color
if (lastytdsales[key][2] >= Targetytdvalue) {
salespageforcast.setText({
fieldId: 'custrecord_htl33_jj_status',
text: "Green.PNG"
});
}
// Condition to show red color
else if (lastytdsales[key][2] < newtarget) {
salespageforcast.setText({
fieldId: 'custrecord_htl33_jj_status',
text: "Red.PNG"
});
}
// Condition to show yellow color
else {
salespageforcast.setText({
fieldId: 'custrecord_htl33_jj_status',
text: "Yellow.PNG"
});
}
}
var recordId = salespageforcast.save({
enableSourcing: true,
ignoreMandatoryFields: false
});
}
}
}
var Countt = searchResultCount1 / 50;
var Count = Math.floor(Countt);
var scriptObj = runtime.getCurrentScript();
var remaining = scriptObj.getRemainingUsage();
logme("remaining", remaining);
if (end != 1) {
//rescheduling the script based on the count
if (n < Count - 1) {
n++;
rescheduleScriptandReturn(startRange + 50, endRange + 50, n);
} else if (n == Count - 1) {
n++;
var lastCount = parseFloat(searchResultCount1) - parseFloat(endRange);
rescheduleScriptandReturn(startRange + 50, endRange + lastCount, n);
} else {
}
}
} catch (e) {
logme("firstTry", getError(e));
}
}
return {
execute: execute
};
/*******************************************************************************
* return categorylist
*
* @param e
* @returns
*
* Created on 22-Feb-2019 by Maria
*/
function categorylist() {
//Load the search2 to get category.
var itemSearchObj = search.load({
id: 'customsearch910'
});
var searchResult = itemSearchObj.run().getRange({
start: 0,
end: 1000
});
logme("categorylist", searchResult.length);
var categoryarray = [],
customercategory = [];
for (var j = 0; j < searchResult.length; j++) {
var singleResult = searchResult[j];
var customercategory = singleResult.getValue({
name: 'custitem_category',
summary: 'GROUP',
label: 'Category'
});
categoryarray.push(customercategory);
}
return categoryarray;
}
/*******************************************************************************
* return Actual YTD Details
*
* @param e
* @returns
*
* Created on 22-Feb-2019 by Maria
*/
function actualytdsearch(customerarray, category, Object) {
var temObject = {};
//search to get Actual YTD Sales
var invoiceSearchObj = search.create({
type: "invoice",
filters: [
["type", "anyof", "CustInvc"],
"AND",
["trandate", "onorafter", "startofthisfiscalyear"],
"AND",
["trandate", "before", "today"],
"AND",
["item.custitem_category", "anyof", category],
"AND",
["customer.internalid", "anyof", customerarray]
],
columns: [
search.createColumn({
name: "entity",
summary: "GROUP",
label: "Name"
}),
search.createColumn({
name: "custitem_category",
join: "item",
summary: "GROUP",
label: "Category"
}),
search.createColumn({
name: "netamountnotax",
summary: "SUM",
label: "Amount (Net of Tax)"
})
]
});
var searchResultCount = invoiceSearchObj.runPaged().count;
var invsearchResults = invoiceSearchObj.run().getRange({
start: 0,
end: 1000
});
var searchResult_length = invsearchResults.length;
//If search has results, get the details like customer, category, Actual YTD
if (searchResult_length > 0) {
try {
for (var i = 0; i < searchResult_length; i++) {
var invsearchResult = invsearchResults[i];
var customer_name = invsearchResult.getValue({
name: "entity",
summary: "GROUP",
label: "Name"
});
var customer_category = invsearchResult.getValue({
name: "custitem_category",
join: "item",
summary: "GROUP",
label: "Category"
});
var actualytd = invsearchResult.getValue({
name: "netamountnotax",
summary: "SUM",
label: "Amount (Net of Tax)"
});
var str = customer_name + "_" + customer_category;
Object[str] = [customer_name, customer_category, actualytd];
}
//logme("temObject Final",temObject);
} catch (e) {
logme("@search2", getError(e));
}
}
return Object;
}
/*******************************************************************************
* return Last YTD Details
*
* @param e
* @returns
*
* Created on 22-Feb-2019 by Maria
*/
function lastytdsearch(customerarray, category, actualytdsales) {
//var Object = {};
var arrayA = [];
//search to get Last YTD Sales
var lastytd_invoiceSearchObj = search.create({
type: "invoice",
filters: [
["type", "anyof", "CustInvc"],
"AND",
["trandate", "onorafter", "startoflastfiscalyear"],
"AND",
["trandate", "before", "lastyeartodate"],
"AND",
["item.custitem_category", "anyof", category],
"AND",
["customer.internalid", "anyof", customerarray]
],
columns: [
search.createColumn({
name: "entity",
summary: "GROUP",
label: "Name"
}),
search.createColumn({
name: "custitem_category",
join: "item",
summary: "GROUP",
label: "Category"
}),
search.createColumn({
name: "netamountnotax",
summary: "SUM",
label: "Amount (Net of Tax)"
})
]
});
var arrayA = {};
var lastytd_search = lastytd_invoiceSearchObj.run().getRange({
start: 0,
end: 1000
});
//var end = 250;
var lastytd_searchResult_leng = lastytd_search.length;
//logme("lastytd_searchResult_leng",lastytd_searchResult_leng);
if (lastytd_searchResult_leng > 0) {
try {
for (var i = 0; i < lastytd_searchResult_leng; i++) {
var lastytd_invResult = lastytd_search[i];
var customer_name = lastytd_invResult.getValue({
name: "entity",
summary: "GROUP",
label: "Name"
});
var customer_category = lastytd_invResult.getValue({
name: "custitem_category",
join: "item",
summary: "GROUP",
label: "Category"
});;
var lastytd = lastytd_invResult.getValue({
name: "netamountnotax",
summary: "SUM",
label: "Amount (Net of Tax)"
});
var ytdlast_array = [lastytd];
var str = customer_name + "_" + customer_category;
actualytdsales[str][3] = lastytd;
}
} catch (e) {
logme("@search3", getError(e));
}
}
//logme("actualytdsales",actualytdsales);
return actualytdsales;
}
function deleterecord(customerarray,category) {
try {
//Search to delete the removed category.
var customrecord_htl33_jj_sales_page_forcastSearchObj = search.create({
type: "customrecord_htl33_jj_sales_page_forcast",
filters:
[
["custrecord_htl33_jj_category","noneof",category],
"AND",
["custrecord_htl33_jj_customer","anyof",customerarray]
],
columns:
[
search.createColumn({name: "custrecord_htl33_jj_category", label: "Category"}),
search.createColumn({name: "custrecord_htl33_jj_forecast", label: "Forecast"}),
search.createColumn({name: "custrecord_htl33_jj_actual_ytd_sales", label: "Actual YTD Sales"}),
search.createColumn({name: "custrecord_htl33_jj_target_ytd_sales", label: "Target YTD Sales"}),
search.createColumn({name: "custrecord_htl33_jj_last_ytd_sales", label: "Last YTD Sales"}),
search.createColumn({name: "custrecord_htl33_jj_status", label: "Status"}),
search.createColumn({name: "internalid", label: "Internal ID"})
]
});
var searchResultCount = customrecord_htl33_jj_sales_page_forcastSearchObj.runPaged().count;
//log.debug("customrecord_htl33_jj_sales_page_forcastSearchObj result count",searchResultCount);
//logme("searchResultCount",searchResultCount);
if (searchResultCount) {
var searchResult = customrecord_htl33_jj_sales_page_forcastSearchObj.run().getRange({
start: 0,
end: 1000
});
for (var j = 0; j < searchResultCount; j++) {
var singleResult = searchResult[j];
var deleteid = singleResult.getValue({
name: "internalid",
label: "Internal ID"
});
//delete
record.delete({
type: 'customrecord_htl33_jj_sales_page_forcast',
id: deleteid
});
}
}
} catch (e) {
logme("@deleterecord", getError(e));
}
}
/*******************************************************************************
* return error
*
* @param e
* @returns
*
* Created on 22-Feb-2019 by Maria
*/
function getError(e) {
var stErrMsg = '';
if (e.getDetails != undefined) {
stErrMsg = '_' + e.getCode() + '<br>' + e.getDetails() + '<br>' +
e.getStackTrace();
} else {
stErrMsg = '_' + e.toString();
}
return stErrMsg;
}
/*******************************************************************************
* Log these data
*
* @param title
* @param details
* @returns
*
* Created on 22-Feb-2019 by Maria
*/
function logme(title, details) {
log.error({
title: title,
details: details
});
}
/*******************************************************************************
* Try to reschedule script
*
* @param task
*
* Created on 22-Feb-2019 by Maria
*/
function rescheduleScriptandReturn(startRange, endRange, n) {
try {
var mrTask = task.create({
taskType: task.TaskType.SCHEDULED_SCRIPT,
scriptId: "customscript_htl33_jj_ss_sales_page_fcst",
deploymentId: "customdeploy1",
params: {
custscript_jj_startrange: startRange,
custscript_jj_endrange: endRange,
custscript_jj_total_count: n
}
});
var scriptTaskId = mrTask.submit();
logme("rescheduleScriptandReturn_scriptTaskId", scriptTaskId);
} catch (e) {
logme("Err@reschedulescript", getError(e));
}
}
});
Client Script:
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/**
* Script Description
* Sales page forecast - On forecst field change the calculatio occurs and status updated.
*
*/
/*******************************************************************************
* Hygiene Technologies Ltd
* **************************************************************************
*
* Date: 22/02/2019
*
* Author: Jobin & Jismi IT Services LLP
*
*
* REVISION HISTORY
*
* Revision 1 $ 22/02/2019 Maria: Create
*
******************************************************************************/
define(['N/record','N/search','N/runtime'],
function(record,search,runtime) {
/**
* Function to be executed after page is initialized.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.mode - The mode in which the record is being accessed (create, copy, or edit)
*
* @since 2015.2
*/
function pageInit(scriptContext) {
document.getElementById("tbl_recmachcustrecord_htl33_jj_customer_insert").remove();
jQuery("#recmachcustrecord_htl33_jj_customer__div > table > tbody > tr > td > .dottedlink").css('display','none');
}
/**
* Function to be executed when field is changed.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
* @param {string} scriptContext.fieldId - Field name
* @param {number} scriptContext.lineNum - Line number. Will be undefined if not a sublist or matrix field
* @param {number} scriptContext.columnNum - Line number. Will be undefined if not a matrix field
*
* @since 2015.2
*/
function fieldChanged(scriptContext) {
try{
var currentRec = scriptContext.currentRecord;
// Triggering the field change of field Forcast
if(scriptContext.fieldId=='custrecord_htl33_jj_forecast')
{
//Getting the sublist
var customRecord = currentRec.getSublist({
sublistId:'recmachcustrecord_htl33_jj_customer'
});
// Get the field value of forcast
var forcastvalue = currentRec.getCurrentSublistValue({
sublistId: 'recmachcustrecord_htl33_jj_customer',
fieldId : 'custrecord_htl33_jj_forecast'
});
// Get the Actual YTD value
var actualytdvalue = currentRec.getCurrentSublistValue({
sublistId: 'recmachcustrecord_htl33_jj_customer',
fieldId : 'custrecord_htl33_jj_actual_ytd_sales'
});
// calculate number of days
var numbrofdays = ((Math.round(Math.abs((new Date("4/1/"+(new Date().getFullYear()-1)) - new Date())/(24*60*60*1000))))-1);
// Calculate target value
Targetytdvalue = (parseFloat((forcastvalue/365)*numbrofdays).toFixed(2));
// Calcuated for showing color Red
newtarget = 0.8*Targetytdvalue;
currentRec.setCurrentSublistValue({
sublistId: 'recmachcustrecord_htl33_jj_customer',
fieldId: 'custrecord_htl33_jj_target_ytd_sales',
value: Targetytdvalue
});
// Condition to show green color
if(actualytdvalue >= Targetytdvalue){
currentRec.setCurrentSublistText({
sublistId: 'recmachcustrecord_htl33_jj_customer',
fieldId: 'custrecord_htl33_jj_status',
text: "Green.PNG"
});
}
// Condition to show red color
else if(actualytdvalue < newtarget){
currentRec.setCurrentSublistText({
sublistId: 'recmachcustrecord_htl33_jj_customer',
fieldId: 'custrecord_htl33_jj_status',
text: "Red.PNG"
});
}
// Condition to show yellow color
else{
currentRec.setCurrentSublistText({
sublistId: 'recmachcustrecord_htl33_jj_customer',
fieldId: 'custrecord_htl33_jj_status',
text: "Yellow.PNG"
});
}
currentRec.commitLine({
sublistId:'recmachcustrecord_htl33_jj_customer'
})
}
}catch (e) {
logme("firstTry", getError(e));
}
}
/*******************************************************************************
* return error
*
* @param e
* @returns
*
* Created on 22-Feb-2019 by Maria
*/
function getError(e) {
var stErrMsg = '';
if (e.getDetails != undefined) {
stErrMsg = '_' + e.getCode() + '<br>' + e.getDetails() + '<br>'
+ e.getStackTrace();
} else {
stErrMsg = '_' + e.toString();
}
return stErrMsg;
}
/*******************************************************************************
* Log these data
*
* @param title
* @param details
* @returns
*
* Created on 22-Feb-2019 by Maria
*/
function logme(title, details) {
log.error({
title : title,
details : details
});
}
return {
pageInit: pageInit,
fieldChanged: fieldChanged
};
});