Home/ Real-World NetSuite Examples/Environment-Proof Custom List Lookups (SuiteScript 2.1 + N/query)

Environment-Proof Custom List Lookups (SuiteScript 2.1 + N/query)

💼 The Problem (Why scripts break across environments)

When you set a Custom List field in SuiteScript, you normally pass the internal ID of the list value. But internal IDs differ per environment (Sandbox vs Production), so hard-coding them causes subtle bugs and post-deploy hotfixes. Internal IDs can diverge if you change list values or deploy with SDF; SuiteBundler often preserves order, while SDF may reassign IDs entirely.

Bottom line: depending on internal IDs is brittle. We need an environment-agnostic way.

✅ The Better Way

Use script IDs for list values (e.g., val_low, val_medium)—they’re consistent across environments—then resolve the runtime internal ID via code. Enable Show ID Field on Sublists to see/edit value script IDs, and standardize them across Sandbox and Production.

🧠 Approach Overview

  1. Give each Custom List value a stable script ID (e.g., val_risk_high).
  2. At runtime, look up the internal ID by script ID using N/query (cleaner than N/search).
  3. Cache results per list to keep governance low (≈10 units per list load, then free lookups).

🧩 Drop-in Helper (SuiteScript 2.1)

A lightweight utility that resolves script ID → internal ID and caches the list. (Human-readable; adapt the paths/IDs for your account.)

/**
 * @NApiVersion 2.1
 * @NModuleScope Public
 * Helper: Resolve Custom List value internal IDs by script ID, environment-agnostic.
 */
define(['N/query'], (query) => {

  // Simple in-memory cache by list type
  const cache = new Map(); // key: listType (e.g., 'customlist_risk_level'), value: Map<scriptidUpper, internalId>

  function loadListMap(listType) {
    if (cache.has(listType)) return cache.get(listType);

    // One SuiteQL to fetch all value IDs + script IDs for the list
    // Note: SuiteQL supports binding the list type table via "?" in NetSuite's documented pattern.
    const sql = 'SELECT id, scriptid FROM ?';
    const rows = query.runSuiteQL({ query: sql, params: [listType] }).asMappedResults();

    const map = new Map();
    rows.forEach(r => {
      // Script IDs in results are uppercased; normalize for consistent lookup
      map.set(String(r.scriptid || '').toUpperCase(), String(r.id));
    });

    cache.set(listType, map);
    return map;
  }

  /**
   * Returns internal ID for the given list value script ID (case-insensitive).
   * @param {string} listType - e.g., 'customlist_risk_level'
   * @param {string} valueScriptId - e.g., 'val_risk_high'
   * @returns {string|null}
   */
  function getListValueId(listType, valueScriptId) {
    const map = loadListMap(listType);
    return map.get(String(valueScriptId || '').toUpperCase()) || null;
  }

  return { getListValueId };
});

Why this works: one SuiteQL query loads the list once; subsequent lookups hit memory only—keeping governance to ~10 units per list, then near-zero, which mirrors the guidance in the source article’s utility pattern.

🧪 Using the Helper in Your Scripts

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 */
define(['N/record', './lib/listLookup'], (record, listLookup) => {
  const LIST_TYPE = 'customlist_risk_level';

  function afterSubmit(ctx) {
    if (ctx.type !== ctx.UserEventType.CREATE && ctx.type !== ctx.UserEventType.EDIT) return;

    const rec = record.load({ type: 'customrecord_risk_assessment', id: ctx.newRecord.id, isDynamic: true });

    // Example: choose value by business logic, using stable script IDs
    const targetScriptId = 'val_risk_high'; // e.g., computed from rules
    const internalId = listLookup.getListValueId(LIST_TYPE, targetScriptId);

    if (internalId) {
      rec.setValue({ fieldId: 'custrecord_risk_level', value: internalId });
      rec.save({ enableSourcing: true, ignoreMandatoryFields: true });
    }
  }

  return { afterSubmit };
});

🔍 Setup Checklist

  1. Turn on Show ID Field on Sublists to view/edit value script IDs.
  2. Give every list value a clean, consistent script ID (val_…) in Sandbox and Production.
  3. Use the helper to resolve at runtime; never hard-code internal IDs.
  4. Prefer N/query (cleaner/no loops) over N/search for the lookup; reserve N/search only if you must.
  5. If you deploy lists with SDF, expect internal ID reshuffles; script IDs remain stable—your code won’t care. (SuiteBundler often preserves ID order, but don’t rely on it.)

