Get a SuiteScript to Automatically Set NetSuite’s External ID

This article is relevant if you need to automatically set the NetSuite’s External ID to a value dependent on another field.

Background

During a recent NetSuite implementation, my firm worked with the accounting department on a complex set of financial dimensions for which they would produce frequent CSV imports. Using Native CSV imports, we sometimes need to reference the parent name element in a hierarchy structure to identify our unique record in the tree.  Referencing the parent can make the data import more complex.

The client wanted to keep this simple and use a name (string) reference. If we could make the NetSuite external ID become the same as the simple name reference, we could use it with the native CSV import tools as the lookup key. The client did not want to think about maintaining the external ID in their day-to-day use. Instead, we would create a related field map for each dimension where the external ID would get its value to be set. For example, although it could be part of a tree (parent/child) in the GL Chart of Accounts definition, we could use the Account Name if it was uniquely named.

In my 2021 article, Guidelines For Working with NetSuite External IDs, I discuss and provide a NetSuite SuiteScript to set the External ID to null. This article also discusses some of the External ID nuances. External IDs need to be unique for the entire table structure; we worried that the client would create names that would produce collisions or essentially duplicates, negating the value of the lookup key. They assured us they would not have collisions — yet we better be cautious. If we tried to implement a special prefix or suffix to make the External ID unique, our client would have trouble in their CSV maps because they would have to manipulate values to add the prefix or suffix versus simply using the exact field value they expect. Thus, our solution needed to be robust enough to inform the user if it found a logical duplicate.

NetSuite SuiteScript 2.1 for Automatically Setting External IDs

The following SuiteScript was written by one of our newer technical associate analysts, Zachariah H. I refined the script below to remove client-identifiable elements. The nice part is that the script can be deployed to any record that needs this automatic naming behavior. The script deployment offers a parameter definition for indicating the field name that is deemed the value to set the External ID.

/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
* @Description A user event script to set the external ID on various record types.
* Reference: https://blog.prolecto.com/2021/08/14/guidelines-for-working-with-netsuite-external-ids/
*/
define(['N/record', 'N/log', 'N/runtime'], 
function(record, log, runtime) {
	function afterSubmit(context) {
		//get the field on the record that is holding the string value that should be unique as a map to set the external ID.
		let field = runtime.getCurrentScript().getParameter('custscript_externalid_field');
		let fVal = context.newRecord.getValue({fieldId: field});

		//check xedit as it will only bring the field that was updated;
		if (context.type === context.UserEventType.XEDIT){
			//if the field id was not changed, the external id does not need to be updated
			if (fVal === '' || fVal == null){
				return;
			}
		}
        
		if (context.newRecord.getValue({fieldId: 'externalid'}) == fVal) {
			return; there is no change;
		} else {
			//anticipate collisions.  Use NetSuite's built-in logic to find them.
			try{
				record.submitFields({type: context.newRecord.type ,id: context.newRecord.id, values: {'externalid': fVal}});
			} catch (e) {
				log.error('Error', e);
				throw 'An error occurred while attempting to set the external ID to the value ' + fVal + '.  This likely indicates a duplicate logical value and thus external IDs for this record are not reliable.  Please investigate.';
			}
		} 
    }

    return {
		afterSubmit: afterSubmit
    };
}); 
 

NetSuite Database Platform to Drive Business Operations

The above example is a good illustration of how my firm thinks about the NetSuite platform. The business users and concerns should be leading requirements. If the native tools and utilities are deemed too cumbersome to use, investing may make sense to improve the situation. Here, we created a new database key through a very simple afterSubmit UserEvent SuiteScript to help with day-to-day CSV data imports. Certainly, by championing business users in the same theme, as discussed in my 2020 article, Fully Automate Complex NetSuite Data Imports, we can tackle quite complex data imports without requiring users to manipulate their data feeds beforehand. Thus, we really are never stuck and always have options.

Feel free to subscribe if you want to be notified of new articles as I post them. If you have similar concerns about enhancing your NetSuite system to better support your business users and would like to work with a team of trustworthy professionals, let’s have a conversation.

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