Custom Columns, Lists, and Records in the Connect Service

Important:

As of November 8, 2021, new Connect users can access the Connect Service using the NetSuite2.com data source only. If you gained access to the Connect Service before this date, you can still access the NetSuite.com data source to ensure a smooth transition to NetSuite2.com.

Note that the NetSuite.com data source is no longer being updated with newly exposed tables and columns, and support for this data source will end in a future release. The use of the NetSuite.com data source is no longer considered a best practice, and all Connect users are encouraged to use the NetSuite2.com data source. For more information about this change, see New Accounts and Access to the Connect Data Source.

To work with the Connect Service and custom records, see the following examples and considerations:

General Considerations for Connect

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.

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".

Important:

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 column on 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.

The color_id in the Transactions table linked to list_id in 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.

Example showing changes to Connect Service if the custom column type is changed.

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.

Use of warranty_id to join the transactions and warranty tables.

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.

Warranty map table linked to transactions and warranty tables.
Note:

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 syntax requirements for NetSuite2.com, see the following:

Related Topics

Connect Service Considerations
Query Language Compliance
Connections
Exceptions
Column Joins in the Connect Service
Driver Access for a Sandbox or Release Preview Account
Third-Party Application Access
Server Restarts

General Notices