The metadata for the tables in NetSuite are obtained during run time based on a number of predefined schemas from NetSuite and your unique NetSuite account information. Custom tables and custom fields will be listed and available to work with just like any other table. The connector offers several different types of tables, which have unique features. In this section you will find notes and examples for working with each type of table.

Entity Tables

Entity tables represent entities in NetSuite. These items exist on standard lists and these entities may show up in child lists or be referenced in transactions via their InternalId. Entity tables may contain aggregate columns. For instance, the Account table has a SubsidiaryListAggregate column. This column will contain an XML-formatted list of the subsidiaries associated with the account. These may be modified by updating the aggregate. For instance:

UPDATE Account SET SubsidiaryListAggregate = '<Account_subsidiaryList><Row><SubsidiaryList_InternalId>00001</SubsidiaryList_InternalId></Row><Row><SubsidiaryList_InternalId>00002</SubsidiaryList_InternalId></Row></Account_subsidiaryList>' WHERE InternalId = @AccountInternalId

A few examples of entity tables are:

Transaction Tables

Transaction tables represent transactions in NetSuite. These transactions will work operationally the same as entities. However, for some transactions you may be required to specify line items in order for the insert to work. These can be specified via the ItemListAggregate field, available on all lists where it is applicable. Set the AggregateColumnMode connection property to List for these columns to show up. For instance, the subsequent parameterized query inserts a CashRefund record that has two line items, shown below:

<CashRefund_itemList>
<Row>
  <itemlist_rate>2.00</itemlist_rate>
  <itemlist_taxcode_name>CA-ALAMEDA</itemlist_taxcode_name>    
  <itemlist_units_name></itemlist_item_units_name>    
  <itemlist_amount>2.0</itemlist_amount>    
  <itemlist_item_internalid>860</itemlist_item_internalid>    
  <itemlist_price_internalid>1</itemlist_price_internalid>    
  <itemlist_taxcode_internalid>-159</itemlist_taxcode_internalid>    
  <itemlist_description>Item Description</itemlist_description>    
  <itemlist_item_name>Backing</itemlist_item_name>    
  <itemlist_taxrate1>8.25%</itemlist_taxrate1>    
  <itemlist_price_name>Base Price</itemlist_price_name>    
  <itemlist_units_internalid></itemlist_units_internalid>    
  <itemlist_quantity>1.0</itemlist_quantity>    
  <itemlist_costestimate>0.0</itemlist_costestimate>  
</Row>  
<Row>    
  <itemlist_taxcode_name>-Not Taxable-</itemlist_taxcode_name>    
  <itemlist_units_name>Ea</itemlist_units_name>    
  <itemlist_amount>100.0</itemlist_amount>   
  <itemlist_item_internalid>704</itemlist_item_internalid>   
  <itemlist_price_internalid>-1</itemlist_price_internalid>   
  <itemlist_taxcode_internalid>-7</itemlist_taxcode_internalid>    
  <itemlist_description>Designer Seat / Back Cushions</itemlist_description>    
  <itemlist_item_name>Designer Seat Cushions</itemlist_item_name>    
  <itemlist_price_name>Custom</itemlist_price_name>    
  <itemlist_units_internalid>1</itemlist_units_internalid>    
  <itemlist_quantity>4.0</itemlist_quantity>   
  <itemlist_costestimate>100.0</itemlist_costestimate>  
  </Row>
</CashRefund_itemList>

INSERT INTO CashRefund (entity_internalId, location_internalid, exchangerate, trandate, postingperiod_internalid, itemListAggregate) VALUES (@EntityInternalId, @LocationInternalId, '1', '01/22/2012', @PostingPeriodInternalId, @ItemListAggregate)

A few examples of transaction tables are:

Child List Tables

For many tables, there are a number of lists that can exist as a part of a given entry on the table. We represent this data as an XML aggregate column directly on the table itself. These child lists can also be accessed as their own tables with each row representing a single entry on the list itself. To enable these child tables, set the IncludeChildTables connection property to true.

Child tables will always appear in the format ParentTableName_ChildListName. For instance, CashRefund_itemList. All child tables may be used to either modify the child list itself or insert/update the base entity or transaction. The only exception is that you cannot delete the base entity from the child table. Delete commands will instead be interpreted as a command to delete the specific row from the child list.

For inserting using a child table, a #TEMP table may be used to populate the structure that will eventually be passed to the parent as explained above. For example:

