NetSuite

From Starfish ETL
Jump to navigation Jump to search

Authentication

Authenticating to a Sandbox takes a few steps. When Creating or Editing the Connection, the Authenticate Now button will not work unless you have the Account ID: filled in with a hyphen. Example: 1234567-SB1.
Once Authenticated, the Connector will not work unless the Account ID has an underscore. Example: 5455890_SB.
So: Fill in your Sandbox Account ID with a hyphen, Authenticate, Save, Edit, Replace the hyphen with an underscore, Save.

Connection String Parameters

AccountId: Required (Number) The Account ID for your Netsuite organization. You can retrieve this number in NetSuite, in the Integration section under Setup.

LogoutUnknownSessions: (Boolean) If you cannot log in because it's complaining there is already an active session, try using this session to force those other sessions to be invalidated.

MaximumConcurrentSessions: (Number) If you wish to use multithreading while writing to NetSuite, you must use this setting. By default, NetSuite does not support mutltithreading. This only becomes available if you have purchased and activated the SuiteCloud Plus license for NetSuite. This will give you the ability to use up to 10 concurrent sessions. If you wish to use 10 threads, then set this parameter to 10, and also use 10 as the value in the Thread Count property on the Run Job tab in Starfish. Using multithreading with NetSuite will greatly improve the speed of writing. If you have to move a large amount of data, we highly recommend acquiring this license.

AggregateColumnMode: (String - available options below) Aggregate columns are the columns that will appear on base tables which aggregate all of the data contained within child collections (such as Address)

  • [Ignore]: All aggregate will be ignored and will not show up as available colums in the table definition.
  • [List]: Aggregate columns will be listed in all tables, but on base tables such as SalesOrders, they will not retrieve data from NetSuite.
  • [ListAndRetrieve]: Aggregate columns will be listed and requested on all tables. When reading data from NetSuite this can significantly slow down requests.

Netsuite Metadata Folder: (String) If you get a certain error, it may be necessary to supply a folder where Starfish can temporarily store metadata retrieved from Netsuite. This allows the metadata to be downloaded a cached to help speed up future requests. Recommended to create a "Metadata" folder under StarfishEngine to ensure proper permissions. Default full suggested path would therefore be: C:\Inetpub\wwwroot\StarfishEngine\Metadata. The Metadata folder will not be created automatically. When first setting this parameter, you'll have to create this folder manually.

RoleId: (Number) The RoleId is the InternalId of the role that will be used to log in to NetSuite. If you do not enter this, it will use the user's default role. Useful for forcing Administrator role in certain scenarios.

UseSandbox: (Boolean) Set to true if you would like to use the sandbox instance of your NetSuite account.

Connection String Parameters Update as of Sept. 25, 2020

When using NetSuite in the cloud, 1234567.app.netsuite.com, you only need the Username, Password and Account ID. If you want to use the sandbox, append "_SB1" to the Account ID: "1234567_SB1".

If you want to use OAuth Access Token, as required by 2-factor authentication on a sandbox, then you must change the underscore to a hyphen in the AccountID before pressing the Authenticate Note button, "1234567-SB1". After authenticating, you must change the hyphen back to an underscore. Yes, it is a bit strange.

Origin SQL Selection

I tested 2 options for querying data. Using the "in" condition was faster.

where Subsidiary_InternalId in ('11','16','27') --812.97 seconds --626 records
--vs
where Subsidiary_InternalId = '11' or Subsidiary_InternalId = '16' or Subsidiary_InternalId = '27' --2328 seconds --626 records

Picklists

When writing to NetSuite Picklist fields, you cannot write the value of the picklist to the field. You must instead reference the Internal Id of the picklist value you wish to use. These can be obtained in the Customization section under Setup. Using a hand-built Xref list for large lists would be recommended practice.


Aggregate Fields

Addresses

When writing addresses to NetSuite, you must use the AggregateColumnMode=List so that the AddressbookListAggregate Column will be available to map to. Address data must be supplied as a chunk of XML containing the complete address record. See below as an example.

