Multi-Select Tables

NetSuite automatically creates hidden junction tables for every multi-select field, enabling many-to-many relationships. This guide covers how to find and query these tables.

---

How Multi-Select Fields Work

MULTI-SELECT FIELD STRUCTURE
═══════════════════════════════════════════════════════════════════════════════

Customer Record                    Hidden Mapping Table              Lead Source List
┌─────────────────┐               ┌─────────────────┐              ┌─────────────────┐
│ ID: 100         │               │ mapone │ maptwo │              │ ID │ Name       │
│ Name: Acme Corp │               ├────────┼────────┤              ├────┼────────────┤
│                 │               │   100  │    1   │──────────────│  1 │ Web Search │
│ Lead Sources:   │───────────────│   100  │    3   │──────────────│  3 │ Referral   │
│ - Web Search    │               │   100  │    5   │──────────────│  5 │ Trade Show │
│ - Referral      │               └────────┴────────┘              └────┴────────────┘
│ - Trade Show    │
└─────────────────┘               map_customer_custentity_lead_sources

The mapping table stores ONE ROW per selected value.

---

Finding Mapping Table Names

Naming Convention

map_{source_table}_{field_id}

Component	Description	Example
map_	Fixed prefix	map_
source_table	Record type table name	customer, item, employee
field_id	Field's internal ID	custentity_lead_sources

Examples

Record	Field	Mapping Table
Customer	Lead Sources	map_customer_custentity_lead_sources
Item	Categories	map_item_custitem_categories
Employee	Skills	map_employee_custentity_skills
Sales Order	Custom Multi-Select	map_transaction_custbody_regions

Finding Table Names

Method 1: Records Catalog

Setup → Records Catalog → Search for your record type → Look for "map_" tables

Method 2: SuiteQL Query Tool Use the Tables Reference feature and search for "map_"

Method 3: Query System Tables

-- Find all mapping tables (may require admin permissions)
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'map_%'
ORDER BY table_name

---

Mapping Table Structure

Every mapping table has exactly two columns:

Column	Type	Description
mapone	Integer	ID of the source record (e.g., customer ID)
maptwo	Integer	ID of the selected value (e.g., lead source ID)

---

Basic Queries

List All Selected Values for a Record

-- Get all lead sources for customer ID 100
SELECT
    m.mapone AS customer_id,
    m.maptwo AS lead_source_id,
    ls.name AS lead_source_name
FROM map_customer_custentity_lead_sources m
INNER JOIN customlist_lead_sources ls ON ls.id = m.maptwo
WHERE m.mapone = 100

Sample Result:

customer_id	lead_source_id	lead_source_name
100	1	Web Search
100	3	Referral
100	5	Trade Show

List Records with a Specific Value Selected

-- Find all customers with "Referral" as a lead source
SELECT
    c.id,
    c.companyname,
    c.email
FROM Customer c
INNER JOIN map_customer_custentity_lead_sources m ON m.mapone = c.id
WHERE m.maptwo = 3  -- Referral ID
ORDER BY c.companyname

Join Through Multi-Select to Get Full Details

-- Complete query: Customers with their lead sources
SELECT
    c.id AS customer_id,
    c.companyname,
    m.maptwo AS lead_source_id,
    ls.name AS lead_source_name
FROM Customer c
INNER JOIN map_customer_custentity_lead_sources m ON m.mapone = c.id
INNER JOIN customlist_lead_sources ls ON ls.id = m.maptwo
WHERE c.isinactive = 'F'
ORDER BY c.companyname, ls.name

---

Aggregation Patterns

Count Selected Values per Record

-- How many lead sources per customer?
SELECT
    c.id,
    c.companyname,
    COUNT(m.maptwo) AS lead_source_count
FROM Customer c
LEFT JOIN map_customer_custentity_lead_sources m ON m.mapone = c.id
WHERE c.isinactive = 'F'
GROUP BY c.id, c.companyname
ORDER BY lead_source_count DESC

Concatenate Values into Single Column

-- List lead sources as comma-separated string
SELECT
    c.id,
    c.companyname,
    LISTAGG(ls.name, ', ') WITHIN GROUP (ORDER BY ls.name) AS lead_sources
