N/query vs N/search

For NetSuite users, analytics are perhaps the most business-critical feature the NetSuite platform can provide and one of the most oft-requested customizations that a SuiteScript developer will be asked to make. From Saved Searches to Reports to Workbooks to completely custom UIs, we have so many options for creating visualizations of the metrics our clients and users need in order to monitor the health of their business.

NetSuite provides us with the N/query and N/search modules to interact with these analytics features from our SuiteScript. At first blush, these two modules would seem to serve similar functions, and as such, one of the most common questions I receive from developers in this area is:

How do I decide between N/query and N/search?

As with most of the questions my students and clients ask, the answer is complicated and depends on a great many factors, and we're here now to explore some of those factors.

Governance Usage

As a SuiteScript developer, the Governance system should be always at the forefront of your designs. While Governance usage won't determine which module you use, I thought a comparison useful here.

For single-page result sets (less than 4000 for N/search and less than 5000 for N/query), the Governance usage of the two APIs is identical (a meager 10 units).

For larger, multi-page result sets, N/query will use double that of N/search for the same amount of pages/results - 10 units per page for N/query as opposed to 5 for N/search.

However, this should not generally have much bearing on your decision either, as when you are processing large result sets, you should be using a bulk background process like a Map/Reduce, where Governance is effectively unlimited when used properly.

Baseline Similarities

On the surface, these two modules do absolutely serve a similar purpose. Both let you query data from just about any record type in the NetSuite database, and both allow you to locate and process large result sets within your SuiteScript. Initially, both modules also have a vaguely similar syntax. After that, however, the similarities start to disappear.

Besides, we're here to explore how they're different so that you can decide which to use between them.

N/search Features

The N/search module provides us with the ability to interact with NetSuite's Searching functionality (the Saved Search interface of the UI). As such, it provides all of the database querying features of that UI and more.

If you need to load an existing Saved Search and work with it in your Script, your choice is made for you. You cannot use N/query to load or run Saved Searches.

Below are some of the unique features of N/search which will help you decide when to use it. In all cases, N/query has no comparable feature. Although in many cases you could build Queries that accomplish the same goal, I can't find any advantages to doing so.

Body-level Lookups

One feature unique to the N/search is the lookupFields function. A very common operation in SuiteScript is to retrieve body-level data from a specific record. For instance, I may want the Email Address of the Sales Rep from a Sales Order. N/search.lookupFields() is hands-down the best function to accomplish this task. It's lightweight, fast, and only uses one single Governance unit to retrieve as many body fields as you want from said record.

If you need body-level data from a single specific record, N/search.lookupFields is your choice.

For more about lookupFields, check out my video on the topic.

Duplicate Detection Search

Duplicate records can be a major headache for a lot of clients, especially when it comes to Leads, Customers, and other Entity records. To alleviate this, NetSuite provides Duplicate Detection functionality to help companies identify and merge duplicate records. Administrators can configure which fields to consider when matching Duplicates as well as how to merge them.

The N/search module provides us with this same Duplicate Detection functionality via its duplicates() method. This method will run a Search with exactly the same criteria as configured in the Duplicate Detection settings for the account, helping us to identify duplicate records via SuiteScript following the company's settings. If those settings ever change, you have no need to update your code as it will continue to use the updated criteria in the search.

Global Search

NetSuite's Global Search provides users with a quick and easy way to access all kinds of data within the system with some very concise shortcut syntax for various operations. Conveniently, we can access this same functionality in SuiteScript via the N/search.global() method; the same syntax supported in the UI can be used in this SuiteScript search, allowing you to perform a Global Search in your script.

More Concise Syntax

In my SuiteScript Cookbooks covering these two modules, I go over several different syntax patterns that can be used when creating Filters, Conditions, and Columns. Having done so with both modules, the N/search module provides much more concise syntax to express similar concepts.

The N/search module allows us to express quite complex structures through its Filter Expression syntax and its intelligent interpretation of strings to create Columns. N/query requires a series of very verbose method calls to accomplish the same thing.

Certainly the N/search modules has its many limitations, but concise, powerful syntax is not one of them.

Faster Development with the Records Browser

The Records Browser is easily the SuiteScript developer's best friend, providing IDs and Types for all scriptable records, fields, joins, filters, and columns.

Unfortunately, those IDs do necessarily apply to the exact same fields when using the Query module. In order to find the correct ID of a field for a Query, the only reference I know of is to open the Analytics UI, create a Workbook of the same type, and check the field details there. While this is generally a good habit to be in when learning a feature set like this, once you've mastered the module, you should be able to focus on the code instead of flipping back and forth to the UI.

This is hands-down the largest drawback of the N/query module at this time. As it stands now, it is a slow and clunky process to create a Query due largely to the lack of a schema reference like the Records Browser for searches.

N/query Features

Like it's counterpart, N/query provides us with many unique features to leverage when building analytics for our clients and users.

Multi-level Joins

You probably knew this would be first on the list. Workbooks and N/query support multiple levels of joins across records, as well as specific directional joins. This gives you much more flexibility and control when joining related records together in your results.

Unless you want to engage in some crazy trickery, NetSuite's Searches are limited to a single-level join, which makes it devastatingly complicated to handle deep record relationships, which are all too common in practice.

If you need your results to contain data from several layers of related records, N/query is your only reasonable choice.

Easy Object Mapping

I hate working with the N/search.Result Objects. I think they're extremely clunky and verbose, and I think they should have been designed as plain JavaScript Objects instead of using the very Java-like getValue() and getText() methods. As such, one of the most common things I do with Search results is map over them and turn them into Objects with a much leaner format.

Luckily for old grumps like me, the N/query module has an amazing feature in its N/query.ResultSet.asMappedResults() method. asMappedResults will iterate over the Query's ResultSet, mapping each Result instance to a plain JavaScript Object. When you define a Column on your Query, you can optionally give it an alias, and that alias will be used as the key name in the resulting Object. If you do not provide an alias, asMappedResults() will just use the Column's fieldId. This makes for very concise, readable result processing.

This is by far my favorite feature of N/query and is perhaps one of the smartest quality-of-life improvements I've seen the SuiteScript team make after the transition to 2.0.

SuiteQL Support

With the release of the Analytics Workbook, NetSuite also released SuiteQL, a NetSuite Query Language. With this, you can write your queries directly as SQL statements instead of using all the dropdowns and drag-and-drop UI.

We can leverage this same query language in SuiteScript by using the N/query.runSuiteQL() method. Unfortunately, the method is not documented as of this writing.

A Note on Performance

To be succinct: I don't have a performance comparison for you.

First, it's a violation of NetSuite's Terms of Service to publish any sort of performance testing/benchmarking results. Check Section 2.3, paragraph A if you don't believe me.

Terms aside, I haven't done a performance benchmark anyway as I don't believe which executes faster will be the primary decision point between these modules. Functionality - not performance - will be the determining factor of which module to use.

Disclaimer: This is not to say you shouldn't care at all about the performance of your scripts; there are certainly patterns to follow and anti-patterns to avoid such that your scripts aren't bogging down the system, your users, and your co-tenants on the server. But that's a topic for another article.

For what it's worth, both modules do offer Promise versions of their APIs for asynchronous execution on the Client Side.

Conclusion

As it turns out, while the two modules both offer entry points into NetSuite's analytics features, they have very different features. As such, I don't see a lot of overlap or competition between the two. The decision of using N/query or N/search will come down to which of those features you need to accomplish your SuiteScript goal.

If you need to master either of SuiteScript's analytics modules, check out my series of SuiteScript Cookbooks.