Access NetSuite Saved Searches as Views

The CData Drivers (JDBC, ODBC, API Server, ADO.NET, SSIS, BizTalk, and Excel) implement standards-based access to various data sources, which enables third-party tools to interoperate. You can use CDrivers to create reports on NetSuite data, and you can also access existing reports. This article shows how to query a saved search in NetSuite.

When you connect to NetSuite, CData drivers dynamically retrieve the table definitions for NetSuite entities, so that changes to the data source are immediately reflected. You can extend the default functionality of CData Drivers for NetSuite to include table definitions for the saved searches you want. You can easily create table definitions by executing the CreateSavedSearchSchema stored procedure.

CreateSavedSearchSchema enables you to create a view of a saved search. A view is a read-only table. Stored procedures are function-like interfaces to the data source that can be used to search, update, and modify information.

Connect to NetSuite

The first step is to establish the connection to NetSuite. In addition to the required connection properties, set the Location connection property, in the Schema section, to the folder where you want the schema to be written.

Refer to the help documentation for a step-by-step guide to connect.

Retrieve the List of Saved Searches

After you connect to NetSuite, you can retrieve a list of saved searches by querying the SavedSearches view.

To query this view, you must specify the SearchType column in the WHERE clause; for example, the query below retrieves all saved searches that have the "transaction" type:

SELECT * FROM SavedSearches WHERE SearchType='Transaction'

Create the Table Definition for the Saved Search

Use the CreateSavedSearchSchema stored procedure to generate a table definition for a saved search. When you reconnect, the driver will detect the resulting schema. You can then query the saved search as a view.

To call the stored procedure, you will need to enter the following input parameters:

• SavedSearch: The name of the saved search.
• SavedSearchId: The Id of the Saved Search. Set this input to the value of the InternalId column from the SavedSearches view.
• SearchType: The type of the Saved Search. Set this input to the value of the SearchType column you specified in the WHERE clause.

The example below creates a table schema for the 'Orders To Ship' search:

EXEC CreateSavedSearchSchema SavedSearch='Orders To Ship', SavedSearchId='32', SearchType='Transaction'

Query a Saved Search as a View

After you generate the schema and set the Location connection property, you can now query the results of the saved search. For example:

SELECT * FROM Orders_To_Ship