NetSuite (v10-15-2015)

NetSuite integration summary

Stitch’s NetSuite integration replicates data using the NetSuite SuiteTalk API (v2015_1). Refer to the Schema section for a list of objects available for replication.

NetSuite feature snapshot

A high-level look at Stitch's NetSuite (v10-15-2015) integration, including release status, useful links, and the features supported in Stitch.

NetSuite table reference

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 recommend setting this table 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 and 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:

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 query would return all invoice records that exist in the netsuite_transaction and netsuite_deleted tables:

   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

The following table contains the transaction types Stitch’s NetSuite integration currently supports.

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.