Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

How to Effectively Query Multiple Select Fields in SuiteQL

6 min read

NetSuite Multiple Select Field Concepts

This article explains how to effectively query multiple select fields using SuiteQL. Despite the official help pages and some good community blog articles on this subject, I still find myself stuck in certain scenarios, hence this article.

Context

I assume that the reader understands the multiple select field type and will focus on queries for different scenarios. Furthermore, I assume that you are generally familiar with SuiteQL and know how to execute queries e.g. using Tim Dietrich’s SuiteQL Query Tool.

To drive our queries, consider the following example drawn from the official NetSuite help page on multiple select fields: Suppose we have a Lead Source {custentity_nsi_source} multiple select field on the customer record to track how customers heard of our business. The values in this field are from a custom list {customlist_nsi_lead_source}. Thus, there is a many-to-many relationship between customers and lead sources. (As an aside, there is a native lead source field but it is a single select field.)

Consider the following dataset based on the above model.

CustomerLead Sources
ABC Inc.Ad
Trade Show
Web
Platinum DoorsFamily & Friends
Social Media
Trade Show
TechnoramaSocial Media
Other
Bird Eye Ltd.<None>
A Tabular Representation of our Sample Dataset

Here is our lead source list with internal IDs:

Internal IDName
1Ad
2Trade Show
3Web
4Social Media
5Family and Friends
6Other
The list of values that drive our example multiple select field

In a NetSuite saved search, the data may look like this:

Multiple Select Field Queries

Given the dataset above, let us interrogate the database to answer various questions that relate to the multiple select lead source field.

1. How to List All Customers and Their Lead Sources

SELECT
	entityid,
	custentity_nsi_source lead_sources,
	BUILTIN.DF(custentity_nsi_source) lead_source_names
FROM
	customer
ORDER BY
	id ASC

Query Results

entityidlead_sourceslead_source_names
ABC Inc.1, 2, 3Ad, Trade Show, Web
Platinum Doors2, 4, 5Family and Friends, Social Media, Trade Show
Technorama4, 6Other, Social Media
Bird Eye Ltd.
Query results for customers and their lead sources (multiple select field)

Observations

  1. Multiple select field values are printed as a comma-separated list of the internal IDs of the corresponding list entries. To show the textual values, we use the BUILTIN.DF function. This is one of handful of built-in functions that can be used in SuiteQL queries. Visit the official SuiteQL Supported Built-in Functions documentation for a list of all supported built-in functions and what they do. Here’s are the other available functions at the time of writing:
    • BUILTIN.CF
    • BUILTIN.CONSOLIDATE
    • BUILTIN.CURRENCY
    • BUILTIN.CURRENCY_CONVERT
    • BUILTIN.HIERARCHY
    • BUILTIN.MNFILTER
    • BUILTIN.NAMED_GROUP
    • BUILTIN.PERIOD
    • BUILTIN.RELATIVE_RANGES
  2. There may be a mismatch between the sequencing of the internal IDs and the textual values! In our example above, the query yielded internal IDs 2, 4, 5 for Platinum Doors which corresponds to Trade Show, Social Media, Family and Friends. However, BUILTIN.DF returned them in a different (alphabetical) order: Family and Friends, Social Media, Trade Show. Thus, if you have logic that ties the internal IDs to textual values, you should NOT rely on matching entries by position. I will provide an alternative solution shortly.
  3. Perhaps quite obvious that string operators can be applied to the textual value returned by BUILTIN.DF. For example, in the SELECT clause, we can replace the commas with a pipe using the REPLACE function like this SELECT REPLACE(BUILTIN.DF(custentity_nsi_source), ',', ' |') FROM… Here’s the official list of SuiteQL Supported and Unsupported Functions

2. How to List Each Customer-Lead Source Combination on a Separate Row

Instead of having a comma-separated list of the entries, we may need the individual values in the multiple select field e.g. to join to another table.

Marty Zigman’s article “Learn How To SQL Query NetSuite Multiple Select Fields” provides the answer. In short, we need to join a (dynamic) mapping table whose naming syntax is map_<source_recordtype>_<multiple_select_field_id>. In our example, the source record type is customer and our lead source multi-select field ID is custentity_nsi_source. Thus, our mapping table’s name is map_customer_custentity_nsi_source. The table contains two fields: mapone – the ID of the source record, customer ID in our case, and maptwo – the ID of the multi-select field value.

Here’s our query:

SELECT
	c.entityid,
	c.custentity_nsi_source lead_sources,
	BUILTIN.DF(c.custentity_nsi_source) lead_source_names,
	--BUILTIN.DF(m.maptwo) lead_source_name_alt, -- Will exclude rows with no lead source since BUILTIN.DF does an inner join
	ls.name lead_source_name,
	m.maptwo lead_source_id
FROM
	customer c
LEFT JOIN 
	map_customer_custentity_nsi_source m
	ON m.mapone = c.id
LEFT JOIN
	customlist_nsi_lead_source ls
	ON ls.id = m.maptwo
ORDER BY
	c.id ASC

Query Results

