Effective SuiteScript

11 Search Columns in SuiteScript 2.0


Published by July 10, 2017 12:32 pm

Level: Beginner

Category: Searches

, ,

Prefer a video? Watch here (8 minutes)


In this edition, we explore working with Search Columns in SuiteScript 2.0.

To coincide with this series on Searching, I have also created a series of SuiteScript cookbooks focused on Searching. If you are tired of NetSuite’s unrealistic or broken examples, then these cookbooks are for you.

Creating Search Columns

We begin by looking at the APIs for creating and manipulating Search Columns. In order to specify Columns, we use the N/search module’s createColumn method. As you can see there are a lot of options when creating a Column; we will focus here on the most commonly used of those, namely the name, the summary, and the sort.

API Equivalencies for Creating Search Columns

As always, these APIs are best explored through an example. Let’s build a Search that finds all active Employees hired exactly one year ago today:

1.0 2.0

This search will return the First Name, Last Name, and Email Address of all the Employees we hired on this day one year ago that are still active.

We use the Search Result’s getValue method to retrieve the value for a particular Column from the Result.

Creating Columns by Name

Much like Search Filters have a more concise Filter Expression syntax, Search Columns also have a more concise syntax where we only need to specify the Column by its name:

Sorting Search Columns

We can of course sort the results of our Searches by various Columns.

In SuiteScript 1.0, we used the Column’s setSort method to define our sorting and passed it a Boolean value indicating the direction of the sort. In SuiteScript 2.0, we specify the sort direction as an option when creating the Column, and the direction is specified using the N/search module’s Sort enumeration.

APIs for defining sorting on Search Columns

Let’s sort the results in our previous example by the Employees’ Last Names:

We switch to the Object syntax for the lastname Column, just like we could with Filters, in order to specify its sort property.

Note that we can intermingle the notation we use to define our Columns based on the options we need to specify for each Column.

Summary Search Columns

We can also summarize our search results using Column functions like sum or count. In SuiteScript 1.0, we specified Summaries by their name as a String. In SuiteScript 2.0, Column Summary types are provided by the N/search module’s Summary enumeration:

APIs for defining Summary Search Columns

Let’s summarize our previous example by simply returning a count of how many employees were hired one year ago today. Typically we count results by a unique Column, like the internalid.:

We can summarize any Column(s) simply by specifying the summary property of the Column Object.

Note that when we want to retrieve the value of a summary Column, we need to specify the summary type in getValue as well.

Manipulating Search Columns

Lastly, we’ll look at modifying the Columns on an existing Search.

This will all look very similar to the Filters functionality. SuiteScript 1.0 provided many methods for retrieving and manipulating Search Columns. In 2.0, we get direct access to the Columns Array via our Search’s columns property, which makes modifying that Array much more concise and powerful.

APIs for manipulating Search Column Objects

This means we can use any standard JavaScript Array method to add or remove Columns from the Array.

Conclusion

At this point in our series we’ve got the essential pieces of Searching in SuiteScript 2.0. We can create and load Searches then add Filters and Columns to them; those are the basic foundational pieces of any Search.

There is still much, much more advanced work we can do with Searches; if you’re looking to accelerate your mastery of searching in SuiteScript with realistic, fully functioning examples, check out my Searching with SuiteScript cookbooks.


Join Effective SuiteScript for weekly content to help you learn NetSuite development