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.
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%'
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%'
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 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.
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.
I hope you find the queries, and especially the SuiteScript, that I've shared in this post to be helpful.
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.
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.