Published on October 27, 2021.

I'm excited to announce that version 2021.2 of the NetSuite SuiteQL Query Tool is now available. This version of the tool follows two beta versions that I've released over the past several months. The Beta 1 and Beta 2 versions were released in July, and while I haven't written much about the tool since then, I have been hard at work on it - tweaking the interface, resolving issues, and evaluating the feedback and suggestions that I've received from the community.

In this post, I'll discuss some of the features in SuiteQL Query Tool v2021.2. If you haven't had a chance to use the beta versions of the tool, but are somewhat familiar with earlier versions of it, then there are a number of big changes that you'll see in the 2021.2. And if you have been following along and using the betas, there are some changes that I've made since Beta 2 that I think you'll find to be helpful.

Improved User Interface

As I mentioned in my post about Beta 1, the most noticeable difference about the 2021.2 release is the new user interface. I developed the app using a new approach and framework, and in the process, decided to experiment with the interface a bit.

The app is now essentially a single-page application (SPA). As a result, you'll generally find that it is faster and easier to use, with little to no page refreshes required.

Here's a short animation that shows the new interface. You can click the image to see a larger version.

The new interface was built using Bootstrap. It also makes use of the popular DataTables jQuery Javascript library, which adds functionality to the tool's HTML tables. When viewing query results, you can choose to view the data with Datatables enabled, which makes it easy for you to page through the results, sort them, and do keyword searches against them - all without having to resubmit the query.

Pagination Options

Another key feature of the 2021.2 release are the various pagination options that it provides. SuiteQL queries that are run using the SuiteScript N/Query module are limited to returning 5,000 results at a time. The tool's pagination options make it possible to get beyond this limitation, and gives the user the option to specify a range of rows to be returned. Additionally, users can choose to have all results returned, and to have the tool return a count of the total number of rows that met any filter criteria that were specified in the SuiteQL query.

The pagination options are disabled by default. To enable them, you simply click the "Enable Pagination Options" checkbox.

There are times when running queries with the pagination options disabled can be advantageous. When the options are disabled, the tool essentially runs your "raw query" without manipulating it in any way. In some cases, this can result in much faster response times. It also helps developers create queries that are more like the queries that they'll embed in their scripts.

Result Formatting Options

By default, query results are displayed in a simple HTML table. As I mentioned above, you can also choose to enable the DataTables option, which makes it easier to page through the results, to run keyword searches against them, and to sort them - and most importantly, to do so without having to resubmit your query. The tool also provides options for viewing the results in a comma-separated (CSV) format, as well as in a JSON-encoded format (which can be very helpful to SuiteSuite developers). And again, toggling through those formatting options does not require that the query be resubmitted.

Two additional formatting options are available, allowing the user to generate PDF and HTML documents based on the merging of query results with advanced PDF/HTML templates. These formatting options are powered by the SuiteScript N/render module. The template can consist of BFO XML tags and FreeMarker code. If you're familiar with NetSuite's Advanced PDF templates, then I think you'll find these new formatting options to be easy to use. And if you're new to Advanced PDF templates, then this might be a good way for you to learn more about them, and explore them, in a convenient way. You might also find this post to be of interest, as it shows how you can use the SuiteQL Query Tool's HTML formatting option to generate dynamic maps based on SuiteQL query results.

As I wrote in my blog post about Beta 2, the technique and code that I've developed to add the PDF and HTML document formatting options to the SuiteQL Query Tool is also being used to develop another tool that I've been working on - a project that I've been calling the "Document Generator." That tool will allow NetSuite users to easily generate PDF and HTML documents based on combinations of SuiteQL queries, NetSuite native and custom records, saves searches, and even external data. To learn more, take a look at the post about Beta 2 from July 25th.

SuiteQL Tables Reference

Earlier version of the SuiteQL Query Tool made it easy to get information about the tables that are available to us via SuiteQL. When entering your query, you could type a table name, followed by two periods, and a popup window would appear, showing information about the table.

In the 2021.2 release, I've added support for a "SuiteQL Tables Reference." This mini-application opens in a new browser tab, and makes it easy for users to search for tables by keyword. It provides information about a selected table, including detailed information about the table's columns, the tables that it joins to, and even presents a simple SELECT query that can be copied and pasted into the query tool.