FROM Customer c
LEFT JOIN map_customer_custentity_lead_sources m ON m.mapone = c.id
LEFT JOIN customlist_lead_sources ls ON ls.id = m.maptwo
WHERE c.isinactive = 'F'
GROUP BY c.id, c.companyname
ORDER BY c.companyname

Sample Result:

id	companyname	lead_sources
100	Acme Corp	Referral, Trade Show, Web Search
101	Beta Inc	Direct Mail
102	Gamma LLC	NULL

Find Records with No Selections

-- Customers with no lead sources selected
SELECT
    c.id,
    c.companyname
FROM Customer c
LEFT JOIN map_customer_custentity_lead_sources m ON m.mapone = c.id
WHERE c.isinactive = 'F'
  AND m.mapone IS NULL
ORDER BY c.companyname

---

BUILTIN.MNFILTER Function

An alternative to joining mapping tables directly. Useful for filtering in WHERE clauses.

Syntax

BUILTIN.MNFILTER(field_name, operator, lookup_type, boolean_condition, search_value)

Parameter	Description	Values
field_name	The multi-select field	Field internal ID
operator	Comparison type	See operators below
lookup_type	Match by ID or name	'' (ID) or 'DF' (name)
boolean_condition	Expected result	'TRUE' or 'FALSE'
search_value	Value to match	ID or name string

Operators

Operator	Description
MN_INCLUDE	Records containing the specified value
MN_INCLUDE_ALL	Records containing ALL specified values
MN_INCLUDE_EXACTLY	Records where ONLY this value is selected
MN_EXCLUDE	Records NOT containing the specified value
MN_EXCLUDE_ALL	Records excluding ALL specified values
MN_EXCLUDE_EXACTLY	Records where value is excluded exclusively

Examples

Find records that INCLUDE a value:

-- Customers with "Referral" lead source (by ID)
SELECT id, companyname
FROM Customer
WHERE BUILTIN.MNFILTER(custentity_lead_sources, 'MN_INCLUDE', '', 'TRUE', '3') = 'T'

-- Same query by name
SELECT id, companyname
FROM Customer
WHERE BUILTIN.MNFILTER(custentity_lead_sources, 'MN_INCLUDE', 'DF', 'TRUE', 'Referral') = 'T'

Find records that EXCLUDE a value:

-- Customers WITHOUT "Web Search" lead source
SELECT id, companyname
FROM Customer
WHERE BUILTIN.MNFILTER(custentity_lead_sources, 'MN_EXCLUDE', 'DF', 'TRUE', 'Web Search') = 'T'

Find records with EXACTLY one value:

-- Customers with ONLY "Referral" selected (no other lead sources)
SELECT id, companyname
FROM Customer
WHERE BUILTIN.MNFILTER(custentity_lead_sources, 'MN_INCLUDE_EXACTLY', 'DF', 'TRUE', 'Referral') = 'T'

Find records with ALL specified values:

-- Customers with BOTH "Referral" AND "Trade Show"
SELECT id, companyname
FROM Customer
WHERE BUILTIN.MNFILTER(custentity_lead_sources, 'MN_INCLUDE', 'DF', 'TRUE', 'Referral') = 'T'
  AND BUILTIN.MNFILTER(custentity_lead_sources, 'MN_INCLUDE', 'DF', 'TRUE', 'Trade Show') = 'T'

---

Real-World Examples

Item Categories Report

-- Items with their categories (multi-select field)
SELECT
    i.id,
    i.itemid,
    i.displayname,
    LISTAGG(cat.name, ', ') WITHIN GROUP (ORDER BY cat.name) AS categories
FROM Item i
LEFT JOIN map_item_custitem_categories m ON m.mapone = i.id
LEFT JOIN customlist_categories cat ON cat.id = m.maptwo
WHERE i.isinactive = 'F'
GROUP BY i.id, i.itemid, i.displayname
ORDER BY i.itemid

Employee Skills Matrix

-- Employees and their skills
SELECT
    e.id,
    e.entityid AS employee_name,
    e.title,
    d.name AS department,
    LISTAGG(s.name, ', ') WITHIN GROUP (ORDER BY s.name) AS skills
