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.

Rookie Asked on September 27, 2021 in Saved Searches.
Add Comment
2 Answer(s)

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

Intermediate Answered on September 27, 2021.

Hello Chris,

Thanks heaps for the quick reply. This just returns 0?

Without the least returns:
SKU1 – 1

SKU2 – 14

SKU3 – 4

SKU4 – 63

on September 27, 2021.

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.

on September 28, 2021.
Add Comment

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

Beginner Answered on September 27, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor