Understand How to Filter on Multi-Select Fields Using SuiteQL’s BUILTIN.MNFILTER Function

In this article, you will learn how to effectively filter on multi-select fields using SuiteQL’s BUILTIN.MNFILTER function. It is a sequel to my earlier article titled “How to Effectively Query Multiple Select Fields in SuiteQL” which I strongly recommend you read if you are working with multi-select fields. However, this article is standalone and can be understood independently.

TL;DR

BUILTIN.MNFILTER is a useful function with some not-so-obvious nuances. Here are the main ones covered in this article:

• BUILTIN.MNFILTER can only appear in a WHERE clause (no trying to figure out the results of the function by adding it to the SELECT clause).
• Because it appears in a WHERE clause, BUILTIN.MNFILTER must have a value which appears to serve no purpose than to meet the syntax requirement.
• Filtering can be done based on textual values or internal IDs as controlled by the ‘DF’ parameter.
• The parameter that controls case-sensitivity must be specified as a boolean string. Everything except ‘True’ (case-insensitive) turns case-sensitivity off.
• It is possible to filter on multiple values by providing a comma-separated list of target values (text or internal IDs).
• BUILTIN.MNFILTER does not offer any reasonable way to find rows where a multi-select field is blank (there are other techniques).

Data Model

We will use the same example as before of a Lead Source {custentity_nsi_source} multiple select field on the customer record which is used to track how customers heard of our business. The values in this field are from a custom list {customlist_nsi_lead_source}.

Here’s our sample dataset based on the above model.

The lead source list with internal IDs:

Exploring SuiteQL’s BUILTIN.MNFILTER Function

BUILTIN.MNFILTER is a SuiteQL Supported Built-in Function for filtering on multiple select fields. In my opinion, although the official documentation gives an overview of the parameters, it does not provide sufficient examples to help users to fully grasp the capabilities. The goal of this article is to bridge that gap.

Syntax

BUILTIN.MNFILTER(
    multiple_select_field,
    operator,
    value_type,
    is_case_sensitive,
    values
)

Parameters

1. multiple_select_field: The script ID of multiple select field you want to filter.
2. operator: Defines the filter’s behavior. Acceptable values are:
   • MN_INCLUDE: Checks if any of the specified values are present.
   • MN_INCLUDE_ALL: Ensures all specified values are present.
   • MN_INCLUDE_EXACTLY: Matches records containing exactly the specified values.
   • MN_EXCLUDE: Ensures none of the specified values are present.
   • MN_EXCLUDE_ALL: Ensures all specified values are absent.
   • MN_EXCLUDE_EXACTLY: Matches records that do not contain exactly the specified values.
3. value_type: Indicates whether the filter values are internal IDs ('') or display names (‘DF’)
4. is_case_sensitive: Indicates whether filtering is case-sensitive or not. This is only useful when value_type is ‘DF’. Expects a boolean string.
5. values: A comma-separated list of the values (numbers or strings) to be filtered against.

Return Value

The documentation is silent on the return value and it appears it does not matter as we will see shortly.

Usage

BUILTIN.MNFILTER should be part of a where clause:

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
	BUILTIN.MNFILTER(custentity_nsi_source, 'MN_INCLUDE', 'DF', 'False', 'Trade Show') = 'dontcare'

Additionally, the function must be compared to something i.e. WHERE BUILTIN.MNFILTER = ‘value’. Otherwise, you will get an error like: Search error occurred: Element does not exist or element class SqlPredicate is expected. Node class: gudusoft.gsqlparser.nodes.TExpression Node type: 30 (Anyone else smelling some good old Java in the guts of NetSuite or is it must my imagination? :D).

However, the actual right operand does not seem to matter. The following all gave correct results:

• BUILTIN.MNFILTER(custentity_nsi_source, 'MN_INCLUDE', 'DF', 'False', 'Trade Show') = 'dontcare'
• BUILTIN.MNFILTER(custentity_nsi_source, 'MN_INCLUDE', 'DF', 'False', 'Trade Show') = 1
• BUILTIN.MNFILTER(custentity_nsi_source, 'MN_INCLUDE', 'DF', 'False', 'Trade Show') = id // result from the query
• BUILTIN.MNFILTER(custentity_nsi_source, 'MN_INCLUDE', 'DF', 'False', 'Trade Show') = ''
• BUILTIN.MNFILTER(custentity_nsi_source, 'MN_INCLUDE', 'DF', 'False', 'Trade Show') != ''

The following failed though so there are some limits afterall:

BUILTIN.MNFILTER By Example

