What is SuiteQL: a Comprehensive Guide -UnlockCommerce

What is SuiteQL: A Comprehensive Guide

If you’re working with NetSuite, you might have come across SuiteQL. SuiteQL is a query language that allows users to access information about the records saved in NetSuite, based on version 92 of SQL. SuiteQL has many features that help users query data, and in this article, we’ll explore some of its features.

Table of Contents

What is SuiteQL?

As mentioned earlier, SuiteQL is a query language that allows users to access information about the records saved in NetSuite. It’s based on version 92 of SQL, and while revisions are made every few years, version 92 has the most features used nowadays. SuiteQL queries refer to records or tables, and a record in NetSuite corresponds to a table in SQL. Every record in NetSuite has fields, and each instance of a record is a row. If you’ve worked with SQL server or MySQL databases before, you’ll find the concept of records, fields, and instances similar.

NetSuite

SQL Database

Record Types

Tables

Fields

Columns

Record Instances

Rows

Understanding SuiteQL Abilities

The capabilities of SuiteQL lie in its interaction with NetSuite records and data, offering an efficient method for managing complex data structures. This robust query language streamlines data extraction, manipulation, and analysis, enabling businesses to make well-informed decisions based on precise insights.

SuiteQL in Conjunction with SuiteAnalytics Connect

SuiteAnalytics Connect, a formidable NetSuite tool, grants users access to SuiteQL for analytics purposes. By employing SuiteQL, businesses can craft custom queries, extract data, and generate intricate reports, ultimately optimizing their analytics workflows and enhancing their data-driven decision-making capacity.

The N/query Module within SuiteScript

SuiteScript, NetSuite’s scripting language, incorporates the N/query module, which eases the use of SuiteQL in script development. This integration allows developers to build complex data-driven applications while retaining the advantages of the NetSuite platform. Moreover, the N/query module facilitates the creation of highly tailored scripts that meet specific business needs.

SuiteTalk REST Web Services Coupled with SuiteQL

The combination of SuiteQL and SuiteTalk REST web services allows users to engage with their NetSuite data through a RESTful API. This feature empowers developers to design robust, secure, and scalable applications that interact with NetSuite data from external systems, granting businesses the flexibility to broaden their digital presence and automate processes.

Using the Records Catalog

To build a query using SuiteQL, we need to know the names of the records and fields in NetSuite. The Records Catalog is a tool in the NetSuite’s backend that lets users look up the names of records and fields. You can find the Records Catalog in Setup, and it’s the last item on the menu. To use it, search for the record you’re interested in, and click on the Suitescript element. In the center of the screen, you’ll find all the fields and their types contained in that record. You can search between the fields or columns, and where it’s indicated, you’ll see the fields’ ID. This information is useful for building queries. For example, you could use “select” altname or currency “from customer” to retrieve fields from the customer record. Joins are also available in the Records Catalog, and you can use them to join tables together to obtain more information.

Records Catalog - UnlockCommerce

Built-in Functions

SuiteQL also has built-in functions that help users convert data or bring additional information on data in the query. One of the functions is Consolidate, which groups data. Another function is DF, which converts IDs to more meaningful values. For example, if you want to display an employee’s name instead of their ID, you can use the DF function.

Built-in Functions - DF - UnlockCommerce

Using SuiteQL in SuiteScript

If you want to use SuiteQL in SuiteScript, you need to import the query module first. The query module is available in SuiteScript 2, from version 2020 of SuiteCommerce. Once you’ve imported the query module, you can use the runSuiteQL function to execute queries.

N/query vs N/search

At the baseline, N/query and N/search serve a similar purpose, allowing developers to query data from just about any record type in the NetSuite database and process large result sets within their SuiteScript.

Governance

One of the most important considerations when choosing between these two modules is governance usage. For single-page result sets, both modules use only 10 units. However, for larger, multi-page result sets, N/query uses 10 units over N/search that uses 5 units, making N/query the better choice for large result sets. However, when processing large result sets, it is recommended to use a bulk background process like Map/Reduce, where governance is effectively unlimited when used properly.

More information here.

Lookup Fields

The lookupFields function is an efficient way to retrieve body-level data from a specific record, such as the email address of a sales representative from a sales order. For more complex queries, N/search provides more concise syntax to express similar concepts, while N/query requires a series of verbose method calls to accomplish the same thing.

Multi Level Joins

Multi-level joins are the first item on the list of N/query’s advantages. Unlike NetSuite’s Searches, N/query supports multiple levels of joins across records, as well as specific directional joins. This enables users to have more control and flexibility when merging related records in their results. When it comes to deep record relationships, N/query is the more viable option.

Simplified results handling

N/query’s ResultSet.asMappedResults() method is a helpful feature that can easily map N/search.Result Objects to plain JavaScript Objects. The column in the Query can be given an alias, which will be utilized as the key name in the output Object. In case an alias is not provided, the fieldId of the column will be used. This method is more concise and easy to read compared to using getValue() and getText() methods in N/search.Result Objects. It is, by far, one of the best features of N/query, and it’s a notable improvement made by the SuiteScript team after transitioning to version 2.0.

Conclusion:

SuiteQL is a valuable tool for NetSuite users looking to access data in their records. By leveraging its powerful features, including the Records Catalog, built-in functions, and different types of joins, users can obtain valuable insights that can inform better decision-making. However, if you’re feeling overwhelmed or unsure about how to use SuiteQL effectively, consider buying consulting services from UnlockCommerce. Our team of experts can guide you through the process of building effective queries and maximizing the benefits of SuiteQL for your business. So don’t hesitate to reach out to UnlockCommerce for assistance with your SuiteQL needs.

Picture of Fabian Rodriguez

Fabian Rodriguez

Senior Full Stack Developer in NetSuite, with a strong background in software engineering and extensive experience in developing customized NetSuite solutions. He brings a wealth of technical expertise to the team and is dedicated to delivering high-quality, efficient, and scalable solutions for our client's ecommerce needs.

Share this post

You may also like

SKU CSV Import into Cart for SuiteCommerce

We are thrilled to announce the launch of our latest SuiteCommerce extension: the SKU CSV Import into Cart. This powerful feature enhances the SuiteCommerce shopping experience by enabling users to upload items directly to their