Saved Search Job

Mitto’s Saved Search Job calls Zuar’s NetSuite Saved Search RESTlet which, in turn, executes a previously defined NetSuite saved search. The RESTlet returns the results of the saved search as JSON data so that the data can be further processed by Mitto. This note describes how to configure a Mitto Saved Search job to load data into Mitto.

Zuar’s Mitto Bundle installs the Mitto User Role and the Mitto Saved Search RESTlet in the host NetSuite account. The Mitto Bundle must be installed prior to following these instructions.

Unlike other NetSuite jobs, a Saved Search job must be created by hand. At a high-level, the steps to create and use a Saved Search job are:

1. Install Zuar’s Mitto Bundle in the host NetSuite account.

2. Create a NetSuite saved search.

3. Create a job configuration, either “load” or “upsert”.

4. Create a Mitto job with the configuration.

5. Run the job

The remainder of this note walks through setting up Mitto and configuring NetSuite for a Saved Search Upsert Job that will collect transactions affecting revenue in 2021.

Install the Mitto Bundle

Each version of the mitto-plugin-netsuite is matched to a specific version of the Mitto Bundle, as is shown in this table:

Bundle and Plugin Version Compatibility

Mitto Bundle / RESTlet	mitto-plugin-netsuite
2.0 / 2021.5.25	2021.6.4
2.0 / 2021.5.25	2021.5.25
2.0 / 2021.5.25	2021.5.13
1.5 / 2021.5.25	2021.5.13
1.4 / 2021.2.25	2021.3.1, 2021.2.25

Warning

When Zuar publishes updated bundles to NetSuite, you may receive email from NetSuite about the update. Additionally, NetSuite will display alerts regarding available updates when viewing installed bundles. Do not update the Mitto bundle without first confirming that it is compatible with the current plugin version. It may be necessary to first upgrade the plugin to ensure compatibility. Mismatched versions are likely to cause job failures.

These instructions describe how to install the Mitto Bundle. Among other things, the bundle will install Mitto’s Saved Search RESTlet and the Mitto User Role; both are necessary for Mitto to be able to collect data from a saved search.

Create a Saved Search

If you have an existing saved search, feel free to use that, but be sure to skim this section as it contains important information. In this example, we’ll create a saved transaction search. Refer to the Figure: jdss_saved_search_criteria.

The criteria shown select income affecting transactions; your criteria can differ. The box next to Public must be checked for Mitto to be able to use the saved search.

When the criteria are complete, click the Results tab and specify the output fields. Refer to Figure: Saved Search Results Fields.

Saved Search Results Fields

Important points about results:

1. NetSuite automatically includes the Internal Id in results fields; it is unnecessary, but not incorrect, to add it as a results field.

2. If Line Id appears in the list of available fields, it must be added to the results columns. This is necessary to prevent data loss in certain situations. If Line Id does not appear in the list of available fields, it can safely be omitted.

3. If the saved search will be used with an upsert job, Last Modified must be included in the results columns.

4. Generally, you should not provide Sort By criteria. The RESTlet automatically sorts results in increasing order of Internal Id and, if present, Line Id.

5. Do not include groups, summaries, or formulas in the results. NetSuite does not return all data to the RESTlet when these are present, causing data loss.

You can provide an Internal Id for the saved search or you can allow NetSuite to automatically create one for you. In the above example, NetSuite assigned an Internal Id of customsearch799 to the search. Record the Internal Id of your search; it will be needed later when configuring the saved search job.

Important: Once the saved search has been saved, it should be tested to ensure that it executes properly and returns the expected records. This is done by viewing the saved search in the UI or downloading the results as a CSV. The viewing/export must be done using the NetSuite role under which the saved search job will ultimately run (e.g., the Mitto User Role).

Create a Job Configuration

Begin by collecting the information necessary to create the job configuration

NetSuite Credentials

