NetSuite: External ID Search Utility

Published on August 22, 2022.

An update to the External ID Search Utility is now available. Learn more >

I was recently asked by a NetSuite client for help with a problem that involves external IDs. This client imports data from a number of external systems, and so they make extensive use of external IDs.

The challenge that they were having was locating records based on external IDs. That's something that you cannot do using NetSuite's Global Search tool.

Initially, the client asked for two SuiteQL queries: One that would let them search for transactions based on an external ID, and a second that they could use to search for customers and suppliers.

The request evolved from that set of simple queries into a much more useful solution. The goal was to provide a simple utility that would let a user enter an external ID (or a partial external ID) and then be presented with a list of transactions (sales orders, purchase orders, etc) and/or entities (customers, vendors, employees, etc) that reference that value.

In this post, I'll share the SuiteQL queries that I had originally created, and then share the Suitescript that they evolved into.

But first, here's an animation showing the application.

Click the image to view a larger version.

Search for Transactions Based on an External ID

I'll start by showing the query that I had developed that can be used to search for a transaction based on an external ID.

SELECT
	ExternalID,
	BUILTIN.DF( Type ) AS RecordType,
	ID AS InternalID,
	TranID AS RecordID,
	TranDisplayName AS Name,
	CreatedDate AS DateCreated
FROM
	Transaction
WHERE
	ExternalID = 'S615'

A limitation of this query is that the user needs to know the exact value of the external ID that they are looking for.

To allow for partial searches, you can simply adjust the WHERE clause so that it uses LIKE, and then specify the external ID value by appending and/or prepending a percent sign to it. For example:

SELECT
	ExternalID,
	BUILTIN.DF( Type ) AS RecordType,
	ID AS InternalID,
	TranID AS RecordID,
	TranDisplayName AS Name,
	CreatedDate AS DateCreated
FROM
	Transaction
WHERE
	ExternalID LIKE 'S61%'

That query will return transactions whose external IDs start with: S61

You could also search for transactions whose external IDs contain "61." For example:

SELECT
	ExternalID,
	BUILTIN.DF( Type ) AS RecordType,
	ID AS InternalID,
	TranID AS RecordID,
	TranDisplayName AS Name,
	CreatedDate AS DateCreated
FROM
	Transaction
WHERE
	ExternalID LIKE '%61%'

Search for Entities Based on an External ID

The query for searching for entities based on an external ID is very similar.

SELECT
	ExternalID,
	'Entity' AS RecordType,
	ID AS InternalID,
	EntityID AS RecordID,
	EntityTitle AS Name,
	DateCreated
FROM
	Entity
WHERE
	ExternalID LIKE 'J35%'

Combining The Two Queries

The query that I used in the app is a combination of the transaction and entity queries, and uses UNION ALL to combine the query results into a single recordset. Here's what that query looks like.

SELECT
	*
FROM
	(

		SELECT
			'<a href="/app/accounting/transactions/transaction.nl?id=' || ID || '" target="_transaction" || ID || ">View</a>' 
				|| ' | ' ||
				'<a href="/app/accounting/transactions/transaction.nl?id=' || ID || '&e=T" target="_transaction" || ID || ">Edit</a>' 
				AS Links,
			ExternalID,
			TranDisplayName AS Name,
			CreatedDate AS DateCreated,												
			BUILTIN.DF( Type ) AS RecordType,
			ID AS InternalID,
			TranID AS RecordID									
		FROM
			Transaction
		WHERE
			ExternalID LIKE ?

		UNION ALL

		SELECT
			'<a href="/app/common/entity/entity.nl?id=' || ID || '" target="_entity" || ID || ">View</a>' 
				|| ' | ' ||
				'<a href="/app/common/entity/entity.nl?id=' || ID || '&e=T" target="_entity" || ID || ">Edit</a>' 
				AS Links,
			ExternalID,
			EntityTitle AS Name,
			DateCreated,						
			'Entity' AS RecordType,
			ID AS InternalID,
			EntityID AS RecordID				
		FROM
			Entity
		WHERE
			ExternalID LIKE ?

	)
