Skip to Content

NetSuite connection guide for Jitterbit App Builder

Overview

This guide describes the system requirements and instructions for connecting App Builder to an Oracle NetSuite cloud database server. Two different NetSuite components will be covered in this guide: Suite QL and SuiteTalk Web Services.

SuiteQL is a query language based on the SQL database query language and used to query data from NetSuite. The default service supported in SuiteTalk is designed using a number of SOAP requests to retrieve data in a format more closely associated with the saved searching in NetSuite. This means that SuiteQL can be used to handle much more complicated queries than SuiteTalk. For instance, joins, aggregations, group by, order by, and formulas can all be handled server side using SuiteQL.

SuiteTalk Web Services, by design, only supports some joins and predicates. In general, SuiteQL is a more powerful option for executing queries faster against NetSuite due to its advanced support for handling SQL.

Our recommendation is to use Suite QL for Reading and Object Building and SuiteTalk for Writing and Updating Data.

System requirements

Database server

Oracle NetSuite

Client web browser

  • Chrome: 84+

  • Firefox: 73+

  • Safari: 13.0.1+

  • Edge: 84+

  • Internet Explorer: 11+

Connection instructions

NetSuite integration

Before starting the App Builder configuration for the connection, you first need to configure a new integration in NetSuite. This setup requires Administrative access to NetSuite. This integration setup will issue a unique ClientID and Secret, information required for the App Builder configuration.

  • Login to NetSuite as an Administrator

    attachment

  • Navigate to Setup > Integration > Manage Integration > New

    attachment

  • Upon finishing the integration configuration, NetSuite will generate a unique Client ID and Secret. Be sure to store this information safely, as you can only fetch this information once and it is required to setup the App Builder configuration.

Adding a server

  1. Navigate to the App Builder Control Center

  2. Click on the Connections link from the Connect menu

    attachment

  3. Click the Create button on the top left of the Data Servers panel. This will bring up the Add Server page.

Filling in credentials

To connect to a NetSuite database server, provide the following information:

  • Server Name: An identifiable and unique server name.

  • Type: Select "Net Suite" from the menu.

  • Host Name: The hostname of your server.

  • Port: The port number for the server. The default port is 1433.

  • Instance Name: Required information – this is the ID part of your unique NetSuite URL. For example: https://4201843.app.netsuite.com/ translates to 4201843 as the Instance Name.

    attachment

  • Advanced Settings: Includes additional configuration options including the Ping Timeout setting, which determines the amount of time (in seconds) that App Builder will wait for a ping back from the server before declaring a timeout.

    • Advanced: This field must be filled in properly to support the connection

      • Schema={{ Schema }}; //This is either set to SuiteQL or SuiteTalk (Suite Talk is used for Writing and Updating Data). Has a totally different schema then SuiteQL.

      • OAuthClientId={{ OauthClientID }}; //Issued when you register your NetSuite Integration

      • OAuthClientSecret={{ OauthClientSecret }}; //Issued when you register your NetSuite integration

      • OAuthAccessToken={{ AccessToken }}; //Issue a test call to generate this initially

      • OAuthAccessTokenSecret={{ AccessSecret }}; //Issue a test call to generate this initially

      • IncludeChildTables=true; // Optional parameter but is required if the Schema is set to SuiteTalk and you need to write to tables with relationships to see the sub tables

    Note

    There are additional parameters supported, if needed. These are elaborated on: https://cdn.cdata.com/help/DNG/ado/pg_suiteql.htm

  • Ping Timeout: {default value is 10}

  • Escape Query Parameter Name: {default is enabled}

    Graphical user interface, application Description automatically generated

After filling in all necessary credentials, click the Save button to save the settings.

Testing the connection

Click the Test Connection button beneath the server information to make sure the server is properly connected. You will see an App Builder "Success" message if the configuration is correct.

Adding a data source

After the NetSuite server settings have been saved, the database needs to be connected to App Builder. Databases that are connected to App Builder are referred to as a "data source".

  1. Navigate to the App Builder Control Center

  2. Click on the Connections link from the Connect menu

  3. Click to select the NetSuite server entry from the Data Servers panel, then click Add a New Data Source > Create New Database from the Data Sources panel. Some data sources are automatically imported to the App Builder data storage page. If the data source has already been added, click on the associated Details button to bring up the details page.

  4. Click the Edit button

  5. Fill in the following credentials for the data source:

    1. Data Source Server: The name of the server being drawn from

    2. Data Source Name: Give the data source a unique display name

    3. Import Pattern: This allows users to import only certain tables by listing them in the box. Prefixes can also be used to only import a subset of tables (for example, MyApp_* will only import tables that start with "MyApp_"). If the space is left blank, it will import all tables.

    attachment

  6. Click Save to update the data source settings.

  7. Once the page refreshes, click the Import button that appears below the source credentials. When prompted, click Proceed. The import will be scheduled to run in the background.

    attachment

  8. After Import has completed, click on Tables to view the tables currently created

NetSuite SuiteQL vs. SuiteTalk Web Services

As noted in the About section of this guide, there are key differences for how you will work with NetSuite SuiteQL vs. SuiteTalk Web Services in App Builder, as well as what information can be retrieved, after the connection has been established.

SuiteQL is a query language based on the SQL query language. It supports advanced query capabilities you can leverage to view NetSuite records and data. Our recommendation is to use SuiteQL for Reading and Object Building. Within App Builder itself, SuiteQL will allow you to display Objects as Views.

attachment

SuiteTalk Web Services is a SOAP based web service used for integration with external systems, in this case App Builder. By design SuiteTalk will only support some joins and predicates.

After configured properly in App Builder, SuiteTalk will provide you with table Endpoints based on SOAP Endpoints in NetSuite.

attachment

Limitations

  • NetSuite limits concurrency of queries that run against it by connection and licenses by connection pool, this will impact read performance

  • NetSuite has some custom rules on filters that you will need to manipulate your data format to be able to properly filter

  • NetSuite does not support the use of parenthesis in object names