Use Excel to Update NetSuite without Traditional Integration Options

Large organizations systems often based on one backbone ERP system incorporation with other subsidiary systems. To update and to be update, these subsidiary systems requires imports and exports operations; As #1 Cloud ERP Netsuite provides various integration options for the external systems. These options includes CSV import export, WebServices, ODBC.

This article is for those who wants to update Netsuite records without using CSV files, Web Service or other third party tools to update Netsuite.

Background
Data entry operators often comfortable with traditional worksheet tools to enter data, such that Microsoft Excel; one of the powerful spreadsheet tool, one of our client having the same situation, for lockbox operation they are using Excel, they enters the data in Excel sheet and update Netsuite Payments using traditional CSV import feature. The client was looking for an easy alternative to update export cash receipts against open invoices in Netsuite.

General Approach But Inefficient
CSV upload is the general approach to update the Netsuite data.  The challenge with the CSV approach is that you must have the hard link to reference appropriate entity IDs, items, and other information before the data will successfully import.  When getting data from a third party source that has no clue about your Netsuite business system, you are confronted with massaging the data in a spreadsheet and use power user functions, such as VLookup, to reference the proper information before it can be uploaded to Netsuite.
All of this data preparation work is a repetitive activity and here comes the need of automation, but traditional types of automation may be unfeasible because of higher cost of development and setup.

Power of suitelet Technology and Excel Macros

Netsuite suitelet plays vital part in integrating 3rd party web applications with the Netsuite, in our case third party application is Excel . suitelet empowers not only develop front end screens in Netsuite , it also facilitates to be called from external world and do the desired tasks via Http request call. While at Excel end we can use macro based on VBA, it empowers the user in Excel to write functions to automates the tasks. In Our approach we will design a macro that will call a Netsuite suitelet via XMLHttpRequest, Netsuite receives the request from Excel and processes the information returning a result that will indicate success or failure.  In our case, if successful, the suitelet will return the URL of the customer payment record we created against the open invoice.

Netsuite suitelet Code Snippet
This is a sample suitelet outline explaining flow via comments:

function GetPaymentLink(request, response) {

 var sMethod = request.getMethod();

 // Get Parameters

 if ( sMethod == 'GET' ){

 //Validate the data

 //If Validation is successful then create payment record and set Payment
 //Record URL in val variable [You can get payment record URL using
 //nlapiResolveURL api function]

 //Else set ‘No Link’ in val variable 

 if (val){
 response.write(val);
 };

 };
};

'Please properly manage the security credential; this is NOT secure 
Const NS_suitelet_URL As String = ""
'macro function to be used as formula

Public Function NSLinkInvoicePayment(Customer,Date,Invoice,Amount)
'call the API    NSLinkInvoicePayment = NSHttpCall (url)         

  Exit Function
End Function   

'Call Netsuite

Private Function NSHttpCall(url)   
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")   
With xmlhttp       
 .Open "GET", url, False       
 .setRequestHeader "Content-Type", "text/html"       
 .setRequestHeader "Accept", "text/html"       
 .send NSCaller = .responseText    

End With   
  Exit Function
End Function

 

Drive Your Netsuite System
Using a software and using a software efficiently are two different ends.  We believe that efficient use of software can play significant role to increase revenue and lower costs to ultimately drive more profit. Netsuite’s power is often locked up primarily because of limited knowledge and / or lack of innovative thinking to solve challenges.

We are Netsuite Official Partner, specialized in Netsuite Integration. Feel free to Contact Us for any query, or to request a quotation .

 

It's only fair to share...Tweet about this on TwitterShare on LinkedInShare on Google+Share on Facebook
How to Append Two Or More PDF Documents In NetSuite
Work Center Calendars

Shoaib Mehmood

Leave a Reply

Your email address will not be published.