Published on August 23, 2021.

The SuiteQL-related question that I get asked the most often is, "Can we query NetSuite for metadata?" What these developers are really asking for are tables that contain information about an instance's tables and fields.

Unfortunately, at this point, NetSuite hasn't made that metadata available to us via SuiteQL yet. However, there are a few interesting tables that we can query to get that type of data for the custom tables and fields in a NetSuite account. In this post, I'll introduce the two tables that provide that information.

Custom Record Types

Information about the custom record types that have been setup in an account can be found in the "CustomRecordType" table. It's a basic table, with columns that you'd probably expect to see, such as each tables name, script ID, and so on. Here's a basic query that you can use with the CustomRecordType table.

SELECT
	Name,
	ScriptID,
	InternalID,
	Description,
	BUILTIN.DF( Owner ) AS Owner,
	AllowQuickSearch,
	AllowInlineEditing,
	AllowAttachments
FROM
	CustomRecordType
ORDER BY
	Name

Here's a screenshot showing an example of the query results.

Custom Fields

Similarly, information about the custom fields that have been setup in an account is stored in a table named "CustomField." And again, its columns are probabaly what you'd expect - field names, script IDs, descriptions, etc.

Here's a query against that table.

SELECT
	Name,
	ScriptID,
	Description,
	FieldType,
	FieldValueType,
	FieldValueTypeRecord,
	BUILTIN.DF( FieldValueTypeRecord ) AS FieldValueTypeRecordName,
	IsMandatory,
	IsStored,
	IsShowInList,
	BUILTIN.DF( Owner ) AS Owner
FROM
	CustomField
WHERE
	RecordType = 297

Notice that I'm filtering on RecordType. This column represents the NetSuite internal ID of the table that the fields are associated with. As I showed earlier, you can query the CustomRecordType table to get the internal ID of a custom table that you're interested in, and then use its InternalID to query the CustomField table for its fields.

Also notice the FieldValueType and FieldValueTypeRecord columns. Of course, FieldValueType indicates the field type, such as Check Box, Date, Image, and so on. But in cases where the field type is List/Record, the ID of the list or record type is stored in the FieldValueTypeRecord column. In the query above, I'm returning the internal ID of the list / record type, and using the BUILTIN.DF function to also return the name of the list / record.

Here's another screenshot, this time showing an example of the CustomField query results.

Standard Record Types and Fields

As I mentioned above, at this point, we do not have a way to query for the standard NetSuite tables and fields. Back in January, I showed how you can use the Records Catalog API to get a complete list of a NetSuite account's record types, fields, and so on. I'm using that technique in the SuiteQL Query Tool. While that technique is helpful, the issue with it is that you cannot run it server-side. As a result, it has to be run as part of a client-side process. Hopefully, at some point we will have access to this additional metadata via SuiteQL.

If you have any questions about this post, please let me know.