Function ScriptedField
	dim xml
	xml = "<Customer_AddressbookList>"
	xml = xml & "<Row>"
	xml = xml & "<AddressbookList_DefaultShipping>true</AddressbookList_DefaultShipping>"
	xml = xml & "<AddressbookList_DefaultBilling>true</AddressbookList_DefaultBilling>"
	xml = xml & "<AddressbookList_Label>Address</AddressbookList_Label>"
	xml = xml & "<AddressbookList_AddressbookAddress_Addressee>TAI v2</AddressbookList_AddressbookAddress_Addressee>"
	xml = xml & "<AddressbookList_AddressbookAddress_Addr1>1111 E Touhy Ave Suite 550</AddressbookList_AddressbookAddress_Addr1>"
	xml = xml & "<AddressbookList_AddressbookAddress_City>Des Plaines</AddressbookList_AddressbookAddress_City>"
	xml = xml & "<AddressbookList_AddressbookAddress_Zip>60056</AddressbookList_AddressbookAddress_Zip>"
	xml = xml & "<AddressbookList_AddressbookAddress_State>IL</AddressbookList_AddressbookAddress_State>"
	xml = xml & "<AddressbookList_AddressbookAddress_Country>_unitedStates</AddressbookList_AddressbookAddress_Country>"
	xml = xml & "</Row>"
        xml = xml & "</Customer_AddressbookList>"
	ScriptedField=xml
End Function

It is possible to supply multiple addresses in a single call by duplicating the entire <Row> node.

Line Items

Sales Order Line Items must be passed into the ItemListAggregate field on the Sales Order entity.

Below is an example of the XML I've passed in. InternalId -3 are comments. InternalId = -2 are subtotals and will total up all products above them up to the previos subtotal.

<ItemList>
    <Row>
        <ItemList_Item_InternalId>531</ItemList_Item_InternalId>
        <ItemList_Quantity>161.00</ItemList_Quantity>
        <ItemList_Price_InternalId>1</ItemList_Price_InternalId>
        <ItemList_Price_Name>Base Price</ItemList_Price_Name>
        <ItemList_Rate>0.000000</ItemList_Rate>
        <ItemList_Amount>11.270000</ItemList_Amount>
    </Row>
    <Row>
        <ItemList_Item_InternalId>-3</ItemList_Item_InternalId>
        <ItemList_Description>Optional items above here.</ItemList_Description>
    </Row>
    <Row>
        <ItemList_Item_InternalId>-2</ItemList_Item_InternalId>
    </Row>
    <Row>
        <ItemList_Item_InternalId>555</ItemList_Item_InternalId>
        <ItemList_Quantity>209.00</ItemList_Quantity>
        <ItemList_Price_InternalId>1</ItemList_Price_InternalId>
        <ItemList_Price_Name>Base Price</ItemList_Price_Name>
        <ItemList_Rate>0.000000</ItemList_Rate>
        <ItemList_Amount>187891.000000</ItemList_Amount>
    </Row>
    <Row>
        <ItemList_Item_InternalId>532</ItemList_Item_InternalId>
        <ItemList_Quantity>202.00</ItemList_Quantity>
        <ItemList_Price_InternalId>1</ItemList_Price_InternalId>
        <ItemList_Price_Name>Base Price</ItemList_Price_Name>
        <ItemList_Rate>0.000000</ItemList_Rate>
        <ItemList_Amount>2262.400000</ItemList_Amount>
    </Row>
    <Row>
        <ItemList_Item_InternalId>-2</ItemList_Item_InternalId>
        <ItemList_Description>Yearly Licenses</ItemList_Description>
    </Row>
    <Row>
        <ItemList_Item_InternalId>523</ItemList_Item_InternalId>
        <ItemList_Quantity>155.00</ItemList_Quantity>
        <ItemList_Price_InternalId>1</ItemList_Price_InternalId>
        <ItemList_Price_Name>Base Price</ItemList_Price_Name>
        <ItemList_Rate>0.000000</ItemList_Rate>
        <ItemList_Amount>154225.000000</ItemList_Amount>
    </Row>
    <Row>
        <ItemList_Item_InternalId>524</ItemList_Item_InternalId>
        <ItemList_Quantity>156.00</ItemList_Quantity>
        <ItemList_Price_InternalId>1</ItemList_Price_InternalId>
        <ItemList_Price_Name>Base Price</ItemList_Price_Name>
        <ItemList_Rate>0.000000</ItemList_Rate>
        <ItemList_Amount>32916.000000</ItemList_Amount>
    </Row>
    <Row>
        <ItemList_Item_InternalId>-2</ItemList_Item_InternalId>
        <ItemList_Description>Services</ItemList_Description>
    </Row>
</ItemList>

