1. SuiteAnalytics
  2. Connect
  3. Connect Data Source
  4. NetSuite2.com Data Source
  5. Record Types and Fields

Record Types and Fields

To query your NetSuite data with SuiteAnalytics Connect, you must know the ID of the record types and fields that you want to use in your queries.

To understand how custom records, lists, and fields are named, see Understanding Custom Records, Lists, and Field Naming.

To find the names of standard and custom record types and fields, you can use the following ways:

Note:

The NetSuite2.com data source applies role-based access control. This means that the features, roles and permissions assigned to your account determine the data that you can access through Connect. Using the Static Data Model, SuiteAnalytics Connect still applies role-based permissions. Therefore, you can only get the data for the records that you can access, but you can see the structure and the name of all available record types and fields. For more information, see Setting the Static Data Model for Connect Drivers.

Understanding Custom Records, Lists, and Field Naming

The IDs of custom records, lists and fields follow a specific naming convention.

Note:

Queries in both NetSuite.com and NetSuite2.com are not case sensitive.

Records, lists and fields naming in NetSuite.com

The ID of custom records, lists and fields in NetSuite.com are based on specific fields in the UI and include some additional formatting changes. Custom records and list are taken from the "Name" field, and custom fields are taken from the "Label" field available in Customization > Lists, Records, & Fields > <field name>.

All letters are changed to upper-case letters, white spaces are replaced by underscores, and hyphens are removed. See the following examples:

  • If the name of a list is "Color list", the ID is "COLOR_LIST".

  • If the name of a custom record is "Custom Record", the ID is "CUSTOM_RECORD".

  • If the name of a custom record is "This-is a-record", the ID is "THISIS_ARECORD".

The ID of custom fields can include up to 29 characters maximum. If the text exceeds the length, the ID is shortened by removing characters. When the ID of a custom field list, record, or field is shortened and matches an existing ID, the suffix _0 is added. When the _0 suffix is added, the ID can include up to 30 characters.

Important:

When the ID of a custom record, list or field is changed, ensure that you review your queries and make adjustments as needed.

Records, lists and fields naming in NetSuite2.com

The ID of custom records, lists and fields in NetSuite2.com are based on the text in the "ID" field available in Customization > Lists, Records, & Fields > <field name>. Therefore, if you rename your custom records, lists or fields, your queries are not affected because the ID does not change. See the following example:

  • The ID of the "Transaction Body" custom field is "custbody1".

  • IDs of custom records look similar to the following example: "CUSTOMRECORD1"

Using SuiteAnalytics Connect

If you know the field name in the NetSuite UI, you can use the oa_tables and oa_columns tables. For example, if the field name is Item, you can run the following query:

            SELECT * FROM OA_TABLES WHERE REMARKS=Item

To get a list of all record types and fields that are available in your account, see SuiteAnalytics Connect System Tables.

Using the NetSuite UI and the Records Catalog

There are several ways to know the names of the record types and fields using the NetSuite UI and the Records Catalog. For more information, see Finding Record Type and Field Names.

Using the NetSuite.com to NetSuite2.com Map

The NetSuite.com to NetSuite2.com spreadsheet that includes a list of standard records and fields in NetSuite.com and their corresponding NetSuite2.com records and fields. To download the spreadsheet, see NetSuite.com to NetSuite2.com Map.

For information about custom records, lists and fields in the NetSuite2.com data source, see Understanding Custom Records, Lists, and Field Naming.

Using the Export Option from SuiteAnalytics Workbook

When you use SuiteAnalytics Workbook, you can export your saved dataset definitions to a SuiteQL TXT file. This allows you to understand how fields are joined in your dataset so that you can build your SuiteQL queries.

Note:

When using the exported SuiteQL file with the NetSuite2.com data source, ensure that you use the same user and role used for the export with SuiteAnalytics Workbook.

For information about how to export your dataset definitions, see Exporting Dataset Definitions to SuiteQL.

Related Topics

General Notices