skcah
Writing / Netsuite

How to Query NetSuite Custom Record Fields With SuiteQL

Use the NetSuite Records Catalog to find CustomRecordCustomField metadata, verify available fields and build a SuiteQL query for custom-record field IDs and labels.

Ryan Sheehy5 minute read

Sometimes the data you need is not stored in a custom record instance. You need information about the custom record’s definition: its fields, script IDs, labels and relationships.

That is why developers go looking for a SuiteQL record such as CustomRecordCustomField.

The awkward part is that NetSuite’s analytics schema is dynamic. The records and fields available to SuiteQL depend on the account, enabled features and the role executing the query. A query copied from another account can therefore fail even when its SQL is valid.

The reliable approach is to use the account’s Records Catalog first and write the query from the schema it exposes.

Start in the Records Catalog

Open Setup → Records Catalog and select the SuiteScript and REST Query API channel.

Oracle describes the Records Catalog as the account-specific reference for record types, fields and joins available to SuiteQL and constructed N/query queries. It reflects the current role’s permissions and the account’s enabled features.

Search for these terms:

  • CustomRecordCustomField
  • custom record field
  • The script ID of a known field, such as custrecord_example_status

If the record is available, record its exact script ID, field IDs and joins. Do not assume that the labels shown in the NetSuite customisation UI are valid SuiteQL identifiers.

The catalog’s Global Field Search is particularly useful when you know a field label or script ID but not the record that exposes it.

Build the query progressively

Begin with only the fields confirmed in your Records Catalog:

SELECT
    field_definition.id,
    field_definition.scriptid,
    field_definition.label
FROM
    CustomRecordCustomField field_definition
ORDER BY
    field_definition.scriptid

The record and column IDs above illustrate the intended shape. Use the exact IDs displayed in your account. If label is unavailable but another name field is exposed, select that field instead.

Once the basic query works, add the field that identifies the owning custom-record type:

SELECT
    field_definition.id,
    field_definition.scriptid,
    field_definition.label,
    field_definition.recordtype
FROM
    CustomRecordCustomField field_definition
WHERE
    field_definition.recordtype = ?
ORDER BY
    field_definition.scriptid

Pass an internal ID as a bind parameter rather than interpolating it into the SQL:

const results = query.runSuiteQL({
  query: suiteql,
  params: [customRecordTypeId],
}).asMappedResults();

If the catalog exposes a join from the field definition to the custom-record type, use that documented relationship to return the type’s script ID or name. Join IDs are account-schema contracts; guessing one from the UI label is unreliable.

Metadata is different from record data

These two questions require different queries:

  1. Which fields are defined on this custom-record type? Query the metadata record exposed by the Records Catalog.
  2. What values are stored in those fields? Query the custom record itself using its customrecord_... table and custrecord_... columns.

For example, after discovering custrecord_example_status, a data query might look like this:

SELECT
    custom_record.id,
    custom_record.name,
    custom_record.custrecord_example_status
FROM
    customrecord_example custom_record
WHERE
    custom_record.isinactive = 'F'

The metadata query discovers the field definition. The data query reads the values stored on individual custom-record instances.

Why a valid-looking query may fail

The record is absent from the catalog

The current role may not have access, the record may not be available through the selected analytics channel, or the feature that provides it may be disabled.

A UI label was used as a field ID

SuiteQL uses analytical field IDs. Confirm them in the Records Catalog instead of converting a display label into a guessed identifier.

A field is visible but unavailable

Turn on Show Unavailable Items in the Records Catalog. It can reveal a permission or feature restriction that would otherwise look like a SQL error.

The query returns metadata but not custom-record values

CustomRecordCustomField describes field definitions. Query the relevant customrecord_... record type to retrieve stored values.

The results differ between roles

The Records Catalog and SuiteQL results are role-aware. Test using the same role that will execute the production script or integration.

Other custom field families

NetSuite distinguishes custom-record fields from other custom field types, including:

  • Transaction body fields
  • Transaction column fields
  • Entity fields
  • Item fields
  • CRM fields
  • Other-record fields

Do not expect one metadata record to describe every family. Search the Records Catalog for the specific field family and confirm its availability before composing a combined metadata report.

A practical discovery checklist

  1. Open Setup → Records Catalog with the execution role.
  2. Select the SuiteScript and REST Query API channel.
  3. Search by record name, field label and script ID.
  4. Copy the analytical record and field IDs exactly.
  5. Run a minimal three-column query.
  6. Add the owning-record relationship only after confirming its field or join.
  7. Use bind parameters for filters.
  8. Test under the production role.

The important lesson is not a single universal query. It is that the Records Catalog is the source of truth for the SuiteQL schema actually available in your NetSuite account.