Learn NetSuite Map/Reduce for Updating Rates on Project Tasks

This article is relevant if you would like to study a NetSuite map/reduce pattern for updating records.

Background

In our NetSuite Systems Integration Practice, we use NetSuite’s Services Resource Planning (aka SRP or Advanced Projects) system to help us manage the assignment of consultants to projects.  In that system, we can assign our consultants’ rates.  We also use the software to track our costs.  Costs are tracked at the employee or vendor level (as we utilize senior professionals that are contractors) via the built-in labor cost field.

There are times we need to modify our rates and costs.  We want to ensure we do this uniformly and avoid mistakes.  While I could have chosen to develop a NetSuite Mass Update, I chose instead to create a NetSuite Map/Reduce script to perform the handy work.  I like the idea behind Map/Reduce because the pattern uses NetSuite’s multi-threaded architecture and I wanted to practice to reinforce the pattern in my understanding.

SuiteScript for Map/Reduce

Below is the basic pattern for the Map/Reduce.  The key input, using a script parameter, is an entity that can be either an employee or a vendor.  This Map/Reduce does not do any specialized grouping based on the data; hence I could have used the map function instead of the reduce function to do the actual work; yet when you only need one function, the reduce function provides more governance capacity.

// Script: PRI_MR_UpdateProjectRates.js
// Description: Use this Map Reduce to drive the updates of project rates by consultant
// 		the scope uses a saved search to avoid investment tasks and those that are connected
//		to the Prolecto phantom customer. All existing project tasks modified to update the cost. 
// Developer: 	Marty Zigman
// Date:        20200527

/**
 * @NApiVersion 2.0
 * @NScriptType MapReduceScript
 * @NModuleScope Public
 */

define([ 'N/error', 'N/record', 'N/runtime', 'N/search'],

function(error, record, runtime, search) {
	
	//global parameters [the target consultant]
	var logTitle = 'PRI_MR_UpdateProjectRates.js';
	var p = runtime.getCurrentScript()

	//get the resource id target
	var n = p.getParameter( {
		name: 'custscript_pri_task_resource_rate'
	});
	
	function getInputData() {
		var logTitle = 'PRI_MR_UpdateProjectRates.js getInputData';
		
		return search.create({
			   type: "projecttask",
			   filters:
			   [
			      ["projecttaskassignment.resource","anyof", n], 
			      "AND", 
			      ["custevent_pri_time_investment","is","F"], //avoid investments
			      "AND", 
			      ["job.customer","noneof","4066"]  //Prolecto
			   ],
			   columns:[
			      search.createColumn({
				         name: "id",
				      }),
			   ]
			});
	}

	function reduce(context){
		var logTitle = 'PRI_MR_UpdateProjectRates.js reduce';
		
		//get the project task ID
		var id = context.key
		

		//get the resource rate n
		var l = search.lookupFields({
             type: search.Type.EMPLOYEE,
             id: n,
             columns: ['laborcost']
        });
		
		//resource may not be an employee; lookup a vendor
		if (!l.laborcost) {
			
			var l = search.lookupFields({
	            type: search.Type.VENDOR,
	            id: n,
	            columns : ['laborcost']
	        });
		}
		
		var laborcost = l.laborcost
		
		
		//do the real work
		var task = record.load({
             type: search.Type.PROJECT_TASK,
             id: id,
		});
		
        var l = task.getLineCount({
            sublistId: 'assignee'
        });

        //walk the assignment sublist
        for (var i = l-1; i >= 0; i--) {
        	
        	var resource = task.getSublistValue({
                sublistId: 'assignee',
                fieldId: 'resource',
                line: i
            });
        	
        	if (resource == n) {
            	var unitprice = task.getSublistValue({
                    sublistId: 'assignee',
                    fieldId: 'unitprice',
                    line: i
                });
        		
            	var unitcost = laborcost;  
            	log.debug(logTitle, 'new unitcost =' + unitcost)
            	
            	task.setSublistValue({
                    sublistId: 'assignee',
                    fieldId: 'unitcost',
                    line: i,
                    value: unitcost
            	})
            	break;
        	}
        	
        }

        task.save();
	}
	return {
		getInputData : getInputData,
		//map : map,  No need for illustration
		reduce : reduce,
		//summarize : No need for illustration 
	};

});

Work with Highly Competent NetSuite Professionals

My hope is that the above code snippet helps teach the basics for producing the Map/Reduce pattern.  The real value to our clients is the bridge between understanding accounting and business operations and possessing a strong understanding of the NetSuite platform.   The intersection of those two capacities enables us to produce recurrently valuable situations for our clients.

If you found this article meaningful, feel free to subscribe to learn as I post new articles.  If you believe that you want to work with strong professionals, feel free to contact us to discuss your situation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube