NetSuite Collectors

4 Required Steps Setting Up NetSuite Collection

1. NetSuite Saved Search Collector

2. NetSuite Web Query Collector

3. NetSuite Bulk Export Collector

4. Configuring the Collector in DataBlend

1. NetSuite Saved Search Collector

NetSuite Saved Search collector is based on Saved Search that is a powerful tool within NetSuite. A key advantage of NetSuite’s Saved Searches is that anyone can use them. NetSuite displays fields and join fields which allow to pull almost every piece of data from associated records by a Saved Search.

Create RESTlet Script Record in NetSuite

DataBlend uses NetSuite RESTlet functionality to run a saved search and collect the results. For this to work, a NetSuite administrator must create the saved search and RESTlet, and then a DataBlend user can configure the collector.

These instructions assume the Saved Search has already been configured.

Configure the RESTlet

1. Download the DataBlend saved search RESTlet from the link above.

2. From the Customization menu, create a new script.

   

3. Mouse over the downward chevrons and then click the '+' button.

   

4. Enter a file name, select the script file downloaded earlier and save the record.
   Important! File name should have .js at the end of the name.

   

5. Create the script record.

   

6. Enter a name for the script.
   Important! File name should have .js at the end of the name.

   
7.  

   Enter GetSavedSearch into GET FUNCTION field. (This step only applies when collecting Saved Searches, all other scripts can skip this step)
   

    

8. Save and Deploy the script.
   

    

9. Set the deployment status to Released and set the security access as appropriate, then save to deploy the script.

10. Note the External URL for the DataBlend collector configuration.

Find the ID of the Saved Search

1. Use the Report → Saved Searches menu option to open the list of NetSuite OpenAir saved searches.

   Identify the internal id of the search results to be collected by DataBlend.

Create Saved Search in NetSuite

A Saved Search is a request for information. By specifying criteria and results information in NetSuite we are able to search through millions of records in NetSuite to pinpoint exactly what is needed. Saved Search can have many advanced search filters and results display options.

To create a Saved Search go to Reports -> New Search

DEFINE A SAVED SEARCH

Scroll down and select a record type for the search. For General Ledger we want to select a Transaction. We can also select Customer, Item, etc. There are different needs depending on the client, but most of the time we select Transaction.

We set up filters on the next page. Filters can be based on Dates, Accounts, etc.

Select Date range in the pop-up window.

Date can be selected as relative dates or specific dates.

We can select Start of this fiscal year. This works when we don’t have Balance Sheet by balances, but use Transaction for Balance Sheet by delta, Income Statement and Transactions. Otherwise we have to make sure that it matches to Balance Sheet by balances which is saved in Saved Reports. (Reports  -> Saved Reports)
Click Set when selected.

Go into the Results tab, where we have all the fields that can be brought in.

 

We can Create Saved Search for now and see all the fields to decide which fields are required to add or remove.

Click Preview.

This is the preview of what we get. We got 2010 in Period as we based our Saved Search on Date and not on Period.

We can Return To Criteria and change to Period.

Periods work differently. In pop-up window select Period and leave the second search field blank. This will also going to get you this year data for This Fiscal Year to Period.

If we preview now we are going to get only this Fiscal Year. It depends on the client where they are going to look at dates or periods. A lot of them look at periods.

ADD/REMOVE FIELDS

Return To Criteria -> Results tab. To remove field, click on it and select Remove.

LABELS

If there more then one field with the same name, we have to bring labels into the DataBlend. Name can be a name of the field for Account Name, Customer Name or Vendor Name. Search at the bottom of the list (scroll all the way down) for the fields with three dots.

In pop-up window select Name.

In Custom Label call it Account. When it comes into the DataBlend it will be called Account, instead of Name. Best practice is to have custom label for all the fields. It will guarantee getting the right data.

FREQUENTLY USED FIELDS

This depends on a client. Sometimes their structure in NetSuite can be different to somewhere else, like Account can be just part of the Account Name and needs to be concatenated with something else. A client will tell what fields we need to bring in. We need to ensure that all columns for the fields out of NetSuite that are going to make up the fields for a destination system.

• Something for Account (Account Number Account Name, etc)

• Something for Date (Period, Date, etc)

• Dimensions (Customer, Vendor, etc.)

• Document Number, Type (sometimes)

• Accounting Period Field (Start and End Dates are located there)

• Memo, Transaction Type (Check or Credit Card) - usually for Transactions

• Level (if it’s going into Adaptive)

• Location (part of the Level process, select Name (no hierarchy) to have the most granular piece)

