Due to the way that NetSuite handles metadata using SuiteQL's native tables (i.e. it isn't provided), there is currently no API-provided way to determine the correct custom field(s) / metadata available for tables with internal types of 'ENTITY' or 'EVENT' (like the 'customer' table). Our connector uses a separate SOAP request to get the custom fields for these tables, but SOAP uses token-based authentication (TBA) only.
The only available alternative for seeing these columns with SuiteQL Schema at this time is to use token-based authentication. If you have not already created the OAuthClientId, OAuthClientSecret, OAuthAccessToken and OAuthAccessTokenSecret needed to use TBA, instructions can be found in the following part of the documentation: https://cdn.cdata.com/help/DNJ/powerbi/pg_createinui.htm
*** Note 2 other properties that might affect data retrieval in similar scenarios:
Double check that "Store Value" is checked in NetSuite in the entity fields you wish to see ("Customization" -> "Lists, Records, & Fields" -> "Entity Fields").
This makes it possible for the custom fields to be exposed to the API (affects custom fields and custom records as well)
The other property would be CData’s Connection String Property RowScanDepth. Per documentation: This property applies only to the SuiteQL schema. SuiteQL has no metadata service available outside of custom records and custom fields. Standard tables and fields must be hardcoded in some fashion. However, this comes with a downside in that NetSuite will throw exceptions when columns and tables that are not available based on your user permissions or NetSuite company settings are attempted to be selected.
To try and ensure that at least the columns displayed are available to you in your connection, a RowScan can be performed during column listing. This will ensure that any columns that are displayed for a given table are made available based on your user credentials.
Note that NetSuite does not return columns that are null. Therefore, this method may result in tables that do not have complete column information. Please be aware that SuiteQL has a maximum pagesize of 1000. Setting this value higher than 1000 will result in multiple page requests. Setting RowScanDepth to 0 will result in no rows being scanned (all available columns will be listed regardless). RowScanDepth may not be set higher than 100000.
If this does not resolve the error then please reach out to [email protected] for further assistance.
