Published on April 2, 2024.

One of the field types that NetSuite supports is the Multiple Select field (sometimes referred to as a "multi-select field"). Multiple select fields allow users to set the value of a field by selecting multiple values from a specified list or a list of records.

When you create a multiple select field, NetSuite automatically creates a join table to support the relationship between the related tables. That join (or "map") table is usually named with a "MAP_" prefix. For example, if you add a multiple select field on the Item table, and give the field an ID of "custitem_ms_test" then the join table's name will likely be something like "MAP_Item_CustItem_MS_Test."

If you're using the latest version of the SuiteQL Query Tool, then you should be able to locate the join table by using the "Tables Reference" function. Search for a table with "MAP" in the name, or look at the related tables for the table that you added the field to.

To keep things simple, for the remainder of this post, I'm going to refer to a multiple select field on a standard NetSuite table. On the Role table, there's a column named "EffectiveSubsidiaries" that indicates the subsidiaries that a role has access to. In this case, the join table between the Role and Subsidiary table is named RoleSubsidiaryRestrictionMap.

To see the subsidiary restrictions for a specific role, you can query the table like this.

SELECT
	Role,
	BUILTIN.DF( Role ) AS RoleName,
	Subsidiary,
	BUILTIN.DF( Subsidiary ) AS SubsidiaryName
FROM 
	RoleSubsidiaryRestrictionMap 
WHERE 
	( Role = 1749 )

To make it easier to query against multiple select fields, NetSuite provides a SuiteQL built-in function named MNFILTER. With it, we can perform more complex types of queries.

For example, suppose that I want to identify roles whose effective subsidiaries include the subsidiary with ID 3. In this case, I can use the BUILTIN.MNFILTER function like this.

SELECT
	Name,
	EffectiveSubsidiaries,
	BUILTIN.DF( EffectiveSubsidiaries ) AS EffectiveSubsidiaryNames
FROM
	Role
WHERE
	BUILTIN.MNFILTER( EffectiveSubsidiaries, 'MN_INCLUDE', '', 'TRUE', '3' ) = 'T'
ORDER BY
	Name

The parameters for the MNFILTER function are:
• The name of the multiple select field.
• An operator, such as MN_INCLUDE, MN_INCLUDE_ALL, MN_INCLUDE_EXACTLY, MN_EXCLUDE, MN_EXCLUDE_ALL, and MN_EXCLUDE_EXACTLY. I'll discuss some of these operators below.
• An indication as to whether the value being searched for is the internal ID of the related record (which is indicated by using an empty string), or its name (which is indicated by using 'DF'). More on this in a moment.
• A boolean value, indicating whether the condition should be evaluated as needing to be True or False.
• The value to be applied to the filter.

In the example above, I used the BUILTIN.MNFILTER function to identify roles whose effective subsidiaries include subsidiary 3, and I passed the subsidiary's internal ID to the function. But I could also have passed the name of the related record (in this case the subsidiary name) to the function. And to do so, I simply pass "DF" as the third parameter, and instead of passing the ID as the fifth parameter, I pass the name.

In my instance, the name of subsidiary 3 is "Canada." So a modified version of the original query would look like this.

SELECT
	Name,
	EffectiveSubsidiaries,
	BUILTIN.DF( EffectiveSubsidiaries ) AS EffectiveSubsidiaryNames
FROM
	Role
WHERE
	BUILTIN.MNFILTER( EffectiveSubsidiaries, 'MN_INCLUDE', 'DF', 'TRUE', 'Canada' ) = 'T'
ORDER BY
	Name

I mentioned that the BUILTIN.MNFILTER function also supports several operators. I've found MN_INCLUDE, MN_INCLUDE_EXACTLY, and MN_EXCLUDE to be particularly helpful.

Suppose that I want to identify roles whose effective subsidiary is only subsidiary 3 (Canada). In this case, I'd use the MN_INCLUDE_EXACTLY operator, like this.

SELECT
	Name,
	EffectiveSubsidiaries,
	BUILTIN.DF( EffectiveSubsidiaries ) AS EffectiveSubsidiaryNames
FROM
	Role
WHERE
	BUILTIN.MNFILTER( EffectiveSubsidiaries, 'MN_INCLUDE_EXACTLY', 'DF', 'TRUE', 'Canada' ) = 'T'
ORDER BY
	Name

Now suppose that I want to identify roles whose effective subsidiaries did not include subsidiary 3 (Canada). In this case, I'd use the MN_EXCLUDE operator, like this.

SELECT
	Name,
	EffectiveSubsidiaries,
	BUILTIN.DF( EffectiveSubsidiaries ) AS EffectiveSubsidiaryNames
FROM
	Role
WHERE
	BUILTIN.MNFILTER( EffectiveSubsidiaries, 'MN_EXCLUDE', 'DF', 'TRUE', 'Canada' ) = 'T'
ORDER BY
	Name

Over the past few years, as I've provided NetSuite developers with SuiteQL consulting sessions, the topic of Multiple Select fields has come up quite often. I hope that this blog post helps you better understand how that type of field works, and how you can use both join tables and the BUILTIN.MNFILTER to work with them in your SuiteQL queries.