NetSuite Connector

NetSuite Connector






Additional Documentation

This Connector is based on the NetSuite ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/DNG/ado/

Establishing a Connection


The User and Password properties, under the Authentication section, must be set to valid NetSuite user credentials. In addition, the AccountId must be set to the Id of a company account that can be used by the specified User. The RoleId can be optionally specified to log in the user with limited permissions.

Connecting to NetSuite



Connecting to NetSuite

Connecting to NetSuite is fairly straightforward. Simply provide the User, Password, and AccountId properties to connect. It is also recommended but not required to specify the RoleId and NetsuiteMetadataFolder: if RoleId is empty, the user's default role is used. The Netsuite Metadata Folder is a folder on disk where NetSuite metadata files will be stored. If you do not specify one, a folder location will be selected automatically. To ensure fast load times when listing metadata about tables, it is best to set this property.

NetSuite Web Services Permissions


The provider communicates with NetSuite through the NetSuite Web services. This means that the user specified in the connection must have permissions on the specified AccountId to connect through NetSuite Web services. If the user does not already have Web services permissions, an exception stating "You do not have permission to access Web services features" will be thrown when trying to connect. If this happens, an administrator will need to grant Web services permissions to the user by doing the following:

1. Create a Web services role

  • Log into NetSuite and under Setup go to User/Roles -> Manage Roles -> New.
  • Click Permissions -> Setup and add the "Web Services" role.
  • Add other permissions that are needed for interacting with various entities and transactions.
  • Under Setup, go to User/Roles -> Manage Users and select the user.
  • On the Access tab, add the newly created role and save the user.
2. Set the user's role to be the WebService default role (optional - can be provided in the connection instead):
  • Log into NetSuite and under Setup go to Integration -> Web Service Preferences.
  • Select the user in the Name menu. In the Web Services Default Role menu, select the newly created role.
  • Click add and save to save changes.

Token Authentication


Instead of using user/password credentials to connect to NetSuite, OAuth authentication can be used instead. Please see Using OAuth Authentication for details on how to connect via OAuth.

User Sessions


By default, NetSuite allows users to establish only one session when connecting to NetSuite at a time. If a second session is started, then the first session will be invalidated. For this reason, we default UseSessions to false. This will cause each request to be its own session. If you set UseSessions to true, be careful to close each connection before starting a new one. Otherwise you may receive an exception that your session has timed out. Please note that some UI editors take up multiple connections, and setting UseSessions to true will cause this exception in this scenario.

Concurrent Requests


By default, NetSuite allows only one request at a time for a given user. If a request is still executing against NetSuite when another one is attempted, an error stating "Only one request may be made against a session at a time" will be thrown on the second connection.

NetSuite Response Times


NetSuite response times can take a long time depending on what you are retrieving. This can range from 20-40 seconds to retrieve 1000 SalesOrders to up to 8-12 minutes to retrieve a single page of the SalesOrder_ItemList. The main factor that can affect performance is whether or not child table information is retrieved. For instance, the SalesOrder_ItemList table would be considered the child table of SalesOrder. In addition, the information in the ItemListAggregate on the SalesOrder table would be considered child table information. Because of the significant delay it adds in response times from NetSuite, we have defaulted AggregateColumnMode to Ignore. When set to either Ignore or List, this will cause any Aggregate columns on base tables such as SalesOrder to not request data from NetSuite. This property has no effect on child tables.

Asynchronous Services


Slow NetSuite response times extend to inserts, updates, and deletes as well.This can be especially noticeable when using batch processing. When inserting, updating, or deleting multiple records at a time, it may be worthwhile to set UseAsyncServices to true. This will cause the request to be processed asynchronously on NetSuite's end and a JobId will be returned in the Info#TEMP table. The JobId can be checked against the stored procedures CheckJobStatus and GetJobResults for information about when the job is completed, if any errors occurred, and for the InternalIds for newly created entities.

 

Using OAuth Authentication


OAuth requires the authenticating user to interact with NetSuite using the browser. The provider facilitates this in various ways as described below.

Custom Credentials


You must register an app to obtain the OAuthClientId and OAuthClientSecret.

When to Create a Custom OAuth App


Creating a custom OAuth app is required in all cases in order to connect via OAuth.

Creating a Custom OAuth App


See Creating a Custom OAuth App for information on how to create a custom app.


Custom Credentials


When to Use a Custom OAuth App

Creating a custom OAuth app is required in order to connect via OAuth.

Desktop Authentication with a Custom OAuth App

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

  • AccountId: Set this to the account id you are connecting to.
  • OAuthClientId: Set this to the Consumer Key assigned when you registered your app.
  • OAuthClientSecret: Set this to the Consumer Secret assigned when you registered your app.
  • CallbackURL: Set this to the redirect URI defined when you registered your app.
  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
When you connect the provider opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The provider then completes the OAuth process:
  1. Extracts the access token from the callback URL and authenticates requests.
  2. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Web Authentication with a Custom OAuth App

When connecting via a Web application, you need to register a custom OAuth app with NetSuite. See Creating a Custom OAuth App. You can then use the provider to get and manage the OAuth token values.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

  • AccountId: Set this to the account id you are connecting to.
  • OAuthClientId: Set this to the Integrator Key assigned when you registered your app.
  • OAuthClientSecret: Set this to the Secret Key assigned when you registered your app.

You can then call stored procedures to complete the OAuth exchange:

  1. Call the GetOAuthAuthorizationURL stored procedure. Set the AuthMode input to WEB and set the CallbackURL input to the Redirect URI you specified in your app settings.

    The stored procedure returns the URL to the OAuth endpoint.

  2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
  3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL.

