N/query Module Script Samples

The following script samples demonstrate how to use the features of the N/query module:

Create a Query for Customer Records and Run It as a Non-Paged Query
Create a Query for Transaction Records and Run It as a Paged Query
Convert a Query to a SuiteQL and Run It
Run an Arbitrary SuiteQL Query
Create a Query for a Custom Field
Create a Query Using a Specific Record Field

Create a Query for Customer Records and Run It as a Non-Paged Query

The following sample creates a query for customer records, joins the query with two other query types, and runs the query.

Note:

This sample script uses the require function so that you can copy it into the SuiteScript Debugger and test it. You must use the define function in an entry point script (the script you attach to a script record and deploy). For more information, see SuiteScript 2.x Script Basics and SuiteScript 2.x Script Types.

Important:

This sample uses SuiteScript 2.1. For more information, see SuiteScript 2.1.

            /**
 * @NApiVersion 2.1
 */
require(['N/query'], query => {
    // Create a query definition for customer records
    let myCustomerQuery = query.create({
        type: query.Type.CUSTOMER
    });

    // Join the original query definition based on the salesrep field. In a customer
    // record, the salesrep field contains a reference to an employee record. When you
    // join based on this field, you are joining the query definition with the employee
    // query type, and you can access the fields of the joined employee record in
    // your query.
    let mySalesRepJoin = myCustomerQuery.autoJoin({
        fieldId: 'salesrep'
    });

    // Join the joined query definition based on the location field. In an employee
    // record, the location field contains a reference to a location record.
    let myLocationJoin = mySalesRepJoin.autoJoin({
        fieldId: 'location'
    });

    // Create conditions for the query
    let firstCondition = myCustomerQuery.createCondition({
        fieldId: 'id',
        operator: query.Operator.EQUAL,
        values: 107
    });
    let secondCondition = myCustomerQuery.createCondition({
        fieldId: 'id',
        operator: query.Operator.EQUAL,
        values: 2647
    });
    let thirdCondition = mySalesRepJoin.createCondition({
        fieldId: 'email',
        operator: query.Operator.START_WITH_NOT,
        values: 'example'
    });

    // Combine conditions using and() and or() operator methods. In this example,
    // the combined condition states that the id field of the customer record must
    // have a value of either 107 or 2647, and the email field of the employee
    // record (the record that is referenced in the salesrep field of the customer
    // record) must not start with 'example'.
    myCustomerQuery.condition = myCustomerQuery.and(
        thirdCondition, myCustomerQuery.or(firstCondition, secondCondition)
    );

    // Create query columns
    myCustomerQuery.columns = [
        myCustomerQuery.createColumn({
            fieldId: 'entityid'
        }),
        myCustomerQuery.createColumn({
            fieldId: 'id'
        }),
        mySalesRepJoin.createColumn({
            fieldId: 'entityid'
        }),
        mySalesRepJoin.createColumn({
            fieldId: 'email'
        }),
        mySalesRepJoin.createColumn({
            fieldId: 'hiredate'
        }),
        myLocationJoin.createColumn({
            fieldId: 'name'
        })
    ];

    // Sort the query results based on query columns
    myCustomerQuery.sort = [
        myCustomerQuery.createSort({
            column: myCustomerQuery.columns[3]
        }),
        myCustomerQuery.createSort({
            column: myCustomerQuery.columns[0],
            ascending: false
        })
    ];

    // Run the query
    let resultSet = myCustomerQuery.run();

    // Retrieve and log the results
    let results = resultSet.results;
    for (let i = results.length - 1; i >= 0; i--)
        log.debug(results[i].values);
    log.debug(resultSet.types);
});

Create a Query for Transaction Records and Run It as a Paged Query

The following sample creates a query for transaction records, joins the query with another query type, and runs the query as a paged query.

Note:

Important:

This sample uses SuiteScript 2.1. For more information, see SuiteScript 2.1.

            /**
 * @NApiVersion 2.1
 */
require(['N/query'], query => {
    // Create a query definition for transaction records
    let myTransactionQuery = query.create({
        type: query.Type.TRANSACTION
    });

    // Join the original query definition based on the employee field. In a transaction
    // record, the employee field contains a reference to an employee record. When you
    // join based on this field, you are joining the query definition with the employee
    // query type, and you can access the fields of the joined employee record in
    // your query.
    let myEmployeeJoin = myTransactionQuery.autoJoin({
        fieldId: 'employee'
    });

    // Create a condition for the transaction query
    let transactionCondition = myTransactionQuery.createCondition({
        fieldId: 'isreversal',
        operator: query.Operator.IS,
        values: true
    });
    myTransactionQuery.condition = transactionCondition;

    // Create a query column
    myTransactionQuery.columns = [
        myEmployeeJoin.createColumn({
            fieldId: 'subsidiary'
        })
    ];

    // Sort the query results based on a query column
    myTransactionQuery.sort = [
        myTransactionQuery.createSort({
            column: myTransactionQuery.columns[0],
            ascending: false
        })
    ];

    // Run the query as a paged query with 10 results per page
    let results = myTransactionQuery.runPaged({
        pageSize: 10
    });

    log.debug(results.pageRanges.length);
    log.debug(results.count);

    // Retrieve the query results using an iterator
    let iterator = results.iterator();
    iterator.each(function(result) {
        let page = result.value;
        log.debug(page.pageRange.size);
        return true;
    })

    // Alternatively, retrieve the query results by looping through
    // each result
    for (let i = 0; i < results.pageRanges.length; i++)  {
        let page = results.fetch(i);
        log.debug(page.pageRange.size);
    }
});

