Bulk delete of NetSuite Custom Table

[asorgi]

Hello all,

I'd like to try and pick your brains for a solution to my question.  I'll start with the business requirement:

1. I need to pull out of NetSuite Spend by Supplier for the last 12 months including transaction count, payment count, etc
   1. I've written this query in SuiteQL and it runs in my recipe just fine
2. Our Purchase 2 Pay (P2P) solution holds also supplier information which we want to join to the associated client NetSuite system suppliers so that in step 1 we don't bring back ALL NetSuite Suppliers, we only want suppliers who are also in our P2P system from NetSuite
3. So my first thought was to create a custom table in NetSuite and then do a bulk insert to that table, then use that table in the SuiteQL script as an inner join
   1. Again, I have this working via SuiteQL query
4. I would then return the result, convert it to CSV and deliver it back in subsequent steps within the recipe
   1. The result set could be up to 50k rows of data

Now my problem that I'd appreciate some help on:  I need to have a way to delete the rows out of the custom table so that it doesn't continue to grow, however the only way I can currently see to do this is via the delete by ID option which isnt viable with the number of rows being inserted to the custom table.

Alternatively, am I thinking about this the wrong way and there is a better way to address the requirement?  

Additionally SuiteQL only allows a select statement therefore I can't:

1. Select into a temp table via SuiteQL in NetSuite for the action and have the table automatically dropped when the session/connection terminates
2. Delete * from customtable via SuiteQL

I started looking into possibly writing a custom script in NetSuite to do the delete of all rows in my custom table and then seeing if I can call that script via Workato but not sure if that's viable either.

Hopefully that's enough information for you but not too verbose.  Any/all help/guidance would be appreciated.

Thanks,

Andy

[beatrice-talens · Accepted solution]

Hi,

NetSuite connector can only delete records by ID. I understand that it's not viable. Execute SuiteQL query in NetSuite action only supports SELECT query.

I suggest using a custom action instead, you may refer to the NetSuite API doc. 

Thank you!