`

Matillion ETL Data Model for Netsuite
Version - 16.0.6375

Note: Data models are true for the latest version of Matillion ETL. If you are on an older version or using a component from an old job, your experience may differ.




Connection String OptionsBack To Top

  1. Account Id
  2. Aggregate Column Mode
  3. Application Id
  4. Auto Cache
  5. Cache Connection
  6. Cache Driver
  7. Cache Location
  8. Cache Metadata
  9. Cache Query Result
  10. Cache Tolerance
  11. Connect On Open
  12. Firewall Password
  13. Firewall Port
  14. Firewall Server
  15. Firewall Type
  16. Firewall User
  17. Include Child Tables
  18. Include Custom Field Columns
  19. Include Custom List Tables
  20. Include Custom Record Tables
  21. Include Reference Columns
  22. Location
  23. Logfile
  24. Logout Unknown Sessions
  25. Maximum Concurrent Sessions
  26. Max Log File Size
  27. Netsuite Metadata Folder
  28. OAuth Access Token
  29. OAuth Access Token Secret
  30. OAuth Client Id
  31. OAuth Client Secret
  32. Offline
  33. Other
  34. Pagesize
  35. Password
  36. Proxy Auth Scheme
  37. Proxy Auto Detect
  38. Proxy Password
  39. Proxy Port
  40. Proxy Server
  41. Proxy SSL Type
  42. Proxy User
  43. Pseudo Columns
  44. Query Cache
  45. Readonly
  46. Request Memorized Transactions
  47. Role Id
  48. RTK
  49. SSL Server Cert
  50. Support Enhanced SQL
  51. Tables
  52. Timeout
  53. Use Async Services
  54. User
  55. User Timezone Offset
  56. Use Sandbox
  57. Use Sessions
  58. Use Upserts
  59. Verbosity
  60. Version
  61. Views
  62. Web Service Host



Account Id

The company account your username is associated with on NetSuite.
Data Type

string

Default Value

""

Remarks

Together with User and Password, this field is used to authenticate to NetSuite.





Aggregate Column Mode

Indicating how aggregate columns should be treated.
Data Type

string

Default Value

"Ignore"

Remarks

Aggregate columns are the columns that will appear on base tables which aggregate all of the data contained within child collections. Because these columns include all the data of a child collection, they can become very large. In some situations, such as writing the data to an offline database, it may be advisable to set AggregateColumnMode to either Ignore or List. The data in child tables can still be retrieved by setting IncludeChildTables to true. Setting AggregateColumnMode to List will still cause aggregate columns to be listed for use with inserts and updates.

IgnoreAll aggregate will be ignored and will not show up as available colums in the table definition.
ListAggregate columns will be listed in all tables, but on base tables such as SalesOrders, they will not retrieve data from NetSuite.
ListAndRetrieveAggregate columns will be listed and requested on all tables.





Application Id

As of version 2015.2, requests to NetSuite require an application ID or token-based authentication details.
Data Type

string

Default Value

""

Remarks

This field is used to authenticate to NetSuite. These IDs are different from application IDs created in versions 2015.1 and older. You can find your applications in the NetSuite UI under Setup > Integration > Manage Integrations. An application ID is set by default, but can optionally be specified.





Auto Cache

Persists the results of SELECT queries, allowing real-time reporting while online and use while offline.
Data Type

bool

Default Value

false

Remarks

When AutoCache is set, the driver maintains a cache in the background and executes queries against the local data by default. The driver incrementally updates the cache, retrieving only changesets instead of first dropping the cached tables and retrieving the entire dataset again. The driver can update the cache on an interval (the default) or when you execute a SELECT query to the remote NetSuite data.

This functionality gives you the option to query a persistent, up-to-date cache or execute real-time queries with a failover solution for connectivity loss.

Caching on an Interval

By default, the driver replicates on an interval. You can use the CacheTolerance property to control how often the driver retrieves changesets from the NetSuite API.

the CacheTolerance functionality makes AutoCache a single setting to implement robust caching. CacheTolerance is also useful for caching if your application generates many queries in succession -- CacheTolerance prevents the driver from running a replication on every query, as is the case when CacheQueryResult is set.

Caching on Every SELECT

CacheQueryResult is a way to query NetSuite in real time while maintaining a cache for offline use. Set CacheQueryResult to update the cache whenever you execute a SELECT statement. When you execute a SELECT statement with AutoCache and CacheQueryResult set, the driver executes the query to the remote data and persists the results; rows that already exist are overwritten. That is, SELECT statements are used to create and refresh the cache, not to query it. Data manipulation commands are executed to the remote data as well.

To query the cached data, set the Offline property. If you need to query the cached data in an online connection, you can append #CACHE to the table name. For example:

SELECT * FROM [Account#CACHE]

Setting the Caching Database

When AutoCache is set, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

See Also





Cache Connection

The connection string for the cache database.
Data Type

string

Default Value

""

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. You will need at least the following information to connect to one of the caching databases below. To load the caching database's driver, refer to CacheDriver.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. The documentation for the underlying JDBC driver will have all of the available properties.

Derby and Java DB

In most caching scenarios, you need to set only the following:

For other Java DB configurations, you will need to use CacheDriver and CacheConnection. Below is the Derby JDBC URL syntax:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]
For example, to cache to an in-memory database, use the following:
jdbc:derby:memory

SQLite

To cache to SQLite, you can use the SQLite JDBC driver. Below is the syntax of the JDBC URL:

jdbc:sqlite:dataSource

MySQL

The installation includes the CData JDBC Driver for MySQL. Below is an example JDBC URL:

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache
Below are typical connection properties:

SQL Server

The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]
For example:
jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true
Below are typical SQL Server connection properties:
Oracle

Below is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:

jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]
For example:
jdbc:oracle:thin:scott/tiger@myhost:1521:orcl
Below are typical connection properties:
PostgreSQL

Below is the JDBC URL syntax for the official PostgreSQL JDBC driver:

jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]
For example, the following connection string connects to a database on the default host (localhost) and port (5432):
jdbc:postgresql:postgres
Below are typical connection properties:





Cache Driver

The database driver to be used to cache data.
Data Type

string

Default Value

""

Remarks

You can cache to any database that you have a JDBC driver for, including CData JDBC drivers.

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you would like to use to cache data.

Note that you must also add the CacheDriver JAR to the classpath.

The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.

Derby and Java DB

The driver simplifies Java DB configuration. Java DB is the Oracle distribution of Derby. The JAR is shipped in the JDK. You can find the JAR, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath.

jdbc:netsuite:CacheLocation='c:/Temp/cachedir';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
To customize the Java DB JDBC URL, use CacheProvider and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:
jdbc:netsuite:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
SQLite

Below is a JDBC URL for the SQLite JDBC driver:

jdbc:netsuite:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
MySQL

Below is a JDBC URL for the included CData JDBC Driver for MySQL:

jdbc:netsuite:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
The CData JDBC Driver for MySQL is located in the lib subfolder of the CData JDBC Driver for NetSuite 2016 installation directory.
SQL Server

The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:

jdbc:netsuite:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
Oracle

Below is a JDBC URL for the Oracle Thin Client:

jdbc:netsuite:Cache Driver=oracle.jdbc.driver.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;
PostgreSQL

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:netsuite:CacheDriver=org.postgresql.Driver;CacheConnection='jdbc:postgresql://localhost:5433/postgres?user=postgres&password=admin';Account Id=XABC123456;Password=password;User=user;Role Id=3;Version=2013_1;Location=C:\\myfolder\\;





Cache Location

Specifies the path to the cache when caching to a file.
Data Type

string

Default Value

""

Remarks

The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR to the classpath. The JAR, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

CacheLocation defaults to the directory specified by the Location setting.

See Also





Cache Metadata

Whether or not to cache the table metadata to a file store.
Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, the relevant system tables in the NetSuite catalog are cached to the file store specified by CacheLocation.

Setting CacheMetadata and CacheLocation will retrieve metadata only once over multiple connections. For example, metadata will be retrieved only when a connection pool is initialized.

When to Use CacheMetadata

The driver automatically caches metadata in memory when you open a connection, so within the same connection schema discovery is only expensive on the first query. QueryCache only becomes useful when retrieving the system catalog on every connection open becomes expensive. For example, when you are working with large amounts of metadata or when you have many simultaneous, short-lived connections.

When Not to Use CacheMetadata





Cache Query Result

When CacheQueryResult and AutoCache is set, the entire result of every SELECT query is cached in the cache database.
Data Type

bool

Default Value

false

Remarks

When CacheQueryResult and AutoCache is set, the entire result of every SELECT query is cached in the cache database. Note that the performance can be significantly impacted when using AutoCache when the NetSuite API does not support the REPLICATE Statements statement.





Cache Tolerance

The tolerance for stale data in the cache specified in seconds.
Data Type

string

Default Value

"600"

Remarks

The tolerance for stale data in the cache specified in seconds. The driver will check with the data source for newer records after the tolerance interval has expired. Otherwise it will return the data directly from the cache.





Connect On Open

Species whether to connect to the NetSuite when the connection is opened.
Data Type

bool

Default Value

false

Remarks

When set to 'true', a connection will be made to NetSuite when the connection is opened. This property enables the 'Test Connection' feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to NetSuite and nothing from this initial connection is maintained.

Setting this property to 'false' may provide performance improvements (depending upon the number of times a connection is opened).





Firewall Password

A password used to authenticate to a proxy-based firewall.
Data Type

string

Default Value

""

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.





Firewall Port

The TCP port for a proxy-based firewall.
Data Type

string

Default Value

""

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.





Firewall Server

The name or IP address of a proxy-based firewall.
Data Type

string

Default Value

""

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.





Firewall Type

The protocol used by a proxy-based firewall.
Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default the driver connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

Type Default Port Description
TUNNEL 80 When this is set, the driver opens a connection to NetSuite and traffic flows back and forth through the proxy.
SOCKS4 1080 When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5 1080 When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.





Firewall User

The user name to use to authenticate with a proxy-based firewall.
Data Type

string

Default Value

""

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication type specified in FirewallType.





Include Child Tables

A boolean indicating if child tables should be displayed.
Data Type

bool

Default Value

false

Remarks

If this is set to true, tables will be displayed for all child lists of a given entity. For instance, the CashRefund table in the NetSuite development environment has a child list called ItemList. Therefore, a new table called CashRefund_ItemList will be displayed if this property is set to true. This can be useful for listing each item in the list in its own row.





Include Custom Field Columns

A boolean indicating if you would like to include custom field columns.
Data Type

bool

Default Value

true

Remarks

Setting this to true will cause custom fields to be displayed directly on tables as their own rows. However, it will cause lower performance when retrieving the table metadata information for the first time on an open connection. Table metadata is stored on the connection and cleared when the connection is closed.





Include Custom List Tables

A boolean indicating if you would like to use tables based on custom lists.
Data Type

bool

Default Value

false

Remarks

Setting this to true will cause custom lists types to be included as their own tables. However, it will cause lower performance when retrieving the table metadata information for the first time on an open connection. Table metadata is stored on the connection and cleared when the connection is closed.





Include Custom Record Tables

A boolean indicating if you would like to use tables based on custom record types.
Data Type

bool

Default Value

true

Remarks

Setting this to true will cause custom record types to be included as their own tables. However, it will cause lower performance when retrieving the table metadata information for the first time on an open connection. Table metadata is stored on the connection and cleared when the connection is closed.





Include Reference Columns

A comma separated list representing the columns to include when retrieving data from a field representing a record reference.
Data Type

string

Default Value

"InternalId, Name"

Remarks

Many fields in NetSuite are references to other types of records. For instance, an Invoice might reference an Account and a Customer record. There are several pieces of data that can be returned by NetSuite when retrieving data from a record reference field. The available values are:

InternalIdThe NetSuite foreign key for the record reference.
ExternalIdAn optional foreign key stored in a data source other than NetSuite associated with the record. ExternalIds are used for synchronizing NetSuite data with other data sources.
NameA readable name for the record referenced.
TypeThe type of record referenced. This is not always given a value as the given field may only have one type.





Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.
Data Type

string

Default Value

""

Remarks

The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The Location property is only needed if you would like to customize definitions (e.g., change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

The schema files used in your application must be deployed with other assemblies. You must also ensure that Location points to the folder that contains the schema files. The folder location can be a relative path from the location of the executable.





Logfile

A path to the log file.
Data Type

string

Default Value

""

Remarks

For more control over what is written to the log file, take a look at Verbosity.





Logout Unknown Sessions

Boolean indicating if unknown sessions already established to the user account should be removed.
Data Type

bool

Default Value

false

Remarks

Most users in NetSuite will get one session they can use to connect to NetSuite Web Services at a time. If one attempts to establish a second session when an existing session is already established, NetSuite will throw an exception. While the CData JDBC Driver for NetSuite 2016 attempts to share sessions across threads for the same user, it is still possible to have an established session that prevents the driver from establishing a new one. For instance, the driver may have been forcibly terminated, resulting in a session that was never properly closed.

Setting this property to true will cause the driver to check for existing sessions when it attempts to create a new one. If an existing session is unrecognized by the driver, it will close it in order to establish its own session. It is recommended to only set this property to true if the driver is the only tool being used to connect to your user account via the web services.





Maximum Concurrent Sessions

The maximum number of concurrent sessions available for use by the username specified in the connection.
Data Type

string

Default Value

"0"

Remarks

Except under special circumstances, most NetSuite user accounts will be limited to one session at a time. This presents potential issues if executing requests across multiple threads as NetSuite will throw an exception if it detects that another session for the user is attempting to be created without closing the previous session. In most cases the existing session will be shared across multiple threads for the same user.

If the user has a SuiteCloud Plus license from NetSuite, then they do have access to concurrent Web services sessions. You can set the maximum number of concurrent sessions they have access to here. That number should be obtained from NetSuite.





Max Log File Size

A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.
Data Type

string

Default Value

""

Remarks

A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. There is no limit by default. Values lower than 100kB will use 100kB as the value instead.





Netsuite Metadata Folder

A path to a directory to download metadata files from NetSuite. Set this for best performance.
Data Type

string

Default Value

""

Remarks

In order to avoid needless overhead, the driver downloads a number of metadata files from the NetSuite API. If NetsuiteMetadataFolder is not specified then the Location will be used. Always set a value for NetsuiteMetadata for best performance.





OAuth Access Token

The access token for connecting using OAuth.
Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your username and password. The access token protects your credentials by keeping them on the server.





OAuth Access Token Secret

The OAuth access token secret for connecting using OAuth.
Data Type

string

Default Value

""

Remarks

The OAuthAccessTokenSecret property is used to connect and authenticate using OAuth. The OAuthAccessTokenSecret is retrieved from the OAuth server as part of the authentication process. It is used with the OAuthAccessToken and can be used for multiple requests until it times out.





OAuth Client Id

The client Id assigned when you register your application with an OAuth authorization server.
Data Type

string

Default Value

""

Remarks

OAuth requires you to register your application. As part of the registration, you will receive a client Id, sometimes also called a consumer key, and a client secret. You must specify both the OAuthClientId and OAuthClientSecret to connect to an OAuth server.





OAuth Client Secret

The client secret assigned when you register your application with an OAuth authorization server.
Data Type

string

Default Value

""

Remarks

OAuth requires you to register your application. As part of the registration you will receive a client Id and a client secret, sometimes also called a consumer secret. You must specify both the OAuthClientId and OAuthClientSecret to connect to an OAuth server.





Offline

Use offline mode to get the data from the cache instead of the live source.
Data Type

bool

Default Value

false

Remarks

When Offline is set to TRUE, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.





Other

The other parameters necessary to connect to a data source, such as username and password, when applicable.
Data Type

string

Default Value

""

Remarks

The Other property is a semicolon-separated list of name-value pairs used in connection parameters specific to a data source.

Caching Configuration

CachePartial=TrueCaches only a subset of columns, which you can specify in your query.
QueryPassthrough=TruePasses the specified query to the cache database instead of using the SQL parser of the driver.

Integration and Formatting

SupportAccessLinkedModeIn Access' linked mode, it is generally a good idea to always use a cache as most data sources do not support multiple Id queries. However if you want to use the driver in Access but not in linked mode, this property must be set to False to avoid using a cache of a SELECT * query for the given table.
ConvertDateTimeToGMTWhether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filenameRecords the underlying socket data transfer to the specified file.
ClientCultureThis property can be used to specify the format of data (e.g., currency values) that is accepted by the client application. This property can be used when the client application does not support the machine's culture settings. For example, Microsoft Access requires 'en-US'.
CultureThis setting can be used to specify culture settings that determine how the driver interprets certain data types that are passed into the driver. For example, setting Culture='de-DE' will output German formats even on an American machine.





Pagesize

The number of results to return per page from NetSuite.
Data Type

string

Default Value

""

Remarks

The pagesize can control the number of results requested from NetSuite on a given query. Setting a higher pagesize will cause more data to come back in a given request, but may take longer to execute. Setting a smaller pagesize is generally recommended to ensure timeout exceptions do not occur.





Password

The password of the NetSuite user used to authenticate.
Data Type

string

Default Value

""

Remarks

Together with User and AccountId, this field is used to authenticate to NetSuite.





Proxy Auth Scheme

The authentication type to use to authenticate to the ProxyServer proxy.
Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.





Proxy Auto Detect

This indicates whether to use the system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.
Data Type

bool

Default Value

true

Remarks

By default, the driver uses the system HTTP proxy. Set this to false if you want to connect to another proxy.

To connect to an HTTP proxy, see ProxyServer.

For other proxies, such as SOCKS or tunneling, see FirewallType.





Proxy Password

A password to be used to authenticate to the ProxyServer proxy.
Data Type

string

Default Value

""

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.





Proxy Port

The TCP port the ProxyServer proxy is running on.
Data Type

string

Default Value

"80"

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.





Proxy Server

The hostname or IP address of a proxy to route HTTP traffic through.
Data Type

string

Default Value

""

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.





Proxy SSL Type

The SSL type to use when connecting to the ProxyServer proxy.
Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

AUTODefault setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYSThe connection is always SSL enabled.
NEVERThe connection is not SSL enabled.
TUNNELThe connection is through a tunneling proxy: The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.





Proxy User

A user name to be used to authenticate to the ProxyServer proxy.
Data Type

string

Default Value

""

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the username of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a username in one of the following formats:

user@domain
domain\user





Pseudo Columns

Indicates whether or not to include pseudo columns as columns to the table.
Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; i.e., "*=*".





Query Cache

The time duration, in seconds, for which data is cached in memory and reused.
Data Type

string

Default Value

""

Remarks

The QueryCache property allows you to cache the results of a query in memory and use them until the cache expires. The cache expires after the seconds specified in this property. The in-memory query cache is shared across connections, so it can help with performance even if more than one connection is being used.

How QueryCache Works

The cache manager for QueryCache will not only use the cache for exactly the same query, but also use the cache for queries that represent a subset of data in the cached query. For example, in the following queries, the cache created while executing Query A will be used to obtain the results for both Query B and Query C.

SELECT * FROM Account; // Query A
SELECT * FROM Account WHERE Name LIKE '%John'; // Query B
SELECT InternalId, Name FROM Account LIMIT 10; // Query C

When to Use QueryCache

Setting the QueryCache property can improve performance in the following scenarios:

When Not to Use QueryCache





Readonly

You can use this property to enforce read-only access to NetSuite from the provider.
Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.





Request Memorized Transactions

A boolean indicating if you would like to request memorized transactions when retrieving transactions from NetSuite.
Data Type

bool

Default Value

false

Remarks

Memorized transactions in NetSuite are transactions that have been memorized for potentially being used again at a later time. They can be set up to recur on a regular basis or as a reminder to the user.





Role Id

The RoleId is the InternalId of the role that will be used to log in to NetSuite. Leave empty to use the user's default role.
Data Type

string

Default Value

""

Remarks

Together with User, Password, and AccountId this field is used to authenticate to NetSuite. If no RoleId is specified, the user's default role will be used. Otherwise, a RoleId may be obtained by logging into NetSuite, and navigating to the Setup -> Integration -> Web Services Preferences page. Here you can select a different Web Services Default Role as well as obtain the Id, displayed next to the specified role.





RTK

The runtime key used for licensing.
Data Type

string

Default Value

""

Remarks

The RTK property may be used to license a build. Please see the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.





SSL Server Cert

The certificate to be accepted from the server when connecting using TLS/SSL.
Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine will be rejected.

This property can take the forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine will be accepted. Use '*' to signify to accept all certificates (not recommended for security concerns).





Support Enhanced SQL

If set to true, the provider will cache the entire table in memory and issue the current query against the memory cache. Allows queries with aggregate functions that are otherwise unsupported. The entire memory cache is discarded after the query is executed, unlike QueryCache.
Data Type

bool

Default Value

true

Remarks

Use QueryCache to cache memory for a given time in seconds or to disable in-memory caching.

Use QueryCache to cache in memory for a given time in seconds or to disable in-memory caching.





Tables

Comma-separated list of tables to be listed. For example: Tables=TableA,TableB,TableC.
Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the driver.





Timeout

The value in seconds until the timeout error is thrown, canceling the operation.
Data Type

string

Default Value

"300"

Remarks

If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition.

If Timeout expires and the operation is not yet complete, the driver throws an exception.

If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition. If Timeout expires and the operation is not yet complete, the driver throws an exception.

In NetSuite, operations can take a very long time to return if retrieving data from child tables or retrieving data from from a given table with AggregateColumnMode set to ListAndRetrieve. For instance, it is not unheard of for it to take in excess of 10 minutes to retrieve 1000 SalesOrders in a single request from NetSuite when AggregateColumnMode is set to ListAndRetrieve. Unfortunately, this is a limitation of the NetSuite Web Services with no known solution. If you need to work with aggregate columns or child tables, set Timeout to 0, set a small Pagesize, and select specific columns instead of everything.





Use Async Services

A boolean indicating if you would like to use asynchronous services when inserting, updating, and deleting.
Data Type

bool

Default Value

false

Remarks

NetSuite responses can be fairly slow, especially when inserting, updating, or deleting many records at a time. In these situations it is best to use asynchronous services to submit the data. Asynchronous services allow NetSuite to process the data while your application continues executing. The downside of using asynchronous services is that you will need to check the Job in NetSuite to see if NetSuite has finished processing the request and see if there were any issues.





User

The user of the NetSuite account used to authenticate.
Data Type

string

Default Value

""

Remarks

Together with AccountId and Password, this field is used to authenticate to NetSuite.





User Timezone Offset

Your user timezone offset as defined in your NetSuite preferences under Home --> Preferences --> Time Zone. Ex: -8:00.
Data Type

string

Default Value

""

Remarks

NetSuite returns dates offset based on your user timezone preferences. This applies only to Dates, and not to Datetimes, which always return the same way no matter your preferences. We attempt a best effort to automatically correct for this and return dates as they would appear in the NetSuite UI. However, this is not 100% perfect due to how a few time zones are offset by 24 hours or greater depending on daylight savings time. If your user preferences are set to +13:00, +12:00, or -12:00, this value must be specified to get dates to show up correctly. For other offsets it is not required but recommended for best accuracy.





Use Sandbox

A boolean indicating if you would like to use the sandbox instance of your NetSuite account.
Data Type

bool

Default Value

false

Remarks

Set UseSandbox to true to use your sandbox instead of your live data.





Use Sessions

Establish sessions with NetSuite, or instead submit user credentials on each request.
Data Type

bool

Default Value

true

Remarks

If UseSessions is set to true, then user credentials will not be submitted on each request to NetSuite. Instead, a single Login will be attempted to establish the session, and from that point on, the session will be used on each subsequent request. This is a more secure method of communication.

In general, most NetSuite users can only establish a single session with a company file. The CData JDBC Driver for NetSuite 2016 is aware of this limitation and attempts to share the session for a given user across connections if multiple threads are active. However, if the same user is being used to connect from multiple machines or multiple different products, then there are likely to be connection problems. If your use case involves using the same user in these situations, it is better to set UseSessions to false and instead submit credentials on each request to avoid leaving sessions open beyond a single request at a time.





Use Upserts

A boolean indicating if you would like to perform an upsert when an insert operation is used.
Data Type

bool

Default Value

false

Remarks

Upserts can be used to potentially update an existing record when inserting. NetSuite handles this by using the ExternalId on a given record. If you perform an insert when the ExternalId you specify exists in NetSuite, it will instead update the corresponding record. Otherwise an insert will take place. Set this value to false to always insert new records regardless.





Verbosity

The verbosity level that determines the amount of detail included in the log file.
Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described below:

1Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.
3Setting Verbosity to 3 will additionally log the body of the HTTP requests.
4Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.





Version

The version of the NetSuite API in usage. Defaults to 2013_1.
Data Type

string

Default Value

"2016_1"

Remarks

In most cases the version should not need to be changed. However, if you modify the version, please make sure beforehand that the version specified exists as part of the NetSuite API. If an incorrect version is specified, the driver will be unable to retrieve or update tables in NetSuite.





Views

Comma-separated list of Views to be listed. For example: Views=ViewsA,ViewsB,ViewsC.
Data Type

string

Default Value

""

Remarks

Listing the Views from some databases can be expensive. Providing a list of Views in the connection string improves the performance of the driver.





Web Service Host

An optional override for the web service host such as webservices.na1.netsuite.com.
Data Type

string

Default Value

""

Remarks

This is an override for the web services host if for some reason the host cannot be dynamically resolved. It intended as a last resort and should not normally need to be specified.

In most cases the web service host will be dynamically determined by using the NetSuite web service request that retrieves the correct web service host for your Account as well as the UseSandbox connection property. This will result in one extra request each time you create and establish a new connection. If you would like to avoid this extra request, you can instead set the WebServiceHost to override it. Note that specifying WebServiceHost will cause UseSandbox to be ignored.

The currently accepted web service hosts for NetSuite are the following: webservices.netsuite.com webservices.na1.netsuite.com webservices.sandbox.netsuite.com





ViewsBack To Top

  1. ConsolidatedExchangeRate
  2. CustomFields
  3. CustomRecords
  4. EnumerationValues
  5. MyRoles
  6. PostingTransactionSummary
  7. SavedSearches
  8. SavedSearchRawXML
  9. Transactions



ConsolidatedExchangeRate

Gets the consolidated exchange rate, by default for each month of the actual year . You can also filter the result by PeriodName (ex. WHERE PeriodName = 'Q2 2008') or by Period_InternalId (ex. WHERE Period_InternalId = '166') .

Table Specific Information
Select

Consolidated exchange rates for every period:

SELECT * FROM ConsolidatedExchangeRate

Consolidated exchange rates for months appearing during and after the year 2010:

SELECT * FROM ConsolidatedExchangeRate WHERE StartYear = '2010'

Consolidated exchange rate for each month of a specific period, filtering by the period name:

SELECT * FROM ConsolidatedExchangeRate WHERE PeriodName = 'Q2 2008'

Consolidated exchange rate for each month of a specific period, filtering by parent internal_id:

SELECT * FROM ConsolidatedExchangeRate WHERE Parent_InternalId = '138'

Consolidated exchange rate for a specific month, filtering by period name:

SELECT * FROM ConsolidatedExchangeRate WHERE PeriodName = 'Mar 2007'

Consolidated exchange rate for a specific month, filtering by period internal_id:

SELECT * FROM ConsolidatedExchangeRate WHERE Period_InternalId = '166'
Columns

Name Type Description
Period_InternalId String
FromSubsidiary_InternalId String
ToSubsidiary_InternalId String
CurrentRate Double Current actual exchange rate
AverageRate Double Average actual exchange rate
HistoricalRate Double Historical actual exchange rate

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Parent_InternalId String
PeriodName String





CustomFields

Returns information about the custom fields in NetSuite. CustomFields may be retrieved by CustomFieldType, or by a combination of CustomFieldType and InternalId. If no CustomFieldType is specified, all custom fields will be returned.

Columns

Name Type Description
InternalId String The InternalId of the custom field.
ScriptId String The ScriptId of the custom field.
Label String The name of the custom field.
CustomFieldType String The type of custom field being requested.
AccessLevel String The level of access granted to interact with this custom field.
AvailableExternally Boolean A boolean indicating if this custom field is available externally.
CheckSpelling Boolean A boolean indicating if spell check is enabled for this custom field.
DefaultChecked Boolean A boolean indicating if this field is checked by default.
DefaultSelection_InternalId String The internalid of the default record for this custom field.
DefaultSelection_Name String The name of the default record for this custom field.
DefaultValue String The default value for this custom field.
Description String A description for the custom field.
DynamicDefault String The dynamic default value determined when an entity is created.
DisplayType String The way the column is displayed to a user on NetSuite.
FieldType String A boolean describing what type of data is stored in the custom field.
Help String Help information that comes up when you mouse over the custom field in NetSuite.
IsFormula Boolean A boolean indicating if the custom field is a formula.
IsMandatory Boolean A boolean indicating if the custom field is mandatory.
IsParent Boolean A boolean indicating if the custom field is a parent.
ItemSubType String What type of items this custom field is available for (purchase, sale, or both).
MaxLength Long The maximum length for the custom field if it is an integer or long.
MaxValue Double The maximum value for the custom field if it is a double.
MinValue Double The minimum value for the custom field if it is a double.
Owner_Name String The name of the owner of the custom field.
Owner_InternalId String The internalid of the owner of the custom field.
SelectRecordType_InternalId String The internalId of the type of record that may be selected for this custom field.
SelectRecordType_Name String The name of the type of record that may be selected for this custom field.
ShowInList Boolean A boolean indicating if this field should be shown in a listing.
ShowIssuesChanges Boolean A boolean indicating if NetSuite shows changes to this field on the Issue record.
StoreValue Boolean A boolean indicating if this field stores a value.
Subtab_Name String The name of the subtab this custom field may be found on.
Subtab_InternalId String The internal id of the subtab this custom field may be found on.
RecType_Name String The name of the record type this custom field is applicable for. Only valid for OtherCustomFields.
RecType_InternalId String The internalid of the record type this custom field is applicable for. Only valid for OtherCustomFields.
AppliesToAllItems Boolean A boolean indicating if this custom field applies to all items.
AppliesToCase Boolean A boolean indicating if this custom field applies to the support case record.
AppliesToCampaign Boolean A boolean indicating if this custom field applies to the campaign record.
AppliesToContact Boolean A boolean indicating if this custom field applies to the contact record.
AppliesToCustomer Boolean A boolean indicating if this custom field applies to the customer record.
AppliesToEmployee Boolean A boolean indicating if this custom field applies to the employee record.
AppliesToEvent Boolean A boolean indicating if this custom field applies to the event record.
AppliesToGiftCerts Boolean A boolean indicating if this custom field applies to gift certificates.
AppliesToGroup Boolean A boolean indicating if this custom field applies to groups.
AppliesToInventory Boolean A boolean indicating if this custom field applies to the inventory items.
AppliesToIssue Boolean A boolean indicating if this custom field applies to the issue record.
AppliesToItemAssembly Boolean A boolean indicating if this custom field applies to the item assemblies.
AppliesPerKeyword Boolean A boolean indicating if this custom field applies per keyword.
AppliesToKit Boolean A boolean indicating if this custom field applies to kit items.
AppliesToLots Boolean A boolean indicating if this custom field applies to all items lots.
AppliesToMfgProjectTask Boolean A boolean indicating if this custom field applies to manufacturing operation tasks.
AppliesToNonInventory Boolean A boolean indicating if this custom field applies to non inventory items.
AppliesToProject Boolean A boolean indicating if this custom field applies to the job record.
AppliesToOtherCharge Boolean A boolean indicating if this custom field applies to other charges.
AppliesToOtherName Boolean A boolean indicating if this custom field applies to other names records.
AppliesToPartner Boolean A boolean indicating if this custom field applies to the partner record.
AppliesToPhoneCall Boolean A boolean indicating if this custom field applies to the phone call record.
AppliesToProjectTask Boolean A boolean indicating if this custom field applies to the project task record.
AppliesToPriceList Boolean A boolean indicating if this custom field applies to the price list.
AppliesToSerialized Boolean A boolean indicating if this custom field applies to all serialized items
AppliesToService Boolean A boolean indicating if this custom field applies to service items.
AppliesToSolution Boolean A boolean indicating if this custom field applies to the solution record.
AppliesToStatement Boolean A boolean indicating if this custom field applies to the statement record.
AppliesToTask Boolean A boolean indicating if this custom field applies to the task record.
AppliesToWebSite Boolean A boolean indicating if this custom field applies to the website record.
AppliesToVendor Boolean A boolean indicating if this custom field applies to the vendor record.
BodyAssemblyBuild Boolean A boolean indicating if this custom field appears on the body of assembly build transactions.
BodyBom Boolean A boolean indicating if this custom field should be printed on on the body of a BOM.
BodyCustomerPayment Boolean A boolean indicating if this custom field appears on the body of customer payment transactions.
BodyDeposit Boolean A boolean indicating if this custom field appears on the body of deposit transactions.
BodyExpenseReport Boolean A boolean indicating if this custom field appears on the body of expense report transactions.
BodyInventoryAdjustment Boolean A boolean indicating if this custom field appears on the body of inventory adjustment transactions.
BodyItemFulfillment Boolean A boolean indicating if this custom field appears on the body of item fulfillment transactions.
BodyItemFulfillmentOrder Boolean A boolean indicating if this custom field appears on the body of item fulfillment order transactions.
BodyItemReceipt Boolean A boolean indicating if this custom field appears on the body of item receipt transactions.
BodyItemReceiptOrder Boolean A boolean indicating if this custom field appears on the body of item receipt order transactions.
BodyJournal Boolean A boolean indicating if this custom field appears on the body of journal transactions.
BodyOpportunity Boolean A boolean indicating if this custom field appears on the body of opportunity transactions.
BodyPickingTicket Boolean A boolean indicating if this custom field should be printed on the body of picking tickets.
BodyPrintFlag Boolean A boolean indicating if this custom field should be printed on the body of standard forms.
BodyPrintPackingSlip Boolean A boolean indicating if this custom field should be printed on the body of packing slips.
BodyPrintStatement Boolean A boolean indicating if this custom field should be printed on the body of statements.
BodyPurchase Boolean A boolean indicating if this custom field appears on the body of purchase transactions.
BodySale Boolean A boolean indicating if this custom field appears on the body of sale transactions.
BodyStore Boolean A boolean indicating if this custom field appears on the body of store transactions.
BodyTransferOrder Boolean A boolean indicating if this custom field appears on the body of transfer order transactions.
BodyVendorPayment Boolean A boolean indicating if this custom field appears on the body of vendor payment transactions.
ColAllItems Boolean A boolean indicating if this custom field appears in the item list for all items.
ColBuild Boolean A boolean indicating if this custom field appears in the item list for work order transactions.
ColExpense Boolean A boolean indicating if this custom field appears in the item list for expense transactions.
ColExpenseReport Boolean A boolean indicating if this custom field appears in the item list for expense report transactions.
ColGroupOnInvoices Boolean A boolean indicating if this custom field is grouped on invoices.
ColItemFulfillment Boolean A boolean indicating if this custom field appears in the item list for item fulfillment transactions.
ColItemFulfillmentOrder Boolean A boolean indicating if this custom field appears in the item list for item fulfillment order transactions.
ColItemReceipt Boolean A boolean indicating if this custom field appears in the item list for item receipt transactions.
ColItemReceiptOrder Boolean A boolean indicating if this custom field appears in the item list for item receipt order transactions.
ColJournal Boolean A boolean indicating if this custom field appears in the item list for journal transactions.
ColKitItem Boolean A boolean indicating if this custom field appears in the item list for kit / assembly components.
ColOpportunity Boolean A boolean indicating if this custom field appears in the item list for opportunity transactions.
ColPackingSlip Boolean A boolean indicating if this custom field should be printed on the item list for packing slips.
ColPickingTicket Boolean A boolean indicating if this custom field should be printed on the item list for picking tickets.
ColPrintFlag Boolean A boolean indicating if this custom field should be printed on the item list for standard forms.
ColPurchase Boolean A boolean indicating if this custom field appears in the item list for purchase transactions.
ColReturnForm Boolean A boolean indicating if this custom field should be printed on the item list for return forms.
ColSale Boolean A boolean indicating if this custom field appears in the item list for sale transactions.
ColStore Boolean A boolean indicating if this custom field applies to your web store items .
ColStoreHidden Boolean A boolean indicating if this custom field should be hidden on your web store transactions.
ColStoreWithGroups Boolean A boolean indicating if this custom field should apply to item groups.
ColTime Boolean A boolean indicating if this custom field appears in the item list for time transactions.
ColTimeGroup Boolean A boolean indicating if this custom field appears apply to time groups.
ColTransferOrder Boolean A boolean indicating if this custom field appears in the item list for transfer order transactions.
ItemsListAggregate String An aggregate of individual items this custom field applies to.
DeptAccessListAggregate String An aggregate of the departments that have access to the custom field.
RoleAccessListAggregate String An aggregate of roles that have access this custom field.
SubAccessListAggregate String An aggregate of the subsidiaries that have access to this custom field.
TranslationsListAggregate String An aggregage of translations for the custom field label and help.





CustomRecords

Returns information about the custom records in NetSuite.

Columns

Name Type Description
InternalId String The InternalId of the custom record.
ScriptId String The ScriptId of the custom record.
Name String The name of the custom record.
AccessType String The access type for the custom record.
AllowAttachments Boolean A boolean indicating if the custom record allows attachments.
AllowNumberingOverride Boolean A boolean for the allow override property fo the custom record.
AllowQuickSearch Boolean A boolean indiating if quick searches are enabled for the custom record.
Description String A description of the custom record.
Disclaimer String A disclaimer for the custom record.
EnableInlineEditing Boolean A boolean indicating if inline editing is enabled.
EnableMailMerge Boolean A boolean indicating if mail merge is enabled.
EnableNameTranslation Boolean A boolean indicating if name translations are enabled.
EnableNumbering Boolean A boolean indicating if numbering is enabled.
IncludeName Boolean A boolean idicating if the name field is included.
IsAvailableOffline Boolean A boolean indicating if the custom record is available offline.
IsInactive Boolean A boolean indicating if the record is inactive.
IsNumberingUpdateable Boolean A boolean indicating if the numbering number is updatable.
IsOrdered Boolean A boolean indicating if records are ordered.
NumberingCurrentNumber Long The current numbering number.
NumberingInit Long The initial numbering number.
NumberingMinDigits Long A minimum number of digits for the numbering number.
NumberingPrefix String A prefix for the numbering number.
NumberingSuffix String A suffix added to the numbering number.
Owner_internalid String The internalid of the owner of the custom record.
Owner_name String The name of the owner of the custom record.
ShowCreationDate Boolean A boolean indicating if the custom record should show the creation date.
ShowCreationDateOnList Boolean A boolean indicating if the custom record should show the creation date when listed.
ShowId Boolean A boolean indicating if the custom record should show the id.
ShowLastModified Boolean A boolean indicating if the custom record should show the last modified date.
ShowLastModifiedOnList Boolean A boolean indicating if the custom record should show the creation date when listed.
ShowNotes Boolean A boolean indicating if the custom record should show notes.
ShowOwner Boolean A boolean indicating if the custom record should show the owner.
ShowOwnerAllowChange Boolean A boolean indicating if the custom record should show if the owner allows changes.
ChildrenListAggregate String An aggregate of the children collection for the custom record.
FormsListAggregate String An aggregate of the forms collection for the custom record.
LinksListAggregate String An aggregate of the links collection for the custom record.
ManagersListAggregate String An aggregate of the managers collection for the custom record.
OnlineFormsListAggregate String An aggregate of the online forms collection for the custom record.
ParentsListAggregate String An aggregate of the parents collection for the custom record.
PermissionsListAggregate String An aggregate of the permissions collection for the custom record.
SublistsListAggregate String An aggregate of the sublists collection for the custom record.
TabsListAggregate String An aggregate of the tabs collection for the custom record.
TranslationsListAggregate String An aggregate of the translations collection for the custom record.





EnumerationValues

Retrieves enumeration values for a given table. Will only return values if the TableName is specified. Only available for native tables within NetSuite and not Custom tables.

Columns

Name Type Description
ColumnName String The InternalId of the role.
TableName String The name of the table to retrieve enumeration value information from.
Values String The available values for the enumeration column.





MyRoles

Returns the roles available for the authenticated user. This may include information for additional accounts the user is authorized to access.

Columns

Name Type Description
AccountId String The account the role is associated with.
RoleId String The InternalId of the role.
AccountName String The name of the account.
RoleName String The name of the role.





PostingTransactionSummary

Retrieves a posting transaction summary from NetSuite. This is the reporting mechanism available from NetSuite. All record reference columns may be used in filter using either either the = or IN operator. Other operator types are not available. For NetSuite OneWorld customers, ConvertToSubsidiary may also be specified as the subsidiary all response values should be converted to.

Columns

Name Type Description
Period_InternalId String
Account_InternalId String
ParentItem_InternalId String
Item_InternalId String
Entity_InternalId String
Department_InternalId String
Class_InternalId String
Location_InternalId String
Subsidiary_InternalId String
Book_InternalId String
OriginalAmount Double The original amount as returned by NetSuite in the currency of the subsidiary returned.
OriginalCurrency String The name of the original currency. Will only have a value if ConvertToSubsidiary is specified.
ConvertedAmount Double The amount converted to the currency of the subsidiary specified by ConvertToSubsidiary.
ConvertedCurrency String The name of the currency that has been converted to. Will only have a value if ConvertToSubsidiary is specified.
ConvertToSubsidiary String The subsidiary to convert to.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
rows@next String This is a system property for controling paging.





SavedSearches

Returns the saved searches in NetSuite. A SearchType must be specified in order to retrieve saved searches.

Columns

Name Type Description
InternalId String The InternalId of the saved search.
Name String The name of the saved search.
SearchType String The type of search. This value must be specified when retrieving data.





SavedSearchRawXML

Returns the raw XML from a saved search.

Columns

Name Type Description
SavedSearchId String The InternalId of the saved search.
SearchType String The type of search being used.
ResponseXML String The ScriptId of the custom field.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
Rows@Next String This is a system input used for paging. Please do not manually set the value.





Transactions

A view for transactions on NetSuite.

Columns

Name Type Description
InternalId [KEY] String The internalid of the transaction.
Type String The transaction type.
Account_InternalId String Internal id of the account.
Account_Name String Name of the account.
Amount Double Amount on the transaction.
BillingAddress String The billing address.
BillingAccount_InternalId String The billing account internal id.
BillingAccount_Name String The billing account name.
CloseDate Datetime The date closed.
CreatedDate Datetime The date created.
CreatedFrom_InternalId String Created from id.
Currency_InternalId String The currency id.
Currency_Name String The currency name.
CustomForm_InternalId String The custom form id.
CustomForm_Name String The custom form name.
DueDate Datetime The due date.
Email String The email address.
EndDate Datetime The end date.
Entity_InternalId String The entity id.
Entity_Name String The entity name.
Entity_Type String The entity type.
ExchangeRate Double The exchange rate.
ExpectedCloseDate Datetime The expected close date.
ExternalId String The transaction external id.
Fob String The email address.
IntercoTransaction_InternalId String The intercompany transaction id.
Job_InternalId Job The job id.
Job_Name Job The job name.
LastModifiedDate Datetime The last modified date.
LeadSource_InternalId String The lead source id.
LeadSource_Name String The lead source name.
LeadSource_Type String The lead source type.
Location_InternalId String The location id.
Location_Name String The location name.
Memo String The memo.
Message String The message.
Partner_InternalId String The partner id.
Partner_Name String The partner name.
PnRefNum String The P/N Ref.
Probability Double The probability of a deal.
ProjectedAmount Double The projected amount.
PromoCode_InternalId String The promo code id.
PromoCode_Name String The promo code name.
SalesEffectiveDate Datetime The sales effective date.
SalesRep_InternalId String The sales rep id.
SalesRep_Name String The sales rep name.
Source String The source.
StartDate Datetime The start date.
Status String The status.
Terms_InternalId String The terms id.
Terms_Name String The terms name.
Title String The title.
TranDate Datetime The transaction date.
TranId String The document number/id.
TransactionNumber String The transaction number.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
SavedSearchId String Specifies the InternalId of the saved search. This is an alternative to supplying the criteria directly in the query. The search can instead be saved on NetSuite's end to perform more complicated operations than those available in the Web API.
rows@next String This is a system property that controls paging.