Refresh the OAuth Access Token

Unlike other sources, NetSuite tokens do not expire. There is no refresh flow.

Connection String Parameters

AccountId: Required (Number) The Account ID for your Netsuite organization. You can retrieve this number in NetSuite, in the Integration section under Setup.

LogoutUnknownSessions: (Boolean) If you cannot log in because it's complaining there is already an active session, try using this session to force those other sessions to be invalidated.

MaximumConcurrentSessions: (Number) If you wish to use multithreading while writing to NetSuite, you must use this setting. By default, NetSuite does not support mutltithreading. This only becomes available if you have purchased and activated the SuiteCloud Plus license for NetSuite. This will give you the ability to use up to 10 concurrent sessions. If you wish to use 10 threads, then set this parameter to 10, and also use 10 as the value in the Thread Count property on the Run Job tab in Connect Creatio. Using multithreading with NetSuite will greatly improve the speed of writing. If you have to move a large amount of data, we highly recommend acquiring this license.

AggregateColumnMode: (String - available options below) Aggregate columns are the columns that will appear on base tables which aggregate all of the data contained within child collections (such as Address)

  • [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. When reading data from NetSuite this can significantly slow down requests.

Netsuite Metadata Folder: (String) If you get a certain error, it may be necessary to supply a folder where Connect Creatio can temporarily store metadata retrieved from Netsuite. This allows the metadata to be downloaded a cached to help speed up future requests. Recommended to create a "Metadata" folder under StarfishEngine to ensure proper permissions. Default full suggested path would therefore be: C:\Inetpub\wwwroot\StarfishEngine\Metadata. The Metadata folder will not be created automatically. When first setting this parameter, you'll have to create this folder manually.

RoleId: (Number) The RoleId is the InternalId of the role that will be used to log in to NetSuite. If you do not enter this, it will use the user's default role. Useful for forcing Administrator role in certain scenarios.

UseSandbox: (Boolean) Set to true if you would like to use the sandbox instance of your NetSuite account.


Picklists

When writing to NetSuite Picklist fields, you cannot write the value of the picklist to the field. You must instead reference the Internal Id of the picklist value you wish to use. These can be obtained in the Customization section under Setup. Using a hand-built Xref list for large lists would be recommended practice.


Aggregate Fields (Addresses)

When writing addresses to NetSuite, you must use the AggregateColumnMode=List so that the AddressbookListAggregate Column will be available to map to. Address data must be supplied as a chunk of XML containing the complete address record. See below as an example.

Function ScriptedField
 dim xml
 xml = "<Customer_AddressbookList>"
 xml = xml & "<Row>"
 xml = xml & "<AddressbookList_DefaultShipping>true</AddressbookList_DefaultShipping>"
 xml = xml & "<AddressbookList_DefaultBilling>true</AddressbookList_DefaultBilling>"
 xml = xml & "<AddressbookList_Label>Address</AddressbookList_Label>"
 xml = xml & "<AddressbookList_addressee>@@ORG:Name@@</AddressbookList_addressee>"
 xml = xml & "<AddressbookList_Addr1>@@ORG:ShippingStreet@@</AddressbookList_Addr1>"
 xml = xml & "<AddressbookList_City>@@ORG:ShippingCity@@</AddressbookList_City>"
 xml = xml & "<AddressbookList_Zip>@@ORG:ShippingPostalCode@@</AddressbookList_Zip>"
 xml = xml & "<AddressbookList_State>@@ORG:ShippingState@@</AddressbookList_State>"
 xml = xml & "<AddressbookList_Country>@@ORG:ShippingCountry@@</AddressbookList_Country>"
 xml = xml & "</Row></Customer_AddressbookList>"
 ScriptedField=xml
End Function

It is possible to supply multiple addresses in a single call by duplicating the entire <Row> node.


Uploading Attachments

It is possible to upload attachments to NetSuite and must be done in two stages.

  • First you must use an Insert stage to create a record in the File table. Here you must map the file's data as base64 to the Content column, and set the Folder_InternalId. This must be the InternalId of the Folder you wish all your attachments to go to. You may use multiple different folders, but for simplicity it's recommended to create a general "Migrated Attachments" folder in NetSuite, then use the InternalId of that newly created folder.
  • Then you must use an Insert stage to create a record in the virtual table, "File Attach". This performs an Attachment operation, which is what is responsible for relating the file to the actual parent object (customer, lead, case, etc). The File Attach table gives you 3 columns. AttachTo_Type should be the name of the entity to relate the file to ("customer", "supportCase", etc). AttachTo_InternalId should be the Id of that parent object. File_InternalId should be the Id from the File insert that was created in the first Stage. Use a Stage Value variable to retreive this (@@STG:0,#ID@@).

CData ADO.NET Provider for NetSuite 2019 - Build 19.0.7354

Parameter
Description
Account ID

Use Sandbox


NetSuite Connection Edit Screen


    • Related Articles

    • Gainsight Connector

      Gainsight's Customer Success Software empowers companies to increase revenue, decrease customer churn, and drive advocacy.  Gainsight is a cloud-based application and available for your migrations and integrations through the Creatio Connect Cloud ...

    • Stripe Connector

      Additional Documentation This Connector is based on the Stripe ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/BOG/ado/

    • Snowflake Connector

      Additional Documentation This Connector is based on the Snowflake ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/OWG/ado/

    • Redshift Connector

      Additional Documentation This Connector is based on the Redshift ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/FRG/ado/

    • Zendesk Connector

      Additional Documentation This Connector is based on the Twitter ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/FZG/ado/