Requirement
We need to ability to select the purchase orders for updating the line-level item cost. We 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. But this should not update the item cost for all the purchase orders. We need to add new functionality for selecting the purchase orders for updating the price.
Solution
When the “Item cost for Supplier” field value in item record is updated and saved, a suitelet page will appear, showing the open POs with the information of the particular item and associated purchase orders, including the old price, new price, and some other purchase order details.
The purchase orders that should be affected by the new item pricing can be chosen from it. When you submit the suitelet page, the selected purchase orders will be updated in the background with the new pricing.
Client script (Executed when item cost is updated)
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(['N/currentRecord', 'N/record', 'N/search','N/url'],
/**
* @param{currentRecord} currentRecord
* @param{record} record
* @param{search} search
* @param{url} url
*/
function (currentRecord, record, search,url) {
var contextMode
/**
* 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) {
contextMode = scriptContext.mode
}
/**
* Validation function to be executed when record is saved.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @returns {boolean} Return true if record is valid
*
* @since 2015.2
*/
function saveRecord(scriptContext) {
try {
if (contextMode == "edit") {
//When the item record is edited
console.log('contextMode', contextMode)
var current_rec = scriptContext.currentRecord
console.log(("current_rec", current_rec))
//Get the old cost from the item record.
var fieldLookUp = search.lookupFields({
type: "inventoryitem",
id: current_rec.id,
columns: ['cost']
});
log.debug('Lookup', fieldLookUp.cost)
//Get the new cost of the item
var value_cost = scriptContext.currentRecord.getValue({
fieldId: 'cost'
})
log.debug('Current reco', value_cost)
//If the cost value is null or empty on its save
if (value_cost == '') {
alert("Please enter a valid rate for Item Cost FOB Supplier (PURCHASE PRICE)!")
log.debug('In true blank cost')
}
//If the old cost and updated cost are different the following line of code executes.
else if ((fieldLookUp.cost != value_cost)) {
//Alert showing that the item cost will update the slected purchase orders from the suitlet purchase orders, so that it opens up into a suitelet page showing the open purchase orders with this item
alert("Change in item cost will update the open purchase orders.Select the purchase orders you wish to update.")
log.debug('In true if')
try{
function callSuiltelet(recId,oldCostValue,newCost) {
log.debug('called suitlet.......')
var suiteletURL = url.resolveScript({
scriptId: 'customscript_jj_sl_item_cost_po_tag_162',
deploymentId: 'customdeploy_jj_sl_item_cost_pos_tag_162',
returnExternalUrl: false,
params: {
'custscript_jj_item_id': recId,
'custscript_jj_old_price': oldCostValue,
'custscript_jj_item_price': newCost,
}
});
return suiteletURL;
}
var recId=current_rec.id;
var oldCostValue=fieldLookUp.cost
var newCost=value_cost
var suiteletFun= callSuiltelet(recId,oldCostValue,newCost)
log.debug('Suitlet url',suiteletFun)
window.open(suiteletFun);
}
catch (e) {
log.debug('Error@Callsuitlet ',e)
}
return true;
}
} else {
var current_rec = scriptContext.currentRecord
var value_cost = current_rec.getValue({
fieldId: 'cost'
})
if (value_cost == '') {
alert("Please enter a valid rate for Item Cost FOB Supplier (PURCHASE PRICE)!")
log.debug('In true blank cost')
}
return true
}
}
catch (e) {
console.log("error@saveRecord", e)
}
}
return {
pageInit: pageInit,
saveRecord: saveRecord,
};
});
Suitelet (Suitelet page is loaded when item cost is changed and saved)
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
define(['N/record', 'N/redirect', 'N/runtime', 'N/search', 'N/ui/serverWidget'],
/**
* @param{record} record
* @param{redirect} redirect
* @param{runtime} runtime
* @param{search} search
* @param{serverWidget} serverWidget
*/
(record, redirect, runtime, search, serverWidget) => {
/**
* Defines the Suitelet script trigger point.
* @param {Object} scriptContext
* @param {ServerRequest} scriptContext.request - Incoming request
* @param {ServerResponse} scriptContext.response - Suitelet response
* @since 2015.2
*/
function getPurchaseOrders(getItemId) {
var purchaseorderSearchObj = search.create({
type: "purchaseorder",
filters:
[
["type", "anyof", "PurchOrd"],
"AND",
["status", "noneof", "PurchOrd:G", "PurchOrd:H", "PurchOrd:F"],
"AND",
["item.internalid", "anyof", getItemId]
],
columns:
[
search.createColumn({
name: "ordertype",
sort: search.Sort.ASC,
label: "Order Type"
}),
search.createColumn({name: "internalid", label: "Internal Id"}),
search.createColumn({name: "trandate", label: "Date"}),
search.createColumn({name: "tranid", label: "Document Number"}),
search.createColumn({name: "entity", label: "Name"}),
search.createColumn({name: "amount", label: "Amount"}),
search.createColumn({name: "subsidiary", label: "Subsidiary"}),
search.createColumn({name: "locationnohierarchy", label: "Location (no hierarchy)"}),
search.createColumn({
name: "formulatext",
formula: "'<a href=\"https://6753382-rp.app.netsuite.com/app/accounting/transactions/purchord.nl?id='||{internalid}||'\" target=\"_blank\">'||{tranid}||'</a>'",
label: "PO"
}),
search.createColumn({
name: "entityid",
join: "vendor",
label: "Name"
}),
search.createColumn({
name: "location",
join: "item",
label: "Location"
}),
search.createColumn({
name: "quantityavailable",
join: "item",
label: "Available"
}),
search.createColumn({name: "statusref", label: "Status"}),
search.createColumn({name: "item", label: "Item"}),
search.createColumn({name: "quantity", label: "Quantity"}),
search.createColumn({name: "rate", label: "Item Rate"}),
search.createColumn({name: "amount", label: "Amount"}),
search.createColumn({
name: "entityid",
join: "CUSTBODY8",
label: "Name"
}),
search.createColumn({name: "memo", label: "Memo"}),
search.createColumn({name: "memomain", label: "Memo (Main)"}),
search.createColumn({name: "custcol_so_number", label: "SO#"}),
search.createColumn({name: "custbody_created_from_cust", label: "Created From(2)"})
]
});
var searchResult = purchaseorderSearchObj.run().getRange({
start: 0,
end: 1000
});
return searchResult;
}
const onRequest = (scriptContext) => {
try {
if (scriptContext.request.method === 'GET') {
log.debug('In get')
var form = serverWidget.createForm({title: 'ITEM COST UPDATE IN PURCHASE ORDERS'});
var itemDetailsGrid = form.addFieldGroup({id: '_itemdet', label: 'Item Details'});
form.clientScriptFileId=4963
var params = scriptContext.request.parameters
log.debug('Params', params)
var itemId = form.addField({
id: 'custpage_itemid',
type: serverWidget.FieldType.TEXT,
label: 'Item Id',
container: '_itemdet'
});
var itemNumber = form.addField({
id: 'custpage_itemnum',
type: serverWidget.FieldType.TEXT,
label: 'Item Number',
container: '_itemdet'
});
itemNumber.updateDisplayType({
displayType: serverWidget.FieldDisplayType.INLINE
});
var itemNum= search.lookupFields({
type: search.Type.ITEM,
id:params.custscript_jj_item_id,
columns: ['itemid']
});
log.debug('Item number',itemNum)
itemId.defaultValue = params.custscript_jj_item_id
itemNumber.defaultValue=itemNum.itemid
//itemId.defaultValue = itemNum.itemid
log.debug('itemid', itemId)
itemId.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
var itemNameLookUp = search.lookupFields({
type: "inventoryitem",
id: params.custscript_jj_item_id,
columns: ['salesdescription']
});
var itemName = form.addField({
id: 'custpage_itemname',
type: serverWidget.FieldType.TEXT,
label: 'Item Name',
container: '_itemdet',
src: 'item'
});
log.debug('iteminame0', itemName)
itemName.updateDisplayType({
displayType: serverWidget.FieldDisplayType.INLINE
});
itemName.defaultValue = itemNameLookUp.salesdescription
var oldPrice = form.addField({
id: 'custpage_oldprice',
type: serverWidget.FieldType.TEXT,
label: 'Item Old Price',
container: '_itemdet'
});
log.debug('old', oldPrice)
oldPrice.defaultValue = params.custscript_jj_old_price
oldPrice.updateDisplayType({
displayType: serverWidget.FieldDisplayType.INLINE
});
var newPrice = form.addField({
id: 'custpage_newprice',
type: serverWidget.FieldType.TEXT,
label: 'Item New price',
container: '_itemdet'
});
log.debug('newp', newPrice)
newPrice.defaultValue = params.custscript_jj_item_price
newPrice.updateDisplayType({
displayType: serverWidget.FieldDisplayType.INLINE
});
var purchaseOrderGrid = form.addFieldGroup({id: '_podet', label: 'Purchase Order Details'});
var sublistDetails = form.addSublist({
id: 'custpage_sublist',
type: serverWidget.SublistType.LIST,
label: 'Purchase Order Details'
});
sublistDetails.addMarkAllButtons();
var select = sublistDetails.addField({
id: 'custpage_checkid',
type: serverWidget.FieldType.CHECKBOX,
label: 'SELECT'
})
var intid = sublistDetails.addField({
id: 'custpage_intid',
type: serverWidget.FieldType.TEXT,
label: 'INTERNAL ID'
})
intid.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
var poId = sublistDetails.addField({
id: 'custpage_docnum',
type: serverWidget.FieldType.TEXT,
label: 'PO #'
})
var date = sublistDetails.addField({
id: 'custpage_date',
type: serverWidget.FieldType.TEXT,
label: 'DATE'
})
var vendor = sublistDetails.addField({
id: 'custpage_vendor',
type: serverWidget.FieldType.TEXT,
label: 'VENDOR'
})
var quantity = sublistDetails.addField({
id: 'custpage_quantity',
type: serverWidget.FieldType.TEXT,
label: 'QUANTITY'
})
var itemCost = sublistDetails.addField({
id: 'custpage_cost',
type: serverWidget.FieldType.TEXT,
label: 'ITEM COST'
})
var amnt = sublistDetails.addField({
id: 'custpage_amnt',
type: serverWidget.FieldType.TEXT,
label: 'AMOUNT'
})
var cust = sublistDetails.addField({
id: 'custpage_cust',
type: serverWidget.FieldType.TEXT,
label: 'CUSTOMER'
})
var memo=sublistDetails.addField({
id: 'custpage_memo',
type: serverWidget.FieldType.TEXT,
label: 'MEMO'
})
var so=sublistDetails.addField({
id: 'custpage_so',
type: serverWidget.FieldType.TEXT,
label: 'SO #'
})
var status = sublistDetails.addField({
id: 'custpage_status',
type: serverWidget.FieldType.TEXT,
label: 'STATUS'
})
var getItemId = params.custscript_jj_item_id;
var getOldPrice = params.custscript_jj_old_price;
var getNewPrice = scriptContext.request.parameters.custscript_jj_item_price;
var purchaseOrders = getPurchaseOrders(getItemId)
for (var i = 0; i < purchaseOrders.length; i++) {
sublistDetails.setSublistValue({
id: 'custpage_intid',
line: i,
value: (purchaseOrders[i].getValue({name: "internalid", label: "Internal Id"}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_cost',
line: i,
value: (purchaseOrders[i].getValue({name: "rate", label: "Item Rate"}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_docnum',
line: i,
value:(purchaseOrders[i].getValue({
name: "formulatext",
formula: "'<a href=\"https://6753382-rp.app.netsuite.com/app/accounting/transactions/purchord.nl?id='||{internalid}||'\" target=\"_blank\">'||{tranid}||'</a>'",
label: "PO"
})) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_cust',
line: i,
value: (purchaseOrders[i].getValue({
name: "entityid",
join: "CUSTBODY8",
label: "Name"
}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_memo',
line: i,
value: (purchaseOrders[i].getValue({name: "memomain", label: "Memo (Main)"}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_so',
line: i,
value: (purchaseOrders[i].getText({name: "custbody_created_from_cust", label: "Created From(2)"}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_date',
line: i,
value: (purchaseOrders[i].getValue({name: "trandate", label: "Date"}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_vendor',
line: i,
value: (purchaseOrders[i].getValue({
name: "entityid",
join: "vendor",
label: "Name"
}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_status',
line: i,
value: (purchaseOrders[i].getText({name: "statusref", label: "Status"}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_quantity',
line: i,
value: (purchaseOrders[i].getValue({name: "quantity", label: "Quantity"}
)) || " "
});
sublistDetails.setSublistValue({
id: 'custpage_amnt',
line: i,
value: (purchaseOrders[i].getValue({name: "amount", label: "Amount"}
)) || " "
});
}
form.addSubmitButton({
id: 'custpage_update_po',
label: 'SUBMIT',
// functionName:fun
})
scriptContext.response.writePage(form);
} else if (scriptContext.request.method === 'POST') {
var internal_id = scriptContext.request.parameters.custpage_itemid;
var OldPrice = scriptContext.request.parameters.custpage_oldprice;
var NewPrice = scriptContext.request.parameters.custpage_newprice;
var sublistCount = scriptContext.request.getLineCount({group: "custpage_sublist"});
var custrecord_new = record.create({
type: 'customrecord_po_auto_update_tag101',
isDynamic: true
})
var recordId = custrecord_new.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
var set_field = record.submitFields({
type: 'customrecord_po_auto_update_tag101',
id: recordId,
values: {
'custrecord_item_details': internal_id,
'custrecord_item_id': internal_id,
'custrecord_new_price': NewPrice,
'custrecord_old_price': OldPrice
}
});
var poArray = []
for (var j = 0; j < sublistCount; j++) {
var getStatus = scriptContext.request.getSublistValue({
group: 'custpage_sublist',
name: 'custpage_checkid',
line: j
});
// var poArray = []
if (getStatus === 'T') {
var po_intId = scriptContext.request.getSublistValue({
group: 'custpage_sublist',
name: 'custpage_intid',
line: j
});
poArray.push(po_intId)
}
}
if (poArray.length > 0) {
// confirmMessage;
var set_poid = record.submitFields({
type: 'customrecord_po_auto_update_tag101',
id: recordId,
values: {
'custrecord_jj_po_ids': poArray
}
});
}
var htmlCode = '<html><body><script type="text/javascript">window.close()</script></body></html>'
scriptContext.response.write(htmlCode);
}
} catch (e) {
log.debug('Error@OnRequest', e)
}
}
return {onRequest}
});
A custom record entry is created on submitting the suitelet page. The item details along with the purchase orders selected is added in the custom record. Userevent script is called when custom record for this is created.
/**
* @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'){
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();
}
}
catch (e) {
log.debug('Error at Aftersubmit',e)
}
}
return {afterSubmit}
});
Map reduce script to update the purchase orders item cost. This is called from the user event script.
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define(['N/currentRecord', 'N/email', 'N/record', 'N/runtime', 'N/search', 'N/task'],
/**
* @param{currentRecord} currentRecord
* @param{email} email
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{task} task
*/
(currentRecord, email, record, runtime, 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"}),
search.createColumn({name: "custrecord_jj_po_ids", label: "PO IDS"})
]
});
var searchResult = customrecord_po_auto_update_SearchObj.run().getRange({
start: 0,
end: 1000
});
return searchResult;
} catch (e) {
log.debug('Error at Getinputdata')
}
}
const reduce = (reduceContext) => {
try {
log.debug('In Reduce', reduceContext)
let dataObj = JSON.parse(reduceContext.values)
log.debug('Custom record search result', dataObj)
log.debug('Current ite id', dataObj.values.custrecord_item_id)
log.debug('value from search res ', dataObj.values.custrecord_jj_po_ids)
//Get the count of the POs to be updated.
var length = dataObj.values.custrecord_jj_po_ids.length
var poArray = []
//Iterating through POs and pushing the PO ids to an array
for (var p = 0; p < length; p++) {
poArray.push(dataObj.values.custrecord_jj_po_ids[p].value)
}
var new_array = [];
//Iterating through the PO arrays and setting the value to the custom field in the custom record.
if (poArray.length > 0) {
for (var j = 0; j < poArray.length; j++) {
var internal_id = poArray[j]
//Setting the value to the Custom record field
var set_field = record.submitFields({
type: 'customrecord_po_auto_update_tag101',
id: dataObj.id,
values: {
'custrecord_po_updated': internal_id,
}
});
new_array.push(internal_id);
log.debug('New array', new_array);
//load purchase record having the id
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',
});
if (item_id_from_itemline == dataObj.values.custrecord_item_id) { // check item id equals item line id
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
});
}
}
//When the update is complete, the completed checkbox is checked and updated POs are added to the field in custom record.
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)
}
}
const summarize = (summaryContext) => {
try {
log.debug('In summarize')
//Custom record search is done for to get the entries with completed checkbox as false.
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"}),
search.createColumn({name: "custrecord_jj_po_ids", label: "PO IDS"})
]
});
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();
}
} catch (e) {
log.debug('Error at summarize')
}
}
return {getInputData, reduce, summarize}
});