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.
Table of Contents
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.
Customer | Lead Sources |
---|---|
ABC Inc. | Ad Trade Show Web |
Platinum Doors | Family & Friends Social Media Trade Show |
Technorama | Social Media Other |
Bird Eye Ltd. | <None> |
Here is our lead source list with internal IDs:
Internal ID | Name |
---|---|
1 | Ad |
2 | Trade Show |
3 | Web |
4 | Social Media |
5 | Family and Friends |
6 | Other |
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
entityid | lead_sources | lead_source_names |
---|---|---|
ABC Inc. | 1, 2, 3 | Ad, Trade Show, Web |
Platinum Doors | 2, 4, 5 | Family and Friends, Social Media, Trade Show |
Technorama | 4, 6 | Other, Social Media |
Bird Eye Ltd. |
Observations
- 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
- 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 toTrade 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. - Perhaps quite obvious that string operators can be applied to the textual value returned by
BUILTIN.DF
. For example, in theSELECT
clause, we can replace the commas with a pipe using theREPLACE
function like thisSELECT 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
entityid | lead_sources | lead_source_names | lead_source_name | lead_source_id |
---|---|---|---|---|
ABC Inc. | 1, 2, 3 | Ad, Trade Show, Web | Ad | 1 |
ABC Inc. | 1, 2, 3 | Ad, Trade Show, Web | Trade Show | 2 |
ABC Inc. | 1, 2, 3 | Ad, Trade Show, Web | Web | 3 |
Platinum Doors | 2, 4, 5 | Family and Friends, Social Media, Trade Show | Trade Show | 2 |
Platinum Doors | 2, 4, 5 | Family and Friends, Social Media, Trade Show | Social Media | 4 |
Platinum Doors | 2, 4, 5 | Family and Friends, Social Media, Trade Show | Family and Friends | 5 |
Technorama | 4, 6 | Other, Social Media | Social Media | 4 |
Technorama | 4, 6 | Other, Social Media | Other | 6 |
Bird Eye Ltd. |
Observations
- 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.
- We retrieve the
lead_source_name
value from the lead source custom list rather than usingBUILTIN.DF(m.maptwo)
which would also give us the names. The reason for not usingBUILTIN.DF
is that it implicitly performs anINNER 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 explicitLEFT JOIN
on thecustomlist_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
entityid | lead_sources | lead_source_names |
---|---|---|
ABC Inc. | 1, 2, 3 | Ad, Trade Show, Web |
Bird Eye Ltd. | ||
Platinum Doors | 2, 4, 5 | Trade Show, Social Media, Family and Friends |
Technorama | 4, 6 | Social Media, Other |
Observations
- 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. - 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
entityid | custentity_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.MN
FILTER 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!