How to Join CustomRecordCustomField to CustomRecordType

You often need both sides of a definition: which fields exist and which custom-record type owns them. That is a join between field-definition metadata and type-definition metadata—not a join to instance rows.

This note supports How to Query NetSuite Custom Record Fields With SuiteQL. It is only about the type ↔ field definition relationship once both analytical records appear in your catalog.

Every record name, column name and join path below is illustrative. Confirm exact IDs under Setup → Records Catalog, SuiteScript and REST Query API channel, with the role that will run the query. Do not treat CustomRecordCustomField or CustomRecordType as universally available.

What you are joining

Side	Question it answers
Field definition (catalog name often like `CustomRecordCustomField`)	Script ID, label, type of each custom-record field
Type definition (catalog name often like `CustomRecordType`)	Script ID / name of the custom-record type that owns those fields
Instance table (`customrecord_...`)	Stored values on rows—not part of this join

A successful definition join still does not return instance values. Query the customrecord_... table for data after you know the field script IDs.

Confirm both records before you join

Open Records Catalog with the execution role.
Select the SuiteScript and REST Query API channel.
Search for the field-definition record (CustomRecordCustomField, custom record field, or a known custrecord_... ID).
Search separately for the type-definition record (CustomRecordType, custom record type, or a known customrecord_... ID).
On the field-definition record, open Joins (or the catalog’s equivalent) and look for a documented relationship to the type record—or a field that already stores the owning type’s internal ID.
Copy the join ID and join keys exactly as the catalog shows them. Do not invent a key from form layout or SDF XML structure.

If either record is missing, stop and diagnose availability (why CustomRecordCustomField may be missing) instead of guessing a join.

Prefer a confirmed owner column first

Many accounts expose an owning-type identifier on the field-definition record without a separate join. That is often enough:

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

recordtype here is illustrative. Use the catalog field that identifies the parent type, and pass the type’s internal ID as a bind parameter.

You only need an explicit join when you must project type-side columns (type script ID, type name, type inactive flag) that the field-definition record does not expose.

Illustrative join shape

Use this only as a shape. Replace every identifier with catalog values for your account. If the catalog documents a different join name or direction, follow the catalog.

SELECT
    field_definition.id,
    field_definition.scriptid,
    field_definition.label,
    record_type.id AS type_id,
    record_type.scriptid AS type_scriptid
FROM
    CustomRecordCustomField field_definition
    INNER JOIN CustomRecordType record_type
        ON field_definition.recordtype = record_type.id
WHERE
    record_type.scriptid = ?
ORDER BY
    field_definition.scriptid

Notes:

The ON clause must use the documented relationship (or a confirmed foreign-key style field). A UI “Record Type” label is not a SuiteQL join key.
Filter with bind parameters; do not interpolate script IDs into SQL (dynamic WHERE and binds).
Keep the first successful query minimal. Add columns one at a time after each is confirmed (column reference).

Join direction and cardinality

Expect many field definitions per type. Ordering and pagination should assume that cardinality.

Filter by type when you care about one custom record’s field list.
If you export every field across types, page results and use a deterministic ORDER BY (result limits and pagination).
Do not join field definitions to customrecord_... instance tables to “get more columns.” Definitions and instances answer different questions.

Common join failures

Symptom	First check
Unknown identifier on the join target	Type-definition record absent for this role/channel; wrong analytical script ID
Unknown join or field in `ON`	Join not listed under the field-definition (or type) record in the catalog
Empty result after join	Wrong type ID, inactive filters, or inner join removing all rows
Works for Administrator only	Re-open the catalog under the integration role
Join copied from saved search	Saved-search joins are a different system—confirm the SuiteQL join

Enable Show Unavailable Items when a join appears in documentation from another account but not in yours. Absence is often permission, feature or channel—not invalid SQL syntax.

Metadata join versus SDF nesting

SuiteCloud object XML nests <customrecordcustomfield> under <customrecordtype>. That nesting is a deployment model, not a SuiteQL join contract. Analytical join IDs can differ from SDF element names. For object import workflow see SuiteCloud: Import a Custom Record and Its Custom Fields; for when to use SuiteQL versus XML see SuiteQL Metadata vs SuiteCloud XML for Custom Records.