DEFINE AUDIENCE FOR SAVED SEARCH

Go to Audience tab and select all the fields as in the screenshot below. Department and Subsidiaries might become unchecked when Public checked. This is be OK but we need to make sure that all departments are brought in. Click Save.

By default, only administrators and a saved search's owner (creator) can edit its definitions. Users can run and view results of a public saved search.

LINKS TO EXTERNAL RESOURCES

• Produce NetSuite OpenAir Balance Sheet Amounts via Saved Search

 

2. NetSuite Web Query Collector

NetSuite Web Query collector is based on NetSuite Reports. Reports provide all the information needed for everyday use. They are easy to manipulate, visually appealing and present data in a way that is easy to understand for the business user.

Note: You don’t need to create NetSuite credentials in DataBlend platform to collect NetSuite data with Web Query Collector.

Add a New Collector with the following fields:

Complete the configuration fields:

Field	Required/Optional	Comment
Type	Required	File
Name	Required	Free text
Data Source	Required	Select from list of configured data sources or create a new one.
File Collector Type	Required	Web Query File
Schema Name	Required	Free-text. Used when writing queries against the collected data. If a schema with this name does not already exist, one will be created under the selected data source.
File	Required	Click or drag .igy file downloaded from NetSuite by “Export - Excel Web Query”
Content	Required	Will be populated once .igy file uploaded. Alternatively, open .igy file in text editor and copy/paste its content into this field.
Parameters email address	Required	NetSuite Admin email address

 

Parameters email field appears when .igy file is uploaded and Content field populated. Enter email address of NetSuite Admin and Save the collector.

Create Web Query Report in NetSuite

A report in NetSuite is a saved set of data results. NetSuite allows customizing most standard reports using Report Builder. You can modify the appearance and information from reports with numerous features to create the perfect report: add new columns with related information gathered from fields in the system, add simple formulas to calculate values into the reports, set filters to classify the data and sort the data in different ways.

1. To create a Report go to Reports -> New Report

2. Select the type of report you would like to create from the options

3. Enter Report Title and select the preferable and click on More Customization.

4. Customize your report by adding more columns, formulas or filters. Available columns can be seen by expanding +Account or +Transactions on the left. Formula fields can be added by clicking on Add Formula Field.

5. Click on More Options and select ALLOW WEB QUERY box. Save the Report.

6. Look for the “Export - Excel Web Query” icon at the bottom right of the page. Click on it to download .igy file. Note the location where the file downloaded.

The next step is to create a NetSuite Web Query collector in DataBlend.

3. NetSuite Bulk Export Collector

NetSuite Bulk Export collector provides an option to collect a large number of resources, such as customers or employees, all at once. This collector allows to pull all available data for an object to explore fields and their values for integration but the resulting data is not easy to understand for the business user.

Configuring the Collector in DataBlend

1. Add a new collector

    

2. Complete the configuration fields and save the collector.

Filters operate according to the NetSuite documentation with the ability to “AND” and “OR” multiple filters together. Parameters are supported using notation.

Filter examples:

• [["lastmodifieddate", "after", "12/21/2022"]]

• [["account", "is", "23"]]

4. Configuring the Collector in DataBlend

1. Add a new collector.

2. Complete the configuration fields and save the collector.

Search ID can be found in NetSuite in the URL field for Saved Search results.

1. Use the Collect Now button to run the collection and initialize the schema.

Flattening Results

Some columns from a NetSuite Saved Search will be returned as embedded objects; a single column may itself include several related fields. In the example below, the collector’s Flatten Results value is “false”; in the collected data account and department are embedded objects while amount is a simple value.

Flattening the collected data places each field of each embedded object in its own column making it easier to query. In the example below, the collector’s Flatten Results value is “true”; the nested fields from each object are placed in their own columns.

Flattening the results can give a cleaner version, but sometimes can omit some columns from the collection. The following steps are recommended:

• Run collector with the Flatten Results OFF.

• In the Advanced tab change the first field Schema Update Type to Add Only.

• Set Flatten Results ON.

• Collect again.

Once the data is collected, it needs to be checked if all the required columns are present. If some columns are missing, but the column name are known, then they can be added manually in the schema. This can be avoided by firstly bringing in the unflatten data, and then flatten it.

Using Labels

This setting will collect fields which have labels added. Note that the fields without labels will not be included in schema. If the Schema Update Type is set to Add Only in Advanced tab, the fields will stay in schema, but won’t be populated with values. When collecting data with Use Labels enabled make sure that all the required fields in Saved Search have labels added.