Early in my NetSuite developer career, I was generally afraid of the Map/Reduce Script Type and did not understand what it was or how to leverage it. So, when asked to perform a process requiring a "find and update" type of logic, I leaned into a Scheduled Script Type as my go-to solution.
Then my first significant initiative landed on me. My stakeholders asked me to automate the customer payment collection process. I was nervous but confident I could manage this ask with a Scheduled Script. So I got to work on the Scheduled Script and delivered what I figured was a solid solution. (It was not)
The solution worked as intended for the first few months, with some routine hotfixes here and there. However, as more and more customers started opting into the Auto-Pay solution, an unknown ticking issue was sneaking up on me, the dreaded USAGE LIMIT EXCEEDED hard throw.
Each payment record the Script was creating cost 30 usage units (10 for record.create() and 20 for Record.save()). A Scheduled Script has 10,000 usage units available to use. The exact math puts that at 333 1/3 maximum transactions the Scheduled Script could create. Considering I was likely doing some record.load() or search.lookupFields(), It was probably closer to around 200 transactions that the Scheduled Script could create.
When the first errors came in, I panicked. I was still relatively early in my NetSuite developer path and had yet to see the USAGE LIMIT EXCEEDED error often. Thankfully I managed by re-running the Scheduled Script ad hoc to finish whatever remained. I knew this wasn't a solution I could be proud of, so I researched potential solutions that could scale up with the volume of customers adopting Auto-Pay, and that would ideally avoid the Usage Limit issues. Finally, after some extended reading online, I read that the Map/Reduce Script potentially had no limits to how many records it could create/update if used correctly.
With more confidence and hope, I went off to replace my hard work in the Scheduled Script with a Script Type I had feared for quite some time.
Full disclosure, the Map/Reduce example below looks nothing like my first Map/Reduce script from the above story. It was a hot mess, and I cringe when I think about it. The examples below are written with my current methods and in SuiteScript 2.1 compliance.
What is a Map/Reduce? Why does it theoretically have no usage limits (if used correctly)? At its base, in simple JavaScript terms, it is an Array and two of the Array prototypes; Array.map() and Array.reduce(). That's an oversimplification, but it's how I like to visualize the Map/Reduce Script in my mind.
Here is an incredibly rough and simple estimation of the inner workings of a Map/Reduce script in terms of JavaScript.
//getInputData() builds the initial JS Array
let getInputArray = getInputData(); //>> returns [{key: 1, value: 100}, {key: 1, value: 101}, {key: 2, value 200}]
//This is the map() entry point
//This loops every element of the getInputArray
let shuffleArray = getInputArray.map(mapContext => {
//Do stuff with the mapContext here
//You have 1,000 usage units to play with
let customerId = mapContext.key;
let invoiceId = mapContext.value;
return mapContext.write({key: customerId, value: invoiceId});
});
//This is the shuffle stage of the Script. It is NOT the reduce() entry point. That comes after the shuffle
let reduceArray = shuffleArray.reduce((acc, mapWriteObj) => {
const keyIndex = acc.findIndex(o => o.key === mapWriteObj.key);
if (keyIndex === -1) {
acc.push({key: mapWriteObj.key, values: [mapWriteObj.value]});
} else {
acc[keyIndex].values.push(mapWriteObj.value);
}
return acc;
}, []);
//>> returns [{key: 1, values: [100, 101]}, {key: 2, values: [200]}]
//The reduce() entry point loops the reduceArray
reduceArray.forEach(reduceContext => {
log.debug('Reduce Key', reduceContext.key); //>> customerId IE: 1
log.debug('Reduce Values', reduceContext.values); //>> Array of invoiceId(s) IE: [100, 101]
//Do stuff with the reduceContext here
//You have 5,000 usage units to play with
});
It's cool to see an approximation of what the Map/Reduce Script is doing, but how does that help with my Usage Limitations? The Map/Reduce Goverance documentation says the reduce() entry point gets 5,000 usage units. How is that better than the Scheduled Script's 10,000 units of usage? As I showed in the above Anatomy of the Map/Reduce Script, the reduce() entry point loops every element of the Array, and each time it loops an element, you get a fresh new limit of 5,000 units! So if my Array is 100 elements, let's say they're 100 transactions that need some fields updated. I have 5,000 units per 1 transaction to update. That's 500,000 potential units I could use.
I will show how to use a Map/Reduce to create payments for customers with open invoices. Although the script sections and the final Script at the end are meant to be educational, feel free to leverage what you want if you find anything worthwhile. That said, I am not guaranteeing it will work out of the box for you. Use at your own risk.
First things first, let's leverage some SuiteQL! I am a massive fan of SuiteQL, and I've been leveraging it in nearly every Script I use since I learned about the N/query Module. This SQL statement below will get me open past due invoices, and only from customers who have opted into Auto-Pay with a custom checkbox on the customer record "custentity_autopay".
SELECT c.id AS customerid
,t.id AS invoiceid
FROM transaction AS t
INNER JOIN customer AS c ON t.entity = c.id
WHERE t.type = 'CustInvc'
AND BUILTIN.DF(t.status) = 'Invoice : Open' -- I use BUILTIN.DF() to easily show I want open invoices, but you could also use t.status = 'A'
AND t.duedate <= SYSDATE
AND NVL(c.custentity_autopay, 'F') = 'T'
Let's feed that into the getInputData() entry point.
const getInputData = (inputContext) => {
log.debug('Getting Data', inputContext);
//SQL to get invoices that are open, past due, and only from auto-pay customers
let sql = `
SELECT c.id AS customerid
,t.id AS invoiceid
FROM transaction AS t
INNER JOIN customer AS c ON t.entity = c.id
WHERE t.type = 'CustInvc'
AND BUILTIN.DF(t.status) = 'Invoice : Open'
AND t.duedate <= SYSDATE
AND NVL(c.custentity_autopay, 'F') = 'T'
`;
return {
type: 'suiteql',
query: sql,
params: []
};
};
It's that easy. I absolutely LOVE using SuiteQL with the getInputData() entry point. No shade at N/search, of course, but if you already know some basic SQL, you can quickly jump directly into complex data gathering. For ease of the article, let's assume that this SQL will return this simple results table.
| customerid | invoiceid |
|---|---|
| 1 | 100 |
| 1 | 101 |
| 2 | 200 |
Let's keep up the momentum and dive straight into the map() entry point. In this example, I will use the map() entry point to pass the customerid as my unique primary key to reduce() accumulate with. That should give us an Array of invoiceid values inside the reduce() entry point.
When using a getInputData() that returns a resultset (both a search and query will return as a resultset), the value of the mapContext is JSON.stringify()'d.
const map = (mapContext) => {
//mapContext.value is returned as a string, JSON.parse it back to an Object
//parses to {"types":["INTEGER","INTEGER"],"values":[1,100]}
let mapValue = JSON.parse(mapContext.value);
//write back consolidating the Customer Id and send the Array of invoices
mapContext.write(mapValue.values[0], mapValue.values[1]);
};
During the shuffle part of the Anatomy shown above, this will generate an Array with a unique customerid key with an Array of invoiceid(s) as values.
[
{
"key": 1,
"values": [
100,
101
]
},
{
"key": 2,
"values": [
200
]
},
]
So far so good. We have a grouped Array ready to be leveraged in the reduce() entry point. We will build a customer payment that applies to all invoices in our values Array of invoiceid(s).
const reduce = (reduceContext) => {
let customerId = reduceContext.key;
let invoices = reduceContext.values;
//Create the Customer Payment Record
let customerPaymentRec = record.create({
type: record.Type.CUSTOMER_PAYMENT,
isDynamic: true,
defaultValues: {
entity: customerId
}
});
customerPaymentRec.setValue({
fieldId: 'memo',
value: 'Auto Payment via Script'
})
.setValue({
fieldId: 'currency',
value: 1 //Assume everything is the base currency
});
invoices.forEach(invoiceId => {
log.debug('Invoice Id', invoiceId);
//All of the invoices load in the apply sublist
//We can find them quickly by their internal id and findSublistLineWithValue
let invoiceApplyIndex = customerPaymentRec.findSublistLineWithValue({
sublistId: 'apply',
fieldId: 'internalid',
value: invoiceId
});
//If the invoice is in the apply list, it will return the line number else -1, if -1, skip it
if (invoiceApplyIndex > -1) {
//We created the Payment Record in dynamic mode so that it will auto-fill the amount fields
//Select the line of the invoiceApplyIndex, check the apply checkbox, and commit the line
customerPaymentRec.selectLine({
sublistId: 'apply',
line: invoiceApplyIndex
})
.setCurrentSublistValue({
sublistId: 'apply',
fieldId: 'apply',
value: true
})
.commitLine({
sublistId: 'apply'
});
}
});
try {
let customerPaymentId = customerPaymentRec.save();
log.audit('Payment Saved', customerPaymentId);
} catch (e) {
log.error('Payment Save Error', e);
}
};
With that, we now have all the main pieces of the Map/Reduce Script that will create Customer Payments automatically for Customers who have opted into Auto-Pay. I decommissioned my old Scheduled Script and scheduled this new Map/Reduce Script, making Usage Limitations an issue of the past.
Of course, this is a basic example, but it's a great way to show off the unlimited power of the Map/Reduce Script. One entry point that I did not go into too many details with is the summarize() entry point. It is required, but I didn't have anything useful to show off with it in this story. In later articles, I will do even deeper dives into each entry point to show how they can each be leveraged.
Here is the final complete Map/Reduce Script.
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
define([
'N/record'
],
(record) => {
const getInputData = (inputContext) => {
log.debug('Getting Data', inputContext);
//SQL to get invoices that are open, past due, and only from auto-pay customers
let sql = `
SELECT c.id AS customerid
,t.id AS invoiceid
FROM transaction AS t
INNER JOIN customer AS c ON t.entity = c.id
WHERE t.type = 'CustInvc'
AND BUILTIN.DF(t.status) = 'Invoice : Open'
AND t.duedate <= SYSDATE
AND NVL(c.custentity_autopay, 'F') = 'T'
`;
return {
type: 'suiteql',
query: sql,
params: []
};
};
const map = (mapContext) => {
//mapContext.value is returned as a string, JSON.parse it back to an Object
//parses to {"types":["INTEGER","INTEGER"],"values":[1,11]}
let mapValue = JSON.parse(mapContext.value);
//write back consolidating the Customer Id and send the Array of invoices
mapContext.write(mapValue.values[0], mapValue.values[1]);
};
const reduce = (reduceContext) => {
let customerId = reduceContext.key;
let invoices = reduceContext.values;
//Create the Customer Payment Record
let customerPaymentRec = record.create({
type: record.Type.CUSTOMER_PAYMENT,
isDynamic: true,
defaultValues: {
entity: customerId
}
});
customerPaymentRec.setValue({
fieldId: 'memo',
value: 'Auto Payment via Script'
})
.setValue({
fieldId: 'currency',
value: 1 //Assume everything is the base currency
});
invoices.forEach(invoiceId => {
log.debug('Invoice Id', invoiceId);
//All of the invoices load in the apply sublist
//We can find them quickly by their internal id and findSublistLineWithValue
let invoiceApplyIndex = customerPaymentRec.findSublistLineWithValue({
sublistId: 'apply',
fieldId: 'internalid',
value: invoiceId
});
//If the invoice is in the apply list, it will return the line number else -1, if -1, skip it
if (invoiceApplyIndex > -1) {
//We created the Payment Record in dynamic mode so that it will auto-fill the amount fields
//Select the line of the invoiceApplyIndex, check the apply checkbox, and commit the line
customerPaymentRec.selectLine({
sublistId: 'apply',
line: invoiceApplyIndex
})
.setCurrentSublistValue({
sublistId: 'apply',
fieldId: 'apply',
value: true
})
.commitLine({
sublistId: 'apply'
});
}
});
try {
let customerPaymentId = customerPaymentRec.save();
log.audit('Payment Saved', customerPaymentId);
} catch (e) {
log.error('Payment Save Error', e);
}
};
const summarize = (summaryContext) => {
log.debug('Finished', summaryContext);
};
return {
getInputData: getInputData,
map: map,
reduce: reduce,
summarize: summarize
};
}
);