This article is relevant if you wish to bulk or mass update NetSuite entity records from email messages that have bounced.

Background

Since starting my blog about NetSuite topics in 2009, now 13 years later, I have held an ethic to not outbound messages to readers or add advertising to these pages.  My firm uses NetSuite for CRM activities.  Prospective individuals and their organizations reach out to our firm independently from the blog when they seek specific attention for their NetSuite-related ambitions.

Naturally, individuals move on to other concerns with time, and their email addresses are no longer valid.  No more than once a year, we may send out, using NetSuite Campaign Email, a Holiday Best Wishes eCard.  During this email outbound event, we naturally discover bounced emails.

Keeping a database trimmed and up to date takes some care. Ideally, records that are no longer trustworthy should be marked as inactive so they do not get in the way of day-to-day use. Thus, this holiday email event offers us an opportunity to update the NetSuite database to mark records with bounced email addresses as inactive.

Updating NetSuite to Mark Entity Records as Inactive from Bounced Email

The general approach is to run the “Undelivered Emails” (see List –> Mailing –> Undelivered Emails) saved search to get the values of the email addresses that had issues during the email campaign. Then, using CSV import, you can update respective records.

Yet, as a NetSuite administrator, like many things that must be done in NetSuite, you realize, as you start your CSV update, that you can’t key on the email address alone to update records. You need the internalid; and NetSuite does not provide a natural saved search join reference.

Use NetSuite SQL to Obtain the Bounced Email Address Internal IDs

Fortunately, we can use SQL to join the Undelivered Emails table to the Entity table structure to get the internalid.  My firm offers its clients a license-free NetSuite SQL Query Tool to make writing SQL queries easy.

Going further, why can’t we update the database with this query if you make the effort to write a NetSuite SQL Query? We can! See my 2022 article, Learn How To Update NetSuite using SQL.

After a bit of discovery trial and error, here is NetSuite SQL I crafted to obtain the bounced email address internalids and related information for updating the database.

SELECT
	id,
	'T' AS isinactive,
	--use builtin else you get RELATIONSHIP FIELD in result when you evaluate type column
	DECODE (BUILTIN.DF(type), 'CustJob', 'customer', BUILTIN.DF(type)) AS recordtype,
	--handle limitiation for the size of the comments field
	'20221223 Permanent Failure: Bad destination mailbox address; ' || 
		SUBSTR(comments, 1, 938 ) AS comments
FROM entity
INNER JOIN undeliveredEmail b ON entity.email = b.recipients 
	AND b.reason = 'Permanent Failure: Bad destination mailbox address'
	AND entity.isinactive = 'F'

It was a breeze to update the NetSuite database with this SQL query.

This SQL will return data you can use to update NetSuite via CSV files. Our NetSuite SQL query tool will provide a way to download the data in CSV format. But you will need to separate the data by the record type to update contacts, customers or other entities.

NetSuite Innovation and Tools

The above example is altogether a common concern for NetSuite administrators.  NetSuite’s built-in capacities are good, but we must go further too often.  This is why we have 60+ bundles we give our clients without a license charge to help them realize the promised value in their NetSuite investment.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them.  If you would like to work with our professional team, 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