Saved Search Job Configuration¶

Unlike other NetSuite jobs, a Saved Search job must be created by hand. This document describes how to do that. Install the Mitto Bundle prior to following these instructions.

Calling a Saved Search via RESTlet¶

Existing saved searches can be called via a NetSuite RESTlet. The RESTlet returns the results of the saved search as JSON data that can be loaded into Mitto. This describes how to configure a Mitto job to load saved search data. Other documents describe how to call/test RESTlets using curl and Postman.

RESTlet Limitations¶

A RESTlet is JavaScript code that is designed to execute saved searches and return data. The RESTlet implementation calls the nlapiSearchRecord function to execute the saved search. Because of this, only record types currently supported by SuiteScript can be executed.

Resources:

Create a Saved Search¶

For purposes of this example, create a saved search returning Contact records:

Create a saved search for all Contact records. In the NetSuite UI:

Lists -> Search -> Saved Searches
Click New Saved Search
Click Contact
Provide a Search Title
Below Owner, check Public.
Accept the default Criteria.
Accept the default Results with one exception: if you will be creating an upsert job, add Last Modified to the results. This will appear as lastmodifieddate in the results returned by the RESTlet.
Click Audience. To the right of ROLES, check the box for Select All.
Click Save
Make a note of the ID and record type. When creating this example, the ID was customsearch770 and the record type was Contact. These will go in the saved_search section of the job configuration.
Important: view the saved search in the UI to ensure that it executes properly and returns the expected records.

Saved Search Limitations¶

The Results included in a saved search can have a profound effect on the results that are returned. For example, in the Zuar NetSuite account, creating a default saved search for Contact returns 99,742 records. If the search is modified by adding all possible fields to the results, 545,265 records are returned, many with the same internal id. This is apparently due to implicit join(s) that take place as a result of some of the non-default fields that were added.

RESTlets¶

A RESTlet is a custom REST endpoint that calls SuiteScript JavaScript code. This mechanism can be used to create a REST endpoint for a NetSuite saved search. If the Mitto Bundle has been installed in the NetSuite account, two RESTlets already exist. One calls a saved search, the other calls a basic search.

This document describes how to use the Mitto Saved Search RESTlet. A separate document describes how to create a RESTlet from scratch.

Collect Information¶

Because each NetSuite account is different, assets installed by the Mitto Bundle will have different ids in each account. Begin by recording information about the bundle’s installed assets:

RESTlet¶

Get the script id and deploy id of the deployment record for the RESTlet.

Customization -> Scripting -> Script Deployments
If necessary, filter results by setting Filters:Type to RESTlet.
Locate the deployment record for “Mitto Saved Search RESTlet”.
Click View.
Locate the URL field. It will look something like: /app/site/hosting/restlet.nl?script=158&deploy=1 Record the script id and deploy id. For the above URL: script is 158 and deploy is 1.
If the Status of the record is Testing, change it to Released.

These will go in the restlet section of the configuration.

If the deployment record is not owned

Configure the Upsert Job¶

Example job configurations are included in the NetSuite connector’s Debian package installed on each Mitto instance. They are located in /opt/mitto-plugin/netsuite/configs_ex. Make a copy of ss_contact-upsert.json, saving it as /var/mitto/conf/ss_contact.json.

To distinguish saved search jobs from automatically created jobs, it is suggested that their configuration files (and consequently, their store) and tables be prefixed with ss_. This is entirely optional.

Edit ss_contact.json, revising it to look like the following:

{
    "title": "Saved Search - Contact",
    "name": "ss_contact",
    "type": "io"
    "input": {
        "credentials": {
            "account": "TSTDRV1762243",
            "email": "zed@zuar.com"
            "password": "XXXXXXXXXXXX",
            "role": 1078
        },
        "restlet": {
            "script": 641,
            "deploy": 1
        },
        "saved_search": {
            "id": "customsearch770",
            "record_type": "Contact",
            "lower_bound": 1,
            "page_size": 1000
        },
        "use": "netsuite.io#NetsuiteSavedSearchInput"
    },
    "output": {
        "dbo": "postgresql://localhost/analytics",
        "schema": "netsuite",
        "tablename": "ss_contact",
        "use": "call:mitto.iov2.db#todb"
    },
    "steps": [
        {
            "use": "mitto.iov2.steps#MaxTimestamp",
            "column": "lastmodifieddate"
        },
        {
            "use": "mitto.iov2.steps.upsert#SetUpdatedAt"
        },
        {
            "use": "mitto.iov2.steps#Input",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#ExtraColumnsTransform"
                    "jpath": "$",
                    "ignores": ["$.columns"]
                },
                {
                    "use": "mitto.iov2.transform#ExtraColumnsTransform"
                    "jpath": "$.columns"
                },
                {
                    "use": "mitto.iov2.transform#ColumnsTransform"
                }
            ]
        },
        {
            "use": "mitto.iov2.steps#CreateTable"
        },
        {
            "use": "mitto.iov2.steps.upsert#CreateTempTable"
        },
        {
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ],
            "use": "mitto.iov2.steps#Output"
        },
        {
            "key": "id",
            "use": "mitto.iov2.steps.upsert#SyncTempTable"
        },
        {
            "use": "mitto.iov2.steps#CollectMeta"
        }
    ],
    "store": {
        "key": [
            "$.id"
        ],
        "updated_at": "$.columns.lastmodifieddate"
    }
}

