The NetSuite GetList activity available within the NetSuite Connector allows you to query records by internal or external ID in a connected NetSuite instance using Jitterbit.
This page describes how to query existing NetSuite records by ID using a simple example with external IDs provided in a CSV file through Jitterbit. This continues the demonstration using example records created and updated during the walkthroughs for NetSuite Connector Create, NetSuite Connector Update, and NetSuite Connector Upsert.
The following sections are divided up into beginning the GetList activity, defining the operation components, and deploying and executing the operation.
Creating a NetSuite GetList Operation
- Within your project within Jitterbit Studio, there are several ways to begin a new NetSuite GetList activity. Each of the following options will start the NetSuite GetList Wizard to guide you through the process.
- Go to File > New > Connectors > New NetSuite GetList.
- In the tree on the left under Connectors, right-click on the NetSuite category, then select New NetSuite GetList.
- In the tree on the left under Connectors, double-click on the NetSuite category, then right-click on New NetSuite GetList and select New NetSuite GetList.
- In the top toolbar, click the connector icon (orange jigsaw piece) . In the popup, select NetSuite, then select NetSuite GetList.
- The Endpoint screen of the NetSuite GetList Wizard should open in the main view of Studio. This screen asks to select the endpoint that you would like to get the data from. You should have already set up your endpoint in NetSuite Connector Endpoint. Use the dropdown to select the appropriate endpoint. Click Next when finished.
The Object screen of the NetSuite GetList Wizard asks you to select the object that you would like to query records from. At this time, only one object type can be queried per request. In this example we want to query existing customer records 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.
- Jitterbit will then proceed with creating a NetSuite API request and response structure that will allow data to be queried within your connected NetSuite account. A new tab should appear in Studio called NetSuite GetLists. You can rename your GetList activity here if desired; in the example ours is called "Example NetSuite GetList Customer." You can also use the + - buttons or arrows to expand all elements within each structure.
- 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 query existing customer data in NetSuite.
A new tab should open in Studio called Operations, containing a graphical representation of the GetList 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 GetList 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 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 query existing customer records by ID. These same IDs originally were mapped to NetSuite External ID during the example NetSuite create, update, and upsert operations.
NOTE: NetSuite GetList supports query of object data by either ExternalID or InternalID.
- ExternalID: If the ID you want to use for NetSuite ExternalID does not yet exist in existing records NetSuite, you can perform a NetSuite Connector Update or NetSuite Connector Upsert operation to add it.
- InternalID: The NetSuite InternalID is generated by NetSuite and can be obtained from the record within NetSuite. It can also be queried from a NetSuite Connector Search operation as well as this GetList operation.
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. CustomerGetListRequest.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 'CustomerGetListRequest.csv'. We will also expand the Options and set Ignore Lines to First: '1' since our CSV file uses a header row.NOTE: A variety of source types are available in Jitterbit. See Sources for more.
Target: Double-click on the Target icon. In the example, we will record the response from NetSuite to an XML file. Click the Create New Target button, give your target a Name (e.g. CustomerGetListResponse.xml), 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 'CustomerGetListResponse.xml'.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.
- 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.
- On the next screen, Source, use the Available File Format Definitions dropdown to 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 ID Only"). To create the text definition based off of our existing CSV file, select Create From File, then in the popup browse to the local 'CustomerGetListRequest.csv' file. Click Finish to continue.
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. In this case, the ID provided in our CSV file maps to the 'externalId' field in NetSuite.
Your transformation mapping should look similar to that shown below:
Response: Right-click on the Response icon and select Pass-Through. This will allow us to capture all the data associated with the records we queried.NOTE: In this example we want to query data from NetSuite and pass it through to an output file, or our XML file. If we wanted to transform any of the data before it was output, we could create a new transformation here, similar to the other examples provided in NetSuite create, update, upsert, and search operations.
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 GetList activity fully configured, we are ready to deploy and execute the operation.
- From the Operations tab, click the deploy icon.
- Then click the execute icon to place the operation in the queue for execution.
- 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.
- You should also now see the output response that was passed through to your target file. In the example, the file is now created in 'C:\NetSuiteSampleFiles\CustomerGetListResponse.xml'. The file should contain all of the data on the customer records queried by ID.