As of release 2020.6.1, the connector supports use of Token-Based Authentication (TBA) for calling RESTlets to invoke saved searches. Earlier versions of the connector supported only Basic Authentication (username/password). All new saved search jobs should be configured to use Token-Based Authentication.

TBA Credentials

Credentials used for Token-Based Authentication are the same as used in all other jobs requiring credentials. E.g.:

"credentials": {
    "account": "TSTDRV1762243",
    "consumer_key": "...consumer-key...",
    "consumer_secret": "...consumer-secret...",
    "token_id": "...token-id...",
    "token_secret": "...token-secret..."
}

Reference: TBACredentials

Basic Credentials

When Basic Credentials are used, the Internal ID of the Mitto User Role must be provided. If NetSuite has been provisioned such that the connector is using a different role, that role’s Internal ID must be provided.

Note that a role is not used with TBA.

"credentials": {
   "account": "TSTDRV1762243",
   "email": "zed@zuar.com",
   "password": "...password...",
   "role": 1078
},

Reference: BasicCredentials

RESTlet Information

The restlet section contains the information necessary to identify the Saved Search RESTlet installed when the Mitto Bundle is installed in the NetSuite account. The configuration values are taken from from the Script Deployment page for the Mitto Saved Search RESTlet.

1. Customization -> Scripting -> Script Deployments

2. If necessary, filter results by setting Filters:Type to RESTlet.

3. Locate the deployment record for “Mitto Saved Search RESTlet”.

4. Click View.

5. Locate the URL field. It will look something like: /app/site/hosting/restlet.nl?script=641&deploy=8 Record the script id and deploy id. For the above URL: script is 641 and deploy is 8.

6. If the Status of the record is Testing, change it to Released.

These will go in the restlet section of the configuration:

"restlet": {
    "script": 641,
    "deploy": 8
},

Configuration <–> NetSuite Relationships

The following diagram depicts the relationship between sections of the job configuration and the customer’s NetSuite account.

Example NetSuite Account Configuration & Matching Mitto Job Configuration

The Job Configuration

Now it is time to take the information collected thus far and combine it into a single job configuration file that can be executed by Mitto. The saved search job supports both “load” jobs as well as “upsert” jobs.

Note

These configurations are in HJSON format, which is supported in Mitto 2.9 and later. When using these with earlier Mitto versions, it will be necessary to remove trailing commas, comments, and other features not supported by plain JSON.

Record Type

The record type associated with the saved search must be included in the job configuration.

When creating a saved seach in the NetSuite UI, the first choice one makes is “Search Type”. The search type specifies the record type to be searched. However, it isn’t always obvious what the internal id of the chosen record type is. While an Account search searches records with a record type of Account, a Project search searches records with a record type of job.

For standard NetSuite records, the Records Browser is the best source to consult when divining the record type of a saved search. The “Internal ID” field contains the the associated record type.

NetSuite Records Browser

In the case of Project:

1. Click Records Browser

2. Click Project

3. Record type is job

Custom record types are a little different. Custom records naturally won’t appear in the Records Browser. You can view all of the custom records configured in your account by going to: Customization ~> Lists, Records & Fields~ > Record Types. The name used when creating the saved search appears in the Edit column; the record type to use in the Mitto job configuration appears in the Id column.

Load Job

Let’s begin by presenting an example “load” job configuration. Each time it is run, this job will load all records returned by the saved search.

“load” Job Configuration

