Connecting Netsuite Database using SQL Workbench

Pre-Requisites :

(I am a Windows 10 User)

1. Download and Install SQL Workbench. Its Open source tool.
2. Confirm with your Netsuite Admin that Suite Connect option is enabled. It resides right under Set Up > Company > Enable Features > Analytics
3. Make sure that your role has Full permissions to connect to Suite Analytics. At role level > Set Up tab there will be permission option available for Suite Analytics, web services etc
4. In order to perform read write operations on any of the record type you need to have that access at your role level. For example you will be performing upsert operations on customer and only read data for vendor. Your role should have Edit or Full permission to Customer and view permission to vendor along with Suiteanalytics permission. Then only you will be able to retrieve the data correctly.
5. Need Netsuite ODBC drivers. 
6. Your Admin will have Set Up Suite Analytics Connect Option on the Home Page in Settings Portlet
   
7. Once your administrator clicks in there. He will see all available configurations.
   
8. You can choose the configuration that suites to your machine, download and install it.
9. At Installed Driver Location get the path for NQjc.jar
10. Open your SQL WorkBench. Go to File > Manager Drivers and Click On Add New Driver.
11. Name Driver as Netsuite or whatever relevant you feel like and give path to your NQjc.jar
12. We are done with all the required set up. 

How To Connect Netsuite DB?

1. Make sure your port 1708 is not blocked for outbound traffic.
2. Choose the connection attributes from the list
   
3. Build your connection string with your credentials.
4. Go To SQL Workbench click on File > Connect Window
5. Choose your Netsuite Driver which we created in Pre-Requisite# 11
6. Enter Your String in URL section along with UserName And Password.
7. Click on Test.
8. And you will see the successful message here.
9. You are all set.

Basic Formula Field Creation On Transaction Record

Activity which needs to be accomplished :
Create a field on Transaction Records like Opportunity, Quotation, Sales Order, Invoice etc which will display the internalid of that particular record.

It's very simple and straightforward activity.

Here are the Steps (Administrator Role):
1. Create Transaction Body Field
2. Click New
3.  Check the check boxes for Sale and Opportunity from "Applies To" Tab
4. Uncheck the check box for Store Value
5. Go To "Validation And Defaulting" Tab and add formula {id} and set formula checkbox to true.


Create New Transaction Body Field


Transaction Body Field Configurations



Validation and Defaulting Tab





In my opinion this is the best and simple way of creating such fields using formula instead of writing a complicated workflow or a script to populate data which is present on that particular record or its present withing the first join to the record. This also reduces load and improves performance of your system if you have many scripts/workflows running on your system.

-
Chinmay Shepal
Gyan 1.1