Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Utilizing the NetSuite Deletion Log

Stitch's NetSuite integration includes a table called netsuite_deleted which 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.

Table Schema

The attributes of the netsuite_deleted table include the record type (ex: invoice), name, deleted date, custom record flag, and internal ID. The internal ID is the numerical ID of the record, while the name is more reader-friendly. Here’s a look at some sample records:

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
journalEntry  145740 Journal #JV17589  false  2016-08-06T07:11:13.000-07:00

Custom Records

It’s important to note that custom record types will look a little different than the NetSuite defaults:

  • The customRecord column will contain true values,
  • The type column will contain a numerical ID, and
  • The internal ID will display in both the internalId and name columns.

Here’s a look at an example of a custom record:

type internalId name customRecord deletedDate
19 128 128 true 2016-07-21T12:05:26.000-07:00

Accounting for Deleted Records

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’

Note the LOWER function in the following query. Because Redshift is case-sensitive and NetSuite uses camel case, 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)

Removing Deleted Records

To remove deleted records, you can run a query like the one below:

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;

RELATED

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!