FROM Employee e
LEFT JOIN Department d ON d.id = e.department
LEFT JOIN map_employee_custentity_skills m ON m.mapone = e.id
LEFT JOIN customlist_skills s ON s.id = m.maptwo
WHERE e.isinactive = 'F'
GROUP BY e.id, e.entityid, e.title, d.name
ORDER BY d.name, e.entityid

Transaction with Multi-Select Body Field

-- Sales Orders with regions (custom body field)
SELECT
    t.id,
    t.tranid,
    t.trandate,
    BUILTIN.DF(t.entity) AS customer,
    LISTAGG(r.name, ', ') WITHIN GROUP (ORDER BY r.name) AS regions
FROM Transaction t
LEFT JOIN map_transaction_custbody_regions m ON m.mapone = t.id
LEFT JOIN customlist_regions r ON r.id = m.maptwo
WHERE t.type = 'SalesOrd'
  AND t.trandate >= ADD_MONTHS(SYSDATE, -3)
GROUP BY t.id, t.tranid, t.trandate, BUILTIN.DF(t.entity)
ORDER BY t.trandate DESC

Role Subsidiaries (System Multi-Select)

-- Roles and their effective subsidiaries
SELECT
    r.id,
    r.name AS role_name,
    s.name AS subsidiary
FROM Role r
INNER JOIN map_role_effectivesubsidiaries m ON m.mapone = r.id
INNER JOIN Subsidiary s ON s.id = m.maptwo
WHERE r.isinactive = 'F'
ORDER BY r.name, s.name

---

Comparison: Mapping Table vs MNFILTER

Approach	Best For	Pros	Cons
Mapping Table JOIN	Reports, aggregations, LISTAGG	Full control, can get all values	Need to know table name
BUILTIN.MNFILTER	Filtering in WHERE clause	Simpler syntax, works by name	Only for filtering, not selecting

When to Use Each

Use Mapping Table:

• When you need to display/list selected values
• When aggregating (COUNT, LISTAGG)
• When joining to get related record details

Use MNFILTER:

• When filtering records in WHERE clause
• When you don't need to display the values
• Quick existence checks

---

Troubleshooting

"RELATIONSHIP FIELD" in Results

When selecting a multi-select field directly, you may see "RELATIONSHIP FIELD":

-- This returns "RELATIONSHIP FIELD"
SELECT custentity_lead_sources FROM Customer WHERE id = 100

Solution: Use BUILTIN.DF() or join to mapping table:

-- Using BUILTIN.DF (returns comma-separated string)
SELECT BUILTIN.DF(custentity_lead_sources) AS lead_sources
FROM Customer
WHERE id = 100

-- Using mapping table (returns individual rows)
SELECT ls.name
FROM map_customer_custentity_lead_sources m
INNER JOIN customlist_lead_sources ls ON ls.id = m.maptwo
WHERE m.mapone = 100

Cannot Find Mapping Table

If you can't find the mapping table:

1. Check Records Catalog - Setup → Records Catalog
2. Verify field is multi-select - Single-select fields don't create mapping tables
3. Check field ID - Use exact internal ID, not label
4. Try alternate table names - Some records use different base table names

---

Quick Reference

Mapping Table Pattern

FROM {source_table} main
INNER JOIN map_{source_table}_{field_id} m ON m.mapone = main.id
INNER JOIN {target_list} target ON target.id = m.maptwo

MNFILTER Pattern

WHERE BUILTIN.MNFILTER({field_id}, 'MN_INCLUDE', 'DF', 'TRUE', 'Value Name') = 'T'

Aggregation Pattern

SELECT
    main.id,
    main.name,
    LISTAGG(target.name, ', ') WITHIN GROUP (ORDER BY target.name) AS values
FROM {source_table} main
LEFT JOIN map_{source_table}_{field_id} m ON m.mapone = main.id
LEFT JOIN {target_list} target ON target.id = m.maptwo
GROUP BY main.id, main.name

---

See Also

• Transaction Link Tables - Transaction-to-transaction linking
• Junction Tables - Complete list of NetSuite junction tables