ORDER BY
	ExternalID,
	RecordType,
	RecordID

There are a few things worth noting about this query.

First, as I mentioned above, I'm using UNION ALL to combine the results of the two inner queries, which makes it possible to search for transactions and entities, and see the results of both, in a single query.

Next, I'm using query parameters. The two question marks in the query represent the parameters. As you'll see in the Suitescript below, I'm specifying the values of the query parameters based on the value that the user enters into the form.

And finally, I've added a "Link" column to the two queries. The column uses string concatenation to generate two URLs for each record - one that a user can click on to view a record in a new tab, and a second that can be used to open the record in edit mode. I wrote about this technique back in January in a blog post titled "NetSuite: Add Links to SuiteQL Query Results."

The SuiteScript

The solution that I ended up developing uses that final query, and an interface that is generated using the SuiteScript N/ui/serverWidget" module. The Suitelet is available at the end of this post, and can also be downloaded here.

Search Tips

Here are some tips on how to use the External ID Search Utility.

If you know the exact external ID that you are looking for, simply search on it.

If you'd like to locate transactions and/or entities that start with part of an external ID, add a "%" to the end of the partial value. For example, searching for "J10%" will return all records whose external IDs start with "J10."

If you want to find transactions and/or entities that end with part of an external ID, add a "%" to the beginning of the partial value. For example, searching for "%102" will return all records whose external IDs end with "102" (such as J102, P1102, Q0102, and so on).

Similarly, you can search for records whose external IDs contain a value by adding "%" to both the beginning and end of the partial value. For example, searching for "%10%" will return all records whose external IDs contain with "10."

And one more tip: If you want to see all records that contain an external ID, simply search for: % But be careful in doing so, as you might end up with a very number of results.

Wrapping Up

I hope you find the queries, and especially the SuiteScript, that I've shared in this post to be helpful.

About Me

Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on NetSuite, including mobile apps, Web portals, Web APIs, and more.

I'm the developer of several popular NetSuite open source solutions, including the SuiteQL Query Tool, SuiteAPI, and more.

I founded SuiteStep, a NetSuite development studio, to provide custom software and AI solutions - and continue pushing the boundaries of what's possible on the NetSuite platform.

The Suitelet

To download the script, click here.

/**
* @NApiVersion 2.1
* @NScriptType Suitelet
* @NModuleScope Public
*/

/* 

------------------------------------------------------------------------------------------
Script Information
------------------------------------------------------------------------------------------

Name:
External Search Utility

ID:
_external_id_search

Description
A utility for searching transactions and entities based on an external ID.


------------------------------------------------------------------------------------------
MIT License
------------------------------------------------------------------------------------------

Copyright (c) 2022 Timothy Dietrich.

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.


------------------------------------------------------------------------------------------
Developer(s)
------------------------------------------------------------------------------------------

Tim Dietrich
• timdietrich@me.com
• https://timdietrich.me


------------------------------------------------------------------------------------------
History
------------------------------------------------------------------------------------------

20220822 - Tim Dietrich
• Initial version.

*/


var 
	log,
	query,
	serverWidget;


define( [ 'N/log', 'N/query', 'N/ui/serverWidget' ], main );


function main( logModule, queryModule, serverWidgetModule ) {

	log = logModule;
	query= queryModule;
	serverWidget = serverWidgetModule;				
	
    return {
    
    	onRequest: function( context ) {
    	
			var form = serverWidget.createForm(
				{
					title: 'External ID Search Utility',
					hideNavBar: false
				}
			);		
			
			form.addSubmitButton( { label: 'Search' } );
			
			var idField = form.addField(
				{
					id: 'custpage_field_externalid',
					type: serverWidget.FieldType.TEXT,
					label: 'External ID'
				}								
			);				
			
			idField.isMandatory = true;	
											
			if ( context.request.method == 'POST' ) {	
			
				idField.defaultValue = context.request.parameters.custpage_field_externalid;

				formProcess( context, form );
			
			}
			
			context.response.writePage( form );	
			
        }
        
    }

}