/* file: mitto-plugin-netsuite/docs/src/examples/load.json
 *
 * Run a job using this config from the commandline in a dev environment via:
 *    $ CREDS=~/run/ns    EX=~/mitto-plugin-netsuite/docs/src/examples
 *    $ job_io.py `mmc.py --env $CREDS/ns-zuardev.env $EX/load.json $CREDS/ns-creds.py`
 */
{
    "title": "Saved Search - Load Example",
    "name": "ss_load",
    "type": "io",
    "input": {
        "use": "netsuite.io#NetsuiteSavedSearchInput",
        "credentials": { /*credentials*/  },
        "restlet": {
            "script": 641,
            "deploy": 8,
        },
        "saved_search": {
            "saved_search_id": "customsearch799",
            "record_type": "Transaction",
	    // must be present if "Line Id" is in saved search output columns
	    "row_line_id": 0,
        },
    },
    "output": {
        "use": "call:mitto.iov2.db#todb",
        "dbo": "postgresql://localhost/analytics",
        "schema": "netsuite",
        "tablename": "ss_load",
    },
    "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"
        },
        {
            "use": "mitto.iov2.steps#Output",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ],
        },
        {
            "use": "mitto.iov2.steps#CollectMeta"
        }
    ],
}

Upsert Job

In many cases, it’s only necessary to get the records that have changed since the last time ran a job. Jobs configured to do this are called upsert jobs.

For an upsert job to work properly, Last Modified must be included in the Results columns. Generally, this places the last modified date of each row in a column named lastmodifieddate. Knowledge of the column name is important to crafting the job configuration.

Upsert Job Configuration

/* file: mitto-plugin-netsuite/docs/src/examples/upsert-default.json
 *
 * Run a job using this config from the commandline in a dev environment via:
 *    $ CREDS=~/run/ns    EX=~/mitto-plugin-netsuite/docs/src/examples
 *    $ job_io.py `mmc.py --env $CREDS/ns-zuardev.env $EX/upsert.json $CREDS/ns-creds.py`
 */
{
    "title": "Saved Search - Upsert Default Example",
    "name": "ss_upsert",
    "type": "io",
    "input": {
        "use": "netsuite.io#NetsuiteSavedSearchInput",
        "credentials": { /*credentials*/  },
        "restlet": {
            "script": 641,
            "deploy": 8,
        },
        "saved_search": {
            "saved_search_id": "customsearch799",
            "record_type": "Transaction",
	    // must match the Last Modified column name in saved search output
	    "last_modified_column": "lastmodifieddate",
	    // must be present if "Line Id" is in saved search output columns
	    "row_line_id": 0,
        },
    },
    "output": {
        "use": "call:mitto.iov2.db#todb",
        "dbo": "postgresql://localhost/analytics",
        "schema": "netsuite",
        "tablename": "ss_upsert",
    },
    "steps": [
	{
	    "use": "mitto.iov2.steps#MaxTimestamp"
	    "schema": "netsuite",
	    "tablename": "ss_upsert",
	    // Database column containing last modified date
	    "column": "last_modified",
	},
	{
	    "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"
        },
        {
            "use": "mitto.iov2.steps#Output",
            "transforms": [
                {
                    "use": "mitto.iov2.transform#FlattenTransform"
                }
            ],
        },
	{
	    "use": "mitto.iov2.steps.upsert#SyncTempTable",
	    "key": [
		"id",
		"line",
	    ],
	},
        {
            "use": "mitto.iov2.steps#CollectMeta"
        }
    ],
    "store": {
	"key": [
	    "$.id",
	    "$.columns.line",
	],
	// Location of last modified date in the store
	"updated_at": "$.last_modified",
    },
}

Highlights from the job configuration:

• Line 22 tells Mitto that output column named lastmodifieddate should be considered the most recent date on which the record was modified by NetSuite. Mitto will automatically save these values in the last_modified column of the output database table.

• Line 24 tells Mitto that the Line Id column is present in the output of the saved search. This line should be omitted if Line Id is not in the output.

• Line 39 tells Mitto that values from the last_modified_column in the saved search output are store in the last_modified column of the output database table..

• Lines 78-79 tell Mitto that, when syncing new data with an existing output table, the key is id + line.

• Lines 88-89 specify the key to use when saving incoming data to the store. These are the jpath locations of the keys in the JSON data received from NetSuite.

