Return 20 if formula value is higher than 20 else return formula value
Hello,
I have the below formula. If the result is greater than 20 I want it to return 20 else return the value from the formula.
CASE WHEN {memberitem.inventorylocation} = ‘Warehouse 1’ AND {type} = ‘Kit/Package’ THEN {memberitem.locationquantityavailable} WHEN {inventorylocation} = ‘Warehouse 1’ THEN {locationquantityavailable} END
Appreciate the help.
Hi,
I think you should be able to do this:
LEAST(CASE WHEN {memberitem.inventorylocation} = ‘Warehouse 1’ AND {type} = ‘Kit/Package’ THEN {memberitem.locationquantityavailable} WHEN {inventorylocation} = ‘Warehouse 1’ THEN {locationquantityavailable} END, 20)
Thanks,
Chris
Hello Chris,
Thanks heaps for the quick reply. This just returns 0?
Without the least returns:
SKU1 – 1
SKU2 – 14
SKU3 – 4
SKU4 – 63
That’s strange. It’s working fine in the test I’ve just done.
Other than that Edo’s suggestion is probably the way to go.
Hi
I have not tried it yet in NetSuite but Oracle allows nested CASE operators so you could try :
CASE
WHEN
CASE
WHEN {memberitem.inventorylocation} = ‘Warehouse 1’ AND {type} = ‘Kit/Package’ THEN {memberitem.locationquantityavailable} WHEN {inventorylocation} = ‘Warehouse 1’ THEN {locationquantityavailable}
END < 20 THEN CASE
WHEN {memberitem.inventorylocation} = ‘Warehouse 1’ AND {type} = ‘Kit/Package’ THEN {memberitem.locationquantityavailable} WHEN {inventorylocation} = ‘Warehouse 1’ THEN {locationquantityavailable}
END
ELSE 20
END
Cheers
Edo Aroni
