NetSuite connector Search in Jitterbit Design Studio

The NetSuite search activity available within the NetSuite connector allows you to search for existing records in a connected NetSuite instance using Jitterbit.

This page describes how to search for existing NetSuite records, demonstrating with the same 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 search activity, defining the operation components, and deploying and executing the operation. Information specific to each type of NetSuite search available is also provided below.

Creating a NetSuite Search operation

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

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

2. The Endpoint screen of the NetSuite Search Wizard should open in the main view of Studio. This screen asks to select the endpoint that you would like to search for 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 Search Wizard asks you to select the object that you would like to search for existing data in. In this walkthrough we want to search for existing customer records in Netsuite, so we select the "Customer" object, then click Finish to continue.

   Note

   You may notice differences in the list of objects displayed during a NetSuite search operation compared with the objects available during the NetSuite create, update, and upsert operations. This is due to access allowed by NetSuite to add or modify records versus access to search records.

   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," "Custom Objects," "Transaction Search Objects," or "Item Search Objects." Try the Refresh button if the results are not what you expect.

   

   Note

   If you are searching within a transaction object and want to search by status, see the special notes provided under "NetSuite Transaction Search by Status" within NetSuite connector advanced.

4. The Search Type screen of the NetSuite Search Wizard asks you to select the type of search you want to perform.

   

   An example using each of these search types is provided in the following subsections. - Basic Search: A basic search will limit the search to only the object you selected in the previous screen. A basic search will always return all available fields for the selected object. For example, a basic search for the selected "Customer" object will query fields in the "Customer" object

   • Expanded Search: An expanded search will include the selected object along with any related objects in NetSuite. For example, an expanded search for the selected "Customer" object will also include additional related objects in NetSuite such as billingAccountJoin, contactJoin, opportunityJoin, transactionJoin, etc.

     Note

     Expanded searches must include a query on a related object, or you will receive an error in Jitterbit. If the criteria and fields being retrieved are on the same object, please use a basic search.

     Note

     Expanded and advanced searches may be disabled for custom objects or other objects that do not have related objects in NetSuite.

   • Advanced Search: An advanced search will include the selected object along with any related objects, and in the next screen will present you with an additional option to select the specific fields you want to query. An advanced search can be much faster than a basic search or advanced search, even if you do not want to query related objects, because it allows you to limit the query to specific fields in NetSuite.

     Note

     Expanded and advanced searches may be disabled for custom objects or other objects that do not have related objects in NetSuite.

     Warning

     In an advanced search, custom segments of the type List/Record are not supported. Learn more under Using NetSuite custom segments in NetSuite connector advanced.

   • Saved Search: A saved search is a reusable search that you have already defined within NetSuite. Instead of building the search within Jitterbit, the search is already set up within NetSuite and can then be pulled into Jitterbit. For additional information on NetSuite saved searches, see NetSuite documentation on Using Saved Searches.

Basic search

A basic search will limit the search to only the object you selected in the previous screen. A basic search will always return all available fields for the selected object.

The following example demonstrates a basic search for the selected "Customer" object. The criteria used in the example return records with a "lastModifiedDate" field after "yesterday" to return the records modified during the examples used in the NetSuite create, update, and upsert operations.

1. The Search Type screen of the NetSuite Search Wizard asks you to select the type of search you want to perform. Select Basic Search, then click Next to continue.

2. The Build Query screen of the NetSuite Search Wizard is where you will enter criteria for your search. Each configurable option is explained as follows.

   

   • Specify criteria to narrow down your search: On the left side of the screen is a list of the fields available within the selected object. For the example, scroll down to "lastModifiedDate." Double-click on the field to add a condition, or select the field and then click the button Add Condition.

   • Add Condition: In the Add Condition popup, use the dropdown to select an Operator. In the example we will choose "after." Below, select the radio button for Use predefined value and select "yesterday." You could alternatively specify an exact date as instructed in the dialogue. Click Add Condition to continue.

   • Current Conditions: A summary of the conditions you have currently selected will appear as a list. Use the Edit or Delete buttons to manage the current selection.

     Note

     You can add any number of conditions to your search by simply selecting another field on the left and adding another condition. In this way you can effectively create any combination of multiple record searches.

   • Search page size: Set the number of records per page if needed.

   • Test Query: Click the Test Query button in the bottom right to test your query. For our example used during the NetSuite create, update, and upsert operations, the test query indicates there are three records modified since yesterday. If the results look correct, click Finish.

3. Jitterbit will then proceed with creating a NetSuite API response structure that shows the structure of the response. A new tab should appear in Studio called NetSuite Searches. You can rename your search activity here if desired; in the example ours is called "Example Basic NetSuite Search Customer." You can also use the + - buttons or arrows to expand all elements within the structure.

   

4. 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 perform the search.

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

   

   Note

   The icons for 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 update activity is now created and saved to your project.

Expanded search

An expanded search will include the selected object along with any related objects in NetSuite. For example, an expanded search for the selected "Customer" object will also include additional related objects in NetSuite such as billingAccountJoin, contactJoin, opportunityJoin, transactionJoin, etc.

The following example demonstrates an expanded search for the selected "Customer" object and related objects. The criteria used in the example return records from the related object "contactJoin" with a "lastModifiedDate" field after "yesterday" to return records. If you have been following along with the examples used for NetSuite create, update, and upsert operations please note that a Contact has been also added to one of the test customers in NetSuite for purposes of this example.

1. The Search Type screen of the NetSuite Search Wizard asks you to select the type of search you want to perform. Select Expanded Search, then click Next to continue.

2. The Build Query screen of the NetSuite Search Wizard is where you will enter criteria for your search. Each configurable option is explained as follows.

   

   • Specify criteria to narrow down your search: On the left side of the screen is the selected object, and any related objects. Use the arrows to expand the fields within the related object that you want to query. For the example, expand the "contactJoin" object and select the "lastModifiedDate" field. Double-click on the field to add a condition, or select the field and then click the button Add Condition.

   • Add Condition: In the Add Condition popup, use the dropdown to select an Operator. In the example we will choose "after." Below, select the radio button for Use predefined value and select "yesterday." You could alternatively specify an exact date as instructed in the dialogue. Click Add Condition to continue.

   • Current Conditions: A summary of the conditions you have currently selected will appear as a list. Use the Edit or Delete buttons to manage the current selection.

     Note

     You can add any number of conditions to your search by simply selecting another field on the left and adding another condition. In this way you can effectively create any combination of multiple record searches.

   • Search page size: Set the number of records per page if needed.

   • Test Query: Click the Test Query button in the bottom right to test your query. The results should indicate the number of records you have modified the Contact object for modified since yesterday. If the results look correct, click Finish.

3. Jitterbit will then proceed with creating a NetSuite API response structure that shows the structure of the response. A new tab should appear in Studio called NetSuite Searches. You can rename your search activity here if desired; in the example ours is called "Example Expanded NetSuite Search Customer." You can also use the + - buttons or arrows to expand all elements within the structure.

   

4. 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 perform the search.

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

   

   Note

   The icons for 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 update activity is now created and saved to your project.

Advanced search

An advanced search will include the selected object along with any related objects, and in the next screen will present you with an additional option to select the specific fields you want to query. An advanced search can be much faster than a basic search or advanced search, even if you do not want to query related objects, because it allows you to limit the query to specific fields in NetSuite.

The following example demonstrates an advanced search for the selected "Customer" object and related objects. In the example we also limit the returned fields to those we mapped to during the request transformation in the example NetSuite create, update, and upsert operations. This example returns records from the "Customer" object with a "lastModifiedDate" field after "yesterday." This example does not query related objects, but you can easily do so by defining the filter criteria in the same way as on the main object.

1. The Search Type screen of the NetSuite Search Wizard asks you to select the type of search you want to perform. Select Advanced Search, then click Next to continue.