function formProcess( context, form ) {	

	var
		externalID = context.request.parameters.custpage_field_externalid;

	var theQuery = `
			SELECT
				*
			FROM
				(
	
					SELECT
						'<a href="/app/accounting/transactions/transaction.nl?id=' || ID || '" target="_transaction" || ID || ">View</a>' 
							|| ' | ' ||
							'<a href="/app/accounting/transactions/transaction.nl?id=' || ID || '&e=T" target="_transaction" || ID || ">Edit</a>' 
							AS Links,
						ExternalID,
						TranDisplayName AS Name,
						CreatedDate AS DateCreated,												
						BUILTIN.DF( Type ) AS RecordType,
						ID AS InternalID,
						TranID AS RecordID									
					FROM
						Transaction
					WHERE
						ExternalID LIKE ?

					UNION ALL

					SELECT
						'<a href="/app/common/entity/entity.nl?id=' || ID || '" target="_entity" || ID || ">View</a>' 
							|| ' | ' ||
							'<a href="/app/common/entity/entity.nl?id=' || ID || '&e=T" target="_entity" || ID || ">Edit</a>' 
							AS Links,
						ExternalID,
						EntityTitle AS Name,
						DateCreated,						
						'Entity' AS RecordType,
						ID AS InternalID,
						EntityID AS RecordID				
					FROM
						Entity
					WHERE
						ExternalID LIKE ?

				)
			ORDER BY
				ExternalID,
				RecordType,
				RecordID
		`;
	
	try {

		var queryResults = query.runSuiteQL(
			{
				query: theQuery, 
				params: [ externalID, externalID ]
			}
		); 				
	
		var records = queryResults.asMappedResults();				
	
		if ( records.length > 0 ) {	

			var resultsSublist = form.addSublist(
				{ 
					id : 'results_sublist', 
					label : ` External ID References for `, 
					type : serverWidget.SublistType.LIST 
				}
			);

			var columnNames = Object.keys( records[0] );

			for ( i = 0; i < columnNames.length; i++ ) {

				resultsSublist.addField(
					{ 
						id: 'custpage_results_sublist_col_' + i,
						type: serverWidget.FieldType.TEXT,
						label: columnNames[i]
					}
				);

			}

			for ( r = 0; r < records.length; r++ ) {

				var record = records[r];

				for ( c = 0; c < columnNames.length; c++ ) {

					var column = columnNames[c];

					var value = record[column];
					
					if ( value != null ) {
					
						value = value.toString();
						
						if ( value.length > 300 ) {			
							value = value.substring( 0, 297 ) + '...';			
						}

						resultsSublist.setSublistValue(
							{
								id : 'custpage_results_sublist_col_' + c,
								line : r,
								value : value
							}
						);        

					}	
					
				}

			}			

		} else {
		
			var errorField = form.addField(
				{
					id: 'custpage_field_error',
					type: serverWidget.FieldType.TEXT,
					label: 'Error'
				}								
			);		
			
			errorField.defaultValue = 'No references were found for: ' + context.request.parameters.custpage_field_itemid;	
			
			// Add an inline HTML field so that JavaScript can be injected.
			var jsField = form.addField(
				{
					id: 'custpage_field_js',
					type: serverWidget.FieldType.INLINEHTML,
					label: 'Javascript'
				}								
			);		
			
			// Add Javascript to make the error field red.
			jsField.defaultValue = '<script>
';
			jsField.defaultValue += 'document.addEventListener(\'DOMContentLoaded\', function() {';
			jsField.defaultValue += 'document.getElementById("custpage_field_error").style.background="red";
';
			jsField.defaultValue += 'document.getElementById("custpage_field_error").style.color="white";
';
			jsField.defaultValue += '}, false);';
			jsField.defaultValue += '</script>';					
		
		}

	} catch( e ) {	
	
		var errorField = form.addField(
			{
				id: 'custpage_field_error',
				type: serverWidget.FieldType.LONGTEXT,
				label: 'Error'
			}								
		);		
	
		errorField.defaultValue = e.message;			
		
	}

}

Copyright © 2025 Tim Dietrich.