The CData Power BI Connector for NetSuite dynamically obtains information about your NetSuite tables at runtime. The tables and columns that are exposed may be controlled by several connection properties. This section will discuss what connection properties may be used to control the listed tables and columns, and how to get the NetSuite data you are looking for. A further discussion about types of tables with examples of how to perform INSERTs and updates to each type can be found in Tables.

NetSuite Default Record Types

There are a number of default record types that come with NetSuite without any customization. These include entities such as Accounts, Customers, and Vendors. It also includes transactions such as SalesOrders, Invoices, and PurchaseOrders. Each of these default record types will be determined at runtime based on the Version connection property. A table will be exposed for every record type found this way.

Note that these default record tables are also frequently called parent tables when talking about child tables below.

Version

At least twice a year, NetSuite will release a new version. The new version often contains new default record types available to all users. In order to get the latest record types to be exposed as tables, set the Version connection property. The default is updated each major release to be in line with the latest from NetSuite.

Child Tables

Many of the default record types in NetSuite will contain collections of data. For instance, Customers can have several addresses. An Invoice can have several line items. These child collections are exposed in what are called Child Tables. For instance, Invoice_itemList would be considered the child table of the parent table Invoice.

Due to the number of child collections, turning them on will cause the total displayed table count to balloon significantly by several hundred. Child tables can be enabled by setting the IncludeChildTables connection property to true. It is false by default.

Child tables are not exposed beyond the first level. For instance, the Expense List on a Purchase Order contains a Linked Order List. There will not be a table exposed for the Linked Order List. Instead, an aggregate column would need to be used to retrieve this data as explained below.

Aggregate Columns

Child collections may also be exposed in a different way via the AggregateColumnMode connection property. Aggregate columns return collections of complex data such as a collection of addresses or line items. These values would not fit into a single column on their own and are thus aggregated together as XML values. Since most child collection information can be obtained easily and more readably by setting IncludeChildTables to true, aggregates are disabled by default.

Aggregates can be useful for exposing further collections on child tables. For instance, if you wanted to retrieve the Linked Order List from the Expense List on a Purchase Order. Set both IncludeChildTables to true and AggregateColumnMode to List. Those settings will cause the ExpenseList_linkedOrderList column to be displayed and return with data on the PurchaseOrder_expenseList table.

It is not recommended to set AggregateColumnMode to ListAndRetrieve except when trying very small samples, such as if you intend to perform INSERTs via an aggregate column and need to get a few samples of what the data looks like. Setting AggregateColumnMode to ListAndRetrieve will cause aggregate column data to be requested at all times on all tables. This will significantly slow down performance on parent tables such as Customers, PurchaseOrders, and Invoices due to NetSuite returning data much slower when this data is requested. When AggregateColumnMode is set to List, aggregate columns will always come back with data when retrieving data from child tables since NetSuite will already come back with the data at no additional performance penalty.

Custom Records

Custom records are exposed as full tables. Note that some types of records that are exposed in NetSuite by default are still considered custom records. We consider anything a custom record if it is available in NetSuite under Customization --> Lists, Records, & Fields --> Record Types. To control this setting, change the IncludeCustomRecordTables.

Custom Lists

Custom lists are not exposed by default. Custom lists in NetSuite are lists of information that may be selected for some types of custom fields. These do not typically contain data that is important since it rarely changes. However, these lists can be exposed as tables if needed by setting the IncludeCustomListTables connection property to true.

Custom Fields

Custom fields are exposed by default and will be displayed directly on the appropriate table. Custom fields are displayed for both parent tables such as Invoice and Customer, and also for child tables such as Invoice_itemList and Customer_addressbookList. Custom Field exposure may be controlled with the IncludeCustomFieldColumns connection property.

Reference Columns

Reference columns are columns that include a record reference. For instance, Invoice contains an Entity record reference. Record references always come back with multiple pieces of information such as an internalid, name, and potentially type and externalid. Due to the number of record reference columns in NetSuite, by default they only display with InternalId and Name. This can be modified by changing the IncludeReferenceColumns connection property.

Reports

Reports are not supported in the NetSuite API. This is a limitation of the API which means that reports are not available for us or other third party tools. NetSuite instead recommends customers make use of the PostingTransactionSummary for getting the sort of information that would appear on reports. The PostingTransactionSummary view can be used to access that information.

Saved Searches

NetSuite saved searches are supported, although there are some limitations due to the NetSuite api. Please see Saved Searches for more information.