NetSuite connector Upsert in Jitterbit Design Studio

The NetSuite upsert activity available within the NetSuite connector allows you to update existing or create new records in a connected NetSuite instance using Jitterbit. An "upsert" is essentially either an update or a create operation, or both, depending on the data provided.

This page describes how to update existing and create new NetSuite records at the same time, using an example of updating the phone number of an existing customer and creating a new customer in NetSuite from source data provided in a CSV file through Jitterbit.

This example demonstrates creating a new customer, and updating customer information in a record that was created as an example during the NetSuite connector create walkthrough. This example is used for demonstration purposes only and does not cover all options available in the product; please refer to Design Studio for more comprehensive documentation.

The following sections are divided up into beginning the upsert activity, defining the operation components, and deploying and executing the operation.

Creating a NetSuite Upsert operation

1. Within your project within Jitterbit Studio, there are several ways to begin a new NetSuite upsert activity. Each of the following options will start the NetSuite Upsert Wizard to guide you through the process.

   • Go to File > New > Connectors > New NetSuite Upsert.
   • In the tree on the left under Connectors, right-click on the NetSuite category, then select New NetSuite Upsert.
   • In the tree on the left under Connectors, double-click on the NetSuite category, then right-click on New NetSuite Upsert and select New NetSuite Upsert.
   • In the top toolbar, click the connector icon (orange jigsaw piece) . In the popup, select NetSuite, then select NetSuite Upsert.

2. The Endpoint screen of the NetSuite Upsert Wizard should open in the main view of Studio. This screen asks to select the endpoint that you would like to create new data or update existing data in. You should have already set up your endpoint in NetSuite connector endpoint. Use the dropdown to select the appropriate endpoint. Click Next when finished.

   

3. The Object screen of the NetSuite Upsert Wizard asks you to select the object that you would like to upsert data in. In this example we want to create a new customer record and update an existing customer record in Netsuite, so we select the "Customer" object, then click Finish to continue.

   Note

   If you have a lot of objects available in your NetSuite account, you may need to wait a moment for them to load. You can also enter an object name into the Filter field or use the Objects to show dropdown to limit the results to "Standard Objects" or "Custom Objects." Try the Refresh button if the results are not what you expect.

   

4. Jitterbit will then proceed with creating a NetSuite API request and response structure that will allow upsert data within your connected NetSuite account. A new tab should appear in Studio called NetSuite Upserts. You can rename your upsert activity here if desired; in the example ours is called "Example NetSuite Upsert Customer." You can also use the + - buttons or arrows to expand all elements within each structure.

   

5. Next, on the same screen, click the button Create Operation located under Use in an Integration in the upper right corner. This will create the Jitterbit operation that will upsert customer data in NetSuite.

6. A new tab should open in Studio called Operations, containing a graphical representation of the upsert activity.

   

   Note

   The icons for Source, Request, Response, and Target in the image above are placeholders for the operation components, which we will define in the next section.

   Save your operation by clicking either the single disk icon to save just this operation or the multiple disk icon to save all changes in your integration project. You should see the asterisk on the Operations tab and operation title disappear as your new upsert activity is now created and saved to your project.

Defining the operation components

Next we need to define the other components of the operation that appear within the graphical representation, including Source, Request, Response, and Target.

For each, the instructions below direct you to return to the graphical representation of the operation you have created and double-click each icon to configure each component. As an alternative, you could also create the Source, Request, Response, and Target separately and then drag and drop them from the tree on the left directly onto the icons in the visual representation of your operation.

For purposes of this walkthrough, we provide the following examples for reference above: Jitterpak NetSuiteExample.jpk and accompanying files in NetSuiteSampleFiles.zip. Many different types of data can be used for each component of the operation. To learn more about additional customization options, refer to the Design Studio section of our documentation.

• Source: In the example, we use a CSV file as the input request to both update an existing customer and create a new customer with the provided field values below. The following table displays the contents of the example file CustomerUpsertRequest.csv.

  ID	Name	Email	Phone	Address	City	State	Zip
  Test Cust 1	Test Customer 1	test1@example.com	1-999-999-9999	1234 Main St.	Denver	CO	80250
  Test Cust 2	Test Customer 2	test2@example.com	1-888-888-8888	1234 Sample Dr.	San Francisco	CA	94101
  Test Cust 3	Test Customer 3	test3@example.com	1-777-777-7777	1234 Example Way	Houston	TX	77001

  Note

  A NetSuite upsert should include an ID field that maps to the customer ExternalID in NetSuite. If there are new records that need to be created, using only InternalID will not be sufficient as this field would not be created yet for new records in NetSuite.

  Note

  In the above table, Test Customer 1 and Test Customer 2 were originally created in the NetSuite connector create walkthrough. The phone number for Test Customer 1 was then updated during the NetSuite connector update walkthrough. The example for an upsert on this page includes (1) the updated phone number for Test Customer 1, so this record will be updated if it was not already; else it will stay the same, (2) a newly updated phone number for Test Customer 2, and (3) a new customer record to add Test Customer 3.

  Note

  A variety of source types are available in Jitterbit. See Sources for more.

  To use the CSV file, first double-click on the Source icon, then click the button Create New Source and give your source a Name (e.g. CustomerUpsertRequest.csv). Next, for this example, we are using a private agent with local files enabled, and thus select a Type of "Local File" and then Browse to the location. If you are using the example files, the Folder is 'C:\NetSuiteSampleFiles' and the Get Files is 'CustomerUpsertRequest.csv'. We will also expand the Options and set Ignore Lines to First: '1' since our CSV file uses a header row.

