Skip to main content

Inventory Report System

This scenario demonstrates building a custom inventory report with filters, export options, and scheduled delivery.

Business Requirements​

┌─────────────────────────────────────────────────────────────────────────────┐
│                    INVENTORY REPORT REQUIREMENTS                             │
└─────────────────────────────────────────────────────────────────────────────┘

     ✓ Filter by location, category, vendor
     ✓ Show on-hand, available, committed quantities
     ✓ Highlight low stock items
     ✓ Export to CSV/PDF
     ✓ Schedule automated delivery
     ✓ Interactive drill-down to item records

Solution Architecture​

┌─────────────────────────────────────────────────────────────────────────────┐
│                    SOLUTION COMPONENTS                                       │
└─────────────────────────────────────────────────────────────────────────────┘

     ┌──────────────────────────────────────────────────────────────────┐
     │  SUITELET                                                         │
     │  ────────────────────────────────────────────────────────────────│
     │  • Search form with filters                                      │
     │  • Results display with sublist                                  │
     │  • Export buttons (CSV, PDF)                                     │
     └──────────────────────────────────────────────────────────────────┘

     ┌──────────────────────────────────────────────────────────────────┐
     │  SCHEDULED SCRIPT                                                 │
     │  ────────────────────────────────────────────────────────────────│
     │  • Generate report daily                                         │
     │  • Email to configured recipients                                │
     │  • Attach PDF summary                                            │
     └──────────────────────────────────────────────────────────────────┘

     ┌──────────────────────────────────────────────────────────────────┐
     │  SAVED SEARCH                                                     │
     │  ────────────────────────────────────────────────────────────────│
     │  • Base query for inventory data                                 │
     │  • Configurable filters                                          │
     └──────────────────────────────────────────────────────────────────┘

Report Suitelet​

src/FileCabinet/SuiteScripts/Suitelets/inventory_report_sl.js

/**
 * @NApiVersion 2.1
 * @NScriptType Suitelet
 * @NModuleScope SameAccount
 */
define(['N/ui/serverWidget', 'N/search', 'N/file', 'N/render', 'N/log'],
    (serverWidget, search, file, render, log) => {

    const onRequest = (context) => {
        if (context.request.method === 'GET') {
            showReportForm(context);
        } else {
            generateReport(context);
        }
    };

    const showReportForm = (context) => {
        const form = serverWidget.createForm({
            title: 'Inventory Report'
        });

        // Filter group
        form.addFieldGroup({
            id: 'filters',
            label: 'Report Filters'
        });

        // Location filter
        const locationField = form.addField({
            id: 'custpage_location',
            type: serverWidget.FieldType.SELECT,
            label: 'Location',
            source: 'location',
            container: 'filters'
        });

        // Category filter
        form.addField({
            id: 'custpage_category',
            type: serverWidget.FieldType.SELECT,
            label: 'Category',
            source: 'customlist_item_category',
            container: 'filters'
        });

        // Low stock threshold
        form.addField({
            id: 'custpage_threshold',
            type: serverWidget.FieldType.INTEGER,
            label: 'Low Stock Threshold',
            container: 'filters'
        }).defaultValue = 10;

        // Show only low stock
        form.addField({
            id: 'custpage_low_only',
            type: serverWidget.FieldType.CHECKBOX,
            label: 'Show Only Low Stock Items',
            container: 'filters'
        });

        form.addSubmitButton({ label: 'Generate Report' });

        form.addButton({
            id: 'custpage_export_csv',
            label: 'Export CSV',
            functionName: 'exportCSV'
        });

        // Check if this is a results request
        const params = context.request.parameters;
        if (params.custpage_location || params.generate === 'true') {
            addResultsSublist(form, params);
        }

        context.response.writePage(form);
    };

    const addResultsSublist = (form, params) => {
        const sublist = form.addSublist({
            id: 'custpage_results',
            type: serverWidget.SublistType.LIST,
            label: 'Inventory Results'
        });

        sublist.addField({ id: 'custpage_item', type: serverWidget.FieldType.TEXT, label: 'Item' });
        sublist.addField({ id: 'custpage_sku', type: serverWidget.FieldType.TEXT, label: 'SKU' });
        sublist.addField({ id: 'custpage_location', type: serverWidget.FieldType.TEXT, label: 'Location' });
        sublist.addField({ id: 'custpage_onhand', type: serverWidget.FieldType.INTEGER, label: 'On Hand' });
        sublist.addField({ id: 'custpage_available', type: serverWidget.FieldType.INTEGER, label: 'Available' });
        sublist.addField({ id: 'custpage_committed', type: serverWidget.FieldType.INTEGER, label: 'Committed' });
        sublist.addField({ id: 'custpage_status', type: serverWidget.FieldType.TEXT, label: 'Status' });

        // Build filters
        const filters = [
            ['isinactive', 'is', 'F'],
            'AND',
            ['type', 'anyof', 'InvtPart']
        ];

        if (params.custpage_location) {
            filters.push('AND', ['inventorylocation', 'anyof', params.custpage_location]);
        }

        if (params.custpage_category) {
            filters.push('AND', ['custitem_category', 'anyof', params.custpage_category]);
        }

        const threshold = parseInt(params.custpage_threshold) || 10;

        if (params.custpage_low_only === 'T') {
            filters.push('AND', ['quantityavailable', 'lessthan', threshold]);
        }

        // Run search
        const inventorySearch = search.create({
            type: search.Type.INVENTORY_ITEM,
            filters: filters,
            columns: [
                'itemid',
                'displayname',
                'custitem_sku',
                'inventorylocation',
                'quantityonhand',
                'quantityavailable',
                'quantitycommitted'
            ]
        });

        let line = 0;
        inventorySearch.run().each((result) => {
            const available = parseInt(result.getValue('quantityavailable')) || 0;
            const status = available < threshold ? 'LOW STOCK' : 'OK';

            sublist.setSublistValue({ id: 'custpage_item', line, value: result.getValue('displayname') || result.getValue('itemid') });
            sublist.setSublistValue({ id: 'custpage_sku', line, value: result.getValue('custitem_sku') || '' });
            sublist.setSublistValue({ id: 'custpage_location', line, value: result.getText('inventorylocation') || '' });
            sublist.setSublistValue({ id: 'custpage_onhand', line, value: result.getValue('quantityonhand') || 0 });
            sublist.setSublistValue({ id: 'custpage_available', line, value: available });
            sublist.setSublistValue({ id: 'custpage_committed', line, value: result.getValue('quantitycommitted') || 0 });
            sublist.setSublistValue({ id: 'custpage_status', line, value: status });

            line++;
            return line < 500;
        });
    };

    const generateReport = (context) => {
        // Redirect with parameters to show results
        const params = context.request.parameters;
        const url = context.request.url + '&generate=true';
        showReportForm(context);
    };

    return { onRequest };
});

