skcah
Writing / Netsuite

Find a Custom Field List or Record Type in SuiteQL

Use SuiteQL and the Records Catalog to identify which list or record a SELECT custom field references, without guessing analytical field IDs.

5 minute read

A SELECT custom field stores a reference to another list or record. Knowing that target is essential for integrations, validation scripts and data dictionaries—yet the UI label alone does not tell SuiteQL how the relationship is modelled.

This post isolates one intent: resolve the list or record type behind a custom field definition. It assumes you can already query field metadata as described in How to Query NetSuite Custom Record Fields With SuiteQL.

Metadata, not instance values

You are reading the field definition, not a value chosen on a form.

Goal Source
Which list or record does this field point at? Field metadata (for example CustomRecordCustomField when available)
Which option did a user pick on a record? Instance data on customrecord_... / transaction / entity tables

Do not join to instance tables expecting them to explain the field’s configured target.

Confirm the field family

Custom-record fields, transaction body fields, entity fields and other families may use different metadata records. Search the Records Catalog for the family you need rather than assuming one table covers every cust... field.

For custom-record fields, search under the SuiteScript and REST Query API channel for CustomRecordCustomField and the field’s custrecord_... script ID. Availability is account-, feature-, role- and channel-specific.

Find the analytical columns that describe the target

On the field-definition record, use Global Field Search and the record’s field list for intents such as:

  • Field type / datatype (to confirm it is a list or record select)
  • Select record type / list reference
  • Source list or related sourcing attributes, if your catalog exposes them

Copy script IDs only. Display labels are not SuiteQL identifiers. Column names in the examples below are illustrative.

SELECT
    field_definition.id,
    field_definition.scriptid,
    field_definition.label,
    field_definition.fieldtype,
    field_definition.selectrecordtype
FROM
    CustomRecordCustomField field_definition
WHERE
    field_definition.scriptid = ?
const rows = query.runSuiteQL({
  query: suiteql,
  params: [fieldScriptId],
}).asMappedResults();

If fieldtype (or your catalog’s equivalent) is not a select-style type, a list-target column may be empty or irrelevant.

Interpret the target identifier

Accounts often expose the select target as an internal ID or a coded value rather than a friendly name. Treat that value as opaque until you resolve it through a catalog-confirmed path:

  1. Documented join from the field definition to a list or record-type metadata record.
  2. Separate lookup against a type or list metadata record using the stored ID.
  3. Cross-check in the UI or SDF XML when analytics does not expose a readable name for that target.

Illustrative join shape only—use the join field and target record from your catalog:

SELECT
    field_definition.scriptid,
    field_definition.label,
    field_definition.selectrecordtype,
    target_type.scriptid AS target_scriptid,
    target_type.name AS target_name
FROM
    CustomRecordCustomField field_definition
    LEFT JOIN SomeListOrRecordTypeMetadata target_type
        ON field_definition.selectrecordtype = target_type.id
WHERE
    field_definition.scriptid = ?

Replace SomeListOrRecordTypeMetadata and every column with IDs your Records Catalog actually lists. If no join exists, export the raw target ID and resolve it offline or via a second verified query.

Standard NetSuite lists and custom lists may resolve through different metadata records. Confirm each path separately.

Cross-check with SuiteCloud XML when needed

SDF object XML often includes a <selectrecordtype> (or similar) element on a <customrecordcustomfield>. That is useful for project-level documentation, but it is not a SuiteQL result.

<customrecordcustomfield scriptid="custrecord_example_status">
  <fieldtype>SELECT</fieldtype>
  <label>Status</label>
  <selectrecordtype>-XXX</selectrecordtype>
</customrecordcustomfield>

The numeric or coded selectrecordtype value in XML is account-specific. Compare it carefully with analytics IDs; do not assume the two schemas always present the same value in the same form. See SuiteQL Metadata vs SuiteCloud XML for Custom Records and SuiteCloud: Import a Custom Record and Its Custom Fields.

Common mistakes

Reading the instance column type from a data query

Selecting custrecord_example_status from customrecord_example returns stored values (often internal IDs of selected options). It does not define which list the field is bound to.

Assuming one metadata table for all custom fields

Transaction column fields and entity fields may live under different analytical records. Search by the exact custbody_..., custcol_... or custentity_... script ID.

Treating a missing target column as a SQL bug

Enable Show Unavailable Items and verify the field under the execution role. See How to Find SuiteQL Table and Field Names.

Inventing join names from the UI

If the catalog does not document a join from the field definition to a list type, do not fabricate one. Export the raw target ID instead.

Practical sequence

  1. Confirm the field-definition record for the correct custom-field family.
  2. Select identity columns plus field type and select-target columns from the catalog.
  3. Filter by field scriptid with a bind parameter.
  4. Resolve the target ID through a documented join or a second query.
  5. Optionally reconcile with SDF XML for project documentation.

For bulk documentation of many fields, combine this resolution step with Export a NetSuite Custom-Field Data Dictionary With SuiteScript.