Was this helpful?
NetSuite
NetSuite is a web-based Customer Relationship Management (CRM) software platform that enables access to information using a Web Services interface. The NetSuite connector supports most entities exposed by the NetSuite Web Service, an open XML-based online platform that provides Simple Object Access Protocol (SOAP) access. This version of NetSuite connector supports WSDL 2018_1_0.
The NetSuite connector supports the following authentication methods to connect to NetSuite:
User ID/password-based authentication - Specify the following to connect to NetSuite:
Account ID
Email
Password
Application ID
Token-based authentication - Specify the following to connect to NetSuite:
Account
TokenId
TokenSecret
ConsumerKey
ConsumerSecret
For record entities, all CRUD operations (including Upsert) are supported. For Regular List names, only read operation is supported.
The Account information that is specified connects you to the required NetSuite instance (sandbox or production instance).
Prerequisites
Before using NetSuite connection, the following must be met:
NetSuite connector must be installed.
Require log in credentials for User ID and Password-based authentication or Token-based authentication. Also, make sure that users have permission to access entities.
NetSuite Connector Properties
You can set the following source (S) and target (T) properties.
Property
S/T
Description
BatchResponse
T
Path of the batch response file, which provides detailed results for each object in a batch. These results are useful for the following:
Capturing system-generated object IDs for use in future updates.
Associating errors with objects for exception handling and error diagnosis.
A batch response entry is generated for each top-level record. For example, when a Customer record is inserted with an Addressbooklist, only one entry appears in the batch response. For more information about Batch Response File, see Data File Formats.
CustomizationLevel
ST
Specify the allowed level of NetSuite customizations:
Full (default) - Use this option if using custom record types. Also, provides access to custom fields defined for the standard record types.
CustomFieldsOnly - Use this option if using custom fields defined for the standard record types. Custom fields are requested based on your requirements. For example, the connector does not retrieve custom fields for sales orders if retrieving only customer records.
None - Use this option when custom record types and custom fields are not defined for the standard record types. It improves performance since this option does not require generating A PI calls at startup.
Note:  This property is applicable only when the DataType property is set to Record.
DataType
S
Specify how the connector obtains allowable values for the Table list:
Record (default) – Queries the normal record types such as Customer, Contact, SalesOrder, Your CustomRecordType.
Dynamic – Queries using NetSuite saved searches.
CustomList – Queries the NetSuite custom, user-defined lists of ID and value pairs.
DisableMandatoryCustomFieldValidation
T
Enable or disable validation for the required custom fields:
Default (default) – Connector uses default settings in NetSuite.
True – Data for a custom field (required for NetSuite UI submissions) is not required when submitting a web services request.
False - Connector returns an error when data for a required custom field is not provided.
DisableSystemNotesCustomFields
T
Enable or disable system-generated notes when importing data from custom fields:
Default - Connector uses the default setting in NetSuite
True - Enable system-generated notes when importing data from custom fields
False - Disable system-generated notes when importing data from custom fields
Note:  When importing large records, you may consider disabling system notes to improve performance. For more information, see the disableSystemNotesForCustomFields property in the NetSuite documentation.
FlushFrequency
T
Number of record operations DataConnect engine caches before passing to the connector. Default is zero.
IgnoreReadOnlyFields
T
Specify whether read-only fields are ignored:
Default - Connector uses the default setting in NetSuite
True - Connector ignores read-only fields.
False - Connector does not ignore read-only fields and enables error messages as required.
NestedChildren
ST
Set the connector to retrieve records (parents) and their associated lists (children):
True – Connector retrieves records (parents) and their associated lists (children).
False (default) - Connector retrieves records (parents) only. This can significantly improve performance.
Note:  This property is applicable only when the DataType property is set to Record.
PageSize
S
Indicates the number of records returned for a page in each web service. It is only applicable when the DataType is selected as Record.
Ideally, if DataType is selected as Dynamic or CustomList, the PageSize property should not be displayed. But, this is currently displayed for the connector.
The value must be in the range of system defined minimum and maximum value. In a typical configuration, default minimum value is 5 and maximum is 1000).
The default value is 50.
Note:  This property is intended for advanced users who have to fine-tune performance. For more information, see the NetSuite documentation.
Role
ST
Specify the NetSuite role ID. A role specifies the permissions required to access different parts of NetSuite. If role ID is not provided, the connector uses the user's default role. For more information, see the NetSuite documentation.
SavedSearchID
ST
The ID of a NetSuite saved search. You can obtain the ID from the URL of the saved search configuration on NetSuite portal. For Example:
https://tstdrv1330641.app.netsuite.com/app/common/search/searchresults.nl?searchid=681&whence=
The SavedSearchID and the Table selection determine the selected saved search. The DataType property must be set as Dynamic.
TreatWarningAsError
T
Controls whether warning messages generated by NetSuite are treated same as errors, which causes a request to be rejected. The options are:
Default - Connector uses the default setting in NetSuite.
True - Warning messages generated by NetSuite are treated same as errors.
False - Warning messages generated by NetSuite are not treated same as errors.
UpdateNulls
T
The NetSuite target may contain empty or null value. The UpdateNulls property controls whether these values are sent to NetSuite:
True - Connector sends null values and empty strings to NetSuite server.
False (the default) - Connector does not send null values and empty strings to NetSuite server.
Note:  The setting for this property affects only parent-level record types since child record types ignores the null values and empty strings.
UseUILabel
ST
When creating a custom field, you must specify a name (label). Also, the system automatically generates an ID. The UseUILabel option allows to display either label or custom ID for a custom field. The available options are:
False (default) - Displays custom IDs for custom fields.
True - Displays labels for custom fields.
Data Types
The following are the data types for the fields in your target database table. If you are appending data to an existing table, then the data type of each field uses the data type in the selected table by default.
Boolean
Date
Double
Integer
Long
String
Limitations
The following are the limitations of the NetSuite connector:
Concurrent Connections - NetSuite allows finite number of concurrent connections for an account only if that account has the concurrency feature enabled. If the account does not have this feature, attempts to use the connector concurrently randomly fails. The concurrency feature is controlled by NetSuite and not the connector.
Invalid Query Error Messaging - The NetSuite connector does not support detailed error messaging for invalid Map Editor queries.
ExternalId Ignored - NetSuite does not support searching transaction type entities such as Sales Order, by external ID.
Additional Information
This section includes additional information for NetSuite connector.
Using Map Editor
To use Map Editor with NetSuite, you must enable the API for your organization within NetSuite.
Entity Names
Entity names change with each version of the API. Custom entities are also supported.
Field Width
The NetSuite connector sets field width in bytes. The number of characters that fit into a given field varies. For more details, see Database Connections.
Custom Field and Record Type Support
The NetSuite connector supports custom fields and record types. There is no distinction in the way custom and non-custom fields and record types appear in the connector. For example, if you define a custom record type called MyCustomRecordType, the NetSuite connector adds MyCustomRecordType to its record type list. You can work with the custom record type similar to the Customer type.
You can insert, update, and delete records of custom record types. You can also retrieve records representing instances of custom record types. However, you cannot add or change existing custom fields or add new custom record types.
The NetSuite connector supports the following custom fields.
Custom FieldType
NetSuite Display Name
Boolean
Check box
Date
Date
Time of Day
Double
Decimal Number
Currency
Percent
Long
Integer Number
MultiSelect
Multiple Select
Select
List/Record
Image
String
Free-Form Text
Text Area
Phone Number
E-mail Address
HyperLink
Rich Text
Help
Long Text
Password
Each NetSuite field has a unique identifier. All field types, except MultiSelect and Select, have field names that match the unique identifiers.
Select field - Represents an item in a list. Each item is represented by a Script ID. The connector creates a field name consisting of the "_ScriptId" identifier appended to it. For example, CUSTFIELD10_ScriptId. You must specify a Script ID for each Select field.
Note:  In previous NetSuite connectors, Internal ID was used instead of Script ID for custom fields of any entity.
MultiSelect field - Allows you to select multiple fields. For any MultiSelect field, the connector creates a field name consisting of its identifier with "_Values" appended. You must specify a comma-delimited list of script IDs for MultiSelect fields.
Parent and Child Records
NetSuite allows you to perform actions on child records only within the context of the parent records. The NetSuite connector uses sequential ordering of records to merge children with their respective parents. For example, you submit the following for inserts (in the exact order):
1. Customer 1
2. CustomerAddressbookList
3. Customer Address 1
4. Customer Address 2
5. Customer 2
6. CustomerAddressbookList
7. Customer Address 3
The connector submits two customers to NetSuite. Customer Address 1 and Customer Address 2 are inserted with Customer 1 and Customer Address 3 is inserted with Customer 2. NetSuite returns a response with two entries and this is similar to invoking the addList operation with two customers. The number of batch response entries equals the number of parent records submitted. In this example, the batch response has two entries.
When working with child records:
Insert and update operations at the child level are ignored.
In many cases, you cannot update or add to child records without recreating the original list. For these child records, the ReplaceAll field is ignored. Even though it is required to resend the original information to update or add child records, there are a few exceptions such as SalesOrderItems. You can update or add to these child records by providing an identifier such as a line number or script ID. In such cases, you can use the parent ReplaceAll${child_name} field to control whether the submitted child records replace the original list or append to it.
The Target option UpdateNulls has no impact on child fields. Null values for child fields are always ignored.
Filtering Records from NetSuite
This section provides information for the following:
Accessing a Saved Search
Using a SQL-Like Statement
Accessing a Saved Search
NetSuite allows you to create a saved search that is useful for filtering fields and records, and joining records. You can define saved searches in the NetSuite UI and access them using the connector by performing the following:
1. Set the DataType property to Dynamic.
2. Select the appropriate Table.
3. Enter the SavedSearchId in the space provided.
Using a SQL-Like Statement
You can use a query statement to restrict received fields and records. The following sections provide additional information:
Example SQL Statements
NetSuite-Specific Operators
Tips
SQL-Like Grammar
Example SQL Statements
The following table provides the example SQL statements.
Example SQL
SQL Statement
Simple query with 'where' clause and field list
Select ExternalId, Title, Status, Message, CUSTEVENT4 from PhoneCall where Select ExternalId, Title, Status, Message, CUSTEVENT4 from PhoneCall where
Search With Numeric Filter
Select Probability, LeadSource_InternalId, Status, Pro-jectedTotal from Opportunity where ProjectedTotal not-GreaterThanOrEqualTo 1396.70
Search with String Filter
Select Title, Status, Message from PhoneCall where Title doesNotStartWith 'TC24889'
Search with Date Filter
Select Title, StartDate, EndDate, Status, NoEndDate from CalendarEvent where StartDate notOnOrBefore '2006-07-25'
Simple Subquery
Select InternalId, (select (select Addr1 from 'Addressbook') from 'AddressbookList') from Customer
Multiple 'and' Clauses
Select EntityId, FirstName, LastName, Email from Contact where EntityId contains 'John' and Email startsWith 'John' and LastName is 'Boy'
Address-Contact Primary Joined Search
Select CompanyName, Email from Vendor where Email notEmpty '' and ContactPrimaryJoin(Title is 'Quality')
Customer-SubCustomer Joined Search
Select EntityId, Email from Customer where Email notEmpty '' and SubCustomerJoin(EntityId contains 'subcust')
Query Using a Single Quote in a Field Value
Select EntityId from Contact where Email is JohnO'Doe@company.com
Query Using Double Quotes in a Field Value
Select EntityId from Contact where NickName is `John "the bomb"`
Query Using a Backtick in a Field Value
Select EntityId from Contact where Height is "5`3"
NetSuite-Specific Operators
The following table provides the operators that are specific to NetSuite.
 
