Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Using the NetSuite Data Source

Starting with Adaptive Integration 2017.1, the NetSuite Adapter supports NetSuite’s 2016.2 SuiteTalk Web Services. All adapters provisioned prior to 2017.1 will be on NetSuite’s 2013.2 Web Services offering. There is no automatic migration of Adaptive’s NetSuite Adapter from NetSuite 2013.2 to NetSuite 2016.2

 Supported NetSuite Tables

The table below shows information about the tables supported in NetSuite Integration. The columns for this table show the following information:

  • Integration Table: The name of the table as shown in the Adaptive Integration data source.
  • Table Source: The name of the table as used in the NetSuite Schema Browser.
  • Access: The access mechanism for each NetSuite table. The types are:
  • “Search” means Integration brings across all records that match the search criteria using the specified API. “Constrained search” means Integration imposes additional constraints on the search in addition to any search constraints NetSuite may impose.
  • “Get all” means Integration brings across all the records using the specified API.
  • “Differential” means Integration brings across only the data that has changed.
  • Comments: Comments about the tables.

Integration also supports custom NetSuite tables that are exposed by the NetSuite Web Services API. You can choose which table/columns to import for each one.

 

Integration Table Table Source Access Comments
Account AccountSearchRowBasic Search  
AccountingPeriod AccountingPeriodSearchRowBasic Search  
AdaptivePostingTransactionSummary    

Table created by Adaptive in the Integration data source, not available in NetSuite. Joins the PostingTransactionSummary table with the standard category tables (Account, Time Period, Subsidiary, Department, Class, Location, Item,Customer)

AllCustomLists CustomList Get all  
Budget BudgetSearchRowBasic Search  
BudgetExchangeRate Special: getBudgetExchangeRate Constrained search  
CalendarEvent CalendarEventSearchRowBasic Search  
Campaign CampaignSearchRowBasic Differential  
CampaignCategory CampaignCategory Get all  
CampaignFamily CampaignFamily Get all  
Classification ClassificationSearchRowBasic Search  
ConsolidatedExchangeRate Special: getConsolidatedExchangeRate Constrained Search  
Contact ContactSearchRowBasic Search  
Currency Currency Get all  
CustomerCategory CustomerSearchRowBasic Differential  
CustomerStatus CustomerCategorySearchRowBasic Search  
DeletedRecord Special: getDeleted Constrained search Not for use by end user
Department DepartmentSearchRowBasic Search  
Employee EmployeeSearchRowBasic Differential  
EntityGroup EntityGroupSearchRowBasic Differential  
ExpenseCategory ExpenseCategorySearchRowBasic Search  
Item ItemSearchRowBasic Differential  
Job JobSearchRowBasic Differential  
JobStatus JobStatusSearchRowBasic Search  
JobType JobTypeSearchRowBasic Search  
Location LocationSearchRowBasic    
NetSuiteServerTime Special: getServerTime    
Opportunity OpportunitySearchRowBasic    
Partner PartnerSearchRowBasic    
PostingTransactionBalance   Constrained search

Table created by Adaptive in the Integration source, not available in NetSuite. This table contains period ending balances.

PostingTransactionSummary Special: getPostingTransactionSummary Constrained search  
ProjectTask ProjectTaskSearchRowBasic Differential  
PromotionCode PromotionCodeSearchRowBasic Search  
SalesTaxItem SalesTaxItem Get all  
Subsidiary SubsidiarySearchRowBasic Search  
SupportCase SupportCaseSearchRowBasic Differential  
SupportCasePriority SupportCasePriority Get all  
SupportCaseStatus SupportCaseStatus Get all  
SupportIssue IssueSearchRowBasic Search  
SupportSolution SolutionSearchRowBasic Search  
SupportTopic TopicSearchRowBasic Search  
Task TaskSearchRowBasic Search  
TaxGroup TaxGroup Get all  
TaxType TaxType Get all  
Timebill TimeBillSearchRowBasic Differential  
Transaction TransactionSearchRowBasic Differential  
Vendor VendorSearchRowBasic Differential  

Commonly Used Tables in AI

The following tables are made importable out-of-the-box in the NetSuite Adapter:

  • AdaptivePostingTransactionSummary (primary source table for Planning)
  • Account (mapped to Adaptive Accounts)
  • Classification (optional mapping)
  • Customer (optional mapping)
  • Department (optional mapping)
  • Items (optional mapping)
  • Location (optional mapping)
  • Posting Transaction Summary
  • Subsidiary (usually Mapped to Adaptive Levels)

