This article is relevant if you are seeking a scalable way to extract or export data from NetSuite.

Background

Many of our clients use third party logistics (3PL) companies to manage their inventory. In this model, our clients capture sales information into NetSuite and then perform integrations with their 3PL providers of choice to send shipment instructions. These integrations take on many shapes depending on the capabilities of the 3PL organization.

One common pattern is to send a file representing the orders to ship. This “transmit file” pattern has been used successfully in systems integrations for decades and is often the most common approach when the parties do not have more sophisticated technology capabilities.

While NetSuite offers saved searches to extract data out, the tools are rudimentary if you want to have a fully automated, hands-free operation that scales as you add more and more transaction volume. Thus, we built a set of tools that we call “Record Import / Export Manager” or RIEM for short that leverages built-in NetSuite platform tools to produce high-performance data exports.

As such, as I explain the use of these tools, readers may want to consider these patterns when deciding to roll-your-own NetSuite file export system.

Considerations in NetSuite Driven File-Based Systems Integration

When performing any type of scalable, enterprise-grade file-based systems integration with NetSuite, there are a number of concerns to address.

Batch Job Concept

The first consideration when defining a file export integration approach is to consider that you are working in a batch operation. Hence, these concerns must be addressed:

1. Scope: what is the scope of business data that must be transmitted? What records are candidates for data export?
2. Duplicate Management: how do you avoid sending the same data again in a subsequent file generation?
3. System under Load: how does the batch routine when under load? What if records change while you are working to export them?

To answer these questions, it’s important to effectively stamp records that are going to be exported with the concept of a job. By stamping business records with job information, subsequent business records that need to be exported will not be in scope for the work at hand; these new records will be part of a different job. Then, an actual automation job can know exactly what data it must act on. This job then represents a batch of work to perform on a clearly defined set of records. Routines can look at the nature of the business record to confirm it has not changed while producing the batch.

Finally, once jobs are constituted, there may be other business logic that needs to fire. For example, in our 3PL order export we use the following pattern:

1. Create Item Fulfillment Records in Picked State: these records are generated to act as an instruction to the 3PL for exactly what to ship.
2. Update Item Fulfillments to Packed State: once the records are batched for export, update the status from Picked to Pack. This gives management time to modify the instruction before it has been sent to the 3PL.

Job Status

Once you have the concept of a job, you then need to make sure that you have a state machine to understand where your job is in the process of extract automation work. Each job then can then be acted on and monitored to determine if the work is happening as expected. Any programming errors can be captured in the job to help diagnose situations. Finally, with status, a dashboard can be built to monitor the health of your jobs.

File Generation and Data Shape

While comma-separated values (CSV) is a common file format used in batch processing, almost anything can work as long as there is an agreement between parties. Other formats such as a flat-file with specific field widths, tab-delimited, XML, HTML, JSON, and others may be defined. NetSuite’s native tools offer CSV, Excel XML, and Word. But getting the data to shape right with saved search sometimes will not be sufficient.

In addition, you may need to name your files distinctly when files are generated. NetSuite’s built-in tools do not offer these capacities.

Secure File Transmission

Once the file is generated, it then needs to be sent to the destination for consumption. NetSuite’s built-in administrator tools are quite basic. While you can indeed have NetSuite send an email with Saved Search, some organizations are concerned about the security of that approach. The options are also limited to what NetSuite offers which usually do not fit the requirements for naming and data shape.

Extending NetSuite’s Platform Tools for Enterprise Batched-Based Export File Generation

All of the concerns above can be addressed by the NetSuite platform. However, the first thing you must do is prepare to use SuiteScript or perhaps Workflows. Sometimes, these tools are beyond the reach of many administrators.  Writing scripts that are robust and work under load demand experienced talent.  Most business managers seek to avoid the time it takes to write and stabilize new scripts.

In our NetSuite Systems Integration practice, we were asked to tackle demanding situations that needed to produce reliable and high-performance results. As such, we went to work to generalize the challenge so that the average NetSuite Administrator would have access to highly scalable job processing.  As mentioned in the opening of this article, we call this tool “Record Import / Export Manager” or RIEM for short.  Here is how it solves the challenges above:

Export Job Definition

We help the administrator create a record that conceptualizes the job concept. The administrator thus defines the following:

1. Saved Search Query for Records to be Exported: a simple saved search can be run that defines if there are records to export.
2. Maximum Records: if there needs to be a limit on the number of records in the batch, this can be specified. This can help to keep the generated file sizes smaller.
3. Folder Reference: where will the file be stored as it is generated?
4. Frequency: the job can be reviewed to look for work on a scheduler or be triggered by other business events.

Click on the image to get a preview.

Export File Generation

For those who have read other articles, I have spoken numerous times about the ability to link multiple NetSuite saved searches together to create a data universe that then can be fed to a template processor that can output anything that is string oriented. Readers may want to refer to a couple of these articles to learn about our Content Renderer Engine (CRE):

1. Join Multiple NetSuite Saved Searches to Build a Data Universe
2. Video: How to Extend Advanced PDFs with Content Renderer Engine
3. Learn how to Batch Generate NetSuite Driven Documents

Thus, the CRE tool can read the job and then grab all the data it needs in the NetSuite database to ultimately be passed to a template that will produce the file output. The name of the file can be dynamically generated based on any type of pattern needed. A simple CSV file is a very basic template; whereas an XML file can be a complex hierarchy.  The point is that we are effectively avoiding the use of generating scripts for complex data shaping.

Click on the image to get a preview.

Secure File Transport and Distribution

Once a file is generated and stored in the NetSuite file cabinet, the next major step is to transport it. NetSuite does not offer any built-in capabilities to send files automatically. However, the platform offers email, https, and sFTP as transport libraries if you are prepared to script.

We built a couple of tools that ease the challenge of setting these up to make the operation configurable and more manageable. Consider these articles:

1. NetSuite sFTP Transfers Made Easy
2. Link NetSuite to FTP via Open Source Middleware

The major point here is to have a controller system in place that demonstrates that files are being transported as expected. The typical pattern is to create a drop-off, pickup, and archive directory structure that moves files once successful delivery has been ensured.

Getting Access to Record Import / Export Manager

While this article focused on the export side of systems integration, there is indeed the respective data import consideration. Readers may be interested in how our framework solves for data imports. See this article, Fully Automate Complex NetSuite Data Imports.

NetSuite provides the platform to enhance and adapt to solve business problems. Our firm’s philosophy is to add value by working with competent actors that listen to concerns and leverage previous efforts to get to a solution. Thus, all of our tools are license-free offers designed to be solution accelerators that continue to become more robust as we tackle more-and-more client challenges. The key is that our clients are not stuck with any recurring charges to get to their solutions — we can educate their NetSuite administrators to solve matters on their own with our tools or we do it for them — it’s about the right relationship according to our clients’ wishes.

If you found this article meaningful, feel free to receive notifications of new articles as I post them. If you are ready to tackle your file export challenges, let’s have a conversation.

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