REQUIREMENT
The requirement is that need to update the none of PO status closed, canceled and billed PO’s with the new item rate when it is being updated in the item record.
SOLUTION
Client script for to check if the item rate is changed in Item Record
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(['N/currentRecord', 'N/record', 'N/search'],
/**
* @param{currentRecord} currentRecord
* @param{record} record
* @param{search} search
*/
function(currentRecord, record, search) {
function saveRecord(scriptContext) {
try{
log.debug('In save record')
var current_rec = scriptContext.currentRecord
var fieldLookUp = search.lookupFields({
type: "inventoryitem",
id: current_rec.id,
columns: ['cost']
});
log.debug('Lookup', fieldLookUp.cost)
var value_cost = scriptContext.currentRecord.getValue({
fieldId: 'cost'
})
log.debug('Current reco', value_cost)
if(value_cost==''){
alert("Please enter a valid item cost!")
log.debug('In true blank cost')
}
else if ((fieldLookUp.cost != value_cost)) {
if (confirm("Changing the item cost will update all the open purchase order item rates. Do you wish to proceed? Select Cancel if you don't want this change.")) {
log.debug('In true if')
var custrecord_new = record.create({
type: 'customrecord_po_auto_update_tag101',
isDynamic: true
})
var recordId = custrecord_new.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
log.debug('Cus record id',recordId)
var set_field = record.submitFields({
type: 'customrecord_po_auto_update_tag101',
id: recordId,
values: {
'custrecord_item_details':current_rec.id,
'custrecord_item_id':current_rec.id,
'custrecord_new_price': value_cost,
'custrecord_old_price': fieldLookUp.cost
}
});
log.debug('Cus record new',set_field)
return true;
}
else{
return false;
}
}
else {
return true;
}
}
catch (e) {
}
}
return {
saveRecord: saveRecord
};
});
A custom record is created to store the updated price, old price, item name and related details. User event script is called on creation of the custom record.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/currentRecord', 'N/record', 'N/runtime', 'N/search','N/task'],
/**
* @param{currentRecord} currentRecord
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{task} task
*/
(currentRecord, record, runtime, search,task) => {
const afterSubmit = (scriptContext) => {
try{
//To call map reduce script when an custom record is created
if (scriptContext.type === 'create'){
log.debug('In userevent')
var scriptTask = task.create({
taskType: task.TaskType.MAP_REDUCE
});
scriptTask.scriptId = 'customscript_jj_mr_itemcost_updte_tag101';
scriptTask.deploymentId = 'customdeploy_jj_mr_itemcost_updte_tag101';
var scriptTaskId = scriptTask.submit();
log.debug('Called Map reduce script')
}
}
catch (e) {
log.debug('Error at Aftersubmit',e)
}
}
return {afterSubmit}
});
Map reduce script is executed to update the item line in the purchase order with the new value .
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define(['N/currentRecord', 'N/record', 'N/search', 'N/task'],
/**
* @param{currentRecord} currentRecord
* @param{record} record
* @param{search} search
* @param{task} task
*/
(currentRecord, record, search, task) => {
/**
const getInputData = (inputContext) => {
try{
log.debug('In getinput')
//Search created to get records for completed checkbox is false
var customrecord_po_auto_update_SearchObj = search.create({
type: "customrecord_po_auto_update_tag101",
filters:
[
["custrecord_completed","is","F"],
"AND",
["custrecord_new_price","isnotempty",""]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "custrecord_item_id", label: "Item"}),
search.createColumn({name: "custrecord_old_price", label: "Old price"}),
search.createColumn({name: "custrecord_new_price", label: "New price"}),
search.createColumn({name: "custrecord_completed", label: "Completed"})
]
});
var searchResult = customrecord_po_auto_update_SearchObj.run().getRange({
start: 0,
end: 1000
});
return searchResult;
}catch (e) {
log.debug('Error at Getinputdata')
}
}
/**
/**
* 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 dataObj = JSON.parse(reduceContext.values)
log.debug('Current ite id',dataObj.values.custrecord_item_id)
//Search created to retrive purchase order records with status none of closed billed
var purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters:
[
["type","anyof","PurchOrd"],
"AND",
["status","noneof","PurchOrd:G","PurchOrd:H","PurchOrd:F"],
"AND",
// ["item.internalid","anyof",dataObj.values.custrecord_item_id]
["item.internalid","anyof","6259"],
"AND",
["internalid","anyof","15215"]
],
columns:
[
search.createColumn({
name: "ordertype",
sort: search.Sort.ASC,
label: "Order Type"
}),
search.createColumn({name: "internalid", label: "Internal Id"})
]
});
var searchResult = purchaseorderSearchObj.run().getRange({
start: 0,
end: 1000
});
var new_array=[];
for (var j = 0; j < searchResult.length; j++) {
var internal_id = searchResult[j].getValue({name: "internalid", label: "Internal Id"})
log.debug('Internal id of purchase orders',internal_id)
new_array.push(internal_id);
log.debug('New array',new_array);
//load purchase record
var purchase_rec = record.load({
type: record.Type.PURCHASE_ORDER,
id: internal_id,
isDynamic: true,
})
//get item line count of particular purchase order
var line_count = purchase_rec.getLineCount({
sublistId: 'item'
});
log.debug('')
//looping through each item line
for (var i = 0; i < line_count; i++)
{
purchase_rec.selectLine({
sublistId: 'item',
line: i
});
//id of item from item line
var item_id_from_itemline = purchase_rec.getCurrentSublistValue({
sublistId: 'item',
fieldId: 'item',
});
log.debug('item id from itemline', item_id_from_itemline)
if (item_id_from_itemline == dataObj.values.custrecord_item_id) { // check item id equals item line id
log.debug('Setting value', '.............')
var each_line_setvalue = purchase_rec.setCurrentSublistValue({ //rate field of item
sublistId: 'item',
fieldId: 'rate',
value: dataObj.values.custrecord_new_price
});
purchase_rec.commitLine({
sublistId: 'item'
});
}
}
purchase_rec.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
}
var set_completed= record.submitFields({
type: 'customrecord_po_auto_update_tag101',
id: dataObj.values.internalid[0].value,
values: {
'custrecord_completed': true,
'custrecord_po_updated':new_array,
},
options: {
enableSourcing: false,
ignoreMandatoryFields : true
}
});
}catch (e) {
log.debug('Error at Reduce',e)
}
}
/**
* 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{
log.debug('In summarize')
var customrecord_po_auto_update_SearchObj = search.create({
type: "customrecord_po_auto_update_tag101",
filters:
[
["custrecord_completed","is","F"]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "custrecord_item_id", label: "Item"}),
search.createColumn({name: "custrecord_old_price", label: "Old price"}),
search.createColumn({name: "custrecord_new_price", label: "New price"}),
search.createColumn({name: "custrecord_completed", label: "Completed"})
]
});
var searchResultCount = customrecord_po_auto_update_SearchObj.runPaged().count;
if(searchResultCount>0){
var scriptTask = task.create({
taskType: task.TaskType.MAP_REDUCE
});
scriptTask.scriptId = 'customscript_jj_mr_itemcost_updte_tag101';
scriptTask.deploymentId = 'customdeploy_jj_mr_itemcost_updte_tag101';
var scriptTaskId = scriptTask.submit();
log.debug('Called Map reduce script in summarize')
}
}catch (e) {
log.debug('Error at summarize')
}
}
return {getInputData, reduce, summarize}
});