Custom Columns, Lists, and Records in the Connect Service
Support ends for the NetSuite.com data source in 2025.1, and it will be removed in 2026.1. Start transitioning to the NetSuite2.com as soon as possible to avoid disruption when this change occurs.
To work with the Connect Service and custom records, see the following examples and considerations:
General Considerations for Connect
The following considerations apply to both data sources:
Custom Field Limitations
Due to an internal limitation, queries over SuiteAnalytics Connect schema tables that have more than 1000 columns only work if you retrieve 1000 columns or less in the query. For example, if the Transactions table has more than 1000 columns due to the number of custom fields that have been added to the Transaction record type, attempting to query the table using the “Select * From” construct results in the following error: “Error: Could not find any column information for table:transactions”.
This run failure may also occur when joining multiple tables in a single query and trying to retrieve all of their fields.
To query over a table with more than 1000 columns, you must enumerate the specific columns that you want to retrieve or, if you have to use the “Select * From” construct, you must deactivate some of the custom fields that have been added to the table so that there are 1000 columns or less.
Newly Created Custom Records
If you are using Connect and at the same time you create a new custom table or column using the same account, the newly custom record is not considered yet in your queries. After you created the custom table or column, you need to log out and open a new Connect session.
Considerations for NetSuite.com
The following examples show how NetSuite represents customizations in the Connect Service.
For more information on custom record types and custom lists in the SuiteAnalytics Connect schema, see Custom Lists and Custom Record Types.
Custom List, Record, or Column Name Conflicts
The names of Connect tables and columns are unique and cannot be duplicated. When you create a custom list, record or field, you must ensure that the name is not used in any existing Connect tables or columns.
If you create a custom list, record, or field, and use a name that already exists, they will be exposed to Connect with the suffix _0, or with a higher number in case of multiple duplicated record names. Therefore, your queries will not work as expected.
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.
You must review your custom field names and your queries, and make adjustments as needed.
You can find the links to all Connect tables in the Connect Schema topic.
Custom Transaction Body Column
In this example, an administrator has created a transaction body column called Color. The custom column is a free-form text column. This column is applied to Purchase and Sales transaction forms.
In the Connect Service, the custom column is added to the Transactions table.
Custom List and Free Form Custom Column
In this example, the administrator has created a custom list called Colors. The values included in this list are Blue, Red, Yellow, and Green.
Let's assume when you created the custom Color column in example 1, you selected the Colors list as the List/Record for that column.
The color column shown in the Transactions table in example 1 has been replaced by a color_id column and a new table has been created for the Colors list.
The color_id column in the Transactions table links to the list_id column on the Colors table.
Custom List and Multiple Select Custom Column
This example illustrates the changes made to the Connect Service if the custom column, Color, is changed from a free-form text column to a multiple select column.
A new table, Color Map, is created and is linked to from the Transactions and Colors tables.
Custom Record and Custom Free Form Custom Column
In this example, an administrator creates a custom record called Warranty and adds the following four columns to the custom record:
|
Column Name |
Column Type |
|---|---|
|
Period |
Numeric |
|
Date of Purchase |
Date |
|
Terms |
Free-form Text |
|
Home Service Included |
Check box |
The administrator also creates a free-form transaction body column called Warranty and selects the new custom Warranty record as the List/Record for this column. This column is applied to Purchase and Sales transaction forms.
In the following graphic, a warranty_id column is included in the Transactions table. A new table, named Warranty, represents the custom record. The Warranty table contains a column for each of the custom record columns. It also includes a warranty_name column. This column indicates that the Include Name Column preference was checked during the initial setup of the custom record.
Custom Record and Multiple Select Custom Column
This example is similar to Custom Record and Custom Free Form Custom Column except the custom Warranty column changes from a free-form text column to a multiple select column. The settings for the custom record are not changed.
A new table, Warranty Map, is created and is linked to from the Transactions table and the Warranty table.
Custom column names that contain non-ASCII characters appear without those characters in the SuiteAnalytics Connect schema. If the custom column name contains only non-ASCII characters, the column's field ID is used instead.
Considerations for NetSuite2.com
For details about general considerations for NetSuite2.com, see the following: