- Use multilevel joins to create queries using field data from multiple record types.
- Create conditions (filters) using AND, OR, and NOT logic, as well as formulas and relative dates.
- Sort query results based on the values of multiple columns.
- Load and delete existing saved queries that were created using the SuiteAnalytics Workbook interface.
- View paged query results.
- Use promises for asynchronous .
- Convert query objects to SuiteQL queries and run arbitrary SuiteQL queries.
Sample :
function getDataByQuery() {
var data = [];
var filters = {};
var joins = {};
// Create Customer Query
var customerQuery = query.create({ type: query.Type.CUSTOMER });
joins.salesRep = customerQuery.autoJoin({ fieldId: ‘salesrep’ });
// Define Start and End Dates
var startDate = new Date(’01/01/1960′);
var endDate = new Date(’01/01/1970′);
// Set filter for birthDate within the date range
filters.birthDate = joins.salesRep.createCondition({
fieldId: ‘birthdate’,
operator: query.Operator.WITHIN,
values: [startDate, endDate]
});
// Define columns
var colData = {
INTERNAL_ID: { fieldId: ‘id’ },
FIRST_NAME: { fieldId: ‘firstname’ },
LAST_NAME: { fieldId: ‘lastname’ },
COMPANY_NAME: { fieldId: ‘companyname’ },
PHONE: { fieldId: ‘phone’ },
EMAIL: { fieldId: ’email’ },
SALES_REP: { fieldId: ‘salesrep’ }
};
// Add columns to query
var colSequence = Object.keys(colData);
for (var col in colData) {
customerQuery.columns.push(customerQuery.createColumn(colData[col]));
}
// Apply conditions
customerQuery.conditions = customerQuery.and(filters.birthDate);
// Run query and process results
var results = customerQuery.run().results;
results.forEach(function (result) {
var line = {};
colSequence.forEach(function (colName, index) {
line[colName] = result.values[index];
});
data.push(line);
});
return data;
}
/*********************************************************************************************************************************************************************/
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define([‘N/query’], function(query) {
function pageInit(context) {
var myCustomerQuery = query.create({
type: ‘customrecordsalesorderform’
});
var firstCondition = myCustomerQuery.createCondition({
fieldId: ‘name’,
operator: query.Operator.CONTAIN,
values: [‘Devid’]
});
myCustomerQuery.condition = myCustomerQuery.and(firstCondition);
myCustomerQuery.columns = [
myCustomerQuery.createColumn({
fieldId: ‘name’
}),
myCustomerQuery.createColumn({
fieldId: ‘custrecordwarehouselocation’
}),
myCustomerQuery.createColumn({
fieldId: ‘custrecordsalesrep’
})
];
var mySQLCustomerQuery = myCustomerQuery.toSuiteQL();
var resultSet = mySQLCustomerQuery.run();
var results = resultSet.results;
log.debug({ details: ‘Number of Records: ‘ + results.length });
for(var i=results.length–1; i<=0 ;i–){
log.debug({ details: ‘Name: ‘ + results[i].values[0] });
}
}
return {
pageInit: pageInit
};
});