Let us now see the function in practice by querying the database using our working example.

Filtering by Textual Value

The following query will return all customers with ‘Trade Show’ as a lead source.

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE', 
       'DF', 
       'F', 
       'Trade Show'
    ) = 'dontcare'

Filtering by Internal ID

To match on internal ID rather than textual value, we replace the third argument (‘DF’) – line 10 – with an empty string and the fifth argument (line 12) with the target internal ID(s):

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
	BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE', 
       '', 
       'F', 
       2
    ) = 'dontcare'

The results are the same as above. Also, the last argument (internal ID of the target lead source) can be enclosed in quotes (‘2’) or written as an integer (2).

Case-Sensitive Filtering

The third parameter controls case-sensitivity. It must be a string with the value ‘TRUE’, ‘True’, or any other variant of the string for case-sensitivity to work. Using a boolean (true without quotes) instead of string will result in an error like Search error occurred: Unknown identifier '"TRUE"'.

The following query yields no results since, with case-sensitivity filter activated, ‘other’ does not equal ‘Other’.

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE', 
       'DF', 
       'True', 
       'other'
    ) = 'dontcare'

The following query incorrectly returns a match because the third parameter has a value (‘T’) that evaluates to false.

Beware as ‘T’ is a common representation of true in SuiteQL results; but it does not work here.

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE', 
       'DF', 
       'T', 
       'other'
    ) = 'dontcare'

Filtering by Multiple Values

The last argument accepts a list of comma-separated values, allowing us to filter on more than one target value. The following query finds all rows where the lead source is either ‘Ad’ or ‘Other’:

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE', 
       'DF', 
       'T', 
       'Ad', 'Other'
    ) = 'dontcare'

This approach works with internal IDs as well:

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE', 
       '', 
       'T', 
       1, 6
    ) = 'dontcare'

Understanding the Operators

The official documentation lists the operators without explaining what they mean. Intuitively, we can guess the distinction between INCLUDE/EXCLUDE (ANY), ALL, and EXACTLY. Let’s confirm our assumptions.

We already covered MN_INCLUDE extensively. MN_INCLUDE_ALL will only find rows where all specified values are present. This query will return customer ABC Inc. because it has all the specified lead sources.

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE_ALL', 
       'DF', 
       'F', 
       'Trade Show', 'Ad'
    ) = 'dontcare'

The same query will fail with MN_INCLUDE_EXACTLY which will only return rows with an exact match. To find ABC Inc. with MN_INCLUDE_EXACTLY, we need to list all three lead sources (order does not matter):

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE_EXACTLY', 
       'DF', 
       'F', 
       'Trade Show', 'Ad', 'Web'
    ) = 'dontcare'

The EXCLUDE operators follow the same semantics. The only additional thing to note is that the EXCLUDE operators also capture rows where is no lead source. For example, excludes all customers with ‘Ad’ as a lead source which includes Bird Eye Ltd. which has no lead source:

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_EXCLUDE', 
       'DF', 
       'F', 
       'Ad'
    ) = 'dontcare'

Finding Rows with Empty Multi-Select Field Values

Lastly, what if we wanted to capture only rows without a value? The following is intuitive but does not work.

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_INCLUDE', 
       'DF', 
       'F', 
      ''
    ) = 'dontcare'

Replacing the empty string in line 12 with null, ‘null’, ‘@NONE@’ (from the saved search days), also failed to find customer Bird Eye Ltd.

Switching to the EXCLUDE side, the approach of excluding all known values as illustrated below works but it is a weak solution as we have to remember to update the query each time a new value is added!

SELECT
    entityid,
    BUILTIN.DF(custentity_nsi_source) lead_source_names,
FROM
    customer
WHERE
    BUILTIN.MNFILTER(
       custentity_nsi_source, 
       'MN_EXCLUDE', 
       'DF', 
       'F', 
       'Ad', 'Trade Show', 'Web', 'Social Media', 'Family and Friends', 'Other'
    ) = 'dontcare'

Refer to Part 1 of this series for two viable options for finding rows without a value that do not involve explicitly excluding all known values.

Closing

We have zoomed in on the BUILTIN.MNFILTER function in this article. Combined with Part 1, you should now be equipped to tackle all things multi-select fields in SuiteQL. Keep learning. Keep sharing.

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

1. How To Hyperlink Multi-Select Fields in NetSuite Saved Search Results
2. How to Effectively Query Multiple Select Fields in SuiteQL
3. Resetting NetSuite Multi-Select Field Values Does Not Work Before Load!