It prefers SuiteQL (N/query) for one-shot fetches and falls back to N/search if needed. It also includes in-memory caching (with TTL) so you pay governance once per list.

/lib/listLookup.js (SuiteScript 2.1)

/**
 * @NApiVersion 2.1
 * @NModuleScope Public
 *
 * listLookup.js
 * Environment-proof resolver for Custom List values.
 *
 * Features:
 *  - Resolve list value internal IDs by value script ID or by value name
 *  - SuiteQL primary path, falls back to N/search
 *  - In-memory cache with TTL
 *  - Input validation to avoid SQL injection in table name
 *
 * Usage:
 *   const id = listLookup.getByScriptId('customlist_risk_level', 'val_risk_high');
 *   const id2 = listLookup.getByName('customlist_risk_level', 'High');
 */

define(['N/query', 'N/search', 'N/log'], (query, search, log) => {
  // ---- Config ----
  const DEFAULT_TTL_MS = 5 * 60 * 1000; // 5 minutes
  const VALID_LIST_ID = /^customlist_[a-z0-9_]+$/i; // whitelists custom list table names

  // Cache format: Map<listType, { expires:number, byScriptId:Map, byName:Map }>
  const CACHE = new Map();

  // ---- Public API ----
  function getByScriptId(listType, valueScriptId, opts = {}) {
    const cache = ensureLoaded(listType, opts.ttlMs);
    if (!valueScriptId) return null;
    return cache.byScriptId.get(String(valueScriptId).toUpperCase()) || null;
  }

  function getByName(listType, valueName, opts = {}) {
    const cache = ensureLoaded(listType, opts.ttlMs);
    if (!valueName) return null;
    return cache.byName.get(normalizeName(valueName)) || null;
  }

  function getAll(listType, opts = {}) {
    const cache = ensureLoaded(listType, opts.ttlMs);
    // Return a shallow copy so callers can't mutate our cache
    return {
      byScriptId: new Map(cache.byScriptId),
      byName: new Map(cache.byName),
    };
  }

  function preload(listTypes, opts = {}) {
    (listTypes || []).forEach((lt) => ensureLoaded(lt, opts.ttlMs));
  }

  function clearCache(listType) {
    if (listType) CACHE.delete(listType);
    else CACHE.clear();
  }

  // ---- Internals ----
  function ensureLoaded(listType, ttlMs = DEFAULT_TTL_MS) {
    validateListType(listType);
    const existing = CACHE.get(listType);
    const now = Date.now();
    if (existing && existing.expires > now) return existing;

    let records;
    try {
      records = fetchViaSuiteQL(listType);
    } catch (e) {
      log.debug('SuiteQL failed; falling back to N/search', { listType, error: e.message });
      records = fetchViaSearch(listType);
    }

    const byScriptId = new Map();
    const byName = new Map();

    (records || []).forEach((r) => {
      const id = String(r.id || '').trim();
      const scriptid = String(r.scriptid || '').trim().toUpperCase();
      const name = normalizeName(r.name);
      if (id) {
        if (scriptid) byScriptId.set(scriptid, id);
        if (name) byName.set(name, id);
      }
    });

    const entry = { expires: now + (Number(ttlMs) || DEFAULT_TTL_MS), byScriptId, byName };
    CACHE.set(listType, entry);
    return entry;
  }

  function fetchViaSuiteQL(listType) {
    // Validate table name (listType) strictly; SuiteQL cannot bind identifiers with params.
    validateListType(listType);

    const sql = `SELECT id, scriptid, name FROM ${listType}`;
    const rows = query.runSuiteQL({ query: sql }).asMappedResults();
    return rows.map((r) => ({
      id: r.id,
      scriptid: r.scriptid,
      name: r.name,
    }));
  }

  function fetchViaSearch(listType) {
    // For custom lists, search.create({ type: 'customlist_xxx' }) is supported.
    const s = search.create({
      type: listType,
      filters: [],
      columns: ['internalid', 'scriptid', 'name'],
    });

    const out = [];
    s.run().each((res) => {
      out.push({
        id: res.getValue({ name: 'internalid' }),
        scriptid: res.getValue({ name: 'scriptid' }),
        name: res.getValue({ name: 'name' }),
      });
      return true;
    });
    return out;
  }

  function validateListType(listType) {
    if (!VALID_LIST_ID.test(String(listType || ''))) {
      throw new Error(
        `Invalid custom list id "${listType}". Expected format like "customlist_risk_level".`
      );
    }
  }

  function normalizeName(name) {
    return String(name || '').trim().toUpperCase();
  }

  return {
    getByScriptId,
    getByName,
    getAll,
    preload,
    clearCache,
  };
});

