# 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: * [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) # 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: 1. `Lists -> Search -> Saved Searches` 1. Click `New Saved Search` 1. Click `Contact` 1. Provide a `Search Title` 1. Below `Owner`, check `Public`. 1. Accept the default `Criteria`. 1. 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. 1. Click `Audience`. To the right of `ROLES`, check the box for `Select All`. 1. Click `Save` 1. 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. 1. **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. 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=158&deploy=1` Record the script id and deploy id. For the above URL: `script` is `158` and `deploy` is `1`. 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: ```javascript { "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: 1. `name` - determines the name of the Store. 1. `credentials` - provide the correct credentials. Note that these differ than the credentials used for other existing NetSuite jobs. 1. `role` - internal id of the Mitto User Role. 1. `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. 1. `deploy` - the id of Mitto Saved Search RESTlet deployment. 1. `saved_search` data is taken from the Saved Search page for the desired saved search. 1. `id` - the internal id of the saved search 1. `record_type` - the record type returned by the saved search. 1. The defaults for `lower_bound` and `page_size` are shown. 1. `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: ```javascript { "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: ```javascript "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: ```javascript "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 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. ### 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": ["(netsuite_toolkit.js:209)", "(mitto_saved_search.js$6594:131)", "(mitto_saved_search.js$6594:117)", "(mitto_saved_search.js$6594:99)", "(mitto_saved_search.js$6594:219)", "restletwrapper(null:4)", "(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.