Removing data from NetSuite is a common request in my line of work. While Techfino is known for its ability to safely backup and remove files using CleanSweep File Manager, and transactions/records using CleanSweep Archive & Purge, in some cases a full blown SuiteApp isn’t what is required. In cases where data just needs to be removed from the system whether its test data, duplicated data, invalid data, or just unwanted data you can leverage the solution that we’re conveniently sharing as a public bundle. These requests can cause undue stress, especially in a production environment, and call for a developed process to ensure nothing outside of the desired criteria is purged.
After an angry phone call, or two in my case, you start to develop a process to get proper alignment on what needs to be deleted. Techfino follows a structured process when it comes to this activity:
- Identify the data to be deleted with a report (saved search usually)
- Review data from report with account owner to obtain sign off
- Run a deletion script that utilizes the saved search in question as input to ensure nothing else is deleted
- Schedule follow up review with account owner to ensure data was removed properly
To safely streamline this process, I created a map/reduce script that runs off a saved search.
The script uses 1 parameter, “Saved Search” and automates everything else. Simply edit the deployment and click save and execute to fire the script on demand. The saved search is loaded, and each result is deleted based on its internalid and record type.
Please Note: If your saved search returns lines on a transaction with mainline = 'F', the script will delete the entire transaction, not just the lines returned by the saved search.
Deploying only involves creating the Map Reduce script, the script parameter and the saved search to use for the process. Below, we provide two options for deploying this Map Reduce script, the first option is easy to install via a bundle so you don’t have to be a developer to use this option. The second option, allows users to install our script in the event that the user does not have permission to install a bundle and is able to deploy and run SuiteScripts.
How to Install and use the Mass Deletion Bundle:
- Install the Techfino Mass Deletion Bundle
- Create a non-summarized public saved search with criteria set to capture all records that should be deleted
- Navigate to the ‘TF | Mass Record Delete (MR)’ script deployment and set the Saved Search script parameter to the search from step #2
- Click save and Execute on the script deployment to initiate the deletion process
How to Deploy Manually:
- Add the Map / Reduce and library files to the file cabinet
- Create a new Map / Reduce script
- Create a script parameter for the script with the id: ‘_tf_saved_search’ so the final name will be ‘custscript_tf_saved_search’
- Deploy the script setting the saved search under parameters and the concurrency to the desired amount. Keep in mind you need to set the saved search as “public” and have other visibility considerations (i.e. role) for it to be an option for the script parameter.
Q: I have a lot of data to remove. Can this script handle large loads?
A: The script is very scalable, I have yet to find a search / dataset it times out on. I designed the script to run chunks of the saved search in the map stage instead of the whole search in the getInputStage to theoretically allow any input without timing out. A common use case is deleting all transactional data in a system and the script hasn’t timed out in these use cases.
Q: Will the script timeout after 60 minutes?
A: No. Map / Reduce has some complicated governance rules but the script doesn’t time out as it does not spend 60 minutes in getInputData or Summarize stage. All the work is done in manageable chunks in the map and reduce portion of the script to prevent timeout.
Q: Some records did not delete. What happened?!
A: Most likely errors are preventing records from being deleted. While slowing down the process, every record delete action is wrapped in a proper try catch statement in case of failure to ensure the script deletes as much data as is possible and completes. The logs will detail what error NetSuite returned. In most cases this is due to related records preventing deletion. For example, contacts would need to be deleted before deleting customers due to the dependency linkage.
Q: I want to delete everything though, even related records. Is there a way I can set the script to deleted related records too?
A: I plan to add this feature in the future.
Q: I used a summary search and it’s not working
A: Currently the script is designed to only work with non-summarized searches so any grouping and summarizing will not provide valid input.
Q: How fast is it?
A: This varies depending on a lot of factors: level of customization on the records, number of processes running at any given time, type of records being deleted, concurrency limit and much, much more. Below are some screenshots showing some examples of runtime
85,000 custom records being deleted in 5 minutes utilizing 10 queues
91,000 transaction records (sales orders/invoices) being deleted in 5 hours 50 minutes utilizing 5 queues
We’ve helped a number of Clients with this solution and we’re sure you’ll find a ton of value in using this. If you do use our solution, we would greatly appreciate you commenting here to let us know how it helped you out!