Search code examples
mysqlsqlnetsuitesuitescriptsaved-searches

Saved Search for items that are not in a bin location

I am trying to put a NetSuite saved search together that shows only items that are not in a bin location. For this I am making an Item Search type...

I added Criteria:

Formula (Numeric) CASE WHEN {inventorylocation} = {binonhand.location} THEN 1 ELSE 0 END is 1

&

Location On Hand is not 0

&

Bin On Hand:On Hand is not 0

The Formula (Numeric) I used in my Results was:

NVL({locationquantityonhand},0)-(SUM/* comment */(NVL({binonhand.quantityonhand},0)) OVER(PARTITION BY {name}))

This formula does what I want it to do, but it still shows the 0 value lines, and you can't use the sum function like this in the criteria. Does anyone have any tips/suggestions that will allow me to filter out the 0 return lines?

Solution

  • Use NetSuites aggregation rather than analytic Sum.

    Summary Criteria: Sum of Bin on Hand: On Hand > 0

    Results: Minimum of Formula Numeric: Max({locationquantityonhand}) - Sum({binonhand.quantityonhand})

    EDIT

    To see location on hand not in bin on hand, replace the above summary Criteria with Example Summary Criteria