Release Status Released Supported By Stitch
Availability Premium Status Page NetSuite Status Page
Default Historical Sync 1 year Default Replication Frequency 30 minutes
Whitelisting Unsupported Destination Incompatibilities None

Connecting NetSuite

Connecting your NetSuite data to Stitch is a six-step process:

  1. Locate your NetSuite Account ID
  2. Create a Stitch NetSuite Admin user
  3. Retrieve the Stitch NetSuite user’s Role ID
  4. Add NetSuite as a Stitch data source
  5. Define the Historical Sync
  6. Define the Replication Frequency
  7. Set tables to replicate

Prerequisites

You must have Administrator permissions in NetSuite. This is required to complete parts of the setup.

Locate your NetSuite Account ID

NetSuite account ID in Web Services Preferences

  1. Sign into your NetSuite account.
  2. Click the Setup option in the top navigation menu, then Integration > Web Services Preferences.
  3. In the Primary Information, locate the Account ID field as shown in the image on the right.

Note: If your Account ID contains a suffix - 1234567_SB2, for example - it should be included when entering the ID into Stitch.

Create a Stitch NetSuite Admin user

To connect NetSuite to Stitch, we recommend that you create a Stitch-specific Admin user for us. We suggest this approach for a few reasons:

  1. This will ensure that Stitch is easily distinguishable in any logs or audits.
  2. NetSuite’s API has some limitations that could make it difficult or impossible for Stitch to replicate data. For example: a single NetSuite user is only allowed to have one open API session at a time. If there’s another connection elsewhere, Stitch will run into problems replicating data.

After you’ve created the Admin user, move onto the next step.

Retrieve the Stitch NetSuite user’s Role ID

All Roles in NetSuite have a Name - for example, Accountant - and Role ID, or Internal ID number. Stitch requires the Role ID to successfully create a NetSuite integration.

Locate the Role ID

Role IDs can be found on the Manage Roles page in NetSuite. From your dashboard, click Setup > Users/Roles > Manage Roles.

Locate the Role of the user in the Roles list. The ID is located in a column called Internal ID:

The Internal ID column contains the user's Role ID.

If you don’t see the Internal ID column in the list, you may need to add it:

  1. Click the Edit View button.
  2. Click the drop-down menu and select Internal ID.
  3. Click Add.
  4. Click Save.

After you add the column to the Roles list, locate the ID for the user.

Add NetSuite as a Stitch Data Source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the NetSuite icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch NetSuite” would create a schema called stitch_netsuite in the destination. Note: Schema names cannot be changed after you save the integration.

  5. Enter the email address and password associated with the Stitch NetSuite user.
  6. Enter the Role ID - the numerical ID, not the name of the role - associated with the user entered above.

    Note: If this field is left blank, Stitch will use NetSuite’s default role ID for Admin roles, which is 3. If you receive an error when trying to save the integration, enter a 3 in this field and try saving again.

  7. In the Account ID field, enter your NetSuite account ID. If your Account ID contains a suffix - 1234567_SB2, for example - it should also be entered into this field.
  8. Select the Account Type - Production or Sandbox.

Define the Historical Sync

The Sync Historical Data setting will define the starting date for your NetSuite integration. This means that:

  • For tables using Incremental Replication, data equal to or newer than this date will be replicated to your data warehouse.
  • For tables using Full Table Replication, all data - including records that are older, equal to, or newer than this date - will be replicated to your data warehouse.

Change this setting if you want to replicate data beyond NetSuite’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Stitch offers two methods of creating a replication schedule:

  • Replication Frequency: This method requires selecting the interval you want replication to run for the integration. Start times of replication jobs are based on the start time and duration of the previous job. Refer to the Replication Frequency documentation for more information and examples.
  • Anchor scheduling: Based on the Replication Frequency, or interval, you select, this method “anchors” the start times of this integration’s replication jobs to a time you select to create a predictable schedule. Anchor scheduling is a combination of the Anchor Time and Replication Frequency settings, which must both be defined to use this method. Additionally, note that:

    • A Replication Frequency of at least one hour is required to use anchor scheduling.
    • An initial replication job may not begin immediately after saving the integration, depending on the selected Replication Frequency and Anchor Time. Refer to the Anchor Scheduling documentation for more information.

