Description
Custom Fields “Project Status field” need to be created in the opportunity record.
We need to automate the update of the project status field with statuses by script.
The sourced custom list will need to contain the following statuses:
- Open
- Won
- Awaiting Shipping / Back Ordered
- Billed
- Closed Paid
Solution
The solution can be achieved by using a workflow and a scheduled script. The workflow is used to set the open and won status to the custom project status field in the Opportunity Record. Awaiting Shipping / Backordered , Billed and Closed Paid Status can be set to the project status field by using a Map reduce Script. This is not Real time. After running the scheduled script the status field will be changed.
Map Reduce Script to Change the Project Status Field
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/************************************************************************************************
NCPS - 70 Project Status Field Update by Script
*********************************************************************************************
*
* Author: Jobin & Jismi IT Services LLP
*
* Date Created : 10-January-2022
*
* Description : Project Status Field Update by Script.
*
* REVISION HISTORY
*
***********************************************************************************************/
define(['N/record', 'N/search'],
/**
* @param{record} record
* @param{search} search
*/
(record, search) => {
/**
*
* @param salesOrderId
* @returns {{}|*[]}
*/
const searchInvoice = (salesOrderId) => {
try {
let invoices = {};
let salesOrderSearchObj = search.create({
type: "salesorder",
filters:
[
["type", "anyof", "SalesOrd"],
"AND",
["mainline", "is", "F"],
"AND",
["internalid", "anyof", salesOrderId],
"AND",
["applyingtransaction.type", "anyof", "CustInvc"]
],
columns:
[
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({
name: "statusref",
join: "applyingTransaction",
label: "Status"
}),
search.createColumn({
name: "internalid",
join: "applyingTransaction",
label: "Internal ID"
})
]
});
let searchResultCount = salesOrderSearchObj.runPaged().count;
salesOrderSearchObj.run().each(function (result) {
let invoiceId = result.getValue({
name: "internalid",
join: "applyingTransaction",
label: "Internal ID"
});
let status = result.getValue({
name: "statusref",
join: "applyingTransaction",
label: "Status"
});
invoices[invoiceId] = status;
return true;
});
return invoices;
} catch (error) {
log.error("Error @searchInvoice", error);
return {};
}
}
/**
* 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) => {
/**
* @description Create a search to get the sales order status
* @type {Search}
*/
var salesorderSearchObj = search.create({
type: "salesorder",
filters:
[
["type", "anyof", "SalesOrd"],
"AND",
["status", "anyof", "SalesOrd:A", "SalesOrd:B", "SalesOrd:D", "SalesOrd:G", "SalesOrd:F", "SalesOrd:E"],
"AND",
["mainline", "is", "T"],
"AND",
["formulatext: {opportunity}", "isnotempty", ""],
"AND",
["opportunity.custbody_jj_cr_project_status_ncps62","noneof","5"]
],
columns:
[
search.createColumn({
name: "trandate",
summary: "MAX",
label: "Date"
}),
search.createColumn({
name: "tranid",
summary: "COUNT",
label: "Document Number"
}),
search.createColumn({
name: "internalid",
join: "opportunity",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "internalid",
summary: "MAX",
label: "Internal ID"
})
]
});
return salesorderSearchObj
}
/**
* 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 flag = false;
let searchResult = JSON.parse(reduceContext.values);
let salesOrderId = searchResult.values["MAX(internalid)"]
var salesOrderStatus = search.lookupFields({
type: search.Type.SALES_ORDER,
id: salesOrderId,
columns: ['statusref']
});
let statusValue = salesOrderStatus.statusref[0].value
let status ="";
if (statusValue === "pendingApproval" || statusValue === "pendingFulfillment" || statusValue === "partiallyFulfilled" || statusValue === "pendingBilling"|| statusValue === "pendingBillingPartFulfilled") {
status = 3
} else if (statusValue === "fullyBilled") {
let invoices = searchInvoice(salesOrderId);
let statusArray = Object.values(invoices);
flag = statusArray.includes("open");
if (flag) {
status = 4;
} else {
status = 5;
}
}
let opportunityID =searchResult.values["GROUP(internalid.opportunity)"].value
if (status && opportunityID) {
let currentRec=record.load({
type: record.Type.OPPORTUNITY,
id: opportunityID
});
currentRec.setValue({
fieldId: 'custbody_jj_cr_project_status_ncps62',
value: status
});
currentRec.save();
}
} catch (err) {
log.debug("Errro @reduce", err);
}
}
return {getInputData, reduce}
});