Large organizations systems often based on one backbone ERP system incorporation with other subsidiary systems. To update and to be updated, these subsidiary systems requires imports and export 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:
[code language=”javascript”]
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);
};
};
};
[/code]
[code language=”vb”]
‘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
[/code]
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 .
