This article is relevant if you’re considering various NetSuite contact-to-entity models and you seek to expand role relationships.

Background

In 2019, I wrote an article titled Breakthrough NetSuite’s Contact to Entity Role Relationship. This article explored how to enhance NetSuite’s contact-to-entity role relationships by implementing a custom cross-reference record, enabling multiple roles per contact and improving search functionality. This approach addresses the limitations in NetSuite’s default model, such as the inability to assign multiple roles to a single contact and limitations with saved searches.

When discussing this model with those unfamiliar with the concept, I found it somewhat abstract and difficult for people to understand quickly. Therefore, this article aims to illustrate how this enhanced contact model can effectively address a common NetSuite business challenge.

Overcoming NetSuite’s Challenge of Emailing Invoices to Multiple Recipients

The days of printing and mailing invoices are long gone. Today, the standard approach is to email customers an attached PDF invoice to request payment.

Many organizations want these invoice emails sent to multiple people within their company.  Often, they set up email aliases like “accounting@company.com” to handle these requests, but the original buyer, such as “john@company.com”, might also want to receive them.

Less commonly known is that NetSuite’s native email field ({tobeemailed}) on an invoice can hold multiple email addresses, separated by semicolons. The key is figuring out how to populate this field with the right list of emails so NetSuite will automatically distribute the invoice to all intended recipients.

A Solution for Populating NetSuite’s Invoice Email Field

To meet this challenge, we need a workaround. NetSuite’s native approach to populating the invoice email field simply copies the customer email field during invoice creation. Unfortunately, that customer email field only supports a single email address; we need multiple.

Since 2015, we’ve taken a streamlined approach in our Prolecto Resources NetSuite account by creating a custom freeform text entity field called “PRI AP Name” (PRI for Prolecto Resources Incorporated).  This custom customer record field allows us to store a semi-colon delimited list of email addresses. With it in place, we can work to source this custom field to populate the native {tobeemailed} field on invoices.  Click on the image to gain a better understanding.

Although NetSuite’s native point-and-click interface doesn’t support sourcing a custom field directly to the native email field, a simple workflow or On Create User Event script gets the job done. Conventional enough. Yet, in a recent article, Enhanced NetSuite Reporting with Historical As-Of Record Stamping and Data Source Mapping Utilities, I introduced a mapping pattern that can automatically handle this sourcing with a license-free Field Mapping Utility available to our clients on request.

To show there’s more than one solution to the {tobeemailed} field, in 2015, I shared SuiteScript 1.0 code that can automatically look up contacts and populate the email field.  You’ll find it in my article, Extend NetSuite’s Transaction Email Address List.

Considering the Broader Contact-to-Customer Entity Model

With the need for multiple email addresses in mind, let’s address a broader modeling challenge. When we work with clients on NetSuite implementations and optimizations, we leverage advanced entity models to maximize NetSuite’s potential.

NetSuite organizes contact records in a network structure linked to transactional entities like Customers, Employees, and Vendors. While contacts themselves cannot transact, they serve a pivotal role.

A powerful approach is to design each contact record around a unique email address.  Since a company (e.g., the customer entity) typically has multiple individuals associated with it, we can unlock significant value by creating a contact record for each email address linked to the company. Returning to our multiple-invoice email scenario, each email address could be represented by a distinct contact record, which is more powerful than a simple string of email addresses.

Concept: Purposeful Contacts to Drive NetSuite Business Logic

Once we’ve established that each email address at a company should correspond to a contact record, the next question is: what role does each contact serve?  The most effective way to define roles is to focus on their business purpose.

Unlike a Contact’s job title (e.g., President or CFO), a contact’s role in the system should only be defined if it drives specific business logic. For our goal of emailing invoices to multiple recipients, we could create a role called “Accounts Payable” to designate contact records responsible for receiving invoice emails.

This is where my 2019 article, Breakthrough NetSuite’s Contact to Entity Role Relationship, comes into play. Why not assign the “Accounts Payable” role to relevant contacts, set their email address and let NetSuite handle distribution? One aspect of the answer lies in NetSuite’s limitations on role definitions outlined in the referenced article.

