Home SuiteScript API: N/search
Post
Cancel

SuiteScript API: N/search

Posted Apr 25, 2023
By Eli Berenbaum
11 min read

Introduction

This post will discuss how to use the N/search. The search module is used to work with saved searches in NetSuite. You can use it to create, load, run, and save saved searches.

Before diving in, I want to promote the use of the extension Netsuite: Search Export for Chrome. This extension greatly simplifies the process of creating saved searches in SuiteScript.

While you are able to create a search in the user interface and load it in SuiteScript, sometimes it makes more sense to create the search in SuiteScript.

We use the search.create() method from the N/search module to create a saved search.

The search.create() method takes a single parameter, which is an object with a few main properties:

  • type - the type of search to create
  • filters - an array of filters to apply to the search
  • columns - an array of columns to return from the search

Let’s look at each of these parameters in more detail.

Search Type

This should be a string that specifies the type of search to create. These are stored in the search.Type enum. Some examples are:

  • search.Type.ITEM - Item Search
  • search.Type.TRANSACTION - Transaction Search
  • search.Type.CUSTOMER - Customer Search

For custom record types, you would use the internal ID of the custom record type.

Filters

There are two ways to create a filter object to add to the filters array. The first is to use the search.createFilter() method.

The search.createFilter() method takes a few parameters:

  • name - the name of the field to filter on
  • operator - the operator to use for the filter (‘is’, ‘anyof’). You can use the search.Operator enum to specify the operator.
  • values - an array of values to filter on or a single value
  • join (optional) - the join to use for the filter
  • summary (optional) - the summary type to use for the filter

Example of creating filters for an Inventory Balance search :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

const locationfilter = search.createFilter({
    name: 'location',
    operator: search.Operator.ANY_OF,
    values: [1, 2, 3]
});

const itemisassemblyfilter = search.createFilter({
    name: 'type',
    operator: search.Operator.IS,
    values: 'Assembly'
    join: 'item'
});

const hasmorethan400filter = search.createFilter({
    name: 'quantityavailable',
    operator: search.Operator.GREATERTHAN,
    values: 400,
    summary: search.Summary.SUM
});

We can also specify the filters as a simple array. This array has only three elements [name, operator, values]

If I have a join or a summary I will specify it in the filter name.

[summary(join.name), , join, values]

Here is an example of the same filters as above but using the array method:

1
2
3

const locationfilter = ['location', search.Operator.ANY_OF, [1, 2, 3]];
const itemisassemblyfilter = ['item.type', search.Operator.IS, 'Assembly'];
const hasmorethan400filter = ['sum(quantityavailable)', search.Operator.GREATERTHAN, 400];

The filters parameter is an array of filter objects. Between the filter objects, you can specify the operator to use to combine the filters. If you need parentheses around a group of filters you can add brackets around the filter objects.

Let’s look at an example of using the above filters in a filters array:

1
2
3
4
5
6
7

const filters = [
    locationfilter,
    'AND',
    itemisassemblyfilter,
    'AND',
    hasmorethan400filter
];

Suppose we want items that either are in the locations or are assemblies and have more than 400 available. If we try:

1
2
3
4
5
6
7

const filters = [
    locationfilter,
    'OR',
    itemisassemblyfilter,
    'AND',
    hasmorethan400filter
];

It will be ambiguous whether the OR or the AND should be evaluated first. We have to use brackets to specify the order of operations:

1
2
3
4
5

const filters = [
    locationfilter,
    'OR',
    [itemisassemblyfilter, 'AND', hasmorethan400filter]
];

Columns

The columns parameter is an array of column objects. We can use the search.createColumn() method to create a column object.

The search.createColumn() method takes a few parameters:

  • name - the name of the field to return
  • join (optional) - the join to use for the column
  • summary (optional) - the summary type to use for the column
  • formula (optional) - the formula to use for the column if it is a formula column
  • label (optional) - the label to use for the column (only used if you are saving the search)

Example of creating columns for an Inventory Balance search :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

const locationcolumn = search.createColumn({
    name: 'location'
});
const itemidcolumn = search.createColumn({
    name: 'itemid',
    join: 'item'
});
const quantityavailablecolumn = search.createColumn({
    name: 'quantityavailable',
    summary: search.Summary.SUM
});
const hasmorethan400column = search.createColumn({
    name: 'formulatext',
    summary: search.Summary.SUM,
    formula: `CASE WHEN {quantityavailable} > 400 THEN 'Yes' ELSE 'No' END`
});

We can use specify columns as a string (item.itemid) but we can’t specify the formula or the label so I prefer to use the search.createColumn() method.

Putting it all together

Now that we have our filters and columns, we can create our saved search.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

const searchObject = search.create({
    type: search.Type.INVENTORY_BALANCE,
    filters: [
        ['location', search.Operator.ANY_OF, [1, 2, 3]],
        'AND',
        [['item.type', search.Operator.IS, 'Assembly'],
        'OR',
        ['sum(quantityavailable)', search.Operator.GREATERTHAN, 400]]
    ],
    columns: [
        search.createColumn({
            name: 'location'
        }),
        search.createColumn({
            name: 'itemid',
            join: 'item'
        }),
        search.createColumn({
            name: 'quantityavailable',
            summary: search.Summary.SUM
        }),
        search.createColumn({
            name: 'formulatext',
            summary: search.Summary.SUM,
            formula: `CASE WHEN {quantityavailable} > 400 THEN 'Yes' ELSE 'No' END`
        }),
        search.createColumn({
            name: 'formulatext',
            summary: search.Summary.SUM,
            formula: `CASE WHEN {quantityavailable} > 500 THEN 'Yes' ELSE 'No' END`
        })
    ]
})

We’ll worry about the ambiguity of the column formulatext later.

We have two options for running the search.

  • searchObject.run() - returns a search.ResultSet object
  • searchObject.runPaged() - returns a search.PagedData object

Let’s look at the searchObject.run() method first.

1
2
3

const searchObject = search.create(...)

const searchResult = searchObject.run();

In this search result we have two ways to get the results.

  • searchResult.getRange(options) - returns an array of search.Result objects
  • searchResult.each(callback) - calls the callback function for each search.Result object

Let’s add the results to an array and then log the array.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

const searchObject = search.create(...)
const searchResult = searchObject.run();

const results = []

searchResult.each(function(result) {
    results.push({
        'location': result.getValue('location'),
        'itemid': result.getValue({
            name: 'itemid',
            join: 'item'
        }),
        'quantityavailable': result.getValue({
            name: 'quantityavailable',
            summary: search.Summary.SUM
        }),
        'hasmorethan400': result.getValue('formulatext'),
        'hasmorethan500': result.getValue({ name: 'formulatext_1'})
    });
});

log.debug({
    title: 'results',
    details: results
});

Becase we have two columns that have the same name formulatext Netsuite will append a number to the end of the name of the second column.

We can also use the searchResult.getRange() method to get the results. This allows us to specify the range of results to return.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

const searchObject = search.create(...)
const searchResult = searchObject.run();

const searchResults = searchResult.getRange({
    start: 0,
    end: 10
});

const results = []

for(let i = 0; i < results.length; i++) {

    results.push({
        'location': searchResults[i].getValue('location'),
        'itemid': searchResults[i].getValue({
            name: 'itemid',
            join: 'item'
        }),
        'quantityavailable': searchResults[i].getValue({
            name: 'quantityavailable',
            summary: search.Summary.SUM
        }),
        'hasmorethan400': searchResults[i].getValue('formulatext'),
        'hasmorethan500': searchResults[i].getValue({ name: 'formulatext_1'})
    });
}

If we want to save the search, we can use the searchObject.save() method. This will allow us to access the search in the UI and use it in other scripts.

1
2
3
4
5
6

const searchObject = search.create(...)
searchObject.name = 'My Saved Search' // required;
searchObject.id = 'customsearch_my_saved_search' // optional, must start with customsearch;
searchObject.isPublic = true; // optional, defaults to false;
searchObject.save();

search.LookupFields

For convenience, NetSuite allows you to use the search.lookupFields() method to get the values of fields on a record.

1
2
3
4
5

const itemFields = search.lookupFields({
    type: search.Type.ITEM,
    id: 1234,
    columns: ['itemid', 'displayname', 'custitem_my_custom_field']
});

The columns parameter can be an array of strings or a single string. The search will return an object containing the fields.

Conclusion

Creating searches in SuiteScript is very confusing. I recommend using SuiteQL instead of the search module whenever possible. See my SuiteQL series to get started with SuiteQL.

SuiteScript, API
saved-search search suitescript
This post is licensed under CC BY 4.0 by the author.
Recently Updated
Contents

SuiteScript API: N/record - Part 1

SuiteScript API: N/task