Published on July 25, 2021.

This afternoon I released the second public beta of the NetSuite SuiteQL Query Tool v2021.2. For information about the first beta, please click here.

With this release, I've added a couple of new features, as well as a few interface enhancements.

SuiteAnalytics Workbook Support

The tool now supports importing the SuiteQL queries that power SuiteAnalytics Workbooks. To load a workbook's query, you simply click the new Workbooks button, select a workbook from the list, and click the Load button. The tool will then load the workbook, extract its query, and load the query into the query editor.

This is an experimental feature, and will evolve in future releases. (To be more specific, I'm waiting for the N/dataset SuiteScript module to reach General Availability status, which is expected to be in late September.)

That being said, in many cases the query that is loaded from a workbook will not run properly. I believe this has to do with the use of functions that SuiteAnalytics uses, and that we don't yet have access to (yet). Regardless, it is interesting to see the underlying queries of some of the workbooks. They can provide clues with regards to the tables and columns that you're looking for, as well as how various tables are joined.

PDF and HTML Document Generation Options

This version of the SuiteQL Query Tool supports two new results formats: PDF and HTML. These options give you the ability to generate PDF and HTML documents based on the results of a SuiteQL query and an advanced PDF/HTML template. If you're familiar with Advanced PDF templates, then this new function will likely be easy for you to put to use. If not, I'm planning to provide examples in the coming weeks, which should help bring you up to speed. There are also buttons available so that you can easily view BFO and FreeMarker resources. (BFO and FreeMarker are two of the technologies that the Advanced PDF functionality is built on.)

To use the feature, enter a query as you normally would, and then select either "PDF" or "HTML" from the "Format Results As" field. A new "Template Editor" area will appear, in where you can enter your PDF or HTML template. Then click the "Generate Document" button. If all goes well, a new browser tab (or window) will open, and the generated document will appear.

Here's an example, where I'm merging the results of a query against the Employee table with a simple PDF template. The query and template that I used in the example are included at the end of this post.

The new PDF and HTML Document Generation function is based on another solution that I have been working on, a project that I've been calling the "Document Generator." (Yeah, I know. Not a very creative name, right?)

The application can be used to dynamically generate PDF and HTML documents based on data from a variety of sources, including SuiteQL queries, saved searches, and records. You can also include data from external systems by setting up RESTlets to pull the data.

I'm hoping to release the Document Generator sometime this Fall. In the meantime, here's a screen shot showing the interface in its current form. This will very likely change as I continue working on the project.

User Interface Improvements

As I mentioned earlier, this release also includes a few improvements to the UI. There's a new button that you can use to hide or show the Query Editor area of the app. Hiding the Query Editor frees up space to display additional query results. It also helps when you're working on a PDF / HTML document.

You'll also find that I've added Datatables support to the Columns and Joins tables in the Tables Reference. That means you can now search against those tables, and more easily find what you're looking for - especially in tables such as Entity, Employee, and Transaction, which include a lot of columns, and join to a lot of other tables.

And one additional UI improvement worth mentioning has to do with the way that the query results table is displayed. When the table is wide (meaning, it consists of a large number of columns), the table is horizontally scrollable.

Next Steps

SuiteQL Query Tool Version 2021.2 Beta 2 is available for download by clicking here. However, if you're already running the first beta, and have "tool upgrades" enabled, you can use the tool's built-in upgrade function. (The tool upgrades function is enabled by default. To set it, look at the "toolUpgradesEnabled" value in the script.)

This will most likely be the final beta. If testing goes well, and the feedback is positive, then I'll officially release the tool in a week or two.

If you have any comments, questions, or suggestions about the tool, please post them to the SuiteQL channel over on the NetSuite Professionals Slack Community. If you'd like to contact me directly, click here.

PDF and HTML Document Generation Example - Query / Template

SELECT
	ID,
	LastName,
	FirstName,
	Phone,
	Email
FROM
	Employee
WHERE
	Email LIKE '%@test.com'
ORDER BY
	LastName,
	FirstName

<?xml version="1.0"?> 
<!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd"> 
<pdf>
	<body>
		<h2 style="margin-bottom: 6px;">Employees</h2>
		<table width="100%">
			<tr>
				<th width="10%">ID</th>
				<th width="30%">Name</th>
				<th width="20%">Phone</th>
				<th width="40%">Email</th>
			</tr>
			<#list results.records as employee>
				<tr>
					<td>${employee.id}</td>
					<td>${employee.lastname}, ${employee.firstname}</td>
					<td>${employee.phone}</td>
					<td><a href="mailto:${employee.email}">${employee.email}</a></td>
				</tr>
			</#list>
		</table>														
	</body>
</pdf>