Netsuite Saved Search Formula Compare Bin on hand count to total on hand

Question

I need to make a simple saved search that will highlight and move to the top of the list any products whose "Bin on hand available" sums DO NOT equal the "total quantity on hand" for that item.

For example, note that lines 2 and 3 consist of the same product, they are displayed on different lines as there are two different bin numbers in our inventory for that single item. So the saved search would then look at the "Bin on hand available" for both lines, and add them which is 19 + 0 = 19. Then if that sum is NOT equal to the "total quantity on hand", the rows that item is on would be highlighted yellow. Then all of the rows that are highlighted yellow would be sorted to the top of the list.

I think I would need to make a formula to do that... but I am not sure how to even start. Thanks in advance for your help, I am very new to NetSuite and am trying to learn a lot of new features about it!

Answer 1

For that you would need a group/summary search.

Criteria standard-> type is inventory item

Criteria summary-> Summary Type: SUM Field: formula(numeric) Description is equal to 0 Formula: {locationquantityonhand} - {binonhandavail}

Results

1) Item specific header fields like Name, Display name, etc. must have summary type Group

2) Inventory Location must have summary type Group

3) Optionally include two fields in the search results i.e. Bin On Hand Available and Location On Hand with summary type as Sum i.e. the two fields that you want to compare

P.S. Adding extra fields with incorrect summary type may lead to unwanted output.

Answer 2

It sounds like you may have one bin per location. If that is the case then criteria like those below may help:

Inactive    is false     
Type    is Inventory Item    
Formula (Numeric)   is 1    case when {binnumber.location} = {inventorylocation} then 1 else 0 end
Formula (Numeric)   is not 0    nvl({binonhandcount},0) - nvl({locationquantityonhand},0)
// use this instead of difference if you want to get all rows where you have some inventory either in bin or location 
//Formula (Numeric) is greater than 0   ABS(nvl({binonhandcount},0))+ ABS(nvl({locationquantityonhand},0))

and then results like:

Name                         
Preferred Bin                        
Bin Number                       
Bin On Hand Count                        
Inventory Location                       
Location On Hand                         
Formula (Numeric)           {binonhandcount} - {locationquantityonhand}

And sort by the Formula Numeric column. Note if you want to sort by a formula numeric column it has to be the the first Formula Numeric column in your results.

If you have multiple bins per location then your search can't easily list bins but it can list locations where there is a discrepancy. Here you use summary functions. Fun fact when a formula field has a summary function in it the formula works on the summary value:

Name                Group                    
Preferred Bin                        
Bin Number                       
Bin On Hand Count   Sum                 
Inventory Location  Group                    
Location On Hand    Group                    
Formula (Numeric)   Sum     SUM({binonhandcount}) - {locationquantityonhand}