This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

In a saved search, what formula works to achieve “sum the on hand quantities of each distinct location in the results”? For example,

INV01 - Item A - Location A - Loc Qty On Hand is 100
INV02 - Item A - Location A - Loc Qty On Hand is 100
INV03 - Item A - Location B - Loc Qty On Hand is 75

When I group the results, I was hoping to get 175 in the Loc Qty On Hand but struggling to do so because I’m not sure how to tell NS to only include the value per unique location once.

You can try to create a hash of item-location (ie {item} || ‘-‘ || {locaition}). Then use DISTINCT on that hash to only pull distinct values

I use this on an item search.  I get a line for each item with a column for each location.  You need to Group the item and apply a summary to the formula (I use MAX).
 CASE WHEN {inventorylocation} = 'Location A' THEN {locationquantityavailable} ELSE 0 END

That's for available.  On hand would be {locationquantityonhand}