NetSuite: Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
Line 18: Line 18:
''UseSandbox'': (Boolean) Set to true if you would like to use the sandbox instance of your NetSuite account.
''UseSandbox'': (Boolean) Set to true if you would like to use the sandbox instance of your NetSuite account.


==Update as of Sept. 25, 2020==
==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".
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".



Revision as of 20:59, 25 September 2020

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.

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_addressee>@@ORG:Name@@</AddressbookList_addressee>"
	xml = xml & "<AddressbookList_Addr1>@@ORG:ShippingStreet@@</AddressbookList_Addr1>"
	xml = xml & "<AddressbookList_City>@@ORG:ShippingCity@@</AddressbookList_City>"
	xml = xml & "<AddressbookList_Zip>@@ORG:ShippingPostalCode@@</AddressbookList_Zip>"
	xml = xml & "<AddressbookList_State>@@ORG:ShippingState@@</AddressbookList_State>"
	xml = xml & "<AddressbookList_Country>@@ORG:ShippingCountry@@</AddressbookList_Country>"
	xml = xml & "</Row></Customer_AddressbookList>"
	ScriptedField=xml
End Function

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


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@@).