NetSuite SuiteScript: Top Level Customer Lookups on Entity Records

This article is relevant if you are programming in SuiteScript and need to obtain parent and top-level parent information from projects and customer records.

Background

NetSuite offers a flexible entity structure for managing customers and projects, supporting multi-level hierarchies for customers. In the underlying system, projects are referred to as “jobs.”

NetSuite supports sub-customers and sub-projects, depending on whether the Advanced Projects (SRP) feature is activated. Without SRP, projects are treated as customers with a “project (job)” type, allowing for the creation of sub-projects beneath them. However, when SRP is enabled, sub-projects are not supported. In this case, customers are reliably the parent entities for Advanced Projects, and projects cannot have sub-projects.

Chidi Okwudire, Prolecto Technology Manager, wrote an article in 2022 that helps us with SuiteScript entity lookups.  In many cases, we need to know the root customer of the tree for our programming model. This article goes further showing you the how the model can be simplified with SuiteQL.

NetSuite SuiteScript Examples for Retrieving Customer/Project Parent and Top-Level Parent

When working with SuiteScript, you may encounter scenarios where you need to identify the top-level root customer in a hierarchy. This can sometimes be tricky to determine.  Fortunately, SuiteQL simplifies this process.

The following examples demonstrate how to implement SuiteScript to retrieve this information. All three examples are designed for AfterSubmit User Events, but the same code can also be used for BeforeSubmit events.  Notice elegance of the SuiteQL model.

SuiteScript 1.0 Top Level Entity Lookup Snippet

/**
 * User Event Script 1.0
 * @param {String} type Operation types: create, edit, delete, etc.
 */

function afterSubmit(type) {
	var entity = nlapiGetNewRecord();
	var entitytype = entity.getRecordType();
	var id = entity.getId();
	
	// if we are at top, reference ourself
	var parent = (entity.getFieldValue('parent')) ? entity.getFieldValue('parent') : id;
	var toplevelparent = 'unknown';
	
	//search the customer record structure to get top level parent
	var filters = [new nlobjSearchFilter('internalid', null, 'anyof', parent)];
	var columns = [new nlobjSearchColumn('internalid', 'toplevelparent')];

	var s = nlapiSearchRecord('customer', null, filters, columns);
	if (s) {
		toplevelparent = s[0].getValue('internalid','toplevelparent');
	}
    nlapiLogExecution('DEBUG', 'Entity', 'Type: ' + entitytype);
    nlapiLogExecution('DEBUG', 'Entity', 'ID: ' + id);
    nlapiLogExecution('DEBUG', 'Parent', 'ID: ' + parent);
    nlapiLogExecution('DEBUG', 'Top Parent', 'ID: ' + toplevelparent);
}

SuiteScript 2.1 afterSubmit Top Level Entity Lookup

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 */
define(['N/search', 'N/log'], function (search, log) {

    function afterSubmit(context) {
        const entity = context.newRecord;
        const entityType = entity.type;
        const id = entity.id;
        // handle that we may be at the root; reference self
		const parent = (entity.getValue({ fieldId: 'parent' })) ? entity.getValue({ fieldId: 'parent' }) : id ;

        // Search the customer record structure to get toplevel parent
        const searchResult = search.create({
            type: 'customer',
            filters: [
                ['internalid', 'anyof', parent]
            ],
            columns: [
                search.createColumn({
                    name: 'internalid',
                    join: 'toplevelparent'
                })
            ]
        }).run().getRange({ start: 0, end: 1 });

        let topLevelParent = 'unknown';

        if (searchResult && searchResult.length > 0) {
            topLevelParent = searchResult[0].getValue({
                name: 'internalid',
                join: 'toplevelparent'
            });
        }

        log.debug('After Submit', `Type: ${entityType}, ID: ${id}, Parent ID: ${parent}, Top Parent ID: ${topLevelParent}`);
    }

    return {
        afterSubmit: afterSubmit
    };
});

SuiteQL based SuiteScript 2.1 Top Level Entity Lookup

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 */

var SQL = `SELECT type, id, parent, toplevelparent 
  FROM entity 
  WHERE id = ?`;

define(['N/query', 'N/log'], function (query, log) {

	function afterSubmit(context) {
		SQL = SQL.replace('?', context.newRecord.id);
		log.debug('SQL Results', executeSuiteQL(SQL));
	};

	function executeSuiteQL(q) {
		var sqlQuery = q;
		var results = query.runSuiteQL({
		    query: sqlQuery
		});
		return results.asMappedResults();
	}

    return {
        afterSubmit: afterSubmit
    };

});

Click on the screenshots to see the debug output.

Join a Highly Regarded NetSuite Leadership Team

Our firm is respected as a NetSuite innovation leader. We capitalize on the platform’s robust capabilities, combined with the ingenuity of our team, to deliver exceptional results.  Since 2008, we built our reputation on a deep understanding of NetSuite’s technical architecture and a commitment to maximizing its native functionality to meet client needs. When requirements extend beyond the platform’s out-of-the-box capabilities, we carefully craft extensions and applications to drive meaningful user satisfaction.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them.   If you thrive on exploring the intricacies of the NetSuite platform and value working alongside a team that challenges your thinking while respecting your insights, 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