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 uses 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 NetSuite2.com are not case sensitive.
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>. Renaming custom records, lists, or fields won't affect your queries since the ID remains the same. 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"
Getting a list of mappings for custom fields using Netsuite2.com
When working with NetSuite2.com, you can use the oa_columns table to get the IDs for NetSuite2.com custom fields:
-
columns_name - Returns the value in the "ID" field available in Customization > Lists, Records, & Fields > <field name>. This value corresponds to the ID of the custom field in NetSuite2.com.
For example, to get the custom field IDs of the TRANSACTION table, use the following query:
select column_name, table_name, remarks from oa_columns where column_name like 'CUST%' AND table_name = 'TRANSACTION'
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.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 General Options for SuiteQL
There are several options you can use to know the names of the record types and fields for SuiteQL when using SuiteAnalytics Connect and other tools. For more information, see Finding Record Type and Field Names.