• Line 92 specifies the jpath location of the last modified value in Mitto’s store.

Data Modified On or After a Date

An upsert job requests data from NetSuite that has been modified on or after the date that the job last got data. The saved search plugin does this using the SuiteScript nlapiSearchRecord function. NetSuite’s handling of the date associated with the request is somewhat unusual, An understanding of it is important; it is described in the SavedSearch section of the job configuration documentation.

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": "customsearch761",
    "record_type": "Contact",
    "date_time_format": "%Y-{month}-{day} %H:%M"
},

Common Challenges

1. I’m not getting all the data I’m expecting.

   This is often due to NetSuite roles and permissions. It’s likely that the Mitto User Role does not have sufficient permissions to access some or all of the data.

   This situation can be easily diagnosed by:

   1. Login to the NetSuite account.

   2. Assume the Mitto User Role (or whatever role is assigned to Mitto). This step is critically important.

   3. Go to Lists -> Search -> Saved Searches.

   4. Locate the row with the desired saved search and click Export (CSV).

   5. This file contains exactly the data that should be retrieved by Mitto.

   6. Remove any existing table and store associated with the Mitto job.
      

   7. Run the associated Mitto saved search job.

   8. Compare the results of the CSV with the contents of the table created by the job.

2. Permission Violation

   If you get an error similar to the following :

   Permission Violation:
       You need a higher permission for custom record type customrecord484
       to access this page. Please contact your account administrator.
   

   Ensure the the Run Unrestricted box is check in the Results tab of the saved search definition.

3. 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": "notAnExistingSearchId",
       },
       "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
   }
   

   Possible causes:

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

   • Ensure that the record_type for a custom record search is correct. A common error is to use the name of the custom record; the Id of the custom record obtained from the Record Types page is the correct value.

4. Saved Search “does not exist”

   If you get errors about a saved search “does not exist”, ensure that “Public” is checked.

5. Mitto Upsert Job Errors

   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.

6. Errors Returned by NetSuite and Logged by Mitto

   1. '{"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.

   2. INVALID_LOGIN_ATTEMPT - This error indicates a problem in an OAuth header.

   3. INVALID_LOGIN_CREDENTIALS - This error indicates a problem in an NLAuth header.

   4. INVALID_REQUEST - The request could not be understood by the server due to malformed syntax.

   5. TWO_FA_REQD - Two-Factor Authentication required.

Debugging in the Browser

Your browser can be an effective tool in tracking down problems with saved searches and the Saved Search RESTlet. This document describes the process.

Appendix

This appendix contains additional in-depth information about the saved search job and RESTlets. It is worth skimming at least once to familiarize oneself with edge-cases that may affect job behavior.

Timezones and NetSuite DateTime Objects

NetSuite uses three different timezones:

NetSuite’s internal / datacenter timezone

This is always Pacific Time (e.g.: America/Los_Angeles, GMT-08:00). This value never changes, regardless of the physical location of the server. NetSuite never uses GMT/UTC.

The company’s timezone

The timezone set for the company in the Setup section of the NetSuite user interface.

The user’s timezone

Each user’s NetSuite account has a timezone setting in their Preferences page. For a user’s timezone change to go into effect, the user must log out and log back in. It can take some time for a user’s timezone change to propagate / go into effect.

To date, we have not found NetSuite documentation discussing how and when each timezone is used. The documentation for nlapiStringToDate states that server-side scripts receive a datetime object in the Pacific Time Zone. Neither have we found NetSuite documentation describing the timezone of data returned by saved search via RESTlet.

Our testing to date leads us to strongly believe that data returned by saved search via RESTlet is in Pacific Time (GMT-08:00) as well.

This is important because it influences how the saved search job saves last modified date values and how it uses those values when requesting data modified after a certain date.

Last Modified Dates and Mitto

A DateTime represents a date and/or time in a specific timezone. This is of particular importance for upsert jobs that make use of last_modified modified database column (or whatever is specified by last_modified_column) and the MaxTimestamp step.

