How to query NetSuite custom records with SuiteQL

Querying NetSuite custom records in SuiteQL is easier than the standard tables, once you know the naming rules — there is no mainline filter, no transaction type code, and no header-and-line split. A custom record type is one table, its fields are columns, and the whole thing behaves like a table you designed yourself. The friction is entirely in discovery: finding the exact table name, finding out which columns exist, and turning internal ids into something a human recognises.

Custom record table naming

A custom record type is exposed as a table named by its script ID, always beginning customrecord. Its custom fields are columns beginning custrecord. Both are literal — SuiteQL does not resolve display names, so Project Milestone is not a table and Status is not a column.

The prefixes, and where each one lives

NetSuite encodes the field's home in its prefix. Getting this wrong is the usual reason a column "does not exist" — the field is real, you are just looking on the wrong table.

How to query NetSuite custom records, step by step

1. 01

   Get the exact script ID

   In NetSuite go to Customization > Lists, Records, & Fields > Record Types and open the record type. The ID field is your table name, verbatim, including any suffix the account added when the ID collided. Copy it rather than retyping it — customrecord_proj_milestone and customrecord_project_milestone are different tables and one of them does not exist.

2. 02

   Dump one row to discover the columns

   Run SELECT * FROM customrecord_project_milestone WHERE ROWNUM <= 1. The keys in the response are the exact column names, including every custrecord_* field and the standard audit columns. This is faster than reading the field list in the UI and it reflects what the query layer actually exposes, which is the thing you care about.

3. 03

   Pin down the standard columns you can rely on

   Custom record tables carry a small set of built-in columns alongside your fields — id and isinactive on every one, plus name if the record type has the name field enabled, an externalid, an owner, and created/last-modified audit stamps. Names for the audit columns vary a little, so take them from the step-2 dump rather than assuming.

4. 04

   Write the query with an explicit column list

   Replace SELECT * with the columns you need. This keeps the payload small and, more importantly, means your query fails loudly if someone renames a field rather than silently returning a different shape to whatever consumes it.

5. 05

   Resolve list and record references

   Any field of type List/Record — a status, a customer, another custom record — stores the internal id. Wrap it in BUILTIN.DF() for the display label, or join to the source table when you need more than the label. Checkbox fields return the strings 'T' and 'F', not booleans.

6. 06

   Rerun it as the role that will use it

   Custom record types have their own access configuration, and it is common for a record type to be locked down in a way the administrator never notices. Verify the row count as the production role or integration user before you ship anything that depends on it.

SELECT * FROM customrecord_project_milestone WHERE ROWNUM <= 1

SELECT
    m.id,
    m.name                                  AS milestone,
    BUILTIN.DF(m.custrecord_pm_status)      AS status,
    BUILTIN.DF(m.custrecord_pm_customer)    AS customer,
    m.custrecord_pm_due_date                AS due_date,
    m.custrecord_pm_value                   AS milestone_value,
    m.custrecord_pm_invoiced                AS invoiced_flag
FROM customrecord_project_milestone m
WHERE NVL(m.isinactive, 'F') = 'F'
  AND m.custrecord_pm_due_date < TO_DATE('2026-08-01', 'YYYY-MM-DD')
  AND NVL(m.custrecord_pm_invoiced, 'F') = 'F'
ORDER BY m.custrecord_pm_due_date

That returns unbilled milestones due before August. Two details worth copying: NVL(..., 'F') on the checkbox fields, because an unchecked box on a record created before the field existed can be null rather than 'F'; and TO_DATE with an explicit format mask, so the query does not depend on the session's date preference.

Joining custom records to standard records

This is where custom records earn their keep. A custrecord_* field of type List/Record stores the internal id of the target record, so the join is a plain equality against that table's id.

SELECT
    c.entityid                          AS customer_no,
    c.companyname,
    m.name                              AS milestone,
    m.custrecord_pm_value               AS milestone_value,
    t.tranid                            AS invoice_no,
    t.trandate                          AS invoice_date,
    t.foreignamountunpaid               AS open_amount
FROM customrecord_project_milestone m
JOIN customer    c ON c.id = m.custrecord_pm_customer
LEFT JOIN transaction t ON t.id = m.custrecord_pm_invoice
WHERE NVL(m.isinactive, 'F') = 'F'
  AND m.custrecord_pm_due_date >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