Simple code to generate XML from a C# DataTable is:

		//This is one way to create XML from a DataTable.  The .Select loops through each row of the DataTable.  If you need more control over what to do with each index, you'll have to use a foreach loop.
            xRoot.Add(dtProducts.AsEnumerable()
            .Select(r => new XElement("Row",//Add Row Element
                        new XElement("ItemList_Item_InternalId", r.Field<string>("product_netsuite_id_c")),//Add Child-Element.
                        new XElement("ItemList_Quantity", r.Field<string>("quantity")),//Add Child-Element.
                        new XElement("ItemList_Price_InternalId", "1"),//Add Child-Element.
                        new XElement("ItemList_Price_Name", "Base Price"),//Add Child-Element.
                        new XElement("ItemList_Rate", r.Field<string>("deal_calc")),//Add Child-Element.
                        new XElement("ItemList_Amount", r.Field<string>("total_amount"))//Add Child-Element.
                        )
                   )
         );

I needed more control over what happened with each row so I then upgraded to using a foreach loop. This sample includes SQL to pull records out of SugarCRM.

object ScriptedField()
{
    //Setup the result variable and some sample result XML.
    string res = "";
    
    string quoteID = Starfish.OriginData["ID"].ToString();
    
    string sql = @"select pb.id,pbq.bundle_index,pb.name as bundlename,pbp.product_index as itemindex,p.name,p.quantity,p.discount_price,p.discount_rate_percent,p.deal_calc,p.total_amount,pc.product_netsuite_id_c from product_bundles pb
                    inner join product_bundle_product pbp on pb.id = pbp.bundle_id and pbp.deleted <> 1
                    inner join products p on pbp.product_id = p.id and p.deleted <> 1 
                    inner join products_cstm pc on p.id = pc.id_c
                    inner join product_bundle_quote pbq on pb.id = pbq.bundle_id and pbq.deleted <> 1
                    where pbq.quote_id = '" + quoteID + @"' and pb.deleted <> 1
                    UNION
                    select pb.id,pbq.bundle_index,pb.name as bundlename,pbnq.note_index as itemindex,pbn.description,'' as quantity,'' as discount_price,'' as discount_rate_percent,'' as deal_calc,'' as total_amount,'-3' as product_netsuite_id_c from product_bundles pb
                    inner join product_bundle_note pbnq on pb.id = pbnq.bundle_id and pbnq.deleted <> 1 
                    inner join product_bundle_notes pbn on pbnq.note_id = pbn.id and pbn.deleted <> 1
                    inner join product_bundle_quote pbq on pb.id = pbq.bundle_id and pbq.deleted <> 1
                    where pbq.quote_id = '" + quoteID + @"' and pb.deleted <> 1
                    order by bundle_index,itemindex asc";
//    Starfish.LogMessage(sql);
    System.Data.DataTable dtProducts;
    dtProducts = new System.Data.DataTable();
    dtProducts = Starfish.SmartQueryDT("System/SelectSQL",sql,"product_netsuite_id_c,bundle_index,bundlename,itemindex,name,quantity,discount_price,discount_rate_percent,deal_calc,total_amount","ORIGIN");
    if (dtProducts.Rows.Count>0) 
	{
//		Starfish.LogMessage(dtProducts.Rows[0][0].ToString());
		XDocument xDoc = new XDocument();
		XElement xRoot = new XElement("ItemList");
		
		int index = 0;
		int currentBundleIndex = 0;
		int ungroupedProduct = 0;
		string priorBundleName = "";
		foreach (DataRow row in dtProducts.Rows) {
//            Starfish.LogMessage("product_netsuite_id_c: " + row["product_netsuite_id_c"].ToString() + "; bundle_index: " + row["bundle_index"].ToString() + "; bundlename: " + row["bundlename"].ToString() + "; itemindex: " + row["itemindex"].ToString() + "; name: " + row["name"].ToString() + "; quantity: " + row["quantity"].ToString() + "; discount_price: " + row["discount_price"].ToString() + "; discount_rate_percent: " + row["discount_rate_percent"].ToString() + "; deal_calc"  + row["deal_calc"].ToString() + "; total_amount: " + row["total_amount"].ToString());
            //if index = 0 and bundle_index is 0 then i know i have a product not in a product group and will need to have a subtotal when I get to bundle_index 1.
            if ((int.Parse(row["bundle_index"].ToString()) == 0) && (int.Parse(row["product_netsuite_id_c"].ToString()) != -3) && (index == 0))
            {
                //Log that I have at least 1 ungrouped product at the top of a Quote.
                ungroupedProduct = 1;
            }
            
            //If necessary, add a subtotal to the XML.
            if (int.Parse(row["bundle_index"].ToString()) > currentBundleIndex)
            {
                if ((int.Parse(row["bundle_index"].ToString()) == 1) && (ungroupedProduct == 1))
                {
                    xRoot.Add(new XElement("Row",//Add Row Element
                        new XElement("ItemList_Item_InternalId", "-2")//Add Child-Element.
                        )
                    );
                } else if (int.Parse(row["bundle_index"].ToString()) > 1) {
                    xRoot.Add(new XElement("Row",//Add Row Element
                        new XElement("ItemList_Item_InternalId", "-2"),//Add Child-Element.
                        new XElement("ItemList_Description", priorBundleName)//Add Child-Element.
                        )
                    );
                }
            } 
            
            //If a comment, insert a comment.  Else if a product, insert a product.
            if (row["product_netsuite_id_c"].ToString() == "-3")
            {
                //do i need to do anything special about comments or just let the standard code do it?  I'll have to test.
//                Starfish.LogMessage("Adding the comment to the XML.");
                xRoot.Add(new XElement("Row",//Add Row Element
                    new XElement("ItemList_Item_InternalId", row["product_netsuite_id_c"].ToString()),//Add Child-Element.
                    new XElement("ItemList_Description", row["name"].ToString())//Add Child-Element.
                    )
                );
            } else if (int.Parse(row["product_netsuite_id_c"].ToString()) > 0)
            {
                //Here I need to add items to the XML.
                xRoot.Add(new XElement("Row",//Add Row Element
                    new XElement("ItemList_Item_InternalId", row["product_netsuite_id_c"]),//Add Child-Element.
                    new XElement("ItemList_Quantity", row["quantity"]),//Add Child-Element.
                    new XElement("ItemList_Price_InternalId", "1"),//Add Child-Element.
                    new XElement("ItemList_Price_Name", "Base Price"),//Add Child-Element.
                    new XElement("ItemList_Rate", row["deal_calc"]),//Add Child-Element.
                    new XElement("ItemList_Amount", row["total_amount"])//Add Child-Element.
                    )
                );
            }
            index++;
            currentBundleIndex = int.Parse(row["bundle_index"].ToString());
            priorBundleName = row["bundlename"].ToString();
		}
		//If there was an product Group in Sugar, then we need to add a subtotal for it.
		if (currentBundleIndex > 0)
		{
		    xRoot.Add(new XElement("Row",//Add Row Element
                new XElement("ItemList_Item_InternalId", "-2"),//Add Child-Element.
                new XElement("ItemList_Description", priorBundleName)//Add Child-Element.
                )
            );
		}
		
         xDoc.Add(xRoot);
//         Starfish.LogMessage(xDoc.ToString());
         res = xDoc.ToString();
	}
	return res;
}

