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:
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.
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 32 characters maximum. If the text exceeds the length, the ID is shortened by removing characters.
If you create a custom list, record, or field, and use a name that already exists, they will be exposed to the NetSuite.com data source with the suffix _0, or with a higher number in case of multiple duplicated record names. Therefore, you must ensure that your queries include the ID exposed. For example, if you create a custom field in the Transactions table and you name it "transaction_id", the custom field will be renamed to "transaction_id_0". Changes to names are retroactive. If a newly exposed table or column has the same name as an existing custom list, record, or field that you created previously, the name that you defined is automatically changed to the same name and the suffix _0.
If you change the name of your custom records, lists or fields, the ID is automatically changed. Therefore, your original queries that include the original ID will no longer work. When you change the name of your custom records, lists or fields, 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.