Quick usage examples

1) User Event (afterSubmit) — set a list field by value script ID

/**
 * @NApiVersion 2.1
 * @NScriptType UserEventScript
 */
define(['N/record', './lib/listLookup'], (record, listLookup) => {
  const LIST = 'customlist_risk_level';
  const FIELD = 'custrecord_risk_level';

  function afterSubmit(ctx) {
    if (ctx.type !== ctx.UserEventType.CREATE && ctx.type !== ctx.UserEventType.EDIT) return;

    const rec = record.load({ type: 'customrecord_risk_assessment', id: ctx.newRecord.id, isDynamic: true });

    // Your business logic decides which value to use:
    const scriptIdToSet = 'val_risk_high';
    const internalId = listLookup.getByScriptId(LIST, scriptIdToSet);

    if (internalId) {
      rec.setValue({ fieldId: FIELD, value: internalId });
      rec.save({ enableSourcing: true, ignoreMandatoryFields: true });
    }
  }

  return { afterSubmit };
});

2) Client Script (fieldChanged) — set by display name

/**
 * @NApiVersion 2.1
 * @NScriptType ClientScript
 */
define(['./lib/listLookup'], (listLookup) => {
  const LIST = 'customlist_risk_level';
  const FIELD = 'custrecord_risk_level';

  function fieldChanged(ctx) {
    if (ctx.fieldId !== 'custrecord_score') return;
    const rec = ctx.currentRecord;
    const score = Number(rec.getValue('custrecord_score')) || 0;

    const label = score >= 80 ? 'High' : score >= 50 ? 'Medium' : 'Low';
    const id = listLookup.getByName(LIST, label);
    if (id) rec.setValue({ fieldId: FIELD, value: id });
  }

  return { fieldChanged };
});

3) Map/Reduce (getInputData) — preload multiple lists once

/**
 * @NApiVersion 2.1
 * @NScriptType MapReduceScript
 */
define(['N/search', './lib/listLookup'], (search, listLookup) => {
  function getInputData() {
    listLookup.preload(['customlist_risk_level', 'customlist_priority']); // warms the cache
    return search.create({ type: 'customrecord_risk_assessment' });
  }
  function map(ctx) { /* ... your logic ... */ }
  function reduce(ctx) {}
  function summarize() {}
  return { getInputData, map, reduce, summarize };
});

Notes & Best Practices

  • No hard-coded internal IDs: always resolve via value script IDs (stable across environments) or names.
  • Safety: we strictly validate listType with a regex, since SuiteQL can’t bind identifiers.
  • Performance: one SuiteQL pull per list → cached for 5 minutes (configurable via ttlMs).
  • Fallback: if SuiteQL isn’t available (permissions, feature toggle), the helper uses N/search.

If you want, I can also provide a tiny TypeScript .d.ts for this module or wire it into your SDF project structure with a ready-to-deploy manifest.

⚠️ Gotchas & Best Practices

  • Governance: load each list once and cache in-memory. (Approx. 10 units per list, then free.)
  • Case sensitivity: SuiteQL returns script IDs uppercase—normalize lookups to uppercase.
  • Define context: don’t run NetSuite APIs inside the define callback; initialize in your entry point.
  • Testing: verify values exist in both environments and script IDs exactly match (no typos).

🧠 Why this matters

This pattern future-proofs your customizations. You can reorder list values, add new ones, or deploy via SDF without breaking scripts or rushing last-minute ID hotfixes. It’s a small utility that saves hours over a project’s lifetime.

Leave a Reply