2. The Build Query screen of the NetSuite Search Wizard is where you will enter criteria for your search. This screen contains two tabs Select Columns to Return and Define Filter Criteria, explained below.

   • Select Columns to Return: On this tab, select the fields within the object(s) you want to query. In the example we will select all of the fields mapped during the request transformation in the example NetSuite create, update, and upsert operations.

     Note

     If there are a large number of fields, you may find it easier to find them using the Filter located below the list. Be careful to select the correct field – In NetSuite there may be multiple fields with similar names. Also note that when using the filter, any fields you have already selected will remain shown.

     

   • Define Filter Criteria: On this tab, enter the criteria for your search. Each option is explained below.

     

     • Specify criteria to narrow down your search: On the left side of the screen is the selected object, and any related objects. Use the arrows to expand the fields within the object that you want to query. For the example, expand the "Customer" object and select the "lastModifiedDate" field. Double-click on the field to add a condition, or select the field and then click the button Add Condition.

     • Add Condition: In the Add Condition popup, use the dropdown to select an Operator. In the example we will choose "after." Below, select the radio button for Use predefined value and select "yesterday." You could alternatively specify an exact date as instructed in the dialogue. Click Add Condition to continue.

     • Current Conditions: A summary of the conditions you have currently selected will appear as a list. Use the Edit or Delete buttons to manage the current selection.

       Note

       You can add any number of conditions to your search by simply selecting another field on the left and adding another condition. In this way you can effectively create any combination of multiple record searches.

     • Search page size: Set the number of records per page if needed.

     • Test Query: Click the Test Query button in the bottom right to test your query. For our example used during the NetSuite create, update, and upsert operations, the test query indicates there are three records modified since yesterday. If the results look correct, click Finish.

3. Jitterbit will then proceed with creating a NetSuite API response structure that shows the structure of the response. A new tab should appear in Studio called NetSuite Searches. You can rename your search activity here if desired; in the example ours is called "Example Advanced NetSuite Search Customer." You can also use the + - buttons or arrows to expand all elements within the structure.

   

   Note

   On an advanced search, notice that when you expand the object (in this example, the "Customer" row above), you can further expand each object to reveal additional properties. Depending on the type of field on the object, the properties available in the field may be different.

   For example, companyName is a string field and includes the properties customLabel and searchValue. The searchValue property will contain the result of the query. However, externalId is a lookup and has expandable searchValues, including internalId, externalId, type, and name. Keep in mind that when doing your transformation mapping in the next section below, you may need to map different properties depending on the type.

4. 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 perform the search.

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

   

   Note

   The icons for 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 update activity is now created and saved to your project.

Saved search

A saved search is a reusable search that you have already defined within NetSuite. Instead of building the search within Jitterbit, the search is already set up within NetSuite and can then be pulled into Jitterbit. For additional information on NetSuite saved searches, see NetSuite documentation on Using Saved Searches.

The following example demonstrates using a NetSuite saved search in a Jitterbit operation. In this example, the saved query within NetSuite returns new records from the "Customer" object that were created on today's date.

1. The Search Type screen of the NetSuite Search Wizard asks you to select the type of search you want to perform. Select Saved Search, then click Next to continue.

2. The Build Query screen of the NetSuite Search Wizard asks you to select a saved search for the selected object. These searches are pulled in directly from your connected NetSuite instance. Select the one that you want to use for your query, then click Test Query to make sure the number of records is what you expect. Click Finish to continue.

   

   Note

   If this dropdown is not populated but you expect to see many saved searches, you may be reaching a NetSuite-imposed 1,000-record limit on API requests. In this case, refer to NetSuite saved search limitations to confirm the limitation and for suggested workarounds.

3. Jitterbit will then proceed with creating a NetSuite API response structure that shows the structure of the response. A new tab should appear in Studio called NetSuite Searches. You can rename your search activity here if desired; in the example ours is called "Example Saved NetSuite Search Customer." You can also use the + - buttons or arrows to expand all elements within the structure.

   

   Note

   Like an advanced search, on a saved search when you expand the object (in this example, the "Customer" row above), you can further expand each object to reveal additional properties. Depending on the type of field on the object, the properties available in the field may be different.

   For example, companyName is a string field and includes the properties customLabel and searchValue. The searchValue property will contain the result of the query. However, externalId is a lookup and has expandable searchValues, including internalId, externalId, type, and name. Keep in mind that when doing your transformation mapping in the next section below, you may need to map different properties depending on the type.

4. 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 perform the search.

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

   

   Note

   The icons for 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 update 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 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 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.

Basic search

The following walks through defining the Target and Response components for the basic search operation that was used as an example under Creating a Search Operation.

• Target: Double-click on the Target icon. In the example, we will record the query response from NetSuite to a CSV file. Click the Create New Target button, give your target a Name (e.g. CustomerBasicSearchResponse.csv). 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 where we want to save the data. In the example, the Folder is 'C:\NetSuiteSampleFiles' and the Filename is 'CustomerBasicSearchResponse.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.

