Understanding N/query module
NetSuite, a leading cloud-based ERP platform, offers a robust set of tools and modules to help businesses streamline their operations. The N/query module is especially important for querying and manipulating data within NetSuite. In this article, we will look at the N/query module, and its key features, and provide a live example to show how it can be used.
Introduction
NetSuite’s N/query module allows users to retrieve, filter, and manipulate data from various NetSuite records and transactions. It has a strong SQL-like syntax that allows developers and administrators to build complex queries to efficiently extract specific information.
Five Key Features of the N/query Module
- Querying NetSuite Data
- Advanced Filtering
- Joins and Subqueries
- Aggregation and Grouping
- Performance Optimization
Querying NetSuite Data
You can query records such as customers, transactions, items, employees, and custom records using the N/query module. Query expressions can be used to retrieve specific fields, apply filters, and sort the results based on your needs.
Advanced Filtering
The N/query module includes a variety of filtering options, such as standard SQL operators (e.g., equals, not equals, greater than, less than), logical operators (e.g., AND, OR), and wildcards. Because of this flexibility, you can precisely define the criteria for your queries.
Joins and Subqueries
The N/query module allows you to perform joins and subqueries to retrieve related data from multiple record types. This feature allows you to create complex queries that span multiple tables and retrieve consolidated data.
Aggregation and Grouping
The N/query module supports aggregation functions such as SUM, COUNT, AVG, MIN, and MAX, allowing you to summarise data and generate meaningful insights. You can also group results based on specific fields to analyse data at various levels of granularity.
Performance Optimisation
NetSuite’s N/query module optimizes query execution by automatically utilizing indexing and caching mechanisms. This helps to improve query performance and ensures efficient data retrieval even when dealing with large datasets.
Approaches to using NetSuite’s N/query module
The N/query module’s SuiteQL API is a powerful interface. It enables you to create SQL-like queries in NetSuite to retrieve, filter, and manipulate data. SuiteQL can be used to query various record types, apply filters and sorting, perform joins and subqueries, and summarise data using aggregation functions.
All NetSuite server-side scripts are supported by the N/query module. Userevent, map/reduce, scheduled script, Restlet script, and suitelet are examples. We can retrieve data from various data tables by using the N/module capability.
Sample codes
This sample is for fetching the customer addressBook from the particular customer record. The following query helps to fetch all the addressBook records from the customer and get the country and time zone details from the country and timeZone join.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/query'], (query) => {
const exports = {
afterSubmit(scriptContext) {
try {
let queryString = `SELECT
customer.id AS customer_id,
customeraddressbook.internalId AS address_id,
customeraddressbook.addressBookAddress AS address_line_1,
customeraddressbookentityaddress.city AS city,
customeraddressbookentityaddress.state AS state,
country.name AS country,
country.nationality as nationality,
country.edition as edition,
timeZone.id AS timeZone
FROM
customer
JOIN
customeraddressbook ON customer.id = customeraddressbook.entity
JOIN
customeraddressbookentityaddress ON customeraddressbook.addressBookAddress = customeraddressbookentityaddress.nkey
JOIN
country ON customeraddressbookentityaddress.country = country.id
JOIN
timeZone ON Country.timezone = TimeZone.id
WHERE
customer.id = 3`
let queryResults = query.runSuiteQL({ query: queryString });
let dataArray = [];
queryResults.asMappedResults().forEach(function (result) { // itreate the result using for each loop for geting data.
let dataObj = {}
dataObj.customerId = result.customer_id;
dataObj.addressId = result.address_id;
dataObj.addressLine1 = result.address_line_1;
dataObj.city = result.city;
dataObj.state = result.state;
dataObj.country = result.country;
dataObj.nationality = result.nationality;
dataObj.edition = result.edition;
dataObj.timeZone = result.timeZone
dataArray.push(dataObj);
return true;
});
} catch (e) {
log.debug('error @ afterSubmit', e)
}
}
}
return exports;
}); This sample is for fetching a particular customer’s transaction details and line items included in that particular customer. Here the primary keys are the customer Id and transaction id.
/**
* @NApiVersion 2.1
* @NScriptType UserEventScript
*/
define(['N/query'], (query) => {
const exports = {
afterSubmit(scriptContext) {
try {
let queryString = `SELECT
TransactionLine.Transaction AS TransactionID,
Transaction.TranDate,
Transaction.Type,
Transaction.TranID,
TransactionLine.Rate,
TransactionLine.NetAmount,
TransactionLine.Quantity,
Entity.AltName
FROM
TransactionLine INNER JOIN Transaction ON (Transaction.ID = TransactionLine.Transaction)
LEFT OUTER JOIN Entity ON (Entity.ID = Transaction.Entity)
WHERE
Transaction.Entity = 3
ORDER BY
Transaction.TranDate,
Transaction.TranID`
let queryResults = query.runSuiteQL({ query: queryString });
let dataArray = [];
queryResults.asMappedResults().forEach(function (result) {
let dataObj = {};
dataObj.TransactionID = result.TransactionID;
dataObj.TranDate = result.TranDate;
dataObj.Type = result.Type;
dataObj.TranID = result.TranID;
dataObj.Rate = result.Rate;
dataObj.NetAmount = result.NetAmount;
dataObj.Quantity = result.Quantity;
dataObj.AltName = result.AltName;
dataArray.push(dataArray);
return true;
});
} catch (e) {
log.debug('error @ afterSubmit', e)
}
}
}
return exports;
});References
- SuiteQl record catalog: This will help to identify the data tables and supporting join pairs.
The navigation will be: Setup -> Record Catalog
Link: https://<Your_NetSuite_Domain>/app/recordscatalog/rcbrowser.nl?whence=#/record_ss/Account
2. W3 school: This helps to learn SQL queries.
Link; https://www.w3schools.com/sql/
3. Netsuite suite Answers.
4. Netsuite connect browser for reference: https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2022_2/odbc/record/account.html