The plugin saves last modified date values in the store exactly as the are received from NetSuite. The plugin saves last modified date values in the in the database as a Postgres timestamp with timezone. In the latter case, the value used is the original value received from NetSuite with a timezone of GMT-08:00.

For example:

1. The plugin receives a lastmodifieddate value of 1/12/2021 7:00:00 am.

2. That record is saved in the store with a $.last_modified value of 2021-01-12T07:00:00-08:00. lastmodifieddate is saved in the store with a value of 1/12/2021 7:00:00 am. Note that in the former case, the value has a timezone offset of -08:00; it has been placed in the Pacific Timezone.

3. The same data will be saved in the database as 2021-01-12 15:00:00.000000. This value is identical to 2021-01-12T07:00:00-08:00; the original value in Pacific Time was been converted to UTC.

RESTlet Limitations

RESTlets use NetSuite’s 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 NetSuite has “client-side” and “server-side” flavors of SuiteScript, each with 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.

The following records are apparently unsupported by server-side SuiteScript, which means that is not possible to obtain them from Mitto’s saved search job:

• DeletedRecord

• Transaction Numbering Audit Log

The following references may be helpful in confirming record availability from a RESTlet:

• Using existing saved searches

• SuiteScript Supported Records

• Records Browser

Saved Search Best Practices

1. When defining the output columns of a saved search, if Line Id is available, include it and use it provide the row_line_id parameter in the saved search section of the job configuration.

2. The columns (Results) included in a saved search can have a material effect on the amount of data returned by the search. For example, in the Zuar NetSuite account, creating a saved search for Contact records using only the default columns returns 99,742 records. If the search is modified by adding all possible columns 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.

   Limiting output columns to only those needed can significantly reduce runtimes.

3. The NetSuite API does not return all data for saved searches that contain formulas or grouping/summaries; formulas and grouping should not be used in saved searches that will be called by the RESTlet.

4. Set Audience: Roles to Select All.

5. Always test saved searches using the NetSuite role that will be used by Mitto jobs.

saved_search.page_size

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

Saved Searches allow Mitto to work with larger page sizes than record searches. In some situations, increasing the page size over the default can provide significant performance improvements. This is done using the page_size parameter of saved_search.

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).

NetSuite Logging

SuiteLets (JavaScript code) installed by the Mitto Bundle in newer versions of the plugin log helpful information on the NetSuite backend. By default, all logging done by the RESTlet is at the DEBUG level.

To view RESTlet logging output:

1. View the Deployment Record for the Mitto Saved Search RESTlet.

2. Click Execution Log in the sub-menu.

Information that can be logged by the RESTlet includes:

• The user name of the user

• The internal id of the role used.

• The roleId of the role used.

• The scriptId of the script called by the job.

• The version of NetSuite in use.

• Other parameters such as the current internal_id and line_id.

In a typical NetSuite account, the RESTlet will be set to Released with a logging level of ERROR. Because all RESTlet logging is DEBUG, it will not appear in the execution log for the RESTlet. RESTlet log messages can be made to appear in the execution log by either of two means:

1. Edit the Deployment Record for the Mitto Saved Search RESTlet.

   1. Ensure the Status is set to Released.

   2. Change Log Level to Debug.

   3. Click Save

   After doing this, RESTlet log messages will appear in the execution log. Note that this requires privileges sufficient to edit the deployment record.

2. An alternative to the above is to use the restlet_log_level parameter of saved_search. By setting this to ERROR, all RESTlet log messages will be at the ERROR level, causing them to appear in the execution log. This is primarily intended for use with accounts in which it is not possible to change the log level of the deployment record.

In either case, enabling logging for searches returning large amount of data can result in thousands of messages being logged in NetSuite. One must keep NetSuite Governance rules regarding the number of allowed log messages when using this feature.