ORDER BY c.companyname, m.custrecord_pm_due_date

The LEFT JOIN on the invoice is deliberate. An inner join would quietly drop every milestone that has not been invoiced yet — which, for a report about unbilled work, is the entire population you care about. Getting inner and left the wrong way round is the most expensive mistake in this genre of query; SuiteQL join examples works through the standard paths in more detail.

The relationship also runs the other way. If a transaction body field holds a custom record id, join from the transaction:

SELECT
    t.tranid,
    t.trandate,
    BUILTIN.DF(t.entity)                AS customer,
    t.foreigntotal,
    BUILTIN.DF(cr.custrecord_ct_region) AS contract_region,
    cr.name                             AS contract
FROM transaction t
JOIN customrecord_service_contract cr ON cr.id = t.custbody_service_contract
WHERE t.type = 'CustInvc'
  AND t.trandate >= TO_DATE('2026-04-01', 'YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01', 'YYYY-MM-DD')

Filter on the id, not on BUILTIN.DF(). WHERE t.custbody_service_contract = 8812 is a key comparison; WHERE BUILTIN.DF(t.custbody_service_contract) = 'Northwind MSA' makes the database resolve a label for every candidate row. The difference is not subtle on a large transaction table — see SuiteQL performance tips.

Field types, and what each one gives you back

Parent-child custom records

There is no line-level sublist on a custom record the way there is on a transaction. A "sublist" is a second custom record type with a custrecord_* field pointing back at the parent, so the query is an ordinary one-to-many join and aggregation works normally.

SELECT
    p.id,
    p.name                                  AS contract,
    COUNT(c.id)                             AS milestone_count,
    SUM(NVL(c.custrecord_pm_value, 0))      AS contract_value,
    MIN(c.custrecord_pm_due_date)           AS next_due
FROM customrecord_service_contract p
LEFT JOIN customrecord_project_milestone c
       ON c.custrecord_pm_contract = p.id
      AND NVL(c.isinactive, 'F') = 'F'
WHERE NVL(p.isinactive, 'F') = 'F'
GROUP BY p.id, p.name
HAVING COUNT(c.id) > 0
ORDER BY 4 DESC

The child-side isinactive filter belongs in the ON clause, not the WHERE clause. Put it in WHERE and you convert the left join back into an inner join, losing every parent with no active children — exactly the rows an exception report is looking for.

Permissions, and the empty result set

Custom record types are configured with their own access model: a record type can require its own permission, defer to a permission list, or be open to anyone with access to the account. Whoever built the record chose one, possibly years ago, possibly by accident. The result is that a SuiteQL query against a custom record can return a full set for an administrator and nothing at all for the role that needs it, with no error to explain the difference.

Check two things before you hand a query over: the record type's access configuration, and the actual row count as the role or integration user that will run it in production. This is the same silent-filtering behaviour that catches people on standard tables, and it is the first thing to check when "the query returns nothing" — the full set of quiet failures is in common SuiteQL errors explained.

Why custom fields vanish from reports but not from SuiteQL

A recurring complaint is that a custom field shows on the record but refuses to appear in a report or a search join. Field type, whether the field is stored, and which record types it is applied to all affect where it can be reported on — that whole subject is reporting on NetSuite custom fields. SuiteQL sidesteps a good part of it, because if the column exists on the table you can select it, join it and aggregate it without asking the reporting layer's permission. That is the strongest single argument for learning SuiteQL in a heavily customised account.

Extracting the result is usually the last step. For a one-off review, dump the rows and open them in a spreadsheet with the JSON to CSV converter; going the other way, if you are staging a list of ids to filter on, the CSV to JSON converter will shape a pasted column into something you can feed a script.

The part that stays awkward is discovery. In an account with 40 custom record types and 300 custom fields, the honest bottleneck is that nobody remembers what custrecord_pm_st2 means. That is the problem ERPray attacks first: it builds a dictionary of your account's own tables, custom records and custom fields, uses it to generate the query, and shows you the SuiteQL alongside the answer — so you can check it named the right custrecord_ field before you trust the number.