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

User Input → Query Builder → Customized Query
├─ Customer?     │             │
├─ Date Range?   │             └→ SELECT ... WHERE ...
├─ Status?       │                (filters applied dynamically)
└─ Sort By?      │

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',
        'creditlimit': 'creditlimit'
    };

    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 });

Why Dynamic Queries?​

Basic Query Builder Pattern​

Simple Dynamic WHERE​

Query Builder Class​

Reusable Query Builder​

Using the Query Builder​

Dynamic IN Clauses​

Multiple Value Filters​

Multiple Customers​

Dynamic ORDER BY​

Sortable Column Mapping​

Complete Suitelet Example​

Dynamic Report Suitelet​

Security Best Practices​

Always Use Parameters​

Whitelist Dynamic Parts​

Quick Reference​

Dynamic Query Template​

IN Clause Template​

Why Dynamic Queries?

Basic Query Builder Pattern

Simple Dynamic WHERE

Query Builder Class

Reusable Query Builder

Using the Query Builder

Dynamic IN Clauses

Multiple Value Filters

Multiple Customers

Dynamic ORDER BY

Sortable Column Mapping

Complete Suitelet Example

Dynamic Report Suitelet

Security Best Practices

Always Use Parameters

Whitelist Dynamic Parts

Quick Reference

Dynamic Query Template

IN Clause Template