NetSuite and Talend: Integrating with Cloud ERP Systems
NetSuite, is a provider of cloud-based Financials/ERP and Omnichannel commerce solutions. Integration platform providers like Talend help with data integration, data migration and automation for NetSuite. The web services from NetSuite enables integration using Java, .NET or any other development language that supports SOAP-based web services. The framework provides comprehensive error handling and security functions with support for authentication, authorization, access control, session management and encryption.
Talend has built special connectors for NetSuite which automate a lot of backend work needed to build and work with NetSuite. Often people with language skills in Java and .Net can start building applications to work with NetSuite with relative ease. However, teams without these skills need not worry if they have an integration platform like Talend in their toolset. Talend provides 3 basic methods for working with NetSuite:
- Using NetSuite components
- Using NetSuite OpenAir SOAP API calls & the tXMLMap component
- Using JDBC connection adapters
In this blog, I’d like to go in-depth on NetSuite components functionality and how to automate operations like establishing connections, reading data, insertions, modification, and deletion. Talend Studio helps perform these functions using 3 components namely tNetsuiteConnection, tNetsuiteInput and tNetsuiteOutput.
Talend & NetSuite Connectors
Let’s look at how the configurations of these components and a few sample data integration job designs for inserts and updates below.
tNetsuiteConnection: This creates a connection to the NetSuite SOAP server so that other NetSuite components in the job can reuse the connection. You have the choice to select your preferred version of API along with other necessary information for authentication.
tNetsuiteInput: Invokes the NetSuite SOAP service and retrieves data according to the conditions you specify. The nice thing about the Input component is its search functionality which enables you to filter records from NetSuite instead of listing everything based on a record type. The record type browse functionality give a listing of all entities like Customer, Purchase Order including customizations in real-time.
The retrieved data from tNetsuiteInput is a pipe separated list of columns. It’s important to note that the sublist or picklist items from NetSuite are retained as JSON. Thus, you’ll need to use tExtractJSONFields component to parse the content and process it accordingly in Talend.
tNetsuiteOutput: Invokes the NetSuite SOAP service and inserts, updates, upserts, or removes data on the NetSuite SOAP server.
Before creating a new entity in NetSuite, you’ll need to understand the concept the ExternalId and InternalId.
ExternalId is used to store a unique key from source systems or self-generated keys which are later required for updating the records to NetSuite.
The InternalId is generated by NetSuite hence when creating a new entry into the NetSuite portal using tNetsuitOutput component, it returns the newly created Internal Id & ExternalId if passed earlier.
The output from tNetsuitOutput can be stored in a repository of files for processing and can be useful for performing updates based on InternalId or ExternalId. One important thing to note here is that the ExternalId is only available for parent entities like “Customer” and not in the sublist addressbookList so if you want to update the sublist at a later point in time then InternalId needs to be used. So, a best practice is to store InternalId of each new sublist item after creation so it can be passed later into tNetsuitOutput during update or upsert operations.
For creating the JSON sublist or picklist payload string fields, you can use the Talend component tWriteJSONField. The tWriteJSONField component provides many options to map the flat file structure or similar data rows from databases to the JSON formats. When configuring a JSON tree, the default element is a string. If an element is not a string, you need to add an attribute for the element to set its type. Here the type can be set to integer, double, array or object types. Please refer to the help documents for all the options available.
Examples for Inserting and Update operations using the NetSuite components are shown below.
In the Job design below, we can see how to construct the fields needed for a submission to NetSuite. The Subsidiary and AddressbookList fields are string data types containing JSON payloads. The ExternalId for a customer record is sent during the web service call and the output returned contains the newly created InternalId. This InternalId or ExternalId can be used for subsequent updates if needed.
Update & Upserts:
For updating or upsertion, the payload sent to the web service needs to have ExternalId or InternalId populated for parent entities like Customers. For sublist items like AddressBookList, the InternalId field needs to be populated with respective InternalId for each address item newly created during the Add Operation. If no InternalId is mentioned during upserts as shown below, then it will be treated as a new record and the same gets created in NetSuite. You can also see below that the picklist can also be modified with a simple change of value, for example, from “_india” to “_germany”.
With just three NetSuite components and few JSON parsing components in Talend, you can easily start working with a NetSuite WSDL based web service. Using Talend, you can be up and running quickly for new implementations and during migrations with Netsuite.