Modules

filter

 

Objects/Functions

module (N/query)

SuiteScript 2.0

Main Examples

N/query Module
Member Type: Name
Search NetSuite - https://system.netsuite.com/app/help/helpcenter.nl?search=N/query Module

// Code Example 1
/**
* @NApiVersion 2.x
*/

require(['N/query'],
function(query) {

// Create a query definition for customer records
var 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.
var 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.
var myLocationJoin = mySalesRepJoin.autoJoin({
fieldId: 'location'
});

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

// 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 'foo'.
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
var resultSet = myCustomerQuery.run();

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

// Code Example 2
/**
* @NApiVersion 2.x
*/

require(['N/query'],
function(query) {

// Create a query definition for transaction records
var 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.
var myEmployeeJoin = myTransactionQuery.autoJoin({
fieldId: 'employee'
});

// 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
var results = myTransactionQuery.runPaged({
pageSize: 10
});

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

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

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

// Code Example 3
/**
* @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();
});

// Code Example 4
/**
* @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;
});
});

// Code Example 5
/*
* @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(customFieldInternalId);
});

//SOURCE: https://system.netsuite.com/app/help/helpcenter.nl?fid=section_1510275060.html