Published on January 31, 2022.

One of the biggest frustrations that I hear from NetSuite developers regarding SuiteQL is that identifying the tables and fields that they need can be very difficult. The native Records Catalog function is helpful, but there are often tables that are missing from it. And while the Tables Reference that I've built into NetSuite provides what I think is a more streamlined and user-friendly interface to the schema, it also has what I think of as "gaps." By that, I mean that there are secondary tables that are difficult to locate - tables that are, for whatever reason, missing from the "top level" record types that the Records Catalog API provides. As a result, the only way to locate those tables is to drill down on a related table, in which case a "missing table" might appear in the list of joins.

I've been working on a project that I hope will resolve some of these issues. I'm calling it the SuiteQL Schema Tool, and it's designed to provide an alternative to both the native Records Catalog function and the SuiteQL Query Tool's Tables Reference.

Schema Files

The SuiteQL Schema Tool works by generating schema files. The files are stored in the File Cabinet, so that they can be reused. Users have the ability to refresh a schema file if they'd like. Schema files are JSON-encoded representations of the schema, and their contents are based on the user's current role. As a result, a user always sees a schema that includes the tables that they actually have access to.

Table and Field Modes

The tool provides access to the schema in two modes: Table Mode, and Field Mode.

Table Mode is very much like the SuiteQL Query Tool's Tables Reference. A list of tables is displayed in the left column, and details about a selected table are shown to the right. The table names and IDs can be sorted, searched on by keyword, and so on.

What makes the SuiteQL Schema Tool's Table Mode different from the SuiteQL Query Tool's Tables Reference is that its list of tables is much more comprehensive. It includes not only the base tables returned by the Records Catalog API, but also all of the sub-tables that they join to. So you're much more likely to find the table that you're looking for - assuming, of course, that the table is exposed via SuiteQL, and that the table is available to the role that you're using.

Field Mode exposes all of the schema's fields via a single, streamlined interface - so you don't have to select a specific table in order to see a list of fields.

For each field, the table name, table ID, field name, field ID, and data type are listed. While you can certainly browse through the list fields if you'd like, Field Mode's real power lies in its keyword search capability. For example, you can search across the entire schema for fields that match one or more keywords, or a specific phrase. Field Mode is very help in cases where you have an idea of the name or ID a field that you're looking for, but aren't sure what table it's in.

Here's an animation showing Field Mode.

Click the image to view a larger version.

Wrapping Up

I'm planning to release the SuiteQL Schema Tool as a stand-alone utility, meaning that it won't be distributed as a function of the SuiteQL Query Tool. Also, I'll likely remove the "Tables Reference" function from future versions of the SuiteQL Query Tool. And like the SuiteQL Query Tool, I'm planning to provide the Schema Tool free of charge under an MIT License.

A few of my colleagues and clients have been beta testing the SuiteQL Schema Tool for several weeks. The testing has been going well, and the feedback that I've received has been positive and helpful.

I still have a few details to work out, but if all goes as planned, I should be able to release the tool as early as next week.

If you're interested in the SuiteQL Schema Tool, please look for future announcements here on my blog, or follow me on LinkedIn.