How To: Avoid Data Downloads with Excel Formula Based NetSuite Saved Search Queries

This article is relevant if you are looking to build Microsoft Excel worksheets using formulas which will retrieve NetSuite data leveraging Saved Search technology.

Background

While helping a client build an Excel based financial reporting environment (see Finally, NetSuite Financial Statements Produced in Excel), they needed to get at information that was not readily available from the new  tools they were using.  Because they loved that they could use easy Excel formulas to retrieve income statement and balance sheet balances, they wanted the same capacity to get at specific operational information.

In relatively short fashion, we invented a provocative way to retrieve data from a NetSuite Saved Search that does NOT require ODBC or Web Services.  Instead, we leverage NetSuite Suitelet technology (Restlets can work too) to expose selective business information to your Excel Workbook.

Software Architecture

The diagram (click to enlarge) illustrates the software model to connect up Excel to your NetSuite Saved Search. While a full framework can be developed that generalizes the capacity, I will offer short code snippets to help you understand better what is going on. Naturally, all of the software must come together to be a full solution and often it is faster to solve a specific use case.    Remember, the code examples below are NOT secure.  Please consider this in your ultimate solution.

Microsoft Excel VBA Code Snippet

Using Microsoft Excel VBA, create a user defined function that will effectively call out the saved search.  In real use, parameterize the query so that it is flexible.

'/ Do not distribute this link; in production use, properly manage the security credential; this is NOT secure
Const NS_URL As String = "https://forms.netsuite.com/app/site/hosting/scriptlet.nl?script=398&deploy=1&compid=852534&h=231a34ed464989202228"

'/user defined function used as a formula in Excel
Public Function getHours(Day, Month, Year)
    If Not IsNumeric(Day) Then
        Exit Function
    End If
    If Not IsNumeric(Month) Then
        Exit Function
    End If
    If Not IsNumeric(Year) Then
        Exit Function
    End If

	'/ craft the URL and add a parameter for the date
	Dim url As String
	url = NS_URL & "&date=" & Month & "/" & Day & "/" & Year
    'ITSLocation = url
    '/ call the API
    getHours = NSCaller(url)
	Exit Function
End Function

'/function to retrieve value from Suitelet
Private Function NSCaller(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

NetSuite Suitelet Code Snippet

This is a simple Suitelet that returns a single value.  In real life, you would  do work to validate input, produce error handling, and consider security.

var CONST_SS = ‘customsearch_gethours’;

function GetHoursSuitelet(request, response) {
	var sMethod = request.getMethod();
	if ( sMethod == 'GET' ){
        var sDate = request.getParameter('date'), get the date

		var filter = new Array();
		filter[0] = new nlobjSearchFilter('custrecord_hours_date', null, 'is', sDate);

		var search_results = nlapiSearchRecord('customrecord_mycohours', CONST_SS, filter, null);
		if (!search_results)return;
		if (search_results.length==0) return;

		// should only return one result
		var hours = search_results[0];

		val = hours.getValue('total');
        if (val){
        	response.write(val);
        };
        return;
	};
};

Get at your NetSuite Data

The example above illustrates the power of the NetSuite platform and it is one of the many reasons we love working in the environment. ¬†We can invent to take care of our customers’ challenges.

Never feel hostage to your information in NetSuite. ¬†If you would like help to address your specific concern, let’s talk.


Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , | Category: Reporting, Technical | 1 Comment

One Trackback

  1. […] on the design pattern offered in this article I previously crafted, How To: Avoid Data Downloads with Excel Formula Based NetSuite Saved Search Queries, we solved the challenge. ¬†The article demonstrates the way to create Excel Macros that uses […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>