The AdaptivePostingTransactionSummary and PostingTransactionSummary tables return a monthly delta, whereas the PostingTransactionBalance table returns period balances. The AdaptivePostingTransactionSummary and PostingTransactionSummary tables support the standard categories (Account, Time Period, Subsidiary, Department, Class, Location, Item, Customer). If you need additional categories you must use the Transaction table.

Differentially Updated Tables

In the current NetSuite data source, the following tables are differentially updated:

  • Campaign
  • Customer
  • Employee
  • EntityGroup
  • Item
  • Job
  • Opportunity
  • Partner
  • ProjectTask
  • SupportCase
  • TimeBill
  • Transaction
  • Vendor

In addition, all Custom Record types are differentially updated. You cannot use the Differential Date Column in your data import filter for differentially updated tables. The system does not prevent you from trying to do this, but it will cause the differential update mechanism to fail.

You can limit the amount of data imported in a differential table when you are "exploring," that is, changing columns and filter settings. There is a mechanism in the data source to short circuit the full load of differential data.

The table below contains all the differential tables along with their differential and creation date columns.

Table Differential Date Column Creation Date Column
Campaign LastModifiedDate CreatedDate
Customer LastModifiedDate DateCreated
CustomRecord (*) LastModified Created
Employee LastModifiedDate CreatedDate
EntityGroup LastModifiedDate -- None --
Item Modified Created
Job LastModifiedDate DateCreated
Opportunity LastModifiedDate DateCreated
Partner LastModifiedDate DateCreated
ProjectTask LastModifiedDate CreatedDate
SupportCase LastModifiedDate CreatedDate
TimeBill LastModified DateCreated
Transaction LastModifiedDate DateCreated
Vendor LastModifiedDate DateCreated

(*) - This applies to all Custom Records.

If you specify a greater-than or a great-than-or-equal filter on the Creation Date Column for your table, the code bypasses the full import and just differentially updates from the specified date and time. This is helpful for limiting the imported data because when you start exploring a large differentially uploaded table, you can set the filter to extract only the last few days’ (or weeks’) worth of data. This kind of load runs very quickly. You can then figure out what columns you need and what other filter conditions you want to use. Once you are satisfied, you can remove the creation date filter and restart the full data load.

For some tables you may not ever want records older than a certain date. For these tables you can continue to use the creation date filter with an appropriate value.

Supported NetSuite Data Types

As data from NetSuite is imported into Integration, the 'type' of that data must be mapped to one of the data types supported by Integration.

Standard NetSuite Data Fields

For standard NetSuite data fields in ‘searched' tables, the following mappings are used:

NetSuite Standard Search Data Type Integration Data Type
SearchColumnStringField Text
SearchColumnSelectField Text
SearchColumnEnumSelectField Text
SearchColumnDoubleField Float
SearchColumnBooleanField Boolean
SearchColumnLongField Integer
SearchColumnTextNumberField Text
SearchColumnDateField DateTime

Standard NetSuite Data Fields in Tables

For standard NetSuite data fields in tables (which are always fetched in their entirety), the following mappings are used:

NetSuite Standard Data Type Integration Data Type
String Text
RecordRef Text
Double Text
Boolean Float
Long Boolean
System.DateTimeFloat Integer

Custom Fields

The NetSuite Data Source supports a subset of the possible custom NetSuite custom fields.

Custom fields are supported on the following record types:

  • CRM Custom Fields
    • CalendarEvent
    • Campaign
    • SupportCase
    • SupportIssue
    • SupportSolution
    • ProjectTask
  • Task
  • Custom Entity Fields
    • Contact
    • Customer
    • Employee
    • Entity Group
    • Job
    • Partner
    • Vendor
  • Custom Item Fields
    • Item
  • Custom Transaction Fields
    • Transaction Body
    • Transaction Column
    • Transaction Item
  • Other Custom Fields
    • Account
    • Classification
    • Department
    • Location
    • PromotionCode

 Note that custom fields must have the "STORE VALUE" attribute checked in order for the field to be exposed on the NetSuite web services API.

Custom Field Data Types

Adaptive Integration supports all of the defined NetSuite custom field data types. However, because Integration internally implements only five basic data types, the NetSuite custom field data types are mapped to most appropriate Integration data types. The following table illustrates this mapping:

NetSuite Data Type Integration Data Type Comment
_checkBox Float  
_currency DateTime  
_date DateTime  
_decimalNumber Float  
_document Text Reference to another record
_eMailAddress Text  
_freeFormText Text  
_help Text StoreValue not set. NetSuite won't allow retrieval of this type.
_hyperlink Text  
_image Text Reference to another record
_inlineHTML Text  
_integerNumber Integer  
_listRecord Text Reference to another record
_longText  Text  
_multipleSelect Text Reference to zero or more other records of a given type
_password Text  
_percent Float  
_phoneNumber Text  
_richText Text  
_textArea Text  
_timeOfDay DateTime  
     

NetSuite Transaction Types

The following is a list of NetSuite transaction types supported through the 2016.2 NetSuite endpoint by the NetSuite adapter within Integration. If a transaction table or NetSuite Saved Search contains transaction types that are not supported through the NetSuite web services, the results of the Saved Search may produce unexpected results. 

See NetSuite's Help Center for more information (NetSuite Login required): https://system.na1.netsuite.com/app/help/helpcenter.nl

Adaptive Supported NetSuite Transaction Types Values for the 2016.2 Endpoint

Supported NetSuite Transaction Types Values
_assemblyBuild
_assemblyUnbuild
_binTransfer
_binWorksheet
_cashRefund
_cashSale
_check
_creditMemo
_custom
_customerDeposit
_customerPayment
_customerRefund
_deposit
_depositApplication
_estimate
_expenseReport
_inventoryAdjustment
_inventoryCostRevaluation
_inventoryTransfer
_invoice
_itemFulfillment
_itemReceipt
_journal
_opportunity
_paycheckJournal
_purchaseOrder
_requisition
_returnAuthorization
_salesOrder
_transferOrder
_vendorBill
_vendorCredit
_vendorPayment
_vendorReturnAuthorization
_workOrder
_workOrderClose
_workOrderCompletion
_workOrderIssue

 

Additional Information

The following sections describe some additional considerations for accessing NetSuite data.

Data Import Filter

Each field in a search can have a maximum of one filter. The filters are ANDed together by NetSuite when the search query is executed.

Only tables that are accessed via a "search" mechanism can have synchronization filters set.

In addition, only those fields that exist in both [TableName]SearchRowBasic and in [TableName]SearchBasic can be used in synchronization filters.

Querying NetSuite Directly from Staging

When querying NetSuite directly (as opposed to querying records imported into staging from NetSuite) filter expressions are limited to the capabilities exposed by NetSuite via Web Services.

  • Simple column filters with Comparison and Logic Expressions may be used when querying NetSuite.
  • Filters can be ANDed together but can not be ORed together.
  • Operators (+, -, /, *, $, ||) may not be used.
  • Scalar functions may not be used.
  • Case statements may not be used.
  • To filter on a custom column, the custom column must be marked for import.
  • Some column filters require specific NetSuite features to be enabled in order for the filter to work. 

Some tables and some columns do not support filtering.

Excluded Fields

A number of NetSuite fields are excluded from import for various reasons. The table below shows some of these excluded fields.

NetSuite field Reasons for exclusion
Customer: ccExpDate Credit card information is excluded for privacy protection.
Customer: ccInternalId Credit card information is excluded for privacy protection.
Employee: socialSecurityNumber Excluded for privacy protection.
SalesTaxItem: zip A delimited list of zip codes. This text field can be exceedingly long (longer than the default 1,024 character limit for a text field) and is not considered particularly useful for Integration operations.
Transaction: ccExpDate Credit card information is excluded for privacy protection.

Information About Fields

Additional information about NetSuite standard and custom fields:

  • Text fields are limited to a maximum size of 1024 characters.
  • Customizations to NetSuite standard fields are not supported.
  • Custom hidden fields are visible. (NetSuite exposes them through their web services.)
  • Only NetSuite custom fields that have the "Store Value" attribute set are imported into Adaptive Integration.

Saved Searches

See the Adaptive Integration section on Importing NetSuite Saved Searches for how to configure a NetSuite Saved Search.

Drill-through from Adaptive Planning Back to NetSuite

Customers can enable drill-through on data imported from NetSuite. Drill-through links will become available on Planning sheets and reports for actuals imported from NetSuite. In order to configure drill-through for NetSuite, you should ensure that your internal identifiers are used as part of column mapping in the Planning loader. You will need to configure a NetSuite external system and attach it to a profile that is used by the Planning loader.

If you are configuring multiple columns on the NetSuite side to map to a single Planning dimension, then those NetSuite columns need to be combined in the data source using a Tuple SQL column. For more details, refer to the Integration sections on column/data mapping and profiles.

