NetSuite Map/Reduce Scripts in Retail Practices

One of Reason that I like NetSuite Platform is the “Flexibility” for Transforming your Real-Life Business Situation to Standard ERP workflow.

In many Food and Grocery retail Industry, there are a huge volume of Point of Sales Transactions in daily that required to integrate with Backend Financial and Logistic System. We always prefer to get the “Summary of Sales Data” in the main Sales Flow instead of covering all raw POS transaction(s).

Let’s have a look for following example about the business case of Donut Chain-Stores:

We are going to build the following Logic(s) in Suite-Cloud Platform:

1. Custom Record: To Store POS raw data such as invoice#, QTY, Amount.
2. Map/Reduce Script: To group and consolidate POS raw data to sales order by specific Time (i.e. Day), Sold Items and Location (i.e. Shop)

1.Custom Record

Actually, this is very straight forward to use Suite-Builder to build a Custom Record. The Most Tricky Thing is Custom Field “IsSycned”, which is check Box Type. The propose is to avoid the duplication of Synchronisation.

Screen Capture for Custom Record Type — 1

Screen Capture for Custom Record Type — 2

List of Custom Record — Raw POS Transaction

2. Map/Reduce Script

Map/Reduce scripts operate in 5 stages, these stages are:

Stage 1: getInputData

This stage is called once at the beginning of your script. In this stage we are defining and/or collecting the source data that we want this script to process. We build a saved search to return the record, which is not synced and created today.

Stage 1: getInputData

Stage 2: Map Phase

By returning a caseSearch. object in the getInputData() stage, the script executes the search and calls map() function for each result. If there are 10 results (i.e. 10 POS Transactions), the map function phase will be called for 10 Times. For Each Time, the map function will prepare a Pair of Key and Value Properties for next Phase.

In our example, the Key is a string, which is comprised of Location internal ID and Items internal ID.(i.e. 3–20, is equivalent to “Central Shopping Mall”- ” D-002 Glazed Raspberry Donut).

The Value is whole chunk of json object that contain all data of each POS data. (All search column in phase 1)

3 Remarks for Map phase

1. When data entry the Map Phase, it is a JSON String of data. We need a method JSON.parse(context.value)to parse the JSON string and construct the Javascript object.

2. If only simple set of data (i.e. Quantity of Donuts Sold) is required for Reduce Phase, we can set the value as value: itemsQTY

3. Self-defined Function Updated_Custom_Table()is called in this phase to update the Custom Field “IsSycned” from False to True in order to avoid the duplication in the next Schedule Job.

Stage 2: Map Phase

Self-defined Function: Updated_Custom_Table()

Stage 3: Shuffle Phase

This stage occurs automatically and we don’t need to write any code to execute.In this shuffle stage, NetSuite will organises or shuffles all of the data so that all values with the same key are combined.

The output of the shuffle stage is a series of unique keys whose values are an array of all the data from the map() function with a matching key. In our example, same items in the same shop will be grouped together for next phase.

Stage 4: Reduce Phase

All Ingredients are prepared. Reduce Phase is the “Main Step” of you script that exactly create/update the Sales Order. Each Time reduce() is called, the system will provide pair of Key and Value(s) properties on the context objects. In our example, Key is Location internal ID + Items internal ID, the values contains an Array of stringify objects that contain all the POS Transaction.

For example: when call the key of 3–20 (i.e. “Central Shopping Mall”- ” D-002 Glazed Raspberry Donut), the following Array are returned:

And then, we will use 2 Looping to Sum Up all Quantity and Amount Together in each cycle of Key.

Finally, 3 set of self defined Functions is executed to create and update the Sales Order(s). The major propose is to create 1 Sales Order for each shop (location) per day.

Stage 4: Reduce Phase

Self-defined Function: Check_SO(locationId): Check if there is any existing SO in such location

Self-defined Function: Create Sales Order Function

Stage 5: Summarize

This is the Final stage of the map/reduce script. This stages let you to do logging and return Error.

Conclusion (Why we need Map Reduce Script?)

You may be curios why we need Map Reduce Script to execute this simple example. The answer is —Large Amounts of data. Imaging that we are not handling 10 record, but Ten Thousands of records. It is best idea for situations where the data can be divided into small, independent parts to execute.

Also, we can also set the concurrency limit (Number of jobs submitted for the map and reduce stages) in the deployment. That mean these phase can be run parallel to handle Large volume of data in short period of time.

Configuration for Script Deployment for Map Reduce Script

What’s next?

After understanding the basic Logic of Map Reduce, we may enhance the M/R Script like the following idea:

1. Add Transform Function to convert the Sales Order to Sales Delivery/Invoicing to update the Inventory Status.
2. Create Linking (Hyper Link) between POS Transaction and Sales Order, etc

• ** Design and Code by Wilson Cheng.

You can download the code in my Github