INSERT INTO CashRefund_itemList#TEMP (entity_internalId, itemlist_rate, itemlist_taxcode_name, itemlist_amount, itemlist_item_internalid, itemlist_price_internalid, itemlist_taxcode_internalid, itemlist_description, itemlist_item_name, itemlist_taxrate1, itemlist_price_name, itemlist_quantity, itemlist_costestimate, itemlist_units_name, itemlist_units_internalid) VALUES (@EntityInternalId, @LocationInternalId, '1', '01/22/2012', @PostingPeriodInternalId, '2.00', 'CA-ALAMEDA', '2.0', '860', '1', '-159', 'xxxx', 'Backing', '8.25%', 'Base Price', '1.0', '0.0', '', '') 
INSERT INTO CashRefund_itemList#TEMP (entity_internalId, itemlist_taxcode_name, itemlist_units_name, itemlist_amount, itemlist_item_internalid, itemlist_price_internalid, itemlist_taxcode_internalid, itemlist_description, itemlist_item_name, itemlist_price_name, itemlist_units_internalid, itemlist_quantity, itemlist_costestimate) VALUES (@CashRefundInternalId, @EntityInternalId, @LocationInternalId, '1', '01/22/2012', @PostingPeriodInternalId, '-Not Taxable-', 'Ea', '100.0', '704', '-1', '-7', 'Designer Seat / Back Cushions', 'Designer Seat Cushions', 'Custom', '1', '4.0', '100.0')
INSERT INTO CashRefund (entity_internalId, location_internalid, exchangerate, trandate, postingperiod_internalid, ItemListAggregate) VALUES (@EntityInternalId, @LocationInternalId, '1', '01/22/2012', @PostingPeriodInternalId, 'CashRefund_itemList#TEMP')

Alternatively, a complete parent and child record may be inserted using the child table. But only a single child record may be inserted using this method. For example, the following will create two separate CashRefund records, each with a single item:

INSERT INTO CashRefund_itemList (entity_internalId, location_internalid, exchangerate, trandate, postingperiod_internalid, itemlist_taxcode_name, itemlist_units_name, itemlist_amount, itemlist_item_internalid, itemlist_price_internalid, itemlist_taxcode_internalid, itemlist_description, itemlist_item_name, itemlist_price_name, itemlist_units_internalid, itemlist_quantity, itemlist_costestimate) VALUES (@EntityInternalId, @LocationInternalId, '1', '01/22/2012', @PostingPeriodInternalId, '-Not Taxable-', 'Ea', '100.0', '704', '-1', '-7', 'Designer Seat / Back Cushions', 'Designer Seat Cushions', 'Custom', '1', '4.0', '100.0')
INSERT INTO CashRefund_itemList (entity_internalId, location_internalid, exchangerate, trandate, postingperiod_internalid, itemlist_taxcode_name, itemlist_units_name, itemlist_amount, itemlist_item_internalid, itemlist_price_internalid, itemlist_taxcode_internalid, itemlist_description, itemlist_item_name, itemlist_price_name, itemlist_units_internalid, itemlist_quantity, itemlist_costestimate) VALUES (@EntityInternalId, @LocationInternalId, '2', '01/23/2012', @PostingPeriodInternalId, '-Not Taxable-', 'Ea', '101.0', '704', '-1', '-7', 'Designer Seat / Back Cushions', 'Designer Seat Cushions', 'Custom', '1', '4.0', '101.0')

To append additional rows to an existing entity, simply specify the InternalId of the entity in the INSERT statement. For instance:

INSERT INTO CashRefund_itemList (InternalId, entity_internalId, location_internalid, exchangerate, trandate, postingperiod_internalid, itemlist_rate, itemlist_taxcode_name, itemlist_amount, itemlist_item_internalid, itemlist_price_internalid, itemlist_taxcode_internalid, itemlist_description, itemlist_item_name, itemlist_taxrate1, itemlist_price_name, itemlist_quantity, itemlist_costestimate, itemlist_units_name, itemlist_units_internalid) VALUES (@CashRefundInternalId, @EntityInternalId, @LocationInternalId, '1', '01/22/2012', @PostingPeriodInternalId, '2.00', 'CA-ALAMEDA', '2.0', '860', '1', '-159', 'xxxx', 'Backing', '8.25%', 'Base Price', '1.0', '0.0', '', '') 
INSERT INTO CashRefund_itemList (InternalId, entity_internalId, location_internalid, exchangerate, trandate, postingperiod_internalid, itemlist_taxcode_name, itemlist_units_name, itemlist_amount, itemlist_item_internalid, itemlist_price_internalid, itemlist_taxcode_internalid, itemlist_description, itemlist_item_name, itemlist_price_name, itemlist_units_internalid, itemlist_quantity, itemlist_costestimate) VALUES (@CashRefundInternalId, @EntityInternalId, @LocationInternalId, '1', '01/22/2012', @PostingPeriodInternalId, '-Not Taxable-', 'Ea', '100.0', '704', '-1', '-7', 'Designer Seat / Back Cushions', 'Designer Seat Cushions', 'Custom', '1', '4.0', '100.0')

Simple Tables

Most tables can be used with a WHERE clause when data is selected from them. However, a few tables do not have this ability in the NetSuite API and instead all items must be listed. These are simple tables that generally do not have much data to return.

The SupportEnhancedSQL feature, set by default, enables you to use a WHERE clause in queries against these tables. The following tables cannot be used in the WHERE clause without this feature:

Custom Tables

A custom table is listed for any custom entity list you have created in NetSuite. These will still work just like transaction and entity tables. However, none of the custom tables will have aggregate columns.