Skip to main content

Dynamic Query Building

Learn how to build flexible, reusable queries that adapt to runtime conditions.

Why Dynamic Queries?​

Static queries have fixed filters. Dynamic queries adapt based on:

  • User input (form fields, parameters)
  • Runtime conditions
  • Optional filters
  • Variable sorting

Basic Query Builder Pattern​

Simple Dynamic WHERE​

/**
 * Build query with optional filters
 * @param {Object} filters - Optional filter values
 * @returns {Array} Query results
 */
const searchTransactions = (filters = {}) => {
    let sql = `
        SELECT
            t.id,
            t.tranid,
            t.trandate,
            t.amount,
            c.companyname
        FROM transaction t
        INNER JOIN customer c ON t.entity = c.id
        WHERE t.type = 'SalesOrd'
    `;
    const params = [];

    // Optional: Customer filter
    if (filters.customerId) {
        sql += ` AND t.entity = ?`;
        params.push(filters.customerId);
    }

    // Optional: Date range
    if (filters.startDate) {
        sql += ` AND t.trandate >= TO_DATE(?, 'YYYY-MM-DD')`;
        params.push(filters.startDate);
    }

    if (filters.endDate) {
        sql += ` AND t.trandate <= TO_DATE(?, 'YYYY-MM-DD')`;
        params.push(filters.endDate);
    }

    // Optional: Minimum amount
    if (filters.minAmount) {
        sql += ` AND t.amount >= ?`;
        params.push(filters.minAmount);
    }

    // Optional: Status
    if (filters.status) {
        sql += ` AND t.status = ?`;
        params.push(filters.status);
    }

    // Default ordering
    sql += ` ORDER BY t.trandate DESC`;

    // Always limit results
    sql += ` LIMIT ?`;
    params.push(filters.limit || 100);

    return query.runSuiteQL({
        query: sql,
        params: params
    }).asMappedResults();
};

// Usage examples
const allOrders = searchTransactions({});

const customerOrders = searchTransactions({
    customerId: 123,
    startDate: '2024-01-01'
});

const bigOrders = searchTransactions({
    minAmount: 10000,
    limit: 50
});

Query Builder Class​

Reusable Query Builder​

/**
 * Fluent Query Builder for SuiteQL
 */
class QueryBuilder {
    constructor(baseTable) {
        this.selectCols = ['*'];
        this.fromClause = baseTable;
        this.joins = [];
        this.conditions = [];
        this.params = [];
        this.groupByCols = [];
        this.havingConds = [];
        this.orderByCols = [];
        this.limitValue = null;
    }

    select(...columns) {
        this.selectCols = columns;
        return this;
    }

    join(type, table, condition) {
        this.joins.push(`${type} JOIN ${table} ON ${condition}`);
        return this;
    }

    innerJoin(table, condition) {
        return this.join('INNER', table, condition);
    }

    leftJoin(table, condition) {
        return this.join('LEFT', table, condition);
    }

    where(condition, ...values) {
        this.conditions.push(condition);
        this.params.push(...values);
        return this;
    }

    whereIf(condition, sqlCondition, ...values) {
        if (condition) {
            this.conditions.push(sqlCondition);
            this.params.push(...values);
        }
        return this;
    }

    groupBy(...columns) {
        this.groupByCols = columns;
        return this;
    }

    having(condition, ...values) {
        this.havingConds.push(condition);
        this.params.push(...values);
        return this;
    }

    orderBy(column, direction = 'ASC') {
        this.orderByCols.push(`${column} ${direction}`);
        return this;
    }

    limit(n) {
        this.limitValue = n;
        return this;
    }

    build() {
        let sql = `SELECT ${this.selectCols.join(', ')}`;
        sql += ` FROM ${this.fromClause}`;

        if (this.joins.length > 0) {
            sql += ' ' + this.joins.join(' ');
        }

        if (this.conditions.length > 0) {
            sql += ' WHERE ' + this.conditions.join(' AND ');
        }

        if (this.groupByCols.length > 0) {
            sql += ' GROUP BY ' + this.groupByCols.join(', ');
        }

        if (this.havingConds.length > 0) {
            sql += ' HAVING ' + this.havingConds.join(' AND ');
        }

        if (this.orderByCols.length > 0) {
            sql += ' ORDER BY ' + this.orderByCols.join(', ');
        }

        if (this.limitValue) {
            sql += ` LIMIT ${this.limitValue}`;
        }

        return { sql, params: this.params };
    }

