Using SuiteQL for the REST API in NetSuite

Introduction

SuiteQL is a powerful querying tool in NetSuite, allowing developers to retrieve and filter data using SQL-like queries via the REST API. Unlike standard REST API endpoints, which are sometimes limited in their filtering capabilities, SuiteQL provides greater flexibility and control over the data you extract. This makes it particularly useful for complex reporting and integration needs.

In this blog, we’ll explore how to use SuiteQL with the NetSuite REST API and provide practical examples to retrieve common datasets.

Setting Up SuiteQL for the REST API

To execute SuiteQL queries via the REST API, follow these steps:

1. Authenticate with NetSuite: Use OAuth 2.0 or Token-Based Authentication (TBA) to access the REST API.
2. Use the Query Endpoint: NetSuite provides a query service at /query/v1/suiteql to execute SuiteQL queries.
3. Format the Request: The request should be a POST request with a JSON body containing the SQL query.
4. Parse the Response: The response will be in JSON format, allowing for easy processing.

---

Example 1: Getting a List of Customers

To retrieve a list of customers from NetSuite, use the following SuiteQL query:

Query:

SELECT id, entityid, companyname, email FROM customer ORDER BY entityid ASC;

REST API Request:

POST https://.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql

Headers:
Content-Type: application/json
Authorization: Bearer 

Body:
{
  "q": "SELECT id, entityid, companyname, email FROM customer ORDER BY entityid ASC;"
}

Response Example:
{
  "items": [
    {
      "id": "123",
      "entityid": "CUST001",
      "companyname": "ABC Corp",
      "email": "contact@abccorp.com"
    },
    {
      "id": "124",
      "entityid": "CUST002",
      "companyname": "XYZ Ltd",
      "email": "info@xyzltd.com"
    }
  ]
}

Example 2: Getting a List of Sales Orders for a Specific Customer

To get all sales orders for a specific customer, use a WHERE clause with the entity field (which corresponds to the customer ID).

Query:

SELECT id, tranid, trandate, status FROM transaction WHERE recordtype = ‘salesorder’ AND entity = 123 ORDER BY trandate DESC;

REST API Request:

POST https:.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql

Headers:
  Content-Type: application/json
  Authorization: Bearer 

Body:
{
  "q": "SELECT id, tranid, trandate, status FROM transaction WHERE recordtype = 'salesorder' AND entity = 123 ORDER BY trandate DESC;"
}

Response Example:
{
  "items": [
    {
      "id": "1001",
      "tranid": "SO-000123",
      "trandate": "2024-02-01",
      "status": "Pending Fulfillment"
    },
    {
      "id": "1002",
      "tranid": "SO-000124",
      "trandate": "2024-01-15",
      "status": "Shipped"
    }
  ]
}

Example 3: Querying Line Items from a Specific Sales Order

To retrieve line items for a particular sales order, use the transactionLine table and filter by the sales order ID.

Query:

SELECT t.id AS order_id, t.tranid, tl.line, tl.item, tl.quantity, tl.rate

FROM transaction AS t

JOIN transactionLine AS tl ON t.id = tl.transaction

WHERE t.recordtype = ‘salesorder’ AND t.id = 1001

ORDER BY tl.line ASC;

REST API Request:

POST https://.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql

Headers:
  Content-Type: application/json
  Authorization: Bearer 

Body:
{
  "q": "SELECT t.id AS order_id, t.tranid, tl.linesequencenumber, tl.item, tl.quantity, tl.rate FROM transaction AS t JOIN transactionLine AS tl ON t.id = tl.transaction WHERE t.recordtype = 'salesorder' AND t.id = 1001 ORDER BY tl. linesequencenumber ASC;"
}

Response Example:
{
  "items": [
    {
      "order_id": "1001",
      "tranid": "SO-000123",
      " linesequencenumber ": 1,
      "item": "Widget A",
      "quantity": 2,
      "rate": "25.00"
    },
    {
      "order_id": "1001",
      "tranid": "SO-000123",
      " linesequencenumber ": 2,
      "item": "Widget B",
      "quantity": 1,
      "rate": "50.00"
    }
  ]
}

---

Conclusion

SuiteQL provides a robust way to retrieve filtered data via the NetSuite REST API. With its SQL-like syntax, it allows for advanced queries that go beyond what’s possible with standard REST API endpoints. Whether you’re pulling customer lists, sales orders, or detailed line items, SuiteQL simplifies the process and enhances your ability to integrate NetSuite data efficiently.

By using these examples as a foundation, you can customize queries to suit your specific business needs and improve the efficiency of your NetSuite integrations. Happy querying!

About Us

We areNetSuite Solutions Providerswith 30+ years of combined experience. We specialize in implementation, optimization, integration, rapid project recovery & rescues, and custom development to meet any business need. Although every business is unique, serving over 40 NetSuite clients during the last 6 years our NetSuite Consulting team has most likely seen your challenge and created a similar solution. For more information on NetSuite solutions or questions about your project contact usHere.