# 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](
https://www.zuar.com/blog/netsuite-setup-instructions-for-mitto/) 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:

.. table:: Bundle and Plugin Version Compatibility
   :align: left

   ======================= =======================
   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](
https://www.zuar.com/blog/netsuite-setup-instructions-for-mitto/)
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: :ref:`jdss_saved_search_criteria`.

.. _jdss_saved_search_criteria:

![foo](img/saved-search-create-1.png)


.. figure:: img/saved-search-create-1.png
   :width: 6in
	
	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: 
:ref:`jdss_saved_search_results_fields`.

.. _jdss_saved_search_results_fields:

.. figure:: img/saved-search-create-2.png
	:width: 6in

	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.
1. 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.
1. If the saved search will be used with an upsert job, `Last
   Modified` must be included in the results columns.
1. Generally, you should not provide `Sort By` criteria.  The RESTlet
   automatically sorts results in increasing order of `Internal Id`
   and, if present, `Line Id`.
1. 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: :ref:`TBACredentials Schema`

### 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: :ref:`BasicCredentials Schema`

## 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`
1. If necessary, filter results by setting `Filters:Type` to `RESTlet`.
1. Locate the deployment record for "Mitto Saved Search RESTlet".
1. Click `View`.
1. 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`.
1. 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.

.. COMMENT
   The source for the image is netsuite-relations.graffle. To make modifications
   1. Modify the graffle file
   2. Save as PDF
   3. Convert PDF to SVG
      A. Use a very new version of OmniGraffle which does this
	  B. Use old version, export as PDF
      C. 'inkscape netsuite-relations.pdf -o netsuite-relations.svg'

.. figure:: img/netsuite-relations.svg
   :target:  _images/netsuite-relations.svg
   
   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](
https://system.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2021_1/script/record/job.html
) 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.

.. figure:: img/records-browser.png
	:width: 6in

	NetSuite Records Browser

In the case of `Project`:
1. Click `Records Browser`
1. Click `Project`
1. 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.

.. admonition:: "load" Job Configuration
   :class: dropdown

   .. literalinclude:: examples/load.json
	  :language: javascript
	  :linenos:

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

.. admonition:: Upsert Job Configuration
   :class: dropdown

   .. literalinclude:: examples/upsert.json
	  :language: javascript
	  :linenos:

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 <schemas/netsuite/types/index_SavedSearchConfig.html#savedsearch>`_
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:

```javascript
        "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:

   #. Login to the NetSuite account.
   #. Assume the Mitto User Role (or whatever role is assigned to
	  Mitto).  **This step is critically important.**
   #. Go to `Lists -> Search -> Saved Searches`.
   #. Locate the row with the desired saved search and click `Export
	  (CSV)`.
   #. This file contains exactly the data that should be retrieved by
	  Mitto.
   #. Remove any existing table and store associated with the Mitto
	  job.	
   #. Run the associated Mitto saved search job.
   #. Compare the results of the CSV with the contents of the table
	  created by the job.

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

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

1. Saved Search "does not exist"

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

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

1. 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`.

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

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

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

   1. `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](
saved_search_debugging.md) 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](
https://tstdrv1762243.app.netsuite.com/app/help/helpcenter.nl?fid=section_n3061128.html#bridgehead_N3061544
) 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`.
   1. 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.
   1. 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](
  https://tstdrv1762243.app.netsuite.com/app/help/helpcenter.nl?fid=section_n3004042.html)
* [SuiteScript Supported Records](
  https://tstdrv1762243.app.netsuite.com/app/help/helpcenter.nl?fid=chapter_N3170023.html)
* [Records Browser](
  https://tstdrv1762243.app.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2019_2/script/record/account.html)
  
## 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.

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

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

1. Set `Audience: Roles` to `Select All`.

1. 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`.
1. 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`.
   1. Change `Log Level` to `Debug`.
   1. 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.

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