Discussions
Join us for complimentary one-day events around the world and step into a future fueled by AI and limitless potential. Explore new breakthroughs, sharpen your skills, and connect with experts who are shaping what’s next. Experience bold keynotes, interactive learning, and connections that span the global NetSuite community. Discover what's next at SuiteConnect Tour 2026.
Item Search with complex formula based on transactions.
I have a saved ITEM search with the following formula:
Formula(Numeric):
(CASE WHEN SUM(CASE WHEN ({transaction.type} = 'Order Reservation') THEN (CASE WHEN ({transaction.type} = 'Order Reservation' AND {transaction.status} = 'Approved' AND {transaction.saleschannel} = 'DTC') THEN ({transaction.quantitycommitted}-{transaction.quantitydemandallocated}) ELSE 0 END)ELSE 0 END)+ MAX(NVL({locationquantityavailable},0)) +MAX(0) <0 THEN 0 ELSE (SUM(CASE WHEN ({transaction.type} = 'Order Reservation') THEN (CASE WHEN ({transaction.type} = 'Order Reservation' AND {transaction.status} = 'Approved' AND {transaction.saleschannel} = 'DTC') THEN ({transaction.quantitycommitted}-{transaction.quantitydemandallocated}) ELSE 0 END)ELSE 0 END)+ MAX(NVL({locationquantityavailable},0)) +MAX(0)) END)-{custitemdtcavailqty}
Summary type = Maximum
This returns the correct values, so the formula is OK. However, I really only want to see values if they are not equal to zero. When I put this formula in as criteria instead of a result, netsuite gives an error: "