TIBCO Scribe® Online Connector For NetSuite (CData)

The TIBCO Scribe® Online Connector for NetSuite (CData) is based on the CData driver for NetSuite. This Connector supports source and target operations on more than one hundred NetSuite entities. It offers native query using standard SQL syntax for filtering and joining tables. Use the NetSuite (CData) Connector to integrate your CRM system with NetSuite ERP or to integrate your marketing automation system with NetSuite CRM.

Use the NetSuite (CData) Connector as a source or target Connection for Integration Solutions. This Connector is based on Scribe.Connector.AdoNet library and CData NetSuite ADO.NET provider.

Connector Specifications

This Connector supports up to version 2020.1 of the NetSuite API.

  Supported

Agent Types

On Premise X
Cloud X

Replication Services

Source  
Target  

Integration Services

Source X
Target X

Migration Services

Source  
Target  

Maps

Integration X
Request-Reply X
Message  

Note: This Connector is available from the TIBCO Scribe® Online Marketplace. See Marketplace TIBCO Scribe® Certified Connectors for more information.

Supported Entities

The CData ADO.NET Provider for NetSuite models NetSuite entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained based on your NetSuite account. See Data Model in the CData documentation for a complete list of supported entities.

Setup Considerations

The user specified in the TIBCO Scribe® Online Connection dialog must have permissions on the specified AccountId to connect through NetSuite Web services.

User Roles For Data Integration

NetSuite roles are sets of permissions that can be assigned to one or more users. For data integration, you could assign users the Administrator role, which would give broad enough permissions to access anything in NetSuite. However, for security purposes, best practice is to limit permissions to the minimum required. In NetSuite Manage Roles under Permissions -> Setup, configure one or more roles with access to the following:

In addition, configure specific permissions for any NetSuite objects you want to integrate, such as access to Customer Lists or Sales Order Transactions. Set permission levels, such as Full access or View Only access, for each object based on what you need your users to accomplish.

Token-Based Authentication

To use Token-Based Authentication, create an Integration in NetSuite with the following enabled: 

Important: When you save the Integration in NetSuite, the Client ID and Client Secret required to configure a Connection to NetSuite are displayed only once. Be sure to copy that information and save it in a secure location. You can generate a new Client ID and Client Secret, if necessary, by editing the Integration.

.

API Usage Limits

The NetSuite API limits each NetSuite account to a set number API connections or sessions based on the license purchased. If the number of sessions associated with your account is not high enough to sustain multiple connections to the API, TIBCO Scribe® Online Solutions can fail if other calls are made to the NetSuite API using the same account.

The authentication type and API version configured in the Connection dialog affect whether or not the Connector can access the maximum number of concurrent sessions configured for a NetSuite account as follows: 

When running a TIBCO Scribe® Online Solution that uses a NetSuite (CData) Connection, any one of the following actions also makes a Connection to the NetSuite API and may cause the running Solution to fail: 

Some options to prevent your NetSuite (CData) Solutions from failing include: 

For more details see The NetSuite Help Center. To open the Help Center, log in to NetSuite and click the Help link in the upper right corner.

Selecting An Agent Type For NetSuite (CData)

Refer to TIBCO Scribe® Online Agents for information on available Agent types and how to select the best Agent for your Solution.

Connecting To NetSuite

Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Scribe® Online users. Depending on the entities supported, a TIBCO Scribe® Online user could alter user accounts in the target system.

  1. Select More > Connections from the menu.
  2. From the Connections page select Add to open the Add a New Connection dialog.
  3. Select the Connector from the drop-down list in the Connection Type field, and then enter the following information for this Connection:
    • Name — This can be any meaningful name, up to 25 characters.
    • Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
  4. Select and configure an Authentication Type for this Connection.
    • User Credentials
      • AccountIDNetSuite Account ID found in NetSuite under Setup > Company > Company Information.

        Example: TSTDRV1167940

      • User — User associated with the Web Service role. Required for the User Credentials Authentication Type.
      • Password — Password for the Web Service role User. Required for the User Credentials Authentication Type.
      • RoleID — Optional. ID of the Web Services role used in the current session, if the Web Services role is not the default for the user.
      • Api Version — Select the version of the NetSuite API to access from the list of supported versions. Setting the version of the API using Additional Parameters overrides this setting.
    • Token-Based Auth (TBA)
      • AccountIDNetSuite Account ID found in NetSuite under Setup > Company > Company Information.

        Example: TSTDRV1167940

      • Client ID — Consumer Key displayed on the Confirmation page when an Integration record is created in NetSuite for Token Based Authentication.

        Note: This Key is displayed only once when the Integration is created.

      • Client Secret — Consumer Secret displayed on the Confirmation page when an Integration record is created in NetSuite for Token Based Authentication.

        Note: This Secret is displayed only once when the Integration is created.

      • RoleID — Optional. ID of the Web Services role used in the current session, if the Web Services role is not the default for the user.
      • Api Version — Select the version of the NetSuite API to access from the list of supported versions. Setting the version of the API using Additional Parameters overrides this setting.
      • Authenticate — When the Connection configuration is complete, including the Additional Parameters section, use the Authenticate button to authenticate with NetSuite. If you are not logged into NetSuite, you are asked to log in. A NetSuite permissions dialog displays. To continue the authentication process select Allow, which allows TIBCO Scribe® Online to access your NetSuite data. Select Close on the confirmation dialog.

        Tip: Do not test the Connection until the message at the top of the Connection dialog reads Authentication complete. Note that it may take a few minutes for this message to display.

  5. Additional Parameters — Optional field where you can specify one or more connection string parameters. See the Connection String Options section of the CData documentation for a list of parameters that can be used and their default values. Note that in some cases the CData ADO.NET Provider For NetSuite does not fully support all of the possible parameters.

    Syntax for the Additional Parameters field is as follows:

    • All blank characters, except those within a value or within quotation marks, are ignored
    • Preceding and trailing spaces are ignored unless enclosed in single or double quotes, such as Keyword=" value"
    • Semicolons (;) within a value must be delimited by quotation marks
    • Use a single quote (') if the value begins with a double quote (")
    • Use a double quote (") if the value begins with a single quote (')
    • Parameters are case-insensitive
    • If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the last occurrence is used
    • If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that the equal sign is part of the keyword
    • The Parameters listed below default to true. If you see any permissions errors associated with these Parameters, they can be set to false using the Additional Parameters field.
      • Include Child Tables
      • Include Custom Field Columns
      • Include Custom List Tables
      • Include Custom Record Tables
    • Parameters that are handled by other fields or default settings in the Connection dialog are ignored if used in the Additional Parameters field, including: 
      • AccountID
      • User
      • Password
      • RoleID
      • Logfile — To enable logging enter a value for the verbosity parameter in the Additional Parameters field. The default log file size is a maximum of 10MB. When the log file reaches 10MB a new log file is started, up to a maximum of five files. Once there are five files, the oldest file is deleted as needed. Any CData log files generated by this setting are stored in the default TIBCO Scribe® Online Agent Logs directory, C:\Program Files (x86)\Scribe Software\TIBCO Scribe® Online Agent\logs\.

        Note: For information on setting log file verbosity, see Verbosity in the CData Help.

      • MaxLogFileCount — This parameter is set by the Connector to a maximum of five files.
      • MaxLogFileSize — This parameter is set by the Connector to a maximum of 10MB.
      • Other
      • RTK
      • UseConnectionPooling
  6. Select Test to ensure that the Agent can connect to your database. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
  7. Select OK to save the Connection.

Metadata Notes

Naming

Connection metadata must have unique entity, relationship, and field names. If your Connection metadata has duplicate names, review the source system to determine if the duplicates can be renamed.

NetSuite (CData) Connector As IS Source

Consider the following when using the NetSuite (CData) Connector as an IS source.

Query

Date and DateTime fields are in DateTime format. NetSuite returns dates offset based on your user timezone preferences. This applies only to Dates, and not to Datetimes, which are always returned the same way no matter your preferences. See User Timezone Offset in the CData documentation.

Net Change

When a datetime is configured on the Query Block on the Block Properties Net Change Tab to query for new and updated records, that configuration is treated as an additional filter. The Net Change datetime filter is applied as an AND after any other filters specified on the Block Properties Filter Tab. TIBCO Scribe® Online builds a query combining both the Net Change filter and the filters on the Filter tab. See Net Change And Filters for an example.

Some Connectors for TIBCO Scribe® Online only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.

Note: The Net Change date is ignored when previewing data on the Preview tab. Filters on the Block Properties Filters tab are used to filter the data on the Preview tab.

Filtering

Native Query

The NetSuite (CData) Connector supports SQL queries in Native Query Blocks to create customized queries for NetSuite. You can use SELECT, UPDATE, and INSERT clauses. With enabled support for Enhanced SQL it is also possible to use JOINs and Aggregate Functions. See the SQL Compliance section of the CData documentation.

Note: It is possible to perform a SQL query with unintended negative consequences, such as dropping a table. TIBCO recommends that you use only SELECT clauses in the Native Query Block.

After entering the SQL query, you must select Test to validate the query. Invalid queries are not accepted by the Connector. See Native Query Block and Creating Native Queries For Microsoft SQL Server for additional information.

When testing a Native Query in a Map, if the source datastore does not return any data, TIBCO Scribe® Online cannot build the schema for the underlying metadata and the Map cannot be saved. To allow TIBCO Scribe® Online to build the schema, do the following:

  1. Create a single temporary record in the source datastore that matches the Native Query.
  2. Test the Native Query and ensure that it is successful.
  3. Save the Map.
  4. Remove the temporary record from the source datastore.

NetSuite RESTlets

RESTlets allow you to create custom endpoints in NetSuite and call them from another application. The NetSuite (CData) Connector supports using RESTlets to query NetSuite data.

Note: RESTlets are an advanced NetSuite feature used to improve query performance. If you are unfamiliar with using RESTlets consult your NetSuite Administrator for assistance.

RESTlet Configuration

RESTlet Notes

NetSuite (CData) Connector As IS Target

Consider the following when using the NetSuite (CData) Connector as an IS target.

Record Matching By Record ID

When a record is created or updated the ID for that record is not being returned to TIBCO Scribe® Online in the target operation results making it unavailable for use later. For example, in many cases users update the source record with the record ID of the target record for future record matching. Without access to the record ID, you may need to match using a unique value other than ID. If there is no other unique value, you can work around the issue as follows:

See Result Fields for more information on operation results and Lookup Block for more information on looking up data.

Notes On Standard Entities

Address

Customer_addressbookList And Contact_addressbookList

InternalId — The InternalId field on a Customer_addressbookList or Contact_addressbookList record corresponds to the associated Customer or Contact. The addressbookList_InternalId is the unique identifier for a specific address. Neither the InternalId nor the addressbookListInternalId is available in the operation results for the Create Block. See Record Matching By Record ID for more information.

Invoice_itemList

The Invoice_itemList and Invoices entities function in a similar fashion to the SalesOrder_itemList entity.

SalesOrder_itemList

InternalId — The InternalId field on a SalesOrder_itemList record corresponds to the associated SalesOrder. The ItemList_LineUniqueKey is the unique identifier for a specific line item. A combination of the InternalId and the ItemList_LineUniqueKey can be used to identify a specific line item in a SalesOrder. Neither the InternalId nor the ItemList_LineUniqueKey is available in the operation results for the Create Block. See Record Matching By Record ID for more information.

TIBCO Scribe® Online API Considerations

To create connections with the TIBCO Scribe® Online API, the NetSuite (CData) Connector requires the following information:

Connector Name

NetSuite (CData)

Connector ID

07494989-1380-4F86-A9FD-B125D24E9849

TIBCO Scribe® Online Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Note: Connection property names are case-sensitive.

NameData TypeRequiredSecuredUsage
AuthType

String

Yes

No

 

AccountId

String

Yes

No

 

User

String

Yes

No

 

Password

String

Yes

Yes

 

RoleId

String

No

No

 

ConnectionString

String

Yes

No

Property is required in the Connection model but specifying a value is not required.

Version

String

Yes

No

Add a supported NetSuite API version, such as 2019.2.

License Agreement

The TIBCO Scribe® Online End User License Agreement for the NetSuite (CData) Connector describes TIBCO and your legal obligations and requirements. TIBCO suggests that you read the End User License Agreement.

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.