An empty list is not an error message

The report

A warehouse Suitelet loads open sales orders for picking. One morning a user at a pack station opened it and saw an empty grid. A supervisor, opening it a minute later, saw hundreds of orders. The script log had two errors from the first user’s session and nothing on screen to say anything had gone wrong.

The bug that hid the bug

The data function looked like this:

function loadOrders(filters) {
    try {
        return query.runSuiteQL({ query: buildSql(filters) }).asMappedResults();
    } catch (e) {
        log.error('loadOrders', e);
        return [];
    }
}

Reasonable instinct: don’t crash the page. But it erases the difference between “there is no work” and “we couldn’t find out.” To a warehouse user, an empty pick list means go do something else. That’s the worst possible response to a failed query.

“Nothing to do” and “couldn’t check” must never look the same.

The first fix was to return the failure, not hide it:

} catch (e) {
    log.error('loadOrders', { message: e.message, sql: sql });
    return { ok: false, message: 'Could not load orders. ' + friendly(e) };
}

The page now shows a plain message when a load fails, and the log carries the SQL that failed, which is what the first investigation was missing.

The theory that turned out to be wrong

The obvious explanation was permissions: the user’s role couldn’t see some record the query touched. That’s common, and the usual fix is to set the Suitelet deployment to execute as a role that can.

Except it already was. With the deployment executing as an administrator role, the user’s own role never reaches the query. Permissions couldn’t explain it.

The actual cause

The difference between the two sessions was the filters. This user had a location and a date range set, and the query built its WHERE clause by concatenating filter values into the SQL string. One value arrived in a format the code didn’t expect, and the assembled SQL was invalid. Same base query, different string, different result.

The fix is the one every SQL developer knows and every quick Suitelet skips: bind parameters, and validate filter values before they get near the query.

const where = ['t.type = ?'];
const params = ['SalesOrd'];

if (filters.location && /^\d+$/.test(filters.location)) {
    where.push('tl.location = ?');
    params.push(Number(filters.location));
}
if (filters.from && isIsoDate(filters.from)) {
    where.push("t.trandate >= TO_DATE(?, 'YYYY-MM-DD')");
    params.push(filters.from);
}

query.runSuiteQL({ query: base + ' WHERE ' + where.join(' AND '), params });

A bad value is now dropped and reported, not concatenated into the SQL.

What to take from it

1. Never let a failed load render as an empty result. Return a failure the page can show.
2. Log the assembled query with the error. Without it you’re guessing.
3. Bind parameters. Every filter value concatenated into SQL is a different query.
4. When a permissions theory doesn’t survive the execute-as setting, drop it and look at what differs between the sessions.

Related: five ways a NetSuite cash forecast goes quietly wrong, and MAP_REDUCE_ALREADY_RUNNING is not an error.