Important points about the configuration:

name - determines the name of the Store.
credentials - provide the correct credentials. Note that these differ than the credentials used for other existing NetSuite jobs.
role - internal id of the Mitto User Role.
restlet data is taken from the Script Deployment page for the Mitto Saved Search RESTlet.
1. script - the internal id of the Mitto Saved Search RESTlet.
2. deploy - the id of Mitto Saved Search RESTlet deployment.
saved_search data is taken from the Saved Search page for the desired saved search.
1. id - the internal id of the saved search
2. record_type - the record type returned by the saved search.
The defaults for lower_bound and page_size are shown.
output.tablename - determines the name of the table in the schema.

Running the Upsert Job¶

Create and run the job as you would any other IO job.

A Mitto Saved Search Load Job¶

It’s possible to create a load job that will always obtain all data from NetSuite. This is mandatory for searches that can’t return Last Modified and optional for other searches.

Configuring the Load Job¶

Make a copy of ss_contact-load.json, saving it as /var/mitto/conf/ss_contact.json.

Edit ss_contact.json, revising it to look like the following:

{
    "title": "Saved Search - Contact - Load",
    "name": "ss_contact",
    "type": "io"
    "input": {
        "credentials": {
            "account": "TSTDRV1762243",
            "email": "zed@zuar.com"
            "password": "XXXXXXXXXXXX",
            "role": 1078
        },
        "restlet": {
            "script": 641,
            "deploy": 1
        },
        "saved_search": {
            "id": "customsearch770",
            "record_type": "Contact"
        },
        "use": "netsuite.io#NetsuiteSavedSearchInput"
    },
    "output": {
        "dbo": "postgresql://localhost/analytics",
        "schema": "netsuite",
        "tablename": "ss_contact",
        "use": "call:mitto.iov2.db#todb"
    },
    "steps": [
        {
            "use": "mitto.iov2.steps#Input",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#ExtraColumnsTransform"
                    "jpath": "$",
                    "ignores": ["$.columns"]
                },
                {
                    "use": "mitto.iov2.transform#ExtraColumnsTransform"
                    "jpath": "$.columns"
                },
                {
                    "use": "mitto.iov2.transform#ColumnsTransform"
                }
            ]
        },
        {
            "use": "mitto.iov2.steps#CreateTable"
        },
        {
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ],
            "use": "mitto.iov2.steps#Output"
        },
        {
            "use": "mitto.iov2.steps#CollectMeta"
        }
    ],
    "store": {
        "key": [
            "$.id"
        ]
    }
}

Running the Load Job¶

Create and run the job as you would any other IO job.

RESTlet Limitations¶

Supported Record Types¶

RESTlets use SuiteScript. Because of that, any limitations of SuiteScript are also limitations of RESTlets and using Saved Search via RESTlets. SuiteScript does not support all NetSuite record types. Additionally client side SuiteScript and server side SuiteScript each have their own set of supported record types.

Among other records, DeletedRecords are not supported by SuiteScript and, therefore, saved searches for them do not work via RESTlet.

Apparently unsupported saved searches:

DeletedRecord
Transaction Numbering Audit Log

Before spending time on attempting to use a saved search via RESTlet, be sure to confirm that the desired record type is supported.

This is the best reference: https://tstdrv1762243.app.netsuite.com/app/help/helpcenter.nl?fid=chapter_n3170023.html

The SuiteScript Records Browser is supposed to contain the same information, but I find it much less useful: https://tstdrv1762243.app.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2019_2/script/record/account.html

Appendix¶

Date/Time Formats¶

Every NetSuite account has a preferences setting controlling the formatting of date and time values. The default appears to be m/d/yyyy hh:mm am (at least in the US):

m - month without leading zero
d - day without leading zero
yyyy - four digit year
hh - hour with leading zero
mm - minute with leading zero
am - AM or PM, any of: am, AM, pm, PM

The date format used by NetsuiteSavedSearchInput must exactly match the date/time format in the account’s NetSuite preferences. By default, NetsuiteSavedSearchInput expects the format above.