Uploading Attachments

It is possible to upload attachments to NetSuite and must be done in two stages.

  • First you must use an Insert stage to create a record in the File table. Here you must map the file's data as base64 to the Content column, and set the Folder_InternalId. This must be the InternalId of the Folder you wish all your attachments to go to. You may use multiple different folders, but for simplicity it's recommended to create a general "Migrated Attachments" folder in NetSuite, then use the InternalId of that newly created folder.
  • Then you must use an Insert stage to create a record in the virtual table, "File Attach". This performs an Attachment operation, which is what is responsible for relating the file to the actual parent object (customer, lead, case, etc). The File Attach table gives you 3 columns. AttachTo_Type should be the name of the entity to relate the file to ("customer", "supportCase", etc). AttachTo_InternalId should be the Id of that parent object. File_InternalId should be the Id from the File insert that was created in the first Stage. Use a Stage Value variable to retreive this (@@STG:0,#ID@@).

Working With NetSuite

To see all data for a record, append "&xml=t" to the end of the URL. This will display the record's data in XML format including data in any hidden fields. Example:
https://sb1.app.netsuite.com/app/common/item/item.nl?id=1849&whence=&cmid=1598435822716_140&xml=t

RESTlets & the REST API

The NetSuite connector is built on code from cData. See these pages for information about RESTlets and Views:
http://cdn.cdata.com/help/DNF/odbc/pg_restlets.htm
https://www.cdata.com/kb/articles/netsuite-restlets.rst