Scheduled Report Script​

src/FileCabinet/SuiteScripts/ScheduledScripts/inventory_report_ss.js

/**
 * @NApiVersion 2.1
 * @NScriptType ScheduledScript
 */
define(['N/search', 'N/email', 'N/runtime', 'N/file', 'N/render', 'N/log'],
    (search, email, runtime, file, render, log) => {

    const execute = (context) => {
        log.audit('Inventory Report', 'Starting scheduled report');

        const script = runtime.getCurrentScript();
        const recipientId = script.getParameter({ name: 'custscript_report_recipient' });
        const threshold = script.getParameter({ name: 'custscript_low_threshold' }) || 10;

        // Get inventory data
        const reportData = getInventoryData(threshold);

        // Generate HTML report
        const reportHtml = generateHtmlReport(reportData, threshold);

        // Send email
        sendReport(recipientId, reportHtml, reportData);

        log.audit('Inventory Report', 'Report sent successfully');
    };

    const getInventoryData = (threshold) => {
        const items = [];
        const lowStockItems = [];

        const inventorySearch = search.create({
            type: search.Type.INVENTORY_ITEM,
            filters: [
                ['isinactive', 'is', 'F'],
                'AND',
                ['type', 'anyof', 'InvtPart']
            ],
            columns: [
                'itemid',
                'displayname',
                'inventorylocation',
                'quantityonhand',
                'quantityavailable'
            ]
        });

        inventorySearch.run().each((result) => {
            const item = {
                name: result.getValue('displayname') || result.getValue('itemid'),
                location: result.getText('inventorylocation'),
                onHand: parseInt(result.getValue('quantityonhand')) || 0,
                available: parseInt(result.getValue('quantityavailable')) || 0
            };

            items.push(item);

            if (item.available < threshold) {
                lowStockItems.push(item);
            }

            return true;
        });

        return {
            totalItems: items.length,
            lowStockCount: lowStockItems.length,
            lowStockItems: lowStockItems
        };
    };

    const generateHtmlReport = (data, threshold) => {
        let html = `
        <html>
        <head>
            <style>
                body { font-family: Arial, sans-serif; }
                table { border-collapse: collapse; width: 100%; }
                th { background-color: #4CAF50; color: white; padding: 12px; }
                td { padding: 8px; border: 1px solid #ddd; }
                .low-stock { background-color: #ffebee; }
                .summary { background: #f5f5f5; padding: 15px; margin: 20px 0; }
            </style>
        </head>
        <body>
            <h1>Daily Inventory Report</h1>
            <p>Generated: ${new Date().toLocaleDateString()}</p>

            <div class="summary">
                <strong>Summary:</strong><br>
                Total Items: ${data.totalItems}<br>
                Low Stock Items: ${data.lowStockCount} (below ${threshold} units)
            </div>
        `;

        if (data.lowStockItems.length > 0) {
            html += `
            <h2>Low Stock Items</h2>
            <table>
                <tr><th>Item</th><th>Location</th><th>On Hand</th><th>Available</th></tr>
            `;

            data.lowStockItems.forEach(item => {
                html += `
                <tr class="low-stock">
                    <td>${item.name}</td>
                    <td>${item.location}</td>
                    <td>${item.onHand}</td>
                    <td>${item.available}</td>
                </tr>
                `;
            });

            html += '</table>';
        } else {
            html += '<p>No low stock items to report.</p>';
        }

        html += '</body></html>';
        return html;
    };

    const sendReport = (recipientId, html, data) => {
        email.send({
            author: runtime.getCurrentUser().id,
            recipients: [recipientId],
            subject: `Inventory Report - ${data.lowStockCount} Low Stock Items`,
            body: html
        });
    };

    return { execute };
});

Deployment XML​

<?xml version="1.0" encoding="UTF-8"?>
<suitelet scriptid="customscript_inventory_report_sl">
    <name>Inventory Report</name>
    <scriptfile>[/SuiteScripts/Suitelets/inventory_report_sl.js]</scriptfile>
    <isinactive>F</isinactive>

    <scriptdeployments>
        <scriptdeployment scriptid="customdeploy_inventory_report_sl">
            <status>RELEASED</status>
            <title>Inventory Report</title>
            <isdeployed>T</isdeployed>
            <allroles>T</allroles>
        </scriptdeployment>
    </scriptdeployments>
</suitelet>

Next Steps​