The UI for the SuiteQL Tables Reference makes use ot the DataTables jQuery Javascript library that I mentioned earlier in this post. This makes it very easy for users to search for tables, columns, and so on.

Here's a screenshot of the SuiteQL Tables Reference.

Remote Query Library

Also included in this version of the SuiteQL Query Tool is support for a shared, hosted library of helpful SuiteQL queries - a function that I'm calling the "Remote Query Library." With the click of a button, users have easy access to a wide range of queries that can be used to get data based on everything from Accounting Periods to Customer Invoices to Transactions, and everything in between. As of this post, there are 43 queries available in the library.

Users can search for queries by keyword, and then load a selected query directly into the query tool. From there, they can run the query, or modify it as needed.

Here's a screenshot of the Remote Query Library.

I've recently added several queries to the Remote Query Library, including:
• Customer Invoices - By Date Range: Returns all customer (accounts receivable) invoices for a given date range, with totals and statuses.
• Employees - Last Logins: Lists all employees that have NetSuite access, and the date/time that they last logged in.
• Items - Without Preferred Bins: Lists items that do not have a preferred bin.
• Transaction Counts - By Type and Status: Returns the total number of transactions in each type / status combination.

Local Query Library

The SuiteQL Query Tool also supports storing SuiteQL queries locally, in the NetSuite File Cabinet. (To enable this feature, simply create a folder in the File Cabinet, and grab its folder ID. Then update the "queryFolderID" variable in the SuiteQL Query Tool script.)

Support for Virtual Views

The latest version of the SuiteQL Query Tool includes support for what I've been referring to as "virtual views." If you're a database developer, then you probably are familiar with SQL views, which are essentially virtual tables that are based on SELECT statements. Views can be very convenient at times. However, NetSuite doesn't provide us with a way to create them - which is why I added support for "virtual views."

To create a virtual view, you simply create a query and save it in your Local Query Library. For example, you might create a query based on employees, and save it with a file name of "MyEmployees.sql." You can then use that query as if it were a view, and simply refer to it with a table name of "#MyEmployees."

I'm hoping that NetSuite does provide us with a way to create our own "real" SQL views at some point. In the meantime, I hope you find the tool's support for virtual views to be helpful. And I'm planning to enhance this functionality in a future release of the tool.

Run Queries Based On Selected Text

Another late arrival to the tool is the ability to run a query based on the selected text in the query field. You simply highlight the portion of the query that you want to run, and then run the query. Only the selected portion of the query will be processed.

I want to thank Paul Reed of acceler8 Inc for suggesting this feature, and for pointing me in the right direction with regards to implementing it. Thanks Paul!

Downloading the SuiteQL Query Tool

The SuiteQL Query Tool is available for immediate download, and you can find it here.

The tool is being offered free of charge under an MIT License, which can be found in the header comments of the script itself.

What's Next?

I've already started working on the next version of the SuiteQL Query Tool. Some of the features that I am adding include support for plugins (which will make it easy for users to add code that enhances the tool's functionality and interface), improved / enhanced support for virtual views, improved support for the PDF / HTML document formatting options, and more.

I'm also working on other NetSuite-related open source projects. As I mentioned above, I'm working on the Document Generator project. I've also got several other tools in the works that I think developers will find to be helpful, including a SuiteScript Sync Utility - a native desktop app that allows developers to work on SuiteScript queries in their preferred development environment while making it easy to upload their scripts to NetSuite. (For details, see this post.) And there's more on the way as well.

To keep up with what I'm working on, please revisit my blog, or follow me on LinkedIn.

Wrapping Up

There are many more features in the SuiteQL Query Tool that I haven't covered in this post, and I'm planning to discuss them in future posts. In the meantime, please let me know if you have any questions or comments about the tool.

And finally, I want to thank everyone in the NetSuite community - including my friends at Oracle NetSuite - who have offered their feedback, comments, and suggestions over the past several months. The tool is a result of that great feedback, and I really appreciate it.