Build a Daily Inventory Zero-Stock History in NetSuite

You need to track how many days each item sits at zero available quantity, but NetSuite's standard inventory reports only show the current snapshot. The workaround is a custom record that logs each day's zero-stock items, then a summary search that counts occurrences per item per period. Here's the complete setup.

Create the Custom Record Type

Start by defining the container that will hold one row per item per day.

1. Go to Setup > Customization > Record Types > New
2. Name: Zero Stock History (ID: customrecord_zero_stock_history)
3. Access Type: No Permission Required (or restrict to specific roles)
4. Allow UI Access: Unchecked, this record is script-populated only
5. Add these fields:

Save. Deploy the record. No workflows, no scripts attached yet.

The custom record ID (customrecord_zero_stock_history) and field IDs (custrecord_zsh_*) are what your SuiteScript will reference. Keep them consistent. Every NetSuite record has an internal ID and every field has a field ID, these are the permanent identifiers used by SuiteScript and saved searches.

Build the Source Saved Search

This search feeds the script. It returns every inventory item with zero available at the moment it runs.

1. Reports > Saved Searches > All Saved Searches > New > Item
2. Criteria tab:

• Type = Inventory Item
• Quantity Available ≤ 0
• (Optional) Location = specific warehouse if you track per-location

3. Results tab, add columns:

• Internal ID (Item) → Summary Type: Group
• Display Name → Summary Type: Group
• Location → Summary Type: Group (if using locations)
• Quantity Available → Summary Type: Minimum (will be 0 or negative)

4. Save as Zero Stock Items Daily (ID: customsearch_zero_stock_daily)

Run it once to verify results. You should see only items currently at or below zero.

Write the Scheduled SuiteScript

This script runs nightly, executes the saved search, and creates one custom record per result row.

Create File Cabinet > SuiteScripts > ZeroStockHistoryPopulate.js:

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 * @NModuleScope SameAccount
 */
define(['N/search', 'N/record', 'N/runtime', 'N/log'], (search, record, runtime, log) => {
  const CUSTOM_RECORD = 'customrecord_zero_stock_history';
  const SEARCH_ID = 'customsearch_zero_stock_daily';
  const BATCH_SIZE = 1000; // governance safety

  function execute(context) {
    const scriptObj = runtime.getCurrentScript();
    const today = new Date();

    try {
      const savedSearch = search.load({ id: SEARCH_ID });
      const resultSet = savedSearch.run();
      let processed = 0;

      resultSet.each((result) => {
        if (scriptObj.getRemainingUsage() < 100) {
          log.audit('Governance low, yielding', { remaining: scriptObj.getRemainingUsage() });
          return false; // stop early, let next scheduled run pick up
        }

        const itemId = result.getValue({ name: 'internalid', summary: 'GROUP' });
        const itemName = result.getValue({ name: 'displayname', summary: 'GROUP' });
        const locationId = result.getValue({ name: 'location', summary: 'GROUP' }) || '';
        const availQty = parseFloat(result.getValue({ name: 'quantityavailable', summary: 'MINIMUM' })) || 0;

        // Idempotency: skip if today's record already exists for this item+location
        const dupFilters = [
          ['custrecord_zsh_log_date', 'on', today],
          'AND',
          ['custrecord_zsh_item', 'anyof', itemId]
        ];
        if (locationId) {
          dupFilters.push('AND', ['custrecord_zsh_location', 'anyof', locationId]);
        } else {
          dupFilters.push('AND', ['custrecord_zsh_location', 'anyof', '@NONE@']);
        }

        const dupSearch = search.create({
          type: CUSTOM_RECORD,
          filters: dupFilters,
          columns: ['internalid']
        });
        const existing = dupSearch.run().getRange({ start: 0, end: 1 });
        if (existing.length) {
          return true; // already logged today
        }

        const rec = record.create({ type: CUSTOM_RECORD, isDynamic: true });
        rec.setValue({ fieldId: 'custrecord_zsh_log_date', value: today });
        rec.setValue({ fieldId: 'custrecord_zsh_item', value: itemId });
        rec.setValue({ fieldId: 'custrecord_zsh_item_desc', value: itemName });
        if (locationId) rec.setValue({ fieldId: 'custrecord_zsh_location', value: locationId });
        rec.setValue({ fieldId: 'custrecord_zsh_avail_qty', value: availQty });
        rec.save({ ignoreMandatoryFields: true });

        processed++;
        return processed < BATCH_SIZE;
      });

      log.audit('Zero Stock History populated', { date: today, recordsCreated: processed });
    } catch (e) {
      log.error('Zero Stock History script failed', e);
      throw e;
    }
  }

  return { execute };
});

