Handling Multi-Select Fields in NetSuite SuiteQL Queries

When working with NetSuite, retrieving data from multi-select fields in SuiteQL queries can be challenging due to the way NetSuite structures these relationships. Multi-select fields store multiple values, often linked via mapping tables, requiring additional joins to retrieve related data properly.

Example Query

The following query demonstrates how to retrieve records associated with a multi-select field. This example retrieves information about bag generations and merges in a custom NetSuite implementation, including associated used bags, which are stored in a multi-select field.

SELECT
    core_tracking.name AS bag_core_name,
    bag_generation.ID AS bag_generation_id,
    bag_generation.name AS bag_name,
    bag_merge.ID AS bag_merge_id,
    bag_merge.custrecord_jj_bagmerge_date AS merge_date,
    bag_merge.custrecord_jj_bagmerge_qty AS merge_qty,
    bag_merge.custrecord_jj_bagmerge_dept AS merge_dept,
    created_bag_gen.name AS created_bag_name,  -- Bag created name
    used_bags.bag_name AS used_bag_name,  -- Multi-select Used Bags
    department.name AS department_name
FROM
    CUSTOMRECORD_JJ_BAG_CORE_TRACKING core_tracking
    LEFT JOIN TRANSACTION txn
        ON core_tracking.custrecord_jj_bagcore_wo = txn.ID
    LEFT JOIN CUSTOMRECORD_JJ_BAG_GENERATION bag_generation
        ON core_tracking.ID = bag_generation.custrecord_jj_baggen_bagcore
    LEFT JOIN CUSTOMRECORD_JJ_BAG_MERGE bag_merge
        ON core_tracking.ID = bag_merge.custrecord_jj_bagmerge_bagcore
        AND bag_generation.ID = bag_merge.custrecord_jj_bagmerge_bagcreated
    LEFT JOIN CUSTOMRECORD_JJ_BAG_GENERATION created_bag_gen
        ON bag_merge.custrecord_jj_bagmerge_bagcreated = created_bag_gen.ID
    LEFT JOIN CUSTOMRECORD_JJ_MANUFACTURING_DEPT department
        ON bag_merge.custrecord_jj_bagmerge_dept = department.ID
    LEFT JOIN (
        SELECT
            map_table.mapone AS merge_id,
            bag_gen.name AS bag_name,
            bag_gen.ID AS bag_id
        FROM
            MAP_customrecord_jj_bag_merge_custrecord_jj_bagmerge_bagsused map_table
        LEFT JOIN
            CUSTOMRECORD_JJ_BAG_GENERATION bag_gen
        ON
            map_table.maptwo = bag_gen.ID
    ) AS used_bags
    ON bag_merge.ID = used_bags.merge_id
WHERE
    NVL(bag_generation.isinactive, 'F') = 'F'
    AND NVL(core_tracking.isinactive, 'F') = 'F'
    AND UPPER(txn.tranid) = ?
    AND NVL(bag_merge.isinactive, 'F') = 'F'
    AND bag_merge.custrecord_jj_bagmerge_bagcreated IS NOT NULL;

Explanation of Handling Multi-Select Fields

1. Mapping Table Approach: Multi-select fields in NetSuite use mapping tables (e.g., MAP_customrecord_jj_bag_merge_custrecord_jj_bagmerge_bagsused). These tables store relationships between parent records and their selected multi-select values.
2. Joining the Mapping Table: The mapping table is joined with the related record table (CUSTOMRECORD_JJ_BAG_GENERATION) to fetch meaningful data.
3. Alias for Clarity: The subquery (used_bags) extracts mapped values separately and is joined back into the main query.