11 Search Columns in SuiteScript 2.0

Created: July 10, 2017

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.

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
nlapiCreateSearch("employee", null,
    [
        new nlobjSearchFilter("hiredate", "on", "sameDayLastFiscalYear"),
        new nlobjSearchFilter("isinactive", "is", "F")
    ],
    [
        new nlobjSearchColumn("firstname"),
        new nlobjSearchColumn("lastname"),
        new nlobjSearchColumn("email")
    ]
).runSearch().forEachResult(processResult);

function processResult(result) {
    // ...
    return true;
}

// 2.0 - Console
require(["N/search"], function (s) {
    s.create({
        type: s.Type.EMPLOYEE,
        filters: [
            ["hiredate", s.Operator.ON, "sameDayLastFiscalYear"], "and",
            ["isinactive", s.Operator.IS, "F"]
        ],
        columns: [
            s.createColumn({name: "firstname"}),
            s.createColumn({name: "lastname"}),
            s.createColumn({name: "email"})
        ]
    }).run().each(processResult);

    function processResult(result) {
        var firstName = result.getValue({name: "firstname"});
        var lastName = result.getValue({name: "lastname"});
        var email = result.getValue({name: "email"});
        var message = [lastName, firstName, email].join(", ");
        log.debug({title: "Happy 1st Anniversary!", message: message});
        return true;
    }
});

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:

require(["N/search"], function (s) {
    s.create({
        type: s.Type.EMPLOYEE,
        filters: [
            ["hiredate", s.Operator.ON, "sameDayLastFiscalYear"],
            "and",
            ["isinactive", s.Operator.IS, "F"]
        ], 
        columns: [
            "firstname",
            "lastname",
            "email"
        ]
    }).run().each(processResult);

    function processResult(result) {
        // ...
        return true;
    }
});

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.

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

require(["N/search"], function (s) {
    s.create({
        type: s.Type.EMPLOYEE,
        filters: [
            ["hiredate", s.Operator.ON, "sameDayLastFiscalYear"],
            "and",
            ["isinactive", s.Operator.IS, "F"]
        ], 
        columns: [
            "firstname",
            {
                name: "lastname",
                sort: s.Sort.ASC
            },
            "email"
        ]
    }).run().each(processResult);

    function processResult(result) {
        // ...
        return true;
    }
});

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:

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:

require(["N/search"], function (s) {
    s.create({
        type: s.Type.EMPLOYEE,
        filters: [
            ["hiredate", s.Operator.ON, "sameDayLastFiscalYear"],
            "and",
            ["isinactive", s.Operator.IS, "F"]
        ], 
        columns: [{
            name: "internalid",
            summary: s.Summary.COUNT
        }]
    }).run().each(processResult);

    function processResult(result) {
        log.debug({
            title: "Employees Hired This Day Last Year:",
            message: result.getValue({
                name: "internalid",
                summary: s.Summary.COUNT
            })
        });
        return true;
    }
});

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.

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.