Deploy it:

1. Customization > Scripting > Scripts > New
2. Script Type: Scheduled
3. Script File: upload the .js file
4. Deployments > New:

• Status: Released
• Run Every: Day
• Time: 11:55 PM (after daily transactions settle)
• Log Level: Audit

Create the Summary Saved Search

Now the reporting layer, count days at zero per item per month.

1. Reports > Saved Searches > All Saved Searches > New > Custom Record > Zero Stock History
2. Criteria tab:

• Log Date ≥ First Day of Current Fiscal Year (or a fixed start date)

3. Results tab, add columns with Summary Type: Group unless noted:

4. Sort by Item, then Log Date (Month)
5. Save as Zero Stock Days by Month

Run it. You'll see each item, each month, and the count of days it appeared in the zero-stock log.

Add a Rolling 12-Month View

Duplicate the search, change Criteria to:

• Log Date ≥ Formula (Date): ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11) (Oracle syntax)
• Log Date ≤ Formula (Date): LAST_DAY(SYSDATE)

This gives a trailing 12-month trend without hardcoding dates.

Retention and Cleanup

At 60 rows × 365 days = ~22K records/year, trivial for NetSuite. But if you scale to thousands of items or multiple locations, add a second scheduled script:

// ZeroStockHistoryPurge.js - run monthly
define(['N/search', 'N/record', 'N/runtime'], (search, record, runtime) => {
  const CUSTOM_RECORD = 'customrecord_zero_stock_history';
  const RETENTION_MONTHS = 24;

  function execute() {
    const cutoff = new Date();
    cutoff.setMonth(cutoff.getMonth() - RETENTION_MONTHS);

    const delSearch = search.create({
      type: CUSTOM_RECORD,
      filters: [['custrecord_zsh_log_date', 'before', cutoff]],
      columns: ['internalid']
    });

    let deleted = 0;
    delSearch.run().each((r) => {
      record.delete({ type: CUSTOM_RECORD, id: r.id });
      deleted++;
      return true;
    });
    log.audit('Purge complete', { deleted, cutoff });
  }
  return { execute };
});

Deploy monthly on the 1st. Adjust RETENTION_MONTHS per your audit policy. For change tracking on the custom records themselves, you can enable System Notes on the record type to capture who created or modified each entry.

Why Not SuiteQL?

You can calculate zero-stock spans directly with SuiteQL by finding gaps between positive-quantity transactions. That works for current analysis, but it fails when:

• You need to show "item was at zero on January 15" but the next receipt wasn't until February 3, the transaction gap exists, but you can't prove the item stayed at zero every day in between without daily snapshots
• Management wants a trend chart of "average days at zero per month" over two years
• You need to correlate with external factors (promotions, vendor lead times) that aren't in NetSuite

The custom record approach gives you a materialized daily fact table. It's the same pattern data warehouses use: snapshot the state once per day, then aggregate freely.

Next Step

Deploy the script tonight. Tomorrow morning, run the summary search. If the counts look right, add the search to a dashboard portlet for the procurement team. They'll stop asking for ad-hoc exports, and you'll have a clean dataset for any future forecasting model.