Convert a Query to a SuiteQL and Run It

The following sample creates a query for customer records, converts it to its SuiteQL representation, and runs it.

Note:

            /**
 * @NApiVersion 2.x
 */
require(['N/query'], function(query) {
    var myCustomerQuery = query.create({
        type: query.Type.CUSTOMER
    });

    myCustomerQuery.columns = [
        myCustomerQuery.createColumn({
            fieldId: 'entityid'
        }),
        myCustomerQuery.createColumn({
            fieldId: 'email'
        })
    ];

    myCustomerQuery.condition = myCustomerQuery.createCondition({
        fieldId: 'isperson',
        operator: query.Operator.IS,
        values: [true]
    });

    var mySQLCustomerQuery = myCustomerQuery.toSuiteQL();

    var results = mySQLCustomerQuery.run();
});

Run an Arbitrary SuiteQL Query

The following sample constructs a SuiteQL query string, runs the query as a paged query, and iterates over the results.

Note:

            /**
 * @NApiVersion 2.x
 */
require(['N/query'], function(query) {
   var sql =
        "SELECT " +
        "  scriptDeployment.primarykey, scriptexecutioncontextmap.executioncontext " +
        " FROM " +
        "  scriptDeployment, scriptexecutioncontextmap " +
        " WHERE " +
        "  scriptexecutioncontextmap.scriptrecord = scriptDeployment.primarykey " +
        " AND " +
        "  scriptexecutioncontextmap.executioncontext IN ('WEBSTORE', 'WEBAPPLICATION')";

    var resultIterator = query.runSuiteQLPaged({
        query: sql,
        pageSize: 10
    }).iterator();

    resultIterator.each(function(page) {
        var pageIterator = page.value.data.iterator();
        pageIterator.each(function(row) {
            log.debug('ID: ' + row.value.getValue(0) + ', Context: ' + row.value.getValue(1));
            return true;
        });
        return true;
    });
});

Create a Query for a Custom Field

The following sample creates a query for a custom field, custrecord_my_custom_field, and obtains the internal ID of the field.

Note:

            /*
 * @NApiVersion 2.x
 */
require(['N/query'], function(query) {
    var customFieldIdQuery = query.create({
        type: query.Type.CUSTOM_FIELD
    });
    customFieldIdQuery.columns = [
        customFieldIdQuery.createColumn({
            fieldId: 'internalid'
        })
    ];
    customFieldIdQuery.condition = customFieldIdQuery.createCondition({
        fieldId: 'scriptid',
        operator: query.Operator.IS,
        values: 'custrecord_my_custom_field'
    });

    var results = customFieldIdQuery.run().asMappedResults();
    var customFieldInternalId = results[0].internalid;
    log.debug({
        title: 'Internal ID of the custom field is ',
        details: customFieldInternalId
    });
});

Create a Query Using a Specific Record Field

The following sample creates a query for records using the value of the operationdisplaytext field. If you run this sample in your account, be sure to replace the placeholder value <transactionId> with a valid value from your account.

Note:

            /**
* @NApiVersion 2.x
*/
require(['N/query'], function(query) {
    var mfgComponent = query.create({
        type: query.Type.MANUFACTURING_COMPONENT
    });

    var mfgOperation = mfgComponent.autoJoin({
        fieldId: 'operationdisplaytext'
    });

    mfgComponent.columns = [
        mfgComponent.createColumn({
            fieldId: 'operationdisplaytext'
        }),
        mfgComponent.createColumn({
            fieldId: 'item'
        }),
        mfgOperation.createColumn({
            fieldId: 'operationsequence'
        })
    ];

    mfgComponent.condition = mfgComponent.and(
        mfgComponent.createCondition({
            fieldId: 'transaction',
            operator: query.Operator.ANY_OF,
            values: <transactionId>
        }),
        mfgOperation.createCondition({
            fieldId: 'operationsequence',
            operator: query.Operator.EQUAL,
            values: 20
        })
    );

    var results = mfgComponent.run();
});

General Notices