Saved searches are supported with some limitations due to NetSuite restrictions.

Note: We recommend taking a look at RESTlets instead of following this guide. While these steps will still work, saved searches used from SuiteTalk API as detailed below are limited both in how metadata can be obtained and content available. RESTlets have fewer limitations and return accurate metadata from NetSuite.

SavedSearches View

The SavedSearches view is a good way to retrieve a list of your available saved searches for a given search type. A SearchType must be specified to retrieve information from the SavedSearches view. For instance:

SELECT * FROM SavedSearches WHERE SearchType='Transaction'

CreateSavedSearchSchema Stored Procedure

The CreateSavedSearchSchema stored procedure is used to generate a schema file for the saved search. This schema file is automatically generated based on the saved search results. The file will be written to the folder indicated by the Location connection property.

Note: Given that this adapter supports multiple schemas, the file path should end with the root folder that contains all of the separate schema folders.

To execute the stored procedure, simply supply the Name, SearchType, and Id (returned from SavedSearches):

EXEC CreateSavedSearchSchema @SavedSearch='MySavedSearch', @SearchType='Transaction', @SavedSearchId='12345'

Unlike normal tables, there is no metadata request for saved searches. To get the metadata, we have to retrieve a sample of the results and make an assumption about the available columns based on the response. Sometimes this may mean columns are missing due to all of their values being null in the sample response.

It is easy to modify these schema files. Just open them with any text editor. You will notice the file is just XML, with the column names defined near the top of the file. You can rename the columns to something more appropriate. Just be aware that valid column names for these XML files must be alphanumeric, must start with an alphabetical character, and may only have the underscore '_' character in addition to alphanumeric characters.

Retrieving Data

Once a schema file has been created, data can be retrieved from the saved search using the schema name. For instance:

SELECT * FROM MySavedSearch

You may notice that the column names do not match what you see displayed in the UI. This is because NetSuite is using labels when you execute the saved search. Sometimes those columns come from different tables (when there is a join), and sometimes they are custom fields. Since there is no metadata service available for saved searches, there is not a good way to automatically obtain these labels. However, as explained earlier, these schema files may by updated manually to express the column names the way you want them to.

Limitations

NetSuite imposes a few limitations on saved searches. Calcualted columns (formulas) cannot be retrieved via the NetSuite API. Only columns directly from the table may be retrieved. If you have a calculation in your saved search, you would need to return each individual column used in the calculation and perform the calculation client side. Ie:

SELECT (col1 / col2) AS calc FROM MySavedSearch

Saved searches that return an aggregation or summary (Group / Count / Sum / Minimum / Maximum / Average) cannot be returned at all. The NetSuite API will throw an exception upon attempting to retrieve these saved searches. Due to this limitation, these saved searches are unavailable for our and any other third party tool.