Tips and Best Practices

Best Practice for Setting Up Planning for Import of GL Actuals

To import GL actuals using the Transaction table, it is recommended to set the GL accounts in Planning to Actuals by monthly delta as shown below. In this method, planning takes care of accumulating amounts on balance sheet accounts.

Best Practice for Dealing NetSuite Timeout Messages

One of the errors that NetSuite can return is:

“Your search has timed out. If your search includes the 'contains' operator, try using 'hasKeywords' instead. If your search includes broad search criteria, try narrowing the criteria.”

This error indicates that the request ran too long in NetSuite. This condition usually occurs because the filter criteria in the request would create an extremely large result. NetSuite allows approximately 3 minutes of processing for a request. (Note that this is time allowed for the actual database query within NetSuite. The amount of time required for a result to be returned can be much longer since it is gated by NetSuite's traffic policies.)

There are several ways to address this error. A good general guideline is to make sure that the request being generated is as specific as possible. For example, only include transaction types that are actually used by the loaders. Another way to prevent or resolve this error is by using the Backfill Batch Duration property. For tables that are differentially updated, this property determines the size of the update chunk. The Backfill Batch Duration is defaulted to 168 hours (one week). However, this value may be too high if you are dealing with a request for high density data. Lowering this value reduces the size of the individual request by tightening the time filter, which can prevent or resolve this error.

The minimum value for the Backfill Batch Duration property is one hour. The tradeoff here is that you are "nibbling" at the data rather than "gulping" it, which means that many more requests have to be sent to NetSuite in order to complete the update. For instance, reducing 168 to 24 means that you are only trying to grab one day at a time, but it means seven times as many requests.

Because there is no practical way to tell the density of data ahead of time, you may want to experiment with your queries and adjust the Backfill Batch Duration property as needed to deal with the issue.

Creating Individual Lists from the AllCustomLists Staging Table

A join table can be used to extract/filter an individual list from the AllCustomLists staging table. For example, to create a table that has the members of the list called Resolutiontime (with InternalId 19), take the following steps:

In this join expression the InternalId value is the 'selector' that picks the particular custom list that you want.

  • Create a new Join table. A reasonable name for the table would be "CustomList[*]" where [*] is the 'Name' value of the list that is being extracted. So for this example the name would be "CustomListResolutiontime".
    • Set the Primary Table to "AllCustomLists".
    • Add a Join.
      • Primary Table: AllCustomLists
      • JoinedTable: AllCustomLists
      • Type: Inner
      • Column Prefix: (blank)
      • Join Expression: P."InternalId" = '19' and R."InternalId" = '19' and P."ValueId" = R."ValueId"

In this join expression the InternalId value is the 'selector' that picks the particular custom list that you want.

Then in the join table pick only the columns from the primary table that you need (usually just "Value" and "ValueId").

The following screenshot captures this result:

Tip for Preparing Account and Level Data

Even though Account and Level data varies from one customer to another, here are a couple of examples to show how a data designer can create SQL columns in the staging area to prepare data before loading into Planning.

Account:

CASE WHEN "acct.Number" is null THEN TRIM("acct.Name") ELSE TRIM("acct.Number") END

Level:

 CASE WHEN "Department.Name" is null THEN 'No Department' ELSE TRIM("Department.Name") END || ' ' || "Location.Name"

Tip for Using Business Rules

The data designer allows you to create business rules for easy loading. Here are some tips for handling rules relating to account information:

  • Account codes must be numerical values so that it’s easy to add the range of GL accounts to be reversed.
  • Avoid using underscores for these type of accounts. If you use an underscore, you have to manually add all account codes that you need to change the signs for.
  • Account codes cannot have embedded spaces.

Timezone Configuration

The Account Time Zone for the NetSuite data source in Adaptive Integration must match the timezone found in NetSuite’s GUI under Preferences > Localization Time Zone.

If the two do not match, do one of the following:
 

  • Change the Account Time Zone within Adaptive Integration so they match.
  • Change the timezone setting within NetSuite for the user associated with the data source so they match.

External References

The core resource for the NetSuite API is the online help system at the NetSuite website, http://www.netsuite.com. NetSuite generates a PDF of this online help with each release of their product. A recent copy can be found by searching for "SuiteTalk (Web Services) Records Guide.”

NetSuite also has a user group that contains useful information at https://usergroup.netsuite.com/users/index.php. This is a closed group, so you need to apply to get access to it.

  • Was this article helpful?