• Target: Double-click on the Target icon. In the example, we will record the response from NetSuite to a CSV file to provide information about success/failure. Click the Create New Target button, give your target a Name (e.g. CustomerUpsertResponse.csv), and again select a Type of "Local File" and Browse to a location on the private agent where we want to save the data. In the example, the Folder is 'C:\NetSuiteSampleFiles' and the Filename is 'CustomerUpsertResponse.csv'.

  Note

  You can also output to a variety of different target types. The type does not have to be the same as the source type. See Targets for options.

• Request: Double-click on the Request icon and select Create New Transformation. This will open the Transformation Wizard which will walk you through creating the request.

  Note

  Additional information on request structures is provided under Transformations.

  1. On the first screen, Name, provide a Name for your transformation or leave as the default. Then use the Source dropdown to select the source type. For the example we used a CSV file, which is considered "Text." Click Next to continue.

  2. On the next screen, Source, use the Available File Format Definitions dropdown to select the same source file format definition created during NetSuite connector create (i.e. "Example Customer Flat File").

     Or, if you are creating from scratch, you can select Create New. This will open up additional options for defining a new file format. Provide a Name for the new file format (in the example we call ours "Example Customer Flat File"). To create the text definition based off of our existing CSV file, select Create From File, then in the popup browse to the local 'CustomerUpsertRequest.csv' file.

     Click Finish to continue.

  3. The Transformations tab should open where you can complete your mapping. Use the + to expand the source and target items in each tree. Then drag and drop each field you would like to be mapped from one tree to the other.

     Note

     A NetSuite upsert should include an ID field that maps to the customer ExternalID in NetSuite. If there are new records that need to be created, using only InternalID will not be sufficient as this field would not be created yet for new records in NetSuite.

     Your transformation mapping should look similar to that shown below:

     

• Response: Double-click on the Response icon and select Create New Transformation. This will open the Transformation Wizard which will walk you through creating the response.

  Note

  Additional information on response structures is provided under Transformations.

  1. On the first screen, Name, provide a Name for your transformation or leave as the default. Then use the Target dropdown to select the target type. For the example we used a CSV file, which is considered "Text." Click Next to continue.

  2. On the next screen, Target, use the Available File Format Definitions dropdown to select the same target file format definition created during NetSuite connector create (i.e. "NetSuite Response Structure").

     Or, if you are creating from scratch, you can select Create New. In the example we set up a typical response structure that will write the external ID, status, and message to the response so we get back feedback if the upsert operation fails. In the first screen, set the Name of the new file format (in the example ours is called "NetSuite Response Structure"), then click the button to Create Manually. In the next screen, click New and add three new fields called "ExternalID," "Status," and "Message."

     Click Finish to continue.

  3. The Transformations tab should open where you can complete your mapping. Use the + to expand the source and target sides. In this example we will add scripts to get the external ID in NetSuite so that if the operation fails, we now know the ID of failed record to troubleshoot. To do this:

     • Drag and drop the "isSuccess" field from the source side to the new "Status" field on the target side.

     • Double-click the "ExternalID" field on the target side to open the Formula Builder and add the following functions to the script, then click OK.

       <trans>
       FindByPos(SourceInstanceCount(),jbroot$jbrequest$upsertList$record#.Customer$externalId)
       </trans>
       

       The FindByPos function finds the instance in the requesting data that corresponds to the response.

     • Double-click the "Message" field on the target side to open the Formula Builder and add the following functions to the script, then click OK.

       <trans>
       SumString(jbroot$jbresponse$upsertListResponse$writeResponseList$writeResponse.status$statusDetail#.message$,"|",true)
       </trans>
       

       The SumString function will concatenate an element that may have more than one instance into a single string.

       Note

       Find additional script functions to use during your Transformation mapping or separately as Scripts under the Formula builder section. In addition there are several NetSuite-specific functions documented in Connector functions that can be used for more complex mapping or scripts.

     Your transformation mapping should look similar to that shown below:

     

• Optional Scripts: You can find additional script functions to use during your Transformation mapping or separately as Scripts under the Formula builder section. For additional information, see NetSuite connector advanced.

Deploying and executing the operation

With the NetSuite upsert activity fully configured, we are ready to deploy and execute the operation.

1. From the Operations tab, click the deploy icon.

2. Then click the execute icon to place the operation in the queue for execution.

3. In the lower portion of the screen, the Operation monitor should indicate that your operation was run successfully. If not, you can double-click the Status icon to view any log messages.

   

4. You should also now see the output response that was mapped to your target file. In the example, the file is now created in 'C:\NetSuiteSampleFiles\CustomerUpsertResponse.csv'. Since the operation was run successfully, we see the ID field followed by a status of '1', indicating success. If the operation failed, we would see a '0' along with a third column showing the log error message.

   ExternalID	Status	Message
   Test Cust 1	1
   Test Cust 2	1
   Test Cust 3	1

5. In addition, when you check your NetSuite account, you should find the information for Test Customer 2 updated with the new phone number, and the record for Test Customer 3 created.

   

   Note

   You can find more information about the API call to NetSuite within the Web Services Usage Log available in NetSuite under Setup > Integration > Web Services Usage Log. This provides detailed information about success/failure as well as the request and response that was sent to Jitterbit.