Only Include the Manual in the First Order of an Item
Functional Spec
The company would like to include the instruction manual for a product only when that product is ordered for the first time by a customer. They have found that people don't read the manual when it's a re-order and resending it is a waste of money.
The company would like an "Include Manual" checkbox to be selected on the line level of the Sales Order if it's the first time this customer is ordering that item.
The "Include Manual" checkbox is only important for the fulfillment team. The order entry clerk doesn't care about it and won't be paying attention to it.
Design Questions
- What type of field should you use for the "Include Manual" checkbox (type of field and data type)?
- What type of script should you write?
- What modules will you need for this script?
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/query'],
/**
* @param{query} query
*/
(query) => {
/**
* Defines the function definition that is executed before record is loaded.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {string} scriptContext.type - Trigger type; use values from the context.UserEventType enum
* @param {Form} scriptContext.form - Current form
* @param {ServletRequest} scriptContext.request - HTTP request information sent from the browser for a client action only.
* @since 2015.2
*/
const beforeLoad = (scriptContext) => {
}
/**
* Defines the function definition that is executed before record is submitted.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.type - Trigger type; use values from the context.UserEventType enum
* @since 2015.2
*/
const beforeSubmit = (scriptContext) => {
try {
// assign newRecord to an easier to write variable
// remember that newRecord is passed in as part of the context and represents the record after save
const so = scriptContext.newRecord;
// getValue is a method of record.Record - a record object
// It is defined in the N/record module, but we didn't need to load the module since the methods are included with the Record object
const customerId = so.getValue({
fieldId: 'entity'
});
// item is a sublist on most transaction records, in this case sales order https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2022_2/script/record/salesorder.html
// count the lines on the order
const lineCount = so.getLineCount({
sublistId: 'item'
});
// we need to extract the item ids, this is where we'll put them
// extracting the item ids allows us to query for them once instead of per line
// we are using a Set, this allows us to easily retrieve unique ids, an array would work too in this case but sometimes it's cleaner to get unique values
const lineItemIds = new Set();
// GET ITEM IDS TO MAKE ONE QUERY FOR ALL
for (let i = 0; i < lineCount; i++) {
const itemId = so.getSublistValue({
fieldId: 'item',
sublistId: 'item',
line: i
});
lineItemIds.add(itemId); // this would be push if using an array
}
// make a lookup for all items on the order, if an item is duplicated on the order it will have the same results
// extract the line item ids into an array
const firstOrderLookup = firstOrder({
soId: so.id,
customerId,
itemList: [...lineItemIds]
});
// SET CHECKBOX ON LINES BASED ON FIRST ORDER LOOKUP
for (let i = 0; i < lineCount; i++) {
// get the item id again. We consolidated the query to once per order but need to get the sublist item value on either side of the consolidated query
// it's more efficient this way since the query hits the NS server (and therefore consumes governance units) but getSublistValue is in local memory (and therefore no memory usage)
const itemId = so.getSublistValue({
fieldId: 'item',
sublistId: 'item',
line: i
});
// we can log out the results in debug, we'll filter this out by switching to Audit log level on the script deployment
log.debug({
title: 'FirstOrderLookup',
details: firstOrderLookup
});
// sometimes logging objects gives very detailed logs without much work
log.debug({
title: 'FirstOrderLookup',
details: {
checkboxValue: firstOrderLookup[itemId],
checkboxValueType: typeof firstOrderLookup[itemId]
}
});
// because our firstOrder function did all the heavy lifting, we can now retrieve the boolean for the checkbox
so.setSublistValue({
fieldId: 'custcol_bb_includemanual',
sublistId: 'item',
line: i,
value: firstOrderLookup[itemId]
});
}
} catch (error) {
// always catch errors in entry point to avoid preventing the user from saving
// error is the most "severe" log level that we can use, less severe are audit (saving/modifying records), and debug (development logs)
log.error({
title: 'Caught Error in Before Submit',
details: error
});
}
}
/**
* Defines the function definition that is executed after record is submitted.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {Record} scriptContext.oldRecord - Old record
* @param {string} scriptContext.type - Trigger type; use values from the context.UserEventType enum
* @since 2015.2
*/
const afterSubmit = (scriptContext) => {
}
/**
* Check if the item was ordered previously by this customer
*
* @param {Object} paramObject
* @param {number|string} paramObject.soId
* @param {number|string} paramObject.customerId
* @param {number|string} paramObject.itemList
* @returns {Object} returnObject an object with keys of itemIds, values of booleans indicating if it's the first time an item was ordered by this customer
*
*/
const firstOrder = ({soId, customerId, itemList}) => {
// because we have two different queries, define results at the function level
let results;
if (soId && soId !== '') {
// soId exists, this means the Sales Order (so) is in the database
// we want to only look for orders created before this order so check for internal ids less than this order
results = query.runSuiteQL({
query:`SELECT COUNT(transaction.id) so_count, transactionline.item FROM transactionline JOIN transaction ON transactionline.transaction = transaction.id AND transaction.type = 'SalesOrd' AND transaction.id < ? WHERE transaction.entity = ? AND transactionline.item IN (${itemList.join(',')}) GROUP BY item`,
params: [soId, customerId]
}).asMappedResults();
} else {
// soId doesn't exist, so don't compare with current id. Current Sales Order is not in the DB so won't show up in query
results = query.runSuiteQL({
query:`SELECT COUNT(transaction.id) so_count, transactionline.item FROM transactionline JOIN transaction ON transactionline.transaction = transaction.id AND transaction.type = 'SalesOrd' WHERE transaction.entity = ? AND transactionline.item IN (${itemList.join(',')}) GROUP BY item`,
params: [customerId]
}).asMappedResults();
}
log.debug({
title: 'Results in FirstOrder',
details: results
});
const returnObj = {};
// iterate through itemList to catch any items that have not been ordered
itemList.forEach((itemId)=>{
// https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/find
const foundResult = results.find(({item})=>{
return item == itemId
});
if (foundResult && foundResult.so_count > 0) {
// item has been ordered before so this isn't the first order
returnObj[itemId] = false;
} else {
// item has not been ordered so this is the first
returnObj[itemId] = true;
}
});
return returnObj;
}
return { beforeSubmit }
});