User Guide > Map Connectors > Source and Target Map Connectors > NetSuite
Was this helpful?
NetSuite
NetSuite is a single-mode connector that provides source and target connections to NetSuite, 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 2019_2_0.
Note:  The traditional NetSuite connector has been renamed to NetSuite Multimode in DataConnect 12.1 and later versions, to better align capabilities with other Multimode connectors. When you load an existing Map that was created when NetSuite referred to a multimode connector, the target will automatically select NetSuite Multimode connector
Connection Parts
Connector parts are the fields you configure to connect with a data source or target. The settings that are available depend on the connector you select.
Note:  For a list of all parts for source connectors, see Specifying Connector, Parts, and Properties. For a list of all parts for target connectors, see Specifying Connector, Parts, and Properties.
The NetSuite connector supports both password-based authentication and token-based authentication. The token-based authentication is the preferred method as it uses a token to access NetSuite through APIs, eliminating the need for RESTlets or web services integrations to store user credentials.
Specify the following connection parts on the Source/Target tab to connect with NetSuite:
Connection Part
S/T
Description
Account
S/T
The NetSuite account id. Required for both types of authentication.
Email
S/T
The NetSuite account email. Required for password-based authentication.
Password
S/T
The NetSuite account password. Required for password-based authentication.
ApplicationId
S/T
The NetSuite CRM application ID created by using the integration record. Required for password-based authentication.
TokenId
S/T
The authentication token id, generated by a NetSuite user. Required for token-based authentication.
TokenSecret
S/T
The authentication token secret, generated by a NetSuite user. Required for token-based authentication.
ConsumerKey
S/T
The NetSuite consumer key from an integration record. Required for token-based authentication.
ConsumerSecret
S/T
The NetSuite consumer secret from an integration record. Required for token-based authentication.
Source Table/View
S
(required) Select any of the following:
• Table/View
• Query Statement
• Query File
Based on the option you select, you will be required to select a table/view, or specify a query statement, or specify a query file.
Table/View
S
(required) You are required to select a table or a view.
Note:  This field is displayed only if Source Table/View is selected as Table/View.
Query Statement
 
(required) You are required to specify a query statement.
Note:  This field is displayed only if Source Table/View is selected as Query Statement.
Query File
 
(required) You are required to browse and select a query file.
Note:  This field is displayed only if Source Table/View is selected as Query File.
Table
T
Shows a list of target tables from where you can select a table. You must have appropriate permissions for this table.
The Account information that is specified connects you to the required NetSuite instance (sandbox or production instance).
Prerequisites
Log in credentials for User ID and Password-based authentication or Token-based authentication should be available, and users must have permission to access entities.
Connector Properties
You can specify 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
S/T
Specifies the allowed level of NetSuite customizations.
• Full (default): Use this option while using custom record types. Also, provides access to custom fields defined for the standard record types.
• CustomFieldsOnly: Use this option while using custom fields defined for the standard record types. Custom fields are requested based on your requirements. You cannot access the custom record types.
• 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 API calls at startup.
Note:  For source, this property is applicable only when the DataType property is set to Record.
DataType
S
Specifies how to obtain allowable values for the Table list.
• Record (default): Query the normal record types such as Customer, Contact, SalesOrder, Your CustomRecordType.
• Dynamic: Query using NetSuite saved searches.
• CustomList: Query the NetSuite custom, user-defined lists of ID and value pairs.
DisableMandatoryCustomFieldValidation
T
Enables or disables validation for the required custom fields.
• Default (default): Default NetSuite setting is used.
• TRUE: Data for a custom field is not required when submitting a web services request.
• FALSE: An error is returned when data for a required custom field is not provided.
DisableSystemNotesCustomFields
T
Enables or disables recording of system-generated notes when you modify custom fields of a standard record. The system-generated notes are used in NetSuite to track changes to a record, including what action was taken, when the record was modified, and the user who is responsible for the change.
• Default (default): The default NetSuite setting is used, which is TRUE.
• TRUE: System-generated notes for custom fields of standard entity are recorded when these fields are updated.
• FALSE: System-generated notes are turned off.
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 records buffered in the memory before being written to the target connector.
Default value is 0 (all records are written at once).
IgnoreReadOnlyFields
T
Specifies whether read-only fields are ignored.
• Default (default): Default NetSuite setting is used.
• TRUE: Read-only fields are ignored.
• FALSE: Read-only fields are not ignored, and an error message is returned.
NestedChildren
S/T
Sets 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.
This property is applicable only when the DataType property is set to 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 must fine-tune performance. For more information, see the NetSuite documentation.
Role
S/T
Specifies 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
S
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 as errors and the request is rejected.
• Default: Default NetSuite setting is used.
• TRUE: Warning messages generated by NetSuite are treated as errors.
• FALSE: Warning messages generated by NetSuite are not treated as errors.
UpdateNulls
T
Controls whether empty or null values are sent to NetSuite.
• TRUE: Null values are sent to the NetSuite server. Blank is not mapped and no value is sent to the server. Empty strings are mapped as “0”.
• FALSE (default): Null values are not sent to the NetSuite server. Blank is not mapped and no value is sent to the server. Empty strings are mapped as “0”.
Note:  
- This property is useful only if some default value is assigned to the column. Default values can be assigned from the Netsuite application.
- The setting for this property affects only parent-level record types since child record types ignores the null values and empty strings.
UseUILabel
S/T
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.
Supported Output Modes
NetSuite connector supports the Append output mode. For more information, see Target Output Modes.
Supported 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
Date Time
Time of Day
Double
Decimal Number
Currency
Percent
Long
Integer Number
MultiSelect
Multiple Select
Select
List/Record
Image
String
Document
E-mail Address
Free-Form Text
Help
HyperLink
Inline HTML
Long Text
Phone Number
Password
Rich Text
Text Area
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
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 Actian 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: 02/09/2024