Join CustomRecordCustomField to CustomRecordType

A useful metadata report lists each custom field beside the custom-record type that owns it. In SuiteQL that means relating field-definition rows to type-definition rows—not joining instance data.

CustomRecordType in this article is a discovery term and an illustrative record name. Do not assume it is the analytical record ID in your account.

This guide covers the join problem only. For the broader discovery workflow, start with How to Query NetSuite Custom Record Fields With SuiteQL. For column selection on the field side, see the CustomRecordCustomField SuiteQL column reference.

Confirm both records exist

In Setup → Records Catalog, select the SuiteScript and REST Query API channel and search for:

• CustomRecordCustomField
• CustomRecordType

Neither name is guaranteed in every account, role or channel. The analytics schema is account-, feature-, role- and channel-specific. If one side is missing, a join cannot be invented from UI labels.

When a record is missing, follow Why CustomRecordCustomField Is Missing From SuiteQL and How to Find SuiteQL Table and Field Names.

Prefer the documented relationship

Open the field-definition record in the catalog and inspect:

1. Fields that reference a custom-record type (often an internal ID of the owning type)
2. Documented joins, including target record and cardinality

Do not copy a join from Saved Search, SuiteScript or SDF XML. SuiteQL joins are analytical contracts exposed by the catalog.

Pattern A: confirmed foreign-key field and type record

If the catalog exposes both a field containing the owning type’s internal ID and a type-definition record, filter or join using those catalog entries. Every identifier below is illustrative and the query must not be run unchanged.

SELECT
    field_definition.id,
    field_definition.scriptid,
    field_definition.label,
    type_definition.id,
    type_definition.scriptid,
    type_definition.name
FROM
    CustomRecordCustomField field_definition
    INNER JOIN CustomRecordType type_definition
        ON field_definition.recordtype = type_definition.id
WHERE
    type_definition.scriptid = ?
ORDER BY
    field_definition.scriptid

Bind the custom-record type script ID (or filter on the type internal ID if that is what you hold):

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

Pattern B: catalog-documented join path

Some accounts expose a named join rather than (or in addition to) a simple foreign-key column. When the catalog lists a join from field definition to type definition, use that relationship’s documented join field and target—not a guessed ON clause.

Test the join alone before adding labels and flags:

SELECT
    field_definition.id,
    type_definition.id
FROM
    CustomRecordCustomField field_definition
    INNER JOIN CustomRecordType type_definition
        ON field_definition.documented_join_field = type_definition.id
WHERE
    ROWNUM <= 10

Replace documented_join_field with the exact join or field ID from the catalog. If the catalog shows a different join syntax or intermediate record, follow that documentation instead of this illustration.

Pattern C: two-step lookup without a join

If a join is unavailable but a type-ID field is present on the field definition, resolve types in application code or with a second catalog-confirmed query:

1. Select field definitions including the owning type ID.
2. Select type definitions for the distinct type IDs.
3. Map them in SuiteScript.

That pattern is often clearer for exports than forcing an unsupported join. See Export a NetSuite Custom-Field Data Dictionary With SuiteScript.

What this join is not

This join	Not this
Field definition → type definition	Instance row → parent instance
Metadata about custrecord_... fields	Values in customrecord_... tables
Analytics records from the Records Catalog	SDF <customrecordtype> / <customrecordcustomfield> XML

Joining metadata does not return stored custom-record values. After you know custrecord_example_status belongs to customrecord_example, query the instance table for data:

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

Use the real customrecord_... and custrecord_... IDs from your account.

Diagnose join failures

Unknown identifier on the join column

The field ID is wrong for SuiteQL, or it is not available in this channel. Re-copy from the catalog under the execution role.

Record not found for CustomRecordType

The type metadata record may be unavailable to the role or feature set. Confirm with Show Unavailable Items.

Cartesian or duplicated rows

Cardinality may be one-to-many in a way you did not expect, or the join field is not unique as assumed. Re-read the catalog join description and add a selective WHERE clause.

Works in Administrator, fails in integration role

Compare the Records Catalog under both roles. Permission-aware schemas commonly differ on metadata records.

Minimal verification checklist

1. Both records visible in the SuiteScript and REST Query API channel.
2. Owning-type field or join confirmed on the field-definition record.
3. Minimal two-ID join returns rows.
4. Type script ID and field script ID columns added only after the join works.
5. Production role re-tested.

For dynamic filters on type or field script IDs, keep bind parameters ordered with the SQL: Build Dynamic SuiteQL WHERE Clauses Without Breaking Bind Parameters.