This article is relevant if you are using NetSuite’s SQL (SuiteQL) and you need to join multiple select fields.

Background

Since the beginning of 2021, we have been delighted to be able to use SQL in our SuiteScript work. We created a license free tool for our clients that allow them to craft NetSuite SQL queries.

One of our lead senior technical analysts, Boban D., continues to help reveal use cases for SQL in the NetSuite environment. More recently, we needed to produce a join on a multiple select field. All conventional ways of joining would not work. Working back and forth for about a month with NetSuite Support, Boban was able to get to the bottom of how to query and join through NetSuite Multiple Select fields. We are grateful for his leadership.

How to Use NetSuite SQL to Join a Multiple Select Field

Whenever you have a multiple-select (multi-select) field, NetSuite automatically creates a hidden “mapping” table for the many:many association. This table has only two fields to provide the internalids named as follows:

1. mapone: the value of the source table internal id.
2. maptwo: the value of the target table internal id.

Now, the name of the table is of the format “map_XXX_YYY” and is derived from the source record and the multiple select field. Here is the syntax:

1. XXX: the value of the source table id.
2. YYY: the value of the multiple select field id.

Example NetSuite Multiple Field SQL Lookup

To better understand the use, I have a simple custom record that joins to another custom record via the multiple-select field. In this use case, we have a database of NetSuite applications my firm has created (see the public-facing list driven out of the NetSuite database). We track all the client accounts we have connected to through the multiple select field. NetSuite nicely shows the list of account names for us. However, suppose we are interested in the client’s NetSuite account number — another field tracked in the database?

Click the image to gain a better understanding of the app.

We now will work through the NetSuite SQL.

NetSuite Multiple Field SQL Lookup Syntax

Our first query will be to look up the main table and the multiple select field without the join. Here, we get the IDs in a comma-separated list. The related images can be clicked to show our Prolecto SQL Tool do the lookup and the results.

SELECT 
	APP.name, 
	APP.id as APPID,  
	APP.custrecord_pri_application_dist_accts MULTIFIELD 
FROM 
	customrecord_pri_application APP

WHERE 
	APP.ID = 109

Here is the result:

Now, we build the syntax of the NetSuite joined multiple-select field:

SELECT 
	APP.name, 
	APP.id as APPID,  
	APP.custrecord_pri_application_dist_accts as MULTIFIELD, 
	MAP.mapone, MAP.maptwo, 
	ACCTS.custrecord_pri_application_accts_id as ACCTNUM 
FROM 
	customrecord_pri_application APP

JOIN 
	map_customrecord_pri_application_custrecord_pri_application_dist_accts 
	MAP ON MAP.mapone = APP.id

JOIN 
	customrecord_pri_application_accts ACCTS
	ON MAP.maptwo = ACCTS.id

WHERE 
	APP.ID = 109

Here is the result:

NetSuite Technical Leadership

Our team and clients appreciate Boban’s leadership to enhance our capacities to solve our client-based NetSuite challenges. When working closely with high-caliber professionals in a practice that is committed to high standards for care, we all become that much better at taking care of our clients.

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 a team of NetSuite professionals recognized for leadership, 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