Fix Slow Workflow Field Search in NetSuite SuiteScript

Loading every workflow, state, and action record individually to find field references burns through governance units and takes minutes on a mature instance. A single SuiteQL query returns the same data in seconds.

Why the Record-Load Loop Fails

The typical approach, saved search for workflow IDs, record.load each workflow, iterate the states sublist, record.load each state, iterate the actions sublist, record.load each action, creates an N+1 query explosion. On an account with 200 workflows averaging 5 states and 8 actions each, that's 8,200 record.load calls. Each call consumes governance units, and a Scheduled Script caps at 10,000 units total. You hit the limit before finishing.

The workflow record type exposes states through a sublist, not a direct join. States and actions are separate record types (workflowstate, workflowaction) with their own internal IDs. SuiteScript's N/record module has no batch-load API. The loop is the only way if you stay in the record API.

One SuiteQL Query Replaces Thousands of Loads

SuiteQL joins across workflow, workflowstate, workflowaction, and workflowactionscript in a single execution. The query below pulls every field reference, header fields set by "Set Field Value" actions, line fields on sublist actions, and script content from custom action scripts, with a fraction of the governance cost.

SELECT
    w.id AS workflow_id,
    w.name AS workflow_name,
    w.isinactive AS workflow_inactive,
    ws.id AS state_id,
    ws.name AS state_name,
    wa.id AS action_id,
    wa.actiontype AS action_type,
    wa.field AS field_id,
    wa.value AS field_value,
    wa.sublist AS sublist_id,
    was.scriptid AS script_id,
    was.scriptcontent AS script_content
FROM workflow w
JOIN workflowstate ws ON ws.workflow = w.id
JOIN workflowaction wa ON wa.workflowstate = ws.id
LEFT JOIN workflowactionscript was ON was.workflowaction = wa.id
WHERE w.isinactive = 'F'
ORDER BY w.name, ws.sequence, wa.sequence

Run it through N/query:

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/query', 'N/log', 'N/file'], (query, log, file) => {
    const execute = (scriptContext) => {
        const sql = `SELECT w.id AS workflow_id, w.name AS workflow_name,
            w.isinactive AS workflow_inactive, ws.id AS state_id,
            ws.name AS state_name, wa.id AS action_id,
            wa.actiontype AS action_type, wa.field AS field_id,
            wa.value AS field_value, wa.sublist AS sublist_id,
            was.scriptid AS script_id, was.scriptcontent AS script_content
        FROM workflow w
        JOIN workflowstate ws ON ws.workflow = w.id
        JOIN workflowaction wa ON wa.workflowstate = ws.id
        LEFT JOIN workflowactionscript was ON was.workflowaction = wa.id
        WHERE w.isinactive = 'F'
        ORDER BY w.name, ws.sequence, wa.sequence`;

        const results = query.runSuiteQL({ query: sql });
        const csvLines = ['Workflow ID,Workflow Name,State ID,State Name,Action ID,Action Type,Field ID,Field Value,Sublist,Script ID,Script Content'];

        results.asMappedResults().forEach(row => {
            const scriptContent = row.script_content ? row.script_content.replace(/"/g, '""') : '';
            csvLines.push([
                row.workflow_id, row.workflow_name, row.state_id, row.state_name,
                row.action_id, row.action_type, row.field_id || '', row.field_value || '',
                row.sublist_id || '', row.script_id || '', `"${scriptContent}"`
            ].join(','));
        });

        file.create({
            name: 'workflow_field_audit.csv',
            fileType: file.Type.CSV,
            contents: csvLines.join('\n')
        }).save();
    };
    return { execute };
});

The field column on workflowaction stores the internal ID of the target field for "Set Field Value" and "Go To Record" actions. For sublist actions, sublist tells you which line list (usually item, expense, or time) and field is the column field ID. These internal IDs are permanent identifiers, unlike labels, they don't change when someone renames a field in the UI.

Parsing Script Content for Hidden References

Custom action scripts (actiontype = 'SCRIPT') hide field references inside scriptcontent. The query above returns the raw script. Post-process it to extract getValue, setValue, getSublistValue, setSublistValue calls:

const fieldRefRegex = /\.(get|set)(Sublist)?Value\(\s*\{\s*fieldId\s*:\s*['"]([^'"]+)['"]/g;
const matches = scriptContent.matchAll(fieldRefRegex);
for (const match of matches) {
    const method = match[1];
    const isSublist = !!match[2];
    const fieldId = match[3];
    // log or collect fieldId with context
}

This catches rec.getValue({ fieldId: 'custbody_approval_limit' }) and rec.setSublistValue({ sublistId: 'item', fieldId: 'custcol_commission_rate', line: i }) patterns. It misses dynamic field IDs built from variables, those require static analysis or runtime instrumentation.

Governance Comparison

SuiteQL consumes significantly fewer governance units than the equivalent record API loop. The same data that crashes a scheduled script in the record API finishes well under the limit. For details on script type limits, see the SuiteScript governance documentation.

Edge Cases to Handle

• Inactive workflows: The WHERE w.isinactive = 'F' filter skips them. Remove it if you need a full historical audit.
• Workflow instances vs. definitions: This queries definitions only. Runtime instances (workflowinstance) store execution state, not field configuration.
• Formula fields: Actions referencing formula fields show the formula's internal ID (e.g., custcol_formula_123). Cross-reference with customfield table if you need the formula definition.
• Bundle-installed workflows: w.ismanaged = 'T' marks bundled workflows. You may want to exclude them from cleanup reports.

Enriching Results with Custom Field Metadata

The raw query returns internal IDs like custbody_approval_limit or custcol_commission_rate. To make the audit immediately actionable, join the customfield table and pull the label, type, and owning record. Add this to the SELECT clause and a LEFT JOIN:

LEFT JOIN customfield cf ON cf.name = wa.field

Then include cf.label AS field_label, cf.type AS field_type, cf.recordtype AS field_record_type in the output. This tells you at a glance whether custbody_123 is a Currency field on the Vendor Bill or a List/Record field on the Purchase Order, no cross-referencing needed. For standard fields (no cust prefix), the join returns null; wrap the label with COALESCE(cf.label, wa.field) to fall back to the internal ID.

What to Do With the Output

The CSV gives you a complete field-usage map. Filter for:

• Fields that no longer exist (deleted custom fields show as orphaned IDs)
• Duplicate Set Field Value actions targeting the same field across states
• Hardcoded values in field_value that should be parameters
• Script actions referencing deprecated APIs (nlapi* calls)

Feed the list into a cleanup project or hand it to the team owning the next SuiteScript 2.1 migration. The query runs in under five seconds on a 500-workflow account, schedule it weekly and diff the output to catch drift.