Overcome NetSuite Summary Search Fields to Store Value

This article is relevant if you are looking to store values into NetSuite custom fields to overcome the limitations of NetSuite Summary Search fields.

Background

In last week’s article, Extend NetSuite’s Additional Filters on Batch Record Generators, I offered a technique to enhance NetSuite’s built-in batch record generators by creating relevant data that NetSuite tools would be able to work with. As is often the case, we need some extra information that is not provided out-of-the-box by NetSuite. Many times, we see a pattern where we want to summarize information into a value related to a specific record. For example, we may want to understand a customer Days Sales Oustanding (DSO) as I discussed in this 2020 article.

However, NetSuite’s built-in Summary Search Result fields have important limitations that often must be overcome. See below how NetSuite’s Help offers limitations.

Current Limitations for Summary Search Custom Fields


Custom field values from summary search results are never stored. Field values are always calculated dynamically at runtime. A user may be able to edit values, depending on the display options set for the custom field, but the edited values are not stored. Also, because its values are not stored, the field is not available in search results, including lists based on saved searches.

Calculated values for summary search custom fields may be different for users with different permissions. Summary search results are rolled up for the records to which the current user has access. Because users with different permissions may have access to different sets of records, the calculated value of the field may vary per user.

Thus, the purpose of this article is to help you use a SuiteScript pattern to overcome these limitations by storing the data in the database and thus make it native.

Pattern to Overcome NetSuite Summary Search Custom Field Limitations

Working with a senior technical analyst on my firm’s team, Boban D., we developed an algorithm that we include in our Utilities library license-free to all of our clients. The simple pattern fires on the back of an AfterSubmit of a record where the script is deployed. In the script deployment, you specify one of two parameters:

1. Saved Search Definition: here, you create a saved search that will run after the respective record is saved in the AfterSubmit event. The saved search does NOT need to be a summary search. The point is that you want to lookup fields in relation to the respective record. Hence, at runtime, we dynamically pass in the filtering criteria of the internal ID of the current record that was just saved.
2. Saved Search List: What if you need to run more than one saved search to get the values you need? Instead of specifying the Saved Search definition in the previous pattern,  specify a list of saved searches. In one AfterSubmit event, each saved search will be run.

Ideally, each saved search will return a single result. This naturally depends on how you define the saved search.

Binding Saved Search Results to Fields in Respective Record

The next innovation is to bind the fields of the result set to the fields in the record that triggered the lookups. By using NetSuite’s capacity to specify a custom column name, we use that to target the field to update. For example, if you want to update the Memo on the customer record, you would indicate {comments} as the column name.

See the related image for how to specify the names on summary results. The script then looks for results and if the field name matches, it updates the record.

SuiteScript 2.0 Template to Dynamically Store Value in Respective Record

As discussed, this algorithm as well as many others are all available license-free to our clients. However, the code pattern below can help you develop your own.

define(['N/record', 'N/runtime', 'N/search'],
function(record, runtime, search) {
	function afterSubmit(context) {

    	var REC = context.newRecord; 
    	try {
			if (context.type == context.UserEventType.CREATE || context.type == context.UserEventType.EDIT || context.type == context.UserEventType.XEDIT) {
				
				//get the saved search via parameter (not showing code pattern to get more than one saved search)
				var searchId = runtime.getCurrentScript().getParameter('custscript_pri_util_field_search_update');
				
				if (!searchId) return;
				
				var ss = search.load({id: searchId});
	        	//add the record ID of the current record driving this search
	        	var newSearchFilter = []; 
	        	for (var sx in ss.filters) {
	        		var searchFilter = ss.filters[sx]; 
	        		if (searchFilter.name != "internalid") newSearchFilter.push(searchFilter);         		
	        	}
	        	newSearchFilter.push(search.createFilter({name: "internalid", operator: search.Operator.ANYOF, values: [REC.id]}));
	        	log.debug(funcName, "Search Filter: " + JSON.stringify(newSearchFilter)); 
	        	ss.filters = newSearchFilter; 
	        	
	        	// get the data
	        	ss = ss.run().getRange(0,1000); 
				if (ss.length == 0) return; 
				    	
				// use the first result even if more than one result returns
				var result = ss[0]; 
				log.debug(funcName, "Search Results: " + JSON.stringify(result)); 
				
				// find related fields and bind data to the record
				var fieldsToUpdate = {}; 
				var searchCols = result.columns;

		    	for (var i = 0; i < searchCols.length; i++) {
		    		var col = searchCols[i]; 
		    		if (col.label) {
		    			var fldLabel = col.label;
		    			if (fldLabel.indexOf(" ") < 0 && fldLabel.startsWith("{") && fldLabel.endsWith("}")) {
		    				fldLabel = fldLabel.substring(1,fldLabel.length-1); 
    		    			fieldsToUpdate[fldLabel] = result.getValue(col);
		    			}	    		    			
		    		}
		    	}			

		    	if (Object.keys(fieldsToUpdate).length == 0) return;
		    	log.audit(funcName, "Updating: " + JSON.stringify(fieldsToUpdate)); 
		    	record.submitFields({type: REC.type, id: REC.id, values: fieldsToUpdate}); 
			}		    		
    	} catch (e) {
    		log.error(funcName, e);
    	}
	} // afterSubmit
	return {
		afterSubmit: afterSubmit			
	}
});

Work with NetSuite Innovators

As a NetSuite Systems Integrator, we encounter so many different client situations. Many situations are common across all of our clients. With the power of the NetSuite Platform and a capacity for invention and technical skill, we can overcome perceived limitations. Since our business is to solve NetSuite related challenges, our client’s don’t want us to waste time doing work we have done before. As such, since 2008, we add value to our relationships by offering our algorithms license-free as accelerators to take care of client concerns.

Perhaps you want to work with professionals that are high caliber, hold high standards for care, and be recognized for your unique ability to look at challenges and invent logic to overcome obstacles?

If you found this article valuable, feel free to sign up to receive new articles as I post them. If you are considering a relationship with a high caliber NetSuite firm, let’s have a conversation.

Be Sociable, Share!

• 
• 

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