MuleSoft Connector for NetSuite

Build 20.0.7606

Tables

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:

Name Description
Account The NetSuite table Account.
Contact The NetSuite table Contact.
Customer The NetSuite table Customer.
Department The NetSuite table Department.
DiscountItem The NetSuite table DiscountItem.
Employee The NetSuite table Employee.
InventoryItem The NetSuite table InventoryItem.
NonInventoryPurchaseItem The NetSuite table NonInventoryPurchaseItem.
Subsidiary The NetSuite table Subsidiary.
Vendor The NetSuite table Vendor.

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:

Name Description
CashRefund The NetSuite table CashRefund.
CreditMemo The NetSuite table CreditMemo.
Estimate The NetSuite table Estimate.
Invoice The NetSuite table Invoice.
PurchaseOrder The NetSuite table PurchaseOrder.
SalesOrder The NetSuite table SalesOrder.
VendorBill The NetSuite table VendorBill.
VendorCredit The NetSuite table VendorCredit.
VendorPayment The NetSuite table VendorPayment.
WorkOrder The NetSuite table WorkOrder.

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.

When inserting to a child table, multiple list items can be specified by appending a #number to the column name when executing the SQL statement. The following parameterized query will create a new CashRefund record with two line items:

INSERT INTO CashRefund_itemList (entity_internalId, location_internalid, exchangerate, trandate, postingperiod_internalid, itemlist_rate#1, itemlist_taxcode_name#1, itemlist_amount#1, itemlist_item_internalid#1, itemlist_price_internalid#1, itemlist_taxcode_internalid#1, itemlist_description#1, itemlist_item_name#1, itemlist_taxrate1#1, itemlist_price_name#1, itemlist_quantity#1, itemlist_costestimate#1, itemlist_units_name#1, itemlist_units_internalid#1, itemlist_taxcode_name#2, itemlist_units_name#2, itemlist_amount#2, itemlist_item_internalid#2, itemlist_price_internalid#2, itemlist_taxcode_internalid#2, itemlist_description#2, itemlist_item_name#2, itemlist_price_name#2, itemlist_units_internalid#2, itemlist_quantity#2, itemlist_costestimate#2)
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', '', '', '-Not Taxable-', 'Ea', '100.0', '704', '-1', '-7', 'Designer Seat / Back Cushions', 'Designer Seat Cushions', 'Custom', '1', '4.0', '100.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#1, itemlist_taxcode_name#1, itemlist_amount#1, itemlist_item_internalid#1, itemlist_price_internalid#1, itemlist_taxcode_internalid#1, itemlist_description#1, itemlist_item_name#1, itemlist_taxrate1#1, itemlist_price_name#1, itemlist_quantity#1, itemlist_costestimate#1, itemlist_units_name#1, itemlist_units_internalid#1, itemlist_taxcode_name#2, itemlist_units_name#2, itemlist_amount#2, itemlist_item_internalid#2, itemlist_price_internalid#2, itemlist_taxcode_internalid#2, itemlist_description#2, itemlist_item_name#2, itemlist_price_name#2, itemlist_units_internalid#2, itemlist_quantity#2, itemlist_costestimate#2)
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', '', '', '-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:

Name Description
BudgetCategory The NetSuite table BudgetCategory.
CampaignAudience The NetSuite table CampaignAudience.
CampaignCategory The NetSuite table CampaignCategory.
CampaignChannel The NetSuite table CampaignChannel.
CampaignFamily The NetSuite table CampaignFamily.
CampaignOffer The NetSuite table CampaignOffer.
CampaignSearchEngine The NetSuite table CampaignSearchEngine.
CampaignSubscription The NetSuite table CampaignSubscription.
CampaignVertical The NetSuite table CampaignVertical.
CostCategory The NetSuite table CostCategory.
Currency The NetSuite table Currency.
LeadSource The NetSuite table LeadSource.
SalesTaxItem The NetSuite table SalesTaxItem.
State The NetSuite table State.
SupportCaseIssue The NetSuite table SupportCaseIssue.
SupportCaseOrigin The NetSuite table SupportCaseOrigin.
SupportCasePriority The NetSuite table SupportCasePriority.
SupportCaseStatus The NetSuite table SupportCaseStatus.
SupportCaseType The NetSuite table SupportCaseType.
TaxGroup The NetSuite table TaxGroup.
TaxType The NetSuite table TaxType.

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.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7606