Operator Category
Operator
Date-Only Operators
after
before
on
onOrAfter
onOrBefore
notAfter
notBefore
notOn
notOnOrAfter
notOnOrBefore
notWithin
within
String-only Operators
contains
doesNotContain
doesNotStartWith
is
isNot
startsWith
Numeric-Only Operators
between
equalTo
greaterThan
greaterThanOrEqualTo
lessThan
lessThanOrEqualTo
notBetween
notEqualTo
notGreaterThan
notGreaterThanOrEqualTo
notLessThan
notLessThanOrEqualTo
ID/Select Field-Only Operators
anyof
noneof
Tips
When working with NetSuite query statements:
Field names in query must be the same as NetSuite search schema names, which do not always match DataConnect object field names. For more information, see the NetSuite Schema Browser documentation.
A joined search specification may contain custom fields.
Reserved words, table names, field names, and query field names are case-sensitive.
You cannot query a child directly. You must query through the parent.
You can use single quotes, double quotes, or back ticks as field token delimiters. If a field value uses any of these characters, use a different character to use as the token delimiter.
For more information about querying NetSuite, see the NetSuite documentation.
SQL-Like Grammar
Use the following grammar as a guideline for writing NetSuite query statements.
Grammar
Query Statements
query
select field_list from table (where query_terms)?
field_list
('(' child_query ')' ',')* ('' (',' '(' child_query ')')) | (field (',' (field | '(' child_query ')'))*)
child_query
select field_list from table
Field
Label
Table
label | string_literal | string_literal_2 | string_literal_3
query_terms
simple_term | joined_search (and (simple_term | joined_search))
*simple_term
label (operation | label)? value (',' value)*
operation
'=' | '>' | '<' | '<>' | '>=' | '<='
Value
string_literal | decimal | macrodef
joined_search
label '(" simple_term (and simple_term)* ')'
Label
character (character | digit | "_")*
character
['A' - 'Z'] | ['a' - 'z']
Digit
['0' - '9']
Select
'SELECT' | 'Select' | 'select'
From
'FROM' | 'From' | 'from'
And
'AND' | 'And' | 'and'
where
'AND' | 'And' | 'and'
string_literal
'''
( (~[''','\\','\n','\r'])
| ('\\'
( ['n','t','b','r','f','\\','\'','\'']
| ['0'-'7'] ( ['0'-'7'] )?
| ['0'-'3'] ['0'-'7'] ['0'-'7']
)
)
)*
'''
string_literal_2
"`"
 
( (~["`","\\","\n","\r"])
| ("\\"
( ["n","t","b","r","f","`","\\","\'","\""]
| ["0"-"7"] ( ["0"-"7"] )?
| ["0"-"3"] ["0"-"7"] ["0"-"7"]
)
)
)* "`"
string_literal_3
"\""
 
( (~["\"","\\","\n","\r"])
| ("\\"
( ["n","t","b","r","f","`","\\","\'","\""]
| ["0"-"7"] ( ["0"-"7"] )?
| ["0"-"3"] ["0"-"7"] ["0"-"7"]
)
)
)* "\""
decimal
number ('.' number)?
number
digit+
macrodef
'$(' ~[')']+ ')'
Last modified date: 08/02/2023