NetSuite Saved Search Running Totals Sublist Challenge

Background

A common request is to look at transactional information with a cumulative total in mind. A good example is the inventory on hand balance. In NetSuite, every transaction that affects the inventory quantity serves to either increment or decrement the total on hand for that item.  Accordingly, it may be valuable to determine what the inventory balance was at any particular point in time.

NetSuite’s Saved Search offers good capacities to generate detail that can be used to attached to specific records and act as a child list. This is NetSuite’s sublist feature. To continue our example, we can create a saved search that accounts for all the transactions that affect the inventory item quantity on hand. For each of these transactions, we can see the positive and negative affects of the transaction. Wouldn’t it be great to have a column that is the cumulative amount, or sometimes referred to as the running balance or running total, of each transaction?

Here is a sample of the transaction output we are seeking:

NetSuite excels as making it easy to get transaction data out of the environment for the first 4 (Date, Type, Item# and Quantity) columns.   It is challenging though to get the “On Hand” quantity which represents a running total.  You can deem the Saved Search as a Sublist which allows you to attach the results to a master entity (specific record with in a form view).   In the above example, we can hook the Saved Search to the Item Form which will then show only records that are of type “A350”. This is exactly what one of our clients wanted: an ability to see the quantity on hand at any time when they performed an item lookup.

Saved Search Summary Lookup Column Reference Limitation

When working with Saved Searches, the general approach is to use the Summarize feature to group and sum information. For those that have SQL query language background, NetSuite is effectively performing a “Group By” type SQL Select statement behind the scenes. In our example, however, we don’t really need to use an aggregate “Group By” function to get our result. That would work well if we wanted to have a single answer up to a particular date in time. Instead, we want to see the detail transactions yet we want to have one column contain summarized information.

The power of the NetSuite Saved Search tool typically lies with the formula functions. It allows you to get closer to NetSuite’s underyling Oracle database by performing PL/SQL function calls.  Oracle offers a nice function to produce this running total:

sum({expression1})
OVER(ORDER BY {expression1}
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "Running Total"

Unfortunately, this special syntax is not supported within NetSuite.   And there is another key weakness: the tool does not allow you to name a formula column so you can reference it in another formula column. This is the “Running Total” portion of the SQL syntax above. The ability to create a calculated and named column is easily supported in SQL yet is missing in the Saved Search function.  Even though NetSuite supports the capacity to define calculated fields as custom fields, you can’t get close enough to the underlying Oracle database to produce the definition you need.

Addendum — Solved nearly 3 year later!

See updated article on how to solve this with Saved Search.

Basic Running Total Algorithm

The basic running total algorithm logic is as follows assuming we are seeking transaction output as illustrated above:

1. Get all the data that fits the criteria you need.
2. Define a integer variable, such as “onhand”
3. Organize the transactions list sorted by date
4. Start at the top of the list and iterate through each row
5. For each row, increment the value of “onhand” variable by the value of the quantity column
6. Output the “onhand” value as a separate column

Key NetSuite Saved Search Running Total Wish Lists Features

The algorithm above is very common when working with business databases. As such, here is an item to add to my NetSuite Saved Search wish lists:

1. The ability to reference a forumla field by a name
2. The ability to connect SuiteScript to a formula.  Combine the power of SuiteScript with Oracle PLSQL.
3. The ability to define when the SuiteScript would execute: before query start, before get row, after get row, after query end.

While I have many other Saved Search wishes, these capacities would allow for a great deal of flexibility and solve our running total problem.

How to Solve the Running Total Challenge

Finally, in order to solve the running total inventory on hand challenge, we alas must use SuiteScript and couple this to the Sublist API to output the results. Below is basic SuiteScript pattern take a manipulated search result  and place the values in a sublist to output the result. While we have solved the problem, it demands more of the user and is often outside the reach of a typical NetSuite Administrator.

// assumes you setup the search and results which
// include any preprocessing to add running totals    
if ( searchresults.length > 0 )
    {
        // first, add the columns to the sublist based on the columns in the results
        var result = searchresults[0];
        var column_list = result.getAllColumns();
        var col_len = column_list.length;
        for (i=0; i<col_len; i++)
        {
            var col = column_list[i];
            var col_name = col.getName();
            var col_label= col.getLabel();
            var col_form = col.getFormula();
            var col_func = col.getFunction();
            nlapiLogExecution('DEBUG', 'column name, label, formula, function', col_name + '|'+ col_label + '|'+ col_form + '|'+ col_func);

            // if there is no label, use the field name
            if (!col_label)
            {
                col_label = col_name;
            }
            var fld_name = 'custpage_fld_' + i;

        }


        // now, show the results; loop through each result
        var rlen= searchresults.length;

        for (ctr=0;ctr<rlen;ctr++)
        {
            var result = searchresults[ctr];

            // for each result, loop the columns
            for (i=0; i<col_len; i++)
            {
                var col = column_list[i];
                var col_name = col.getName();
                var col_label= col.getLabel();
                // if there is no label, use the field name
                if (!col_label)
                {
                    col_label = col_name;
                }
                var fld_name = 'custpage_fld_' + i;
                var value = result.getText(col);
                if (!value)
                {
                    value = "";
                }
                if (value.length == 0)
                {
                    value = result.getValue(col);
                }

                var lin = parseInt(ctr) + parseInt(1);
                if (value)
                {
                    YOURLIST.setLineItemValue(fld_name, lin, value);
                }
            }
        }
    }