• 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 source file format definition created during NetSuite connector create (i.e. "Example Customer Flat 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

     During the transformation mapping used in this example, a loop node became present on the "addressbook" element as indicated by the location of the thick black line shown below. Any data element that can have multiple records, such as "addressbook" in NetSuite, can cause the loop node to loop off of that object rather than the main object such as Customer.

     

     Note

     To fix the loop node, in this example we specify in a script to use the first record only by adding '#1' after the loop node in the field variable name. Alternatives include specifying custom logic to pull the default billing or shipping address, or modifying your target data structure to also support multiple address.

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

       <trans>
       searchResponse$searchResult$recordList$record.Customer$addressbookList$addressbook#1.addressbookAddress$addr1$
       </trans>
       

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

       <trans>
       searchResponse$searchResult$recordList$record.Customer$addressbookList$addressbook#1.addressbookAddress$city$
       </trans>
       

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

       <trans>
       searchResponse$searchResult$recordList$record.Customer$addressbookList$addressbook#1.addressbookAddress$state$
       </trans>
       

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

       <trans>
       searchResponse$searchResult$recordList$record.Customer$addressbookList$addressbook#1.addressbookAddress$zip$
       </trans>
       

     Your final transformation mapping should look similar to that shown below:

     

Expanded search

The following walks through defining the Target and Response components for the expanded search operation that was used as an example under Creating a Search Operation.

• Target: Double-click on the Target icon. In the example, we will record the query response from NetSuite to a CSV file. Click the Create New Target button, give your target a Name (e.g. CustomerExpandedSearchResponse.csv). 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 where we want to save the data. In the example, the Folder is 'C:\NetSuiteSampleFiles' and the Filename is 'CustomerExpandedSearchResponse.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.

• 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, we will need to create a new file format definition because the expanded search includes an additional field for Contact not used in the other examples.

     To create the new file format, 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 with Contact"). We can start with a text definition based off of our existing CSV file used for the other examples and then add the additional field. To do this, select Create From File, then in the popup browse to the local 'CustomerCreateRequest.csv' file. In this next screen, click New and add an additional field called "Contact" to the file format definition.

     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

     During the transformation mapping used in this example, a loop node became present both on the "addressbook" element and on the "contact" element as indicated by the location of the thick black lines. To fix the loop nodes, in this example we specify in a script to use the first record only by adding '#1' after the loop node in the field variable name. The '#1' was added in the script on the target Address, City, State, Zip, and Contact fields. For more explicit instructions on how to do this, see the example provided in Basic Search above, which also experienced a loop node issue, or reference the final mapping in the Jitterpak.

     Your final transformation mapping should look similar to that shown below:

     

Advanced search

The following walks through defining the Target and Response components for the advanced search operation that was used as an example under Creating a Search Operation.

• Target: Double-click on the Target icon. In the example, we will record the query response from NetSuite to a CSV file. Click the Create New Target button, give your target a Name (e.g. CustomerAdvancedSearchResponse.csv). 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 where we want to save the data. In the example, the Folder is 'C:\NetSuiteSampleFiles' and the Filename is 'CustomerAdvancedSearchResponse.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.

• 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 source file format definition created during NetSuite connector create (i.e. "Example Customer Flat 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. Notice that on the source side, you only have the fields you selected in the search. Then drag and drop each field you would like to be mapped from one tree to the other.

     Your transformation mapping should look similar to that shown below:

     

Saved search

The following walks through defining the Target and Response components for the saved search operation that was used as an example under Creating a Search Operation.

• Target: Double-click on the Target icon. In the example, we will record the query response from NetSuite to a CSV file. Click the Create New Target button, give your target a Name (e.g. CustomerSavedSearchResponse.csv). 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 where we want to save the data. In the example, the Folder is 'C:\NetSuiteSampleFiles' and the Filename is 'CustomerSavedSearchResponse.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.

• 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 source file format definition created during NetSuite connector create (i.e. "Example Customer Flat 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.

     Your transformation mapping should look similar to that shown below:

     

Deploying and executing the operation

With the NetSuite search 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\' with the name of the type of search performed. The file should contain data for your specific search within each column that you mapped to the output file.