entityidlead_sourceslead_source_nameslead_source_namelead_source_id
ABC Inc.1, 2, 3Ad, Trade Show, WebAd1
ABC Inc.1, 2, 3Ad, Trade Show, WebTrade Show2
ABC Inc.1, 2, 3Ad, Trade Show, WebWeb3
Platinum Doors2, 4, 5Family and Friends, Social Media, Trade ShowTrade Show2
Platinum Doors2, 4, 5Family and Friends, Social Media, Trade ShowSocial Media4
Platinum Doors2, 4, 5Family and Friends, Social Media, Trade ShowFamily and Friends5
Technorama4, 6Other, Social MediaSocial Media4
Technorama4, 6Other, Social MediaOther6
Bird Eye Ltd.

Observations

  1. As seen from the results above, using the dynamic mapping table allows us to correctly map multiple select field IDs to the corresponding textual values.
  2. We retrieve the lead_source_name value from the lead source custom list rather than using BUILTIN.DF(m.maptwo) which would also give us the names. The reason for not using BUILTIN.DF is that it implicitly performs an INNER JOIN to get the textual value. Thus, if we used it instead, our results will not include customer “Bird Eye Ltd.” which has no lead source value. We address this concern by doing an explicit LEFT JOIN on the customlist_nsi_lead_source table instead.

Watch out for the following when using BUILTIN.DF

  • It returns multi-select field textual values in alphabetic order, causing possible mismatch with the internal ID values.
  • It performs an INNER JOIN which causes any rows that do not have the target field populated to be quietly excluded from the results. This can lead to subtle bugs.

3. How to List All Customers and Their Lead Sources in Correct Sequence

Building on our insights from #2 above, we can now solve the sequencing issue we observed in #1 by using the LISTAGG function.

Here’s our refined query:

SELECT
	c.entityid,
	LISTAGG(ls.id, ', ') WITHIN GROUP (ORDER BY ls.id) AS lead_sources,
	LISTAGG(ls.name, ', ') WITHIN GROUP (ORDER BY ls.id)  AS lead_source_names
FROM
	customer c
LEFT JOIN 
	map_customer_custentity_nsi_source m
	ON m.mapone = c.id
LEFT JOIN
	customlist_nsi_lead_source ls
	ON ls.id = m.maptwo
GROUP BY
	c.entityid

Query Results

entityidlead_sourceslead_source_names
ABC Inc.1, 2, 3Ad, Trade Show, Web
Bird Eye Ltd.
Platinum Doors2, 4, 5Trade Show, Social Media, Family and Friends
Technorama4, 6Social Media, Other

Observations

  1. We now have the lead source IDs and textual values in the same sequence thanks to the LISTAGG function. Ironically, it is listed as an unsupported function in SuiteQL but it (gladly) works! Exploring this function and how it works is beyond the scope of this discourse but it is definitely worth investigating on your own.
  2. We had to give up the ORDER BY clause in our previous queries as we needed the GROUP BY clause and SuiteQL apparently does not support both in a single query. Adding both clauses results in the infamous Search error occurred: Invalid or unsupported search error. There are tricks to achieve the desired result using a subquery but we will not go into that now as it is immaterial in our use case.

4. How to Filter on Multiple Select Field Values

The BUILTIN.MNFILTER function can be used to target specific multiple select field values. We will explore this rather powerful function in a subsequent article. In the meantime, refer to Tim Dietrich’s article “NetSuite: Use SuiteQL’s BUILTIN.MNFILTER to Query Multiple Select Fields” which we will expand upon in our sequel as Tim’s article does not cover some important details of the function.


5. How to Find Rows With No Value in a Multiple Select Field

Now, this is interesting. Suppose, we want to write a query to find all customers where there is no lead source. In our example, the query should yield only customer “Bird Eye Ltd.” as a result.

The following query appears logical but does not work; it does not give any errors but returns zero results.

SELECT
	c.entityid,
	c.custentity_nsi_source
FROM
	customer c
WHERE 
	c.custentity_nsi_source IS NULL

Nevertheless, with our knowledge of the dynamic mapping table discussed earlier, we can produce a working query that meets our objectives as follows:

SELECT
	c.entityid,
	c.custentity_nsi_source
FROM
	customer c
LEFT JOIN 
	map_customer_custentity_nsi_source m
	ON m.mapone = c.id
WHERE 
	m.maptwo IS NULL

Query Result

entityidcustentity_nsi_source
Bird Eye Ltd.

The following query using BUILTIN.DF also works and is more concise. However, for large datasets, I found it to be significantly slower than the above approach using an explicit LEFT JOIN.

SELECT
	c.entityid,
	c.custentity_nsi_source
FROM
	customer c
WHERE 
	BUILTIN.DF(c.custentity_nsi_source) IS NULL

Closing

In this article, we have delved into various nuances of working with multiple select fields in NetSuite some of which are not officially documented. I trust this discourse has enriched your understanding of SuiteQL particularly as it relates to multiple select fields. Do you have other multiple select field or general SuiteQL tips? Please share them via the comments section. Also, watch out for a sequel article where we will focus on the BUILTIN.MNFILTER function for filtering multiple select field values.

NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, learn how you can become an author/collaborator here.

Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!


Other Articles You Might Be Interested In

Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

Leave a Reply

Your email address will not be published. Required fields are marked *

×