Streamlining NetSuite Record Inactivation: A Custom Table Approach

This article is relevant if you need to inactivate email addresses or other entity information in NetSuite.

Background

For the SuiteWorld 2023 season, we proactively communicated with our clients and contacts, issuing invitations and announcements. In keeping with our ethos of minimal yet meaningful outreach, we only extend communications when we believe there will be meaningful value to the recipient.

When operating a large database for communication, email bounces are inevitable. This isn’t specific to NetSuite; it’s a CRM hygiene imperative. The goal is to maintain database integrity by flagging these records as inactive.

In this situation, the messages gave us nothing but the bounced email address (we all experience it when we email and get a bounce back).  Meaning, we had no other identifiers.   This differs from my 2022 article, How to Update NetSuite Records for Bounced Email Addresses, where I dealt with NetSuite’s Undeliverable Email record structure. It also diverges from the 2010 solution I provided (Solution for Syncing and Bounced Emails between NetSuite and Outlook) concerning NetSuite and Outlook synchronization, which isn’t pertinent here.

Here’s the real issue: We needed to update NetSuite, and the only identifier we had was the bounced email address. To streamline this process, I developed a utility that could serve as a blueprint for NetSuite Administrators seeking similar solutions.

Easily Setting Email Records to Inactive in NetSuite Without Scripting

The task of setting records to ‘inactive’ seems straightforward, but NetSuite’s native capabilities can complicate matters. Here’s the challenge:

1. Record Lookup: The objective is to identify the record linked with the faulty email address. For one or two instances, the built-in Global Search function suffices. But scale that to 50 or more, and you’ll find yourself grappling with the limitations of Saved Searches. Crafting a formula that uses a complex CASE WHEN statement isn’t just laborious; it’s unscalable due to formula text size restrictions.
2. Flagging Inactive: After identification, the next step is flagging these records as inactive. This is generally simpler. Armed with the internal IDs, one can easily upload a CSV file to set the records to ‘inactive’ status.

Notice, though, that we are going to be working outside NetSuite for a solution.

Leveraging a Custom Table for Streamlined Email Inactivation

To bypass the limitations of NetSuite’s native tools, I conceptualized a simplified approach—import each email address into a custom table and let specialized utilities handle the rest. Here’s how I did it:

1. Initiated an ‘Entity Inactivated’ Table: The objective was to connect the email addresses to their corresponding entity records. Hence, I introduced a “Lookup Email” field. While at it, I included another field named “Lookup Entity ID.” Populate these lookup fields and let the system execute the heavy lifting.  Click the image to get a feel for the table.
2. Employed SQL for Database Modification: The game-changer is a set of tools my firm developed and made freely available to all our clients. My 2022 article, Learn How To Update NetSuite using SQL, demonstrates a pattern that enables administrators to perform NetSuite database updates without resorting to scripts. This technique mandates SuiteQL proficiency—a skill most administrators, and certainly everyone on our team, possess.
3. Executed the Lookup and Update Utility: Using the integrated Map/Reduce utility, which takes some simple parameter data, we accomplished two key tasks (click the images to see how the script deployments take SQL to do the work):
   • Identify the email or entity and associate it with the custom record.
   • Flag the linked entity as ‘inactive.’

To automate the process, I scheduled this utility to run daily. Now, when records need to be set to ‘inactive,’ I simply add them to the custom table and let the utility take over.

Unlocking the Potential with License-Free Tools and the Utilities Library

The aim of this article is to broaden the administrative mindset, illustrating how to overcome some understandable limitations within the NetSuite ecosystem. While various solutions exist for these challenges, understanding how others in the community navigate them can be a catalyst for your own unique methodology.

The utility, as I have crafted, can be activated in any NetSuite account. Alternatively, we provide the toolkit (Prolecto Utilities Bundle) for you to customize it to your specific needs. All intellectual property we’ve engineered is complimentary to our clients, contingent upon an established support relationship.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are seeking more muscle with your NetSuite administrative challenges and it makes sense to have a powerful companion, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube