Overview
The Lyftron Provider for Netsuite offers the most natural way to access Netsuite data from Lyftron with ease and also enables to connect with BI, MDM & ML tools, Data warehouses, Databases and other SAAS based applications with zero code and zero infrastructure requirements. The provider wraps the complexity of accessing Netsuite data into easy-to-integrate relational fully managed ANSI Sql format. Make faster and better business decisions with Lyftron’s Netsuite data provider and automatically build your data migration pipelines in minutes, not months
The provider hides the complexity of accessing data and provides additional powerful security features, smart caching, batching, socket management, and more.
Key Features
- Comprehensive Delta load mechanism.
- Real-time access to Netsuite.
- Comprehensive full support of ANSI Sql to query data with ease.
- Collaborative query processing.
Prerequisites
The user must have credentials for Netsuite, Lyftron and your destination data warehouse, lake or database to perform the data pipeline operation with Lyftron
Establishing a Connection with Lyftron's Quickstart Steps
Create your Netsuite connection with Lyftron by following the 5 easy steps show below:
Step1. Add your connection
Click on Connect section on the left panel → Click on Add Connection button
Step2. Select your connector
In the connector selection panel, search and click Netsuite for your connection
Step3. Enter your connection details
In the Connection String section enter the values of the below parameters. The following connection string is required to establish Netsuite connection with Lyftron.
Account Id=[your_accountId];OAuthClientId=[your_OauthClientId];OAuthClientSecret=[your_ClientSecret];OAuthAccessToken=[your_Token];OAuthAccessTokenSecret=[your_TokenSecret];IncludeChildTables=True;Logfile=[Logfile Path];| Key | Value | Field |
| Connection Name | Enter your connection details | Required |
| Account Id | XABC123456 | Required |
| Password | your Netsuite password | Required |
| User | your Netsuite user | Required |
| Role Id | 3 | Required |
| Version | 2013_1 | Required |
| Location | C:\\myfolder\\ | Required |
| Logfile** | Use the logfile option to debug your job and provide your connection name to generate the log file. [ConnectionConfigurationPath]\Connection_name_log.tx | Optional |
| Verbosity** | Choose verbosity 1-5 based on the severity of debugging | Optional |
** For more information, check the Lyftron logging and debugging section.
If you want more detailed information about how to establish a connection with Lyftron, click on Lyftron Connection Quick Start guide.
Step4. Test your connection
Once you are done entering your connection details, simply click on the Test Connection button to test the connectivity. In case your connection fails, add Logfile and Verbosity parameters and check the Lyftron logging and debugging section, to debug the error.
Step5. Save your connection
Hurray! Now you have successfully connected with the Lyftron Netsuite connector and can utilize the connector to Extract, Warehouse, Analyze, Visualize and Share your data.
Data Model
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 provider 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:
| Name | Type | Description |
| Account | Tables | The NetSuite table Account. |
| Contact | Tables | The NetSuite table Contact. |
| Customer | Tables | The NetSuite table Customer. |
| Department | Tables | The NetSuite table Department. |
| DiscountItem | Tables | The NetSuite table DiscountItem. |
| Employee | Tables | The NetSuite table Employee. |
| InventoryItem | Tables | The NetSuite table InventoryItem. |
| NonInventoryPurchaseItem | Tables | The NetSuite table NonInventoryPurchaseItem. |
| Subsidiary | Tables | The NetSuite table Subsidiary. |
| Vendor | Tables | 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)
| Name | Type | Description |
| CashRefund | Tables | The NetSuite table CashRefund. |
| CreditMemo | Tables | The NetSuite table CreditMemo. |
| Estimate | Tables | The NetSuite table Estimate. |
| Invoice | Tables | The NetSuite table Invoice. |
| PurchaseOrder | Tables | The NetSuite table PurchaseOrder. |
| SalesOrder | Tables | The NetSuite table SalesOrder. |
| VendorBill | Tables | The NetSuite table VendorBill. |
| VendorCredit | Tables | The NetSuite table VendorCredit. |
| VendorPayment | Tables | The NetSuite table VendorPayment. |
| WorkOrder | Tables | 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 | Type | Description |
| BudgetCategory | Tables | The NetSuite table BudgetCategory. |
| CampaignAudience | Tables | The NetSuite table CampaignAudience. |
| CampaignCategory | Tables | The NetSuite table CampaignCategory. |
| CampaignChannel | Tables | The NetSuite table CampaignChannel. |
| CampaignFamily | Tables | The NetSuite table CampaignFamily. |
| CampaignOffer | Tables | The NetSuite table CampaignOffer. |
| CampaignSearchEngine | Tables | The NetSuite table CampaignSearchEngine. |
| CampaignSubscription | Tables | The NetSuite table CampaignSubscription. |
| CampaignVertical | Tables | The NetSuite table CampaignVertical. |
| CostCategory | Tables | The NetSuite table CostCategory. |
| Currency | Tables | The NetSuite table Currency. |
| LeadSource | Tables | The NetSuite table LeadSource. |
| SalesTaxItem | Tables | The NetSuite table SalesTaxItem. |
| State | Tables | The NetSuite table State. |
| SupportCaseIssue | Tables | The NetSuite table SupportCaseIssue. |
| SupportCaseOrigin | Tables | The NetSuite table SupportCaseOrigin. |
| SupportCasePriority | Tables | The NetSuite table SupportCasePriority. |
| SupportCaseStatus | Tables | The NetSuite table SupportCaseStatus. |
| SupportCaseType | Tables | The NetSuite table SupportCaseType. |
| TaxGroup | Tables | The NetSuite table TaxGroup. |
| TaxType | Tables | 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.
Views
Views are composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure is available to update the data if such functionality is applicable to the data source.
Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.
| Name | Type | Description |
| BudgetExchangeRate | Views | Retrieves the budget expense rate from NetSuite. |
| ConsolidatedExchangeRate | Views | Gets the consolidated exchange rate, by default for each month of the actual year . You can also filter the result by PeriodName (ex. WHERE PeriodName = 'Q2 2008') or by Period_InternalId (ex. WHERE Period_InternalId = '166') . |
| CustomFields | Views | Returns information about the custom fields in NetSuite. CustomFields may be retrieved by CustomFieldType, or by a combination of CustomFieldType and InternalId. If no CustomFieldType is specified, all custom fields will be returned. |
| CustomRecords | Views | Returns information about the custom records in NetSuite. |
| EnumerationValues | Views | Retrieves enumeration values for a given table. Will only return values if the TableName is specified. Only available for native tables within NetSuite and not Custom tables. |
| MyRoles | Views | Returns the roles available for the authenticated user. This may include information for additional accounts the user is authorized to access. |
| PostingTransactionSummary | Views | Retrieves a posting transaction summary from NetSuite. This is the reporting mechanism available from NetSuite. All record reference columns may be used in filter using either either the = or IN operator. Other operator types are not available. For NetSuite OneWorld customers, ConvertToSubsidiary may also be specified as the subsidiary all response values should be converted to. |
| SavedSearches | Views | Returns the saved searches in NetSuite. A SearchType must be specified in order to retrieve saved searches. |
| SavedSearchRawXML | Views | Returns the raw XML from a saved search. |
| Transactions | Views | A view for transactions on NetSuite. |
Advanced Settings
To view a detailed advanced settings options, go to Netsuite Advanced Settings. Complete list of the parameters you can configure in the connection string can be found by clicking Connection String Parameters.