This article is relevant if you want to use Excel to update NetSuite but you are not interested in purchasing third party tools and you want to avoid tedious CSV file exports and imports.

Background

For one of our client engagements, the Accounting department was looking for a more rapid way to update customer payments from their lockbox operation.   On a daily basis, an Excel file was delivered to the client representing that day’s cash receipts.  The client was looking for an easy way to update NetSuite to apply the lockbox cash receipts against open invoices.

When the General Approach to Update NetSuite is Inefficient

The general way that most approach this situation is to target NetSuite through a CSV upload.  The challenge with the CSV approach is that you have to get all your data lined up 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 negates the value and begs for automation — especially when receiving data in a frequent manner.  Traditional types of automation may be unfeasible.  For example, our client’s lockbox operation was offered as a competitively priced service by their bank — yet the bank had no capacities to develop an integration with NetSuite.  Furthermore, our client was not going to invest in any old-school IT which may typically would entail setting up local database applications.

Leverage NetSuite RESTLet / SuiteLet Technology and Excel Macros

Based 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 Microsoft’s  XMLHttpRequest library to call out over the web to a custom NetSuite SuiteLet (or RESTlet).  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.

The actual mechanics of what we created is not being presented here because it simply is an enhancement of the work in the original article.  But what is important to illustrate is that you can do quite a bit of processing outside of conventional approaches for data import or full blown system integration.

In our client’s case, the solution was as simple as cutting-and-pasting a formula each day and recalculating the worksheet to do the work.  This is very easy for the novice Excel user.  If the data didn’t link, the client would research the reason, refine the data in the sheet, and then hit F9 (Excel’s manual calculate) to recalculate.

Take Control of Your NetSuite System

Most people just use software.  We believe that software was meant to be enhanced to increase revenue and lower costs to ultimately drive more profit.  NetSuite’s power is often locked up primarily because of limited leadership and / or lack of innovative thinking to solve challenges.  If you are looking for superior help with your NetSuite system, contact us.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top 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 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

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