To help prevent overages, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Set tables to replicate

To complete the setup, you’ll need to select tables you want to replicate to your data warehouse.

Check out the Schema section to learn more about the available tables in NetSuite and how they replicate.

  1. In the Integration Details page, click the Tables to Replicate tab.
  2. Locate a table you want to replicate.
  3. To track a table, click the checkbox next to the table’s name. A green checkmark means the table is set to replicate.

  4. Repeat this process for all the tables you want to replicate.

Initial and historical replication jobs

After you finish setting up NetSuite, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


NetSuite Schema

For more detailed info on the attributes in each table, click the table names to check out NetSuite's documentation.

Table Name Replication Method Primary Key Notes
netsuite_account Full Table internalid
netsuite_accounting_period Full Table internalid
netsuite_app_definition Full Table internalid
netsuite_app_package Full Table internalid
netsuite_billing_schedule Full Table internalid
netsuite_bin Full Table internalid
netsuite_budget Full Table internalid
netsuite_calendar_event Key-based Incremental internalid
netsuite_campaign Key-based Incremental internalid
netsuite_charge Full Table internalid
netsuite_classification Full Table internalid
netsuite_contact Key-based Incremental internalid
netsuite_contact_category Full Table internalid
netsuite_contact_role Full Table internalid
netsuite_coupon_code Full Table internalid
netsuite_currency_rate Full Table internalid
netsuite_custom_list Full Table internalid
netsuite_customer Key-based Incremental internalid
netsuite_customer_category Full Table internalid
netsuite_customer_message Full Table internalid
netsuite_customer_status Full Table internalid
netsuite_custom_records Full Table internalid
netsuite_deleted Key-based Incremental internalid:_type See Deleted Records for info on using this table.
netsuite_department Full Table internalid
netsuite_employee Key-based Incremental internalid
netsuite_entity_group Key-based Incremental internalid
netsuite_expense_category Full Table internalid
netsuite_file Full Table internalid
netsuite_folder Key-based Incremental internalid
netsuite_gift_certificate Full Table internalid
netsuite_global_account_mapping Full Table internalid
netsuite_inventory_number Full Table internalid
netsuite_issue Full Table internalid
netsuite_item Key-based Incremental internalid
netsuite_item_account_mapping Full Table internalid
netsuite_item_demand_plan Key-based Incremental internalid
netsuite_item_revision Full Table internalid:_type
netsuite_item_supply_plan Key-based Incremental internalid
netsuite_job Key-based Incremental internalid
netsuite_job_status Full Table internalid
netsuite_job_type Full Table internalid
netsuite_location Full Table internalid
netsuite_manufacturing_cost_template Full Table internalid
netsuite_manufacturing_operation_task Full Table internalid
netsuite_manufacturing_routing Full Table internalid
netsuite_message Full Table internalid
netsuite_nexus Full Table internalid
netsuite_note Full Table internalid
netsuite_note_type Full Table internalid
netsuite_opportunity Key-based Incremental internalid
netsuite_originating_lead Key-based Incremental internalid:type
netsuite_other_name_category Full Table internalid
netsuite_partner Key-based Incremental internalid
netsuite_partner_category Full Table internalid
netsuite_payment_method Full Table internalid
netsuite_payroll_item Full Table internalid
netsuite_phone_call Key-based Incremental internalid
netsuite_price_level Full Table internalid
netsuite_pricing_group Full Table internalid
netsuite_project_task Key-based Incremental internalid
netsuite_promotion_code Full Table internalid
netsuite_resource_allocation Full Table internalid
netsuite_rev_rec_schedule Full Table internalid
netsuite_rev_rec_template Full Table internalid
netsuite_sales_role Full Table internalid
netsuite_site_category Full Table internalid
netsuite_solution Key-based Incremental internalid
netsuite_subsidiary Full Table internalid
netsuite_support_case Key-based Incremental internalid
netsuite_task Key-based Incremental internalid
netsuite_term Full Table internalid
netsuite_time_bill Key-based Incremental internalid
netsuite_time_entry Key-based Incremental internalid
netsuite_time_sheet Full Table internalid
netsuite_topic Full Table internalid
netsuite_transaction Key-based Incremental internalid:_type See Supported Transaction Types for a list of the transaction types Stitch will replicate.
netsuite_units_type Full Table internalid
netsuite_vendor Key-based Incremental internalid
netsuite_vendor_category Full Table internalid
netsuite_win_loss_reason Full Table internalid

