While creating saved search, sometimes it may not be feasible to do GROUP or SUMMARY functions but we can still create summary values and show them as columns.
Calculating Inventory on Hand
Below is an example,
- NetSuite environment has multiple bins feature enabled.
- Bins can be associated to multiple items (and vice –versa).
The management would like to know if the bin has enough OnHandQty and if there is, they would like to know the associated item(s) and also the binQty linked to the item(s) but also cumulative BinOnHand qty across all linked items.
Let us create ITEM based saved search,
In the results tab, choose
- BinNumber,
- Name (Item Name)
- Bin OnHand Available
Set the sort on BinNumber.
With above details, the saved search will show results sorted on Bin. Let us say, if Bin named Pencils is containing Items such as YellowPencils, BlackPencils. The above saved search will show on row 1 the qty for YellowPencils and on row2 for BlackPencils. If row1 has 100 and then row2 has 200, then it is total of 300. If we summary TYPE and remove the Name from above search, it shows 300 but we will lose visibility of which are the linked items to that bin.
To achieve the sum of binOnHand qty , we can leverage the Oracle function “OVER PARTITION BY”
The syntax is something below,
(sum/* comment */({binonhandavail}) OVER(PARTITION BY {binnumber} ORDER BY {binnumber} ))
Create a formula text and paste above value.
SUM is the type of summary function and binonhandavail and binnumber are NetSuite column names.
The above formula will simply produce the sum of binOnHandAvailable with BinNumber as the partition.
Since we ordered by BinNumber it will order the results by binNumber.
Below is how saved search results look like,
41-A-1-1 binNumber is linked to two items, and has total binOnHand (across all linked items) 3000. 41-A-1-2 binnumber has 43612 total but it is scattered between 2 items. 41-A-2-1 binnumber is zero across the 2 items.
With this, we can show the sum of binQty based on Bin and linked items and we didn’t use any summary type functions.
This saved search will be useful for management to see which item in the bin has more or less or zero qty and they can plan or move items as necessary.
Lastly, there is another summary function that can be used to show how many items are there in a bin. This will be helpful if management wants to know which bins have more items or which less.
Create formaul text column and paste below value.
(count/* comment */({binnumber}) OVER(PARTITION BY {binnumber} ORDER BY {binnumber} ))
The result will be something like below (last column in saved search shows the COUNT)
Hope the above article provides information as to how to use summary functions. You can try other summary functions as well.
Feel free to reach MIBAR staff should have any questions or need any further inputs.
Thanks for reading.
