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.