Deleted Records

Stitch’s NetSuite integration includes a table called netsuite_deleted; this table contains a row for every deleted record that supports deletes. Accounting for deleted records is especially important if you’re performing any sort of aggregate function - for example, totaling invoices or balancing your books.

For this reason, we strongly recommend you set this table to sync when selecting tables to replicate.

netsuite_deleted Table Schema

The attributes of the netsuite_deleted table include:

  • type: This indicates the type of record. For example: invoice.
  • name: This is the name of the record. For example: Invoice #INV197
  • deletedDate: The date the record was deleted.
  • customRecord: This indicates if the record was a custom record.
  • internalId: This is the numerical ID of the record.

Custom records will look a little different than other records. In this case, you’ll see the following:

  • type: This column will contain a numerical ID.
  • name & internalid: The internalid of the record will display in both columns.
  • customRecord: This column will contain a true value.

For example: the first two records in this table are “normal” records, while the third is a custom record:

type internalId name customRecord deletedDate
invoice 124831 Invoice #INV197 false 2016-08-02T09:33:07.000-07:00
journalEntry 111366 Journal #JV13526 false 2016-08-04T12:01:22.000-07:00
19 128 128 true 2016-07-21T12:05:26.000-07:00

Accounting for Deleted Records

To account for deleted records, you can use a LEFT JOIN to tie deleted records back to the appropriate table. For example, the following SQL query would return all invoice records that exist in both the netsuite_transaction table and netsuite_deleted table:

SELECT * 
FROM netsuite_transactions tran 
LEFT JOIN netsuite_deleted del ON tran.internalId = del.internalId 
AND tran.type = invoice 
AND del.type = invoice

If you’re using a data warehouse that is case-insensitive (like Redshift), some queries may result in errors. If this occurs, try using LOWER to resolve the issue:

SELECT * 
FROM netsuite_transactions tran 
LEFT JOIN netsuite_deleted del 
ON tran.internalId = del.internalId 
AND LOWER(tran.type) = LOWER(del.type)

To filter out deleted records from other data, you can run a query like this one:

SELECT * 
FROM netsuite_transactions tran 
LEFT JOIN netsuite_deleted del ON tran.internalId = del.internalId 
AND LOWER(tran.type) = LOWER(del.type) 
WHERE del.deletedDate is null;

Supported Transaction Types

Because NetSuite includes so much under transactions, it may be a little difficult to know what to expect in the netsuite_transaction table. To give you a better idea, here’s a full list of what our integration will pull into this table:

AssemblyBuild CustomerPayment ItemFulfillment VendorBill
AssemblyUnBuild CustomerRefund ItemReceipt VendorCredit
BinTransfer Deposit Journal VendorPayment
BinWorksheet DepositApplication Opportunity VendorReturnAuthorization
CashRefund Estimate PaycheckJournal WorkOrder
CashSale ExpenseReport PurchaseOrder WorkOrderClose
Check InventoryAdjustment Requisition WorkOrderCompletion
CreditMemo InventoryCostRevaluation ReturnAuthorization WorkOrderIssue
Custom InventoryTransfer SalesOrder  
CustomerDeposit Invoice TransferOrder  


Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.