One of the challenges of working in NetSuite accounts where multiple developers are involved is that the developers are often unaware of all of the changes that are being made. I've run into this several times. For example, I might be working on a Suitelet, while another developer is making changes to a User Event Script, and without realizing it, one script is having an adverse effect on the other.

Recently, I ran into this situation, and used SuiteQL to help determine what scripts had been changed. In this post, I'll share the queries that I wrote.

The File Table

My first step was to query the File table. I wanted a list of all files that had been updated in the last week, and specifically Javascript files.

Here's the query.

SELECT
	File.LastModifiedDate,	
	File.Name
FROM 
	File 
WHERE 
	( File.LastModifiedDate >= BUILTIN.RELATIVE_RANGES( 'DAGO7', 'START' ) )
	AND ( File.FileType = 'JAVASCRIPT' )
ORDER BY 
	File.LastModifiedDate DESC

Notice that for the filter on the LastModifiedDate column I'm using a "Dynamic Calendar Date Range." I've written about those a few times in the past, and if you'd like to learn more about them, check out this post.

That query is a good start, but it doesn't provide information about the scripts that the files are attached to.

The Script Table

So my next step was to join the File table to the Script table. It provides information about all of the Suitescripts that are setup in the account, and includes a column that indicates the ID of the file that the script is based on.

Here's an updated query with the join from the File table to the Script table.

SELECT
	Script.Name AS ScriptName,
	Script.ScriptType,
	Script.ScriptID,
	Script.Description,
	File.LastModifiedDate,	
	File.Name AS FileName,
	BUILTIN.DF( Script.Owner ) AS ScriptOwner
FROM 
	File
	INNER JOIN Script ON
		( Script.ScriptFile = File.ID )
WHERE 
	( File.LastModifiedDate >= BUILTIN.RELATIVE_RANGES( 'DAGO7', 'START' ) )
	AND ( File.FileType = 'JAVASCRIPT' )
	AND ( Script.IsInactive = 'F' )
ORDER BY 
	File.LastModifiedDate DESC

A few quick notes about the changes:
• I'm using the BUILTIN.DF function to get the owner of the script.
• I've updated the WHERE clause to filter out inactive scripts.

That script is much better, in that it provides helpful information about the scripts. But I then realized that it would also be helpful to see information about how the scripts have been deployed.

The ScriptDeployment Table

Thankfully, NetSuite also provides us with a ScriptDeployment table, which can be easily joined to from the Script table. Here's a query showing how that join works.

SELECT
	Script.Name AS ScriptName,
	Script.ScriptType,
	Script.ScriptID,
	Script.Description,
	File.LastModifiedDate,	
	File.Name AS FileName,
	BUILTIN.DF( Script.Owner ) AS ScriptOwner,
	ScriptDeployment.DeploymentID,
	ScriptDeployment.Status AS DeploymentStatus,
	ScriptDeployment.IsDeployed,
	ScriptDeployment.ExecutionContext,
	ScriptDeployment.RecordType
FROM 
	File
	INNER JOIN Script ON
		( Script.ScriptFile = File.ID )
	INNER JOIN ScriptDeployment ON
		( ScriptDeployment.Script = Script.ID )
WHERE 
	( File.LastModifiedDate >= BUILTIN.RELATIVE_RANGES( 'DAGO7', 'START' ) )
	AND ( File.FileType = 'JAVASCRIPT' )
	AND ( Script.IsInactive = 'F' )
ORDER BY 
	File.LastModifiedDate DESC

By joining to the ScriptDeployment table, you now have information about the type of record that a script is deployed against, and its execution contexts.

Adding A Link to the File

The last change that I made to the script was to turn the file name into a clickable link, so that it reviewing the script would be easy to do. This is similar to the technique that I shared in this post, but with a few minor changes that are necessary so that the links work with files.

SELECT
	Script.Name AS ScriptName,
	Script.ScriptType,
	Script.ScriptID,
	Script.Description,
	File.LastModifiedDate,	
	'<a href="' || File.ID || '?folder=' || File.ID || '" target="_self" onclick="previewMedia(''' || File.ID || ''', true, true); return false;">' || File.Name || '</a>' As FileName,
	BUILTIN.DF( Script.Owner ) AS ScriptOwner,
	ScriptDeployment.DeploymentID,
	ScriptDeployment.Status AS DeploymentStatus,
	ScriptDeployment.IsDeployed,
	ScriptDeployment.ExecutionContext,
	ScriptDeployment.RecordType
FROM 
	File
	INNER JOIN Script ON
		( Script.ScriptFile = File.ID )
	INNER JOIN ScriptDeployment ON
		( ScriptDeployment.Script = Script.ID )
WHERE 
	( File.LastModifiedDate >= BUILTIN.RELATIVE_RANGES( 'DAGO7', 'START' ) )
	AND ( File.FileType = 'JAVASCRIPT' )
	AND ( Script.IsInactive = 'F' )
ORDER BY 
	File.LastModifiedDate DESC

Wrapping Up

One of the nice things about this query is that you can modify it to narrow down the scripts that you're interested in. For example, you could filter on the ScriptDeployment.RecordType to limit the results to script deployments that apply to a certain type of record.

As always, I hope you've found this post to be helpful.