Sometimes while creating saved searches in NetSuite, it may not be feasible to do group or summary functions. Still, it is possible to create summary values and show them as columns.

Looking at an example with calculating inventory on hand

Below is an example where:

• The NetSuite environment has multiple bins feature enabled.
• Bins can be associated to multiple items and vice versa.

Management wants to know if the bin has enough OnHandQty, and if there is, they would like to know the associated item(s) as well as the BinQty linked to the item(s) and the cumulative BinOnHand quantity across all linked items.

Creating item-based saved searches in NetSuite

In the results tab, choose:

1. BinNumber,
2. Name (Item Name)
3. Bin OnHand Available

Then, set the sort on BinNumber.

With the above details, the saved search will show results sorted on Bin. If Bin named Pencils is containing items such as YellowPencils, BlackPencils. The above saved search will show on Row 1 the quantity for YellowPencils and on Row 2 for BlackPencils. If Row 1 has 100 and Row 2 has 200, then it is a total of 300. If you use Summary Type and remove the name from above search, it shows 300, but you will lose visibility of which items are linked to that bin.

To achieve the sum of BinOnHand quantity, you can leverage the Oracle function “OVER PARTITION BY.” The syntax is something like:

(SUM/* COMMENT */({BinOnHandAvailable}) OVER(PARTITION BY {BinNumber} ORDER BY {BinNumber} ))

Create a formula text column and paste the above value. Sum is the type of summary function, and BinOnHandAvailable and BinNumber are NetSuite column names. The above formula will simply produce the sum of BinOnHandAvailable with BinNumber as the partition. Since the results are sorted by BinNumber, they will be ordered by BinNumber.

Below is how the results of saved searches in NetSuite will look:

How to utilize saved search results

Through this, you can show the sum of BinQty based on Bin and linked items without using any summary type functions. This saved search is useful for management to see which item in the bin has more, less, or no quantity, so they can plan or move items as necessary.

Lastly, there is another summary function that you can use to show how many items there are in a bin. This is particularly helpful if management wants to know which bins have more items and which have less.

Create a formula text column and paste the value below:

(COUNT/* COMMENT */({BinNumber}) OVER(PARTITION BY {BinNumber} ORDER BY {BinNumber} ))

The result will be something like below – the last column in saved search shows the Count:

Understanding how to use summary functions can help provide management with the information they need to make effective business decisions.

NetSuite support provided by Citrin Cooperman

Citrin Cooperman’s Digital Services Practice maximizes benefits by utilizing saved searches in NetSuite. For more information on saved searches and other summary functions that may improve your business processes and decision making, reach out to your Citrin Cooperman advisor or sales@citrincooperman.com.