Record Filtering and Query

The query operation is used to execute a query on a specific record type based on a set of criteria.

Record query only returns record IDs and HATEOAS links. That is, query results have a form of non-expanded references. Additionally, you can only use body fields in query conditions. Saved queries, multilevel joins, and sublist and subrecord queries are not supported.

REST web services only support limited record query. Joins are not supported.

We can obtain the list of all records of a record type by sending an HTTP GET request to …/services/rest/record/v1/record_type

Sample request:

GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer

Sample Response:

{
    "links": [
        {
            "rel": "self",
            "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?limit=1000&offset=0"
        }
    ],
    "items": [
        {
            "links": [
                {
                    "rel": "self",
                    "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer/107"
                }
            ],
            "id": "107"
        },
        {
            "links": [
                {
                    "rel": "self",
                    "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer/41"
                }
            ],
            "id": "41"
        },
        {
            "links": [
                {
                    "rel": "self",
                    "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer/90"
                }
            ],
            "id": "90"
        }
    ],
    "totalResults": 3
}

You can filter the collection of all record instances by using the q query parameter to specify filter conditions. Each condition consists of a field name, an operator, and a value. You can join several conditions using the AND / OR logical operators, and you can use () to mark precedence.

REST web services uses the N/query module to perform record collection filtering.

Sample Request:

GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=email START_WITH barbara

Sample Response:

{
    "links": [
        {
            "rel": "self",
            "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?limit=1000&offset=0"
        }
    ],
    "items": [
        {
            "links": [
                {
                    "rel": "self",
                    "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer/107"
                }
            ],
            "id": "107"
        },
    ],
    "totalResults": 1
}

When your condition value contains spaces, you should use quotation marks around the constraint, for instance, firstname IS “Barbara Allen”. See the following additional query examples:

• Find customer by company name (string value):
• GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=companyname START_WITH “Another Company”
• Find inactive customers (boolean value):
• GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=isinactive IS true
• Find customers created in 2019 (date value, AND operator):
• GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=dateCreated ON_OR_AFTER “1/1/2019” AND dateCreated BEFORE “1/1/2020”
• Find customers with high or low credit limit (number constraint, OR operator):
• GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=creditlimit GREATER_OR_EQUAL 1000 OR creditlimit LESS_OR_EQUAL 10