Now, consider the possibilities: any logic requiring contact involvement could be controlled through role definitions. By simply adding or removing contacts from specific roles, from the user’s perspective, the system would intuitively understand the required actions, streamlining operational processes through role-based automation.

It’s a mindset that opens logic possibilities that are elegant to the end user; for the analyst, they are building the database to be optimized for logic.

Bridging NetSuite Logic with Purposeful Contact Modeling

Since our Prolecto Resources account already handles the multiple-invoice email challenge, our next step is implementing the Purposeful Contact model, specifically an “Accounts Payable” role. The goal is for all contacts tagged with this role to automatically populate the custom customer email address solution we discussed.

To achieve this, we’ll leverage advanced SuiteQL to extract the data we need. We’ll query the link table that holds relationships between customers and contacts, targeting those where the contact role is “Accounts Payable,” and output a single field with a semicolon-delimited email list.

The included SuiteQL SQL examples demonstrate how to structure these queries, with images illustrating how SuiteQL generates the outputs. Once we refine the SQL, we can use our license-free tools license-free tools for updating NetSuite fields directly with SQL, providing administrators with a powerful way to manage data efficiently.

SELECT
	customer.entityid,
	NVL(contact.firstname, contact.entityid) apname,
	contact.email email,
	custrecord_pri_bpa_contact,
	custrecord_pri_bpa_contact_role,
	custrecord_pri_bpa_contact_entity
FROM 
	customrecord_pri_bpa_contact_role_link
JOIN 
	contact on contact.id = customrecord_pri_bpa_contact_role_link.custrecord_pri_bpa_contact 
JOIN 
	customer on customer.id = customrecord_pri_bpa_contact_role_link.custrecord_pri_bpa_contact_entity AND customer.custentity_pri_ap_email IS NOT NULL
WHERE 
	BUILTIN.MNFILTER(custrecord_pri_bpa_contact_role, 'MN_INCLUDE', '', 'TRUE', '1') = 'T'
ORDER BY 
	custrecord_pri_bpa_contact_entity

Here is another. Click images to see the results.

SELECT
	custrecord_pri_bpa_contact_entity,
	LISTAGG(NVL(contact.firstname, contact.entityid), ', ') WITHIN GROUP (ORDER BY NVL(contact.firstname, contact.entityid)) AS custentity_pri_ap_name,
	LISTAGG(LOWER(contact.email), ';') WITHIN GROUP (ORDER BY contact.email) AS custentity_pri_ap_email
FROM 
	customrecord_pri_bpa_contact_role_link 
JOIN 
	contact ON contact.id = customrecord_pri_bpa_contact_role_link.custrecord_pri_bpa_contact
WHERE 
	BUILTIN.MNFILTER(custrecord_pri_bpa_contact_role, 'MN_INCLUDE', '', 'TRUE', '1') = 'T' AND
	custrecord_pri_bpa_contact_entity = 36206
GROUP BY 
	custrecord_pri_bpa_contact_entity

Modeling Insights with Purposeful Contact Implementation

It’s worth noting that our custom single text field email solution dates back to 2015, while the Purposeful Contact Model came into play in 2019. Had we started with this superior model, our approach would have been even more streamlined.

The underlying principle remains the same: think through the business requirements, effectively model your data structure, centralize your business logic, and drive NetSuite to work exactly as needed, all while minimizing technical debt and ongoing maintenance.

Producing NetSuite Solutions through Reusable Patterns and Algorithms

When helping clients unlock the full potential of the NetSuite platform, we begin with solid business requirements and effective database modeling. Quality modeling is the foundation that allows us to build native or custom logic, ensuring we’ve captured the core of the challenge.

The Purposeful Contact pattern exemplifies this approach, offering an intelligent model for customer and contact relationships in NetSuite.

Through our Prolecto LABs initiative, we provide all our patterns, algorithms, and bundles to our clients free of license fees. Our commitment as a high-quality NetSuite service provider is to leverage our prior achievements to deliver tailored solutions efficiently and without driving extra software costs.  Our focus is on delivering complete, elegant solutions that minimize time to value.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If the Purposeful Contact pattern makes sense to you and you have a challenge, 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