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

        

General Notices