If an account uses a date/time format different from the above, the connector must be informed of the account’s date/time format. This is done via the saved_search.date_time_format value in the job’s config. The value is a string containing a date/time format specification. The value of date_time_format is ultimately used as an argument to ``datetime.strftime()` <http://strftime.org/>`_.

date_time_format can contain all strftime formatting options except for %m and %d. You may ask yourself, “That’s strange, why is that?”

NetSuite does not allow leading zeros in month and day. strftime only formats month and day with leading zeros. Therefore, %m and %d are not allowed in a date_time_format value. Instead, the connector support {month} and {day}; they provide month and day without a leading zero.

The default value for date_time_format used by the connector is "{month}/{day}/%Y %I:%M %p".

As an example, assume that a NetSuite account has its date/time format set to something like this: yyyy-m-d HH:MM, where HH:MM is 24 hour time. The configuration described earlier would need to be modified like this:

"saved_search": {
    "id": "customsearch770",
    "record_type": "Contact",
    "date_time_format": "%Y-{month}-{day} %H:%M"
},

`saved_search.page_size`¶

The default page_size used by the connector is 1,000.

Saved Searches appear to support working with larger page sizes than record searches. For some saved searches, it is possible to use page_size of 10,000 or more. Setting a custom page_size is done like this:

"saved_search": {
    "id": "customsearch770",
    "record_type": "Contact",
    "page_size": 5000
},

page_size is always rounded to the nearest thousand.

Saved Searches return results so quickly, using a page size larger than 1,000 may not increase performance a great deal.

`saved_search.lower_bound`¶

When the connector calls the RESTlet, it provides an internal id. When the RESTlet calls the saved search, it adds a search filter requesting only records with an internal id greater than the one provided. This, coupled with page_size, is how the RESTlet implements paging. The internal_id used by the RESTlet in its first call to the saved search is defined by lower_bound, whose default is 1.

If desired, a value for lower_bound can be specified in the saved_search section of the configuration. Negative values are allowed (some builtin NetSuite records have negative internal ids).

Debugging¶

Errors Returned by NetSuite and Logged by Mitto¶

'{"error" : {"code" : "SSS_INVALID_SCRIPTLET_ID", "message" : "That Suitelet is invalid, disabled, or no longer exists."}}' means that either the restlet configuration is incorrect, or that the Status of the deployment record is Testing, making it invisible to the Mitto User Role. In the latter case, change the Status to Released.
INVALID_LOGIN_ATTEMPT - This error indicates a problem in an OAuth header.
INVALID_LOGIN_CREDENTIALS - This error indicates a problem in an NLAuth header.
INVALID_REQUEST - The request could not be understood by the server due to malformed syntax.
TWO_FA_REQD - Two-Factor Authentication required.

Request Returns HTTP 200 with Errors¶

Although the request completes with a 200, no data is returned and result in the response looks like this:

{
    "params": {
        "record_type": "Contact",
        "search_id": "customsearch763kkk",
        "batch_size": "1000",
        "lower_bound": "1"
    },
    "result": {
        "message": "That search or mass update does not exist.",
        "trace": ["<anonymous>(netsuite_toolkit.js:209)", "<anonymous>(mitto_saved_search.js$6594:131)", "<anonymous>(mitto_saved_search.js$6594:117)", "<anonymous>(mitto_saved_search.js$6594:99)", "<anonymous>(mitto_saved_search.js$6594:219)", "restletwrapper(null:4)", "<anonymous>(mitto_saved_search.js$6594:223)"]
    },
    "success": true
}

Ensure that Public underneath the Owner field in the Saved Search configuration is checked.

Saved Search¶

It may be necessary in the Saved Search to:

Set Audience: Roles to Select All. This can also be a cause of the first point above.

Last Modified Errors from Mitto¶

If an upsert configuration was used for the job, be sure to include Last Modified in the data that is returned by the saved search. If Last Modified is not available, it will be necessary to use a load configuration.

Table Of Contents

Saved Search Job Configuration¶

Calling a Saved Search via RESTlet¶

RESTlet Limitations¶

Create a Saved Search¶

Saved Search Limitations¶

RESTlets¶

Collect Information¶

RESTlet¶

Configure the Upsert Job¶

Running the Upsert Job¶

A Mitto Saved Search Load Job¶

Configuring the Load Job¶

Running the Load Job¶

RESTlet Limitations¶

Supported Record Types¶

Appendix¶

Date/Time Formats¶

saved_search.page_size¶

saved_search.lower_bound¶

Debugging¶

Errors Returned by NetSuite and Logged by Mitto¶

Request Returns HTTP 200 with Errors¶

Saved Search¶

Last Modified Errors from Mitto¶

`saved_search.page_size`¶

`saved_search.lower_bound`¶