    execute() {
        const { sql, params } = this.build();
        return query.runSuiteQL({ query: sql, params }).asMappedResults();
    }
}

Using the Query Builder​

// Example 1: Simple query
const customers = new QueryBuilder('customer')
    .select('id', 'companyname', 'email')
    .where('isinactive = ?', 'F')
    .orderBy('companyname')
    .limit(50)
    .execute();

// Example 2: With joins and optional filters
const buildOrderReport = (filters) => {
    const qb = new QueryBuilder('transaction t')
        .select(
            't.id',
            't.tranid',
            't.trandate',
            't.amount',
            'c.companyname'
        )
        .innerJoin('customer c', 't.entity = c.id')
        .where("t.type = ?", 'SalesOrd')
        .whereIf(filters.customerId, 't.entity = ?', filters.customerId)
        .whereIf(filters.startDate, "t.trandate >= TO_DATE(?, 'YYYY-MM-DD')", filters.startDate)
        .whereIf(filters.endDate, "t.trandate <= TO_DATE(?, 'YYYY-MM-DD')", filters.endDate)
        .whereIf(filters.minAmount, 't.amount >= ?', filters.minAmount)
        .orderBy('t.trandate', 'DESC')
        .limit(filters.limit || 100);

    return qb.execute();
};

// Usage
const report = buildOrderReport({
    customerId: 123,
    startDate: '2024-01-01',
    minAmount: 1000
});

Dynamic IN Clauses​

Multiple Value Filters​

/**
 * Handle dynamic IN clause with multiple values
 */
const searchByStatuses = (statuses) => {
    if (!statuses || statuses.length === 0) {
        statuses = ['A', 'B', 'C']; // Default
    }

    // Build placeholders: ?, ?, ?
    const placeholders = statuses.map(() => '?').join(', ');

    const sql = `
        SELECT id, tranid, status, amount
        FROM transaction
        WHERE type = 'SalesOrd'
          AND status IN (${placeholders})
        ORDER BY trandate DESC
        LIMIT 100
    `;

    return query.runSuiteQL({
        query: sql,
        params: statuses
    }).asMappedResults();
};

// Usage
const pending = searchByStatuses(['A']);
const activeOrders = searchByStatuses(['A', 'B']);
const allStatuses = searchByStatuses(['A', 'B', 'C', 'D']);

Multiple Customers​

/**
 * Search transactions for multiple customers
 */
const getCustomerTransactions = (customerIds, type = 'SalesOrd') => {
    if (!customerIds || customerIds.length === 0) {
        return [];
    }

    const placeholders = customerIds.map(() => '?').join(', ');

    const sql = `
        SELECT
            t.id,
            t.tranid,
            t.trandate,
            t.amount,
            c.companyname
        FROM transaction t
        INNER JOIN customer c ON t.entity = c.id
        WHERE t.type = ?
          AND t.entity IN (${placeholders})
        ORDER BY c.companyname, t.trandate DESC
    `;

    return query.runSuiteQL({
        query: sql,
        params: [type, ...customerIds]
    }).asMappedResults();
};

// Usage
const orders = getCustomerTransactions([101, 102, 103], 'SalesOrd');

Dynamic ORDER BY​

Sortable Column Mapping​

/**
 * Allow user to choose sort column safely
 */
const getSortableResults = (filters) => {
    // Whitelist of allowed sort columns (prevents SQL injection)
    const sortColumns = {
        'date': 't.trandate',
        'amount': 't.amount',
        'customer': 'c.companyname',
        'id': 't.tranid'
    };

    const sortDirections = {
        'asc': 'ASC',
        'desc': 'DESC'
    };

    // Validate sort column
    const sortCol = sortColumns[filters.sortBy] || 't.trandate';
    const sortDir = sortDirections[filters.sortDir?.toLowerCase()] || 'DESC';

    const sql = `
        SELECT
            t.id,
            t.tranid,
            t.trandate,
            t.amount,
            c.companyname
        FROM transaction t
        INNER JOIN customer c ON t.entity = c.id
        WHERE t.type = 'SalesOrd'
        ORDER BY ${sortCol} ${sortDir}
        LIMIT 100
    `;

    return query.runSuiteQL({ query: sql }).asMappedResults();
};

// Usage
const byDate = getSortableResults({ sortBy: 'date', sortDir: 'desc' });
const byAmount = getSortableResults({ sortBy: 'amount', sortDir: 'desc' });
const byCustomer = getSortableResults({ sortBy: 'customer', sortDir: 'asc' });

Complete Suitelet Example​

Dynamic Report Suitelet​

/**
 * @NApiVersion 2.1
 * @NScriptType Suitelet
 */
define(['N/query', 'N/ui/serverWidget', 'N/runtime'], (query, serverWidget, runtime) => {

    const TRANSACTION_TYPES = {
        'SalesOrd': 'Sales Order',
        'CustInvc': 'Invoice',
        'CustPymt': 'Payment',
        'Estimate': 'Quote'
    };

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

    const renderForm = (context) => {
        const form = serverWidget.createForm({ title: 'Dynamic Transaction Report' });

        // Transaction Type (multi-select)
        const typeField = form.addField({
            id: 'custpage_types',
            type: serverWidget.FieldType.MULTISELECT,
            label: 'Transaction Types'
        });
        Object.entries(TRANSACTION_TYPES).forEach(([value, text]) => {
            typeField.addSelectOption({ value, text });
        });

        // Customer
        form.addField({
            id: 'custpage_customer',
            type: serverWidget.FieldType.SELECT,
            label: 'Customer',
            source: 'customer'
        });

        // Date Range
        form.addField({
            id: 'custpage_start',
            type: serverWidget.FieldType.DATE,
            label: 'Start Date'
        });

        form.addField({
            id: 'custpage_end',
            type: serverWidget.FieldType.DATE,
            label: 'End Date'
        });

        // Amount Range
        form.addField({
            id: 'custpage_min',
            type: serverWidget.FieldType.CURRENCY,
            label: 'Min Amount'
        });

        form.addField({
            id: 'custpage_max',
            type: serverWidget.FieldType.CURRENCY,
            label: 'Max Amount'
        });

        // Sort Options
        const sortField = form.addField({
            id: 'custpage_sort',
            type: serverWidget.FieldType.SELECT,
            label: 'Sort By'
        });
        sortField.addSelectOption({ value: 'date_desc', text: 'Date (Newest First)' });
        sortField.addSelectOption({ value: 'date_asc', text: 'Date (Oldest First)' });
        sortField.addSelectOption({ value: 'amount_desc', text: 'Amount (Highest First)' });
        sortField.addSelectOption({ value: 'amount_asc', text: 'Amount (Lowest First)' });
        sortField.addSelectOption({ value: 'customer', text: 'Customer Name' });

        form.addSubmitButton({ label: 'Run Report' });
        context.response.writePage(form);
    };

    const runReport = (context) => {
        const params = context.request.parameters;

        // Parse form inputs
        const filters = {
            types: params.custpage_types ? params.custpage_types.split('\u0005') : [],
            customerId: params.custpage_customer || null,
            startDate: params.custpage_start || null,
            endDate: params.custpage_end || null,
            minAmount: parseFloat(params.custpage_min) || null,
            maxAmount: parseFloat(params.custpage_max) || null,
            sort: params.custpage_sort || 'date_desc'
        };

        // Build dynamic query
        const results = buildDynamicQuery(filters);

        // Render results
        renderResults(context, results, filters);
    };

    const buildDynamicQuery = (filters) => {
        let sql = `
            SELECT
                t.id,
                t.tranid,
                t.type,
                t.trandate,
                t.amount,
                t.status,
                c.companyname,
                c.email
            FROM transaction t
            INNER JOIN customer c ON t.entity = c.id
            WHERE 1=1
        `;
        const queryParams = [];

        // Transaction Types
        if (filters.types && filters.types.length > 0) {
            const placeholders = filters.types.map(() => '?').join(', ');
            sql += ` AND t.type IN (${placeholders})`;
            queryParams.push(...filters.types);
        }

        // Customer
        if (filters.customerId) {
            sql += ` AND t.entity = ?`;
            queryParams.push(parseInt(filters.customerId));
        }

        // Date Range
        if (filters.startDate) {
            sql += ` AND t.trandate >= TO_DATE(?, 'MM/DD/YYYY')`;
            queryParams.push(filters.startDate);
        }

        if (filters.endDate) {
            sql += ` AND t.trandate <= TO_DATE(?, 'MM/DD/YYYY')`;
            queryParams.push(filters.endDate);
        }

        // Amount Range
        if (filters.minAmount) {
            sql += ` AND t.amount >= ?`;
            queryParams.push(filters.minAmount);
        }

        if (filters.maxAmount) {
            sql += ` AND t.amount <= ?`;
            queryParams.push(filters.maxAmount);
        }

        // Sorting (use whitelist)
        const sortMap = {
            'date_desc': 't.trandate DESC',
            'date_asc': 't.trandate ASC',
            'amount_desc': 't.amount DESC',
            'amount_asc': 't.amount ASC',
            'customer': 'c.companyname ASC'
        };
        sql += ` ORDER BY ${sortMap[filters.sort] || 't.trandate DESC'}`;

        // Limit
        sql += ` LIMIT 500`;

        try {
            return query.runSuiteQL({
                query: sql,
                params: queryParams
            }).asMappedResults();
        } catch (e) {
            log.error('Query Error', { sql, params: queryParams, error: e.message });
            return [];
        }
    };

    const renderResults = (context, results, filters) => {
        const form = serverWidget.createForm({ title: 'Report Results' });

        // Summary
        form.addField({
            id: 'custpage_summary',
            type: serverWidget.FieldType.INLINEHTML,
            label: ' '
        }).defaultValue = `
            <div style="padding: 10px; background: #f5f5f5; margin-bottom: 20px;">
                <strong>Results: ${results.length} transactions found</strong><br>
                Total Amount: $${results.reduce((sum, r) => sum + (r.amount || 0), 0).toLocaleString()}
            </div>
        `;

        // Results sublist
        const sublist = form.addSublist({
            id: 'custpage_results',
            type: serverWidget.SublistType.LIST,
            label: 'Transactions'
        });

        sublist.addField({ id: 'tranid', type: serverWidget.FieldType.TEXT, label: 'Number' });
        sublist.addField({ id: 'type', type: serverWidget.FieldType.TEXT, label: 'Type' });
        sublist.addField({ id: 'trandate', type: serverWidget.FieldType.DATE, label: 'Date' });
        sublist.addField({ id: 'customer', type: serverWidget.FieldType.TEXT, label: 'Customer' });
        sublist.addField({ id: 'amount', type: serverWidget.FieldType.CURRENCY, label: 'Amount' });

        results.forEach((row, idx) => {
            sublist.setSublistValue({ id: 'tranid', line: idx, value: row.tranid || '' });
            sublist.setSublistValue({ id: 'type', line: idx, value: TRANSACTION_TYPES[row.type] || row.type });
            sublist.setSublistValue({ id: 'trandate', line: idx, value: row.trandate || '' });
            sublist.setSublistValue({ id: 'customer', line: idx, value: row.companyname || '' });
            sublist.setSublistValue({ id: 'amount', line: idx, value: row.amount || 0 });
        });

        form.addButton({ id: 'back', label: 'New Search', functionName: 'history.back()' });

        context.response.writePage(form);
    };

    return { onRequest };
});

Security Best Practices​

Always Use Parameters​

// NEVER do this (SQL injection vulnerability)
const badQuery = (userInput) => {
    return query.runSuiteQL({
        query: `SELECT * FROM customer WHERE name = '${userInput}'`  // DANGEROUS!
    });
};

// ALWAYS do this
const goodQuery = (userInput) => {
    return query.runSuiteQL({
        query: `SELECT * FROM customer WHERE name = ?`,
        params: [userInput]
    });
};

Whitelist Dynamic Parts​

// NEVER directly use user input for column/table names
const badDynamic = (sortColumn) => {
    return query.runSuiteQL({
        query: `SELECT * FROM customer ORDER BY ${sortColumn}`  // DANGEROUS!
    });
};

// ALWAYS whitelist allowed values
const goodDynamic = (sortColumn) => {
    const allowed = {
        'name': 'companyname',
        'date': 'datecreated',
        'balance': 'balance'
    };

    const safeColumn = allowed[sortColumn] || 'companyname';

    return query.runSuiteQL({
        query: `SELECT * FROM customer ORDER BY ${safeColumn}`
    });
};

Quick Reference​

Dynamic Query Template​

const dynamicQuery = (filters) => {
    let sql = `SELECT ... FROM table WHERE 1=1`;
    const params = [];

    if (filters.field1) {
        sql += ` AND column1 = ?`;
        params.push(filters.field1);
    }

    if (filters.field2) {
        sql += ` AND column2 >= ?`;
        params.push(filters.field2);
    }

    // Add ORDER BY (whitelisted)
    // Add LIMIT

    return query.runSuiteQL({ query: sql, params }).asMappedResults();
};

IN Clause Template​

const placeholders = values.map(() => '?').join(', ');
const sql = `SELECT * FROM table WHERE column IN (${placeholders})`;
const results = query.runSuiteQL({ query: sql, params: values });