Netsuite Query Data Model

Connection String Options

  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. Connect On Open
  10. Firewall Password
  11. Firewall Port
  12. Firewall Server
  13. Firewall Type
  14. Firewall User
  15. Include Child Tables
  16. Include Custom Field Columns
  17. Include Custom List Tables
  18. Include Custom Record Tables
  19. Include Reference Columns
  20. Location
  21. Logfile
  22. Logout Unknown Sessions
  23. Maximum Concurrent Sessions
  24. Max Log File Size
  25. Netsuite Metadata Folder
  26. Offline
  27. Other
  28. Pagesize
  29. Password
  30. Proxy Auth Scheme
  31. Proxy Auto Detect
  32. Proxy Password
  33. Proxy Port
  34. Proxy Server
  35. Proxy SSL Type
  36. Proxy User
  37. Pseudo Columns
  38. Query Cache
  39. Readonly
  40. Request Memorized Transactions
  41. Role Id
  42. RTK
  43. SSL Server Cert
  44. Support Enhanced SQL
  45. Tables
  46. Timeout
  47. Use Async Services
  48. User
  49. User Timezone Offset
  50. Use Sandbox
  51. Use Sessions
  52. Use Upserts
  53. Verbosity
  54. Version
  55. Web Service Host

Account Id

Data Type

string

Default Value

""

Remarks

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

 

Aggregate Column Mode

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.

 

 

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

 

 

Application Id

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

Data Type

bool

Default Value

false

Remarks

When AutoCache is set, the driver automatically caches the results of SELECT queries to a cache specified by the CacheLocation option. CacheLocation defines the path to a simple, file-based cache.

AutoCache is the simplest caching configuration available, however, like any caching scheme, using a cache is not without pitfalls such as reporting on stale data. The driver is designed to be fully functional without relying on caching.

The following sections outline how and when to use AutoCache. Understanding how AutoCache works and its limitations will help you choose an effective caching strategy. For more information on deploying other caching strategies, see Caching Data.

How AutoCaching Works

 

When you execute a SELECT statement with AutoCache set, the driver executes the query to the remote data and persists the results; rows and columns that already exist are overwritten. That is, SELECT statements are used to create and refresh the cache, not to query it.

Non-queries (such as UPDATE/INSERT/DELETE statements) are executed to the remote data as well; these statements will not modify the data in the cache at all, regardless of the value set for AutoCache.

To work with the local data, append #CACHE to the table name. For example:

SELECT * FROM [Account#CACHE]
Limitations of AutoCache

 

In the following scenarios, consider the alternatives listed below:

  • When you need to work with the cache transparently: Because AutoCache requires a special syntax to utilize the cache, it is not suitable for use with BI, analytics, and reporting. Many of these tools generate SQL statements for you; these generated statements will still be executed to NetSuite instead of the cache.

    In these situations, one solution is to use the Offline property. When this is set, all queries are executed locally. See Caching: Best Practices for examples.

    One downside of this approach is that it requires a separate connection. As an alternative, consider using the CData Sync tool to maintain a local database that is kept fresh with scheduled updates.

     

  • When you need more control over cached data: The AutoCache feature does not have the ability to remove rows from the cache that were deleted from the remote data. It also does not support dropping a table from the cache or more advanced cache maintenance such as changing the cached table schemas.

    In this scenario, consider REPLICATE Statements and CACHE Statements. REPLICATE statements leverage incremental update support in the NetSuite API. With incremental updates, only the changeset needs to be retrieved from NetSuite, so the driver is able to merge any changes into the cache much more quickly. For more granular control, use CACHE statements.

    CACHE statements can remove cached rows that no longer exist in NetSuite.

    See Caching Explicitly for more information on how to use CACHE statements.

  • When you need to work with an RDBMS: AutoCache can only be used with the default database, JavaDB or SQLite. Many enterprises will need to use an RDBMS to support more concurrent writes or integrate with existing infrastructure. You can specify a database driver with CacheConnection and CacheDriver.

 

 

Cache Connection

Data Type

string

Default Value

""

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheConnection defines the connection properties necessary to connect to the cache database.

Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost:7437;user=sa;password=123456;databaseName=Cache'

 

 

 

Cache Driver

Data Type

string

Default Value

""

Remarks

You can cache to any database that you have a JDBC driver for. The driver has been tested with SQL Server, Derby and Java DB, MySQL, Oracle, and SQLite.

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

Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost:7437;user=sa;password=123456;databaseName=Cache'
Note that the driver JAR must be specified on the classpath.

 

 

Cache Location

Data Type

string

Default Value

""

Remarks

If AutoCache is set but the cache location is not specified, CacheLocation defaults to the cache folder on the directory specified by the Location setting.

The CacheLocation is a simple, file-based cache. See the CacheConnection and CacheDriver properties to cache to other databases.

 

Cache Metadata

Data Type

bool

Default Value

false

Remarks

The cache.db file will be created in the location specified by the CacheConnection or if that is not set, the CacheLocation.

 

Connect On Open

Data Type

bool

Default Value

true

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

Data Type

string

Default Value

""

Remarks

If FirewallServer is specified, the FirewallUser and FirewallPassword properties are used to connect and authenticate to the given firewall.

 

Firewall Port

Data Type

string

Default Value

""

Remarks

Note that the driver sets the FirewallPort to the default port associated with the specified FirewallType. See the description of the FirewallType option for details.

 

Firewall Server

Data Type

string

Default Value

""

Remarks

If this property is set to a domain name, a DNS request is initiated and the name is translated to the corresponding IP address.

 

Firewall Type

Data Type

string

Default Value

"NONE"

Remarks

The applicable values are:

 

   
Firewall Type Default FirewallPort
TUNNEL 80
SOCKS4 1080
SOCKS5 1080

 

 

Firewall User

Data Type

string

Default Value

""

Remarks

If the FirewallServer is specified, the FirewallUser and FirewallPassword properties are used to connect and authenticate against the firewall.

 

Include Child Tables

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

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

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

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

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:

 

 

   
InternalId The NetSuite foreign key for the record reference.
ExternalId An 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.
Name A readable name for the record referenced.
Type The type of record referenced. This is not always given a value as the given field may only have one type.

 

 

Location

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

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

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

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

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

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.

 

Offline

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

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=True Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True Passes the specified query to the cache database instead of using the SQL parser of the driver.

 

Integration and Formatting

 

   
SupportAccessLinkedMode In 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.
ConvertDateTimesToGMT Whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.
ClientCulture This 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'.
Culture This 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

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

Data Type

string

Default Value

""

Remarks

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

 

 

Proxy Auth Scheme

Data Type

string

Default Value

"BASIC"

Remarks

This value may be BASIC, DIGEST, NONE, NTLM, NEGOTIATE or PROPRIETARY.

 

Proxy Auto Detect

Data Type

bool

Default Value

true

Remarks

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

 

Proxy Password

Data Type

string

Default Value

""

Remarks

If the ProxyServer is specified, the ProxyUser and ProxyPassword properties are used to connect and authenticate against the firewall.

 

Proxy Port

Data Type

string

Default Value

"80"

Remarks

See the description of the ProxyServer field for details.

 

Proxy Server

Data Type

string

Default Value

""

Remarks

If this property is set to a domain name, a DNS request is initiated and the name is translated to the corresponding address.

 

Proxy SSL Type

Data Type

string

Default Value

"AUTO"

Remarks

This value may be AUTO, ALWAYS, NEVER, or TUNNEL.

 

Proxy User

Data Type

string

Default Value

""

Remarks

If a ProxyServer is specified, the ProxyUser and ProxyPassword options are used to connect and authenticate against the firewall.

 

Pseudo Columns

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

Data Type

string

Default Value

""

Remarks

The QueryCache allows you to cache the results of a query in-memory and use them until the cache expires. Setting the QueryCache can improve performance if the same or similar (see below) queries are executed often. The in-memory query cache is shared across connections, so it can help with performance even if more than one connection is being used.

The cache manager for QueryCache will not only use the results in the cache for exactly the same query, but also 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 from Account LIMIT 10; // Query C

 

Setting the QueryCache to zero disables in-memory caching.

 

Readonly

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

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

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

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

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 can take the form of a full PEM certificate, the path to a file containing the certificate, the public key, the MD5 thumbprint, or the SHA1 thumbprint. If not specified, any trusted certificate will be accepted. Use '*' to signify to accept all certificates (not recommended for security concerns).

 

Support Enhanced SQL

Data Type

bool

Default Value

true

Remarks

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

 

Tables

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

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

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

Data Type

string

Default Value

""

Remarks

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

 

 

User Timezone Offset

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

Data Type

bool

Default Value

false

Remarks

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

 

Use Sessions

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

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

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:

 

   
1 Setting 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.
2 Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and HTTP headers.
3 Setting Verbosity to 3 will additionally log the body of the HTTP requests.
4 Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5 Setting 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

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.

 

Web Service Host

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




 

Views

  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.