Is there a way to get on a saved search the minimu...
# suitescriptg
G
03/16/2023, 6:55 PMIs there a way to get on a saved search the minimum value of any of the on-hand, available, backordered divide by member quantity? I've got a formula however not getting a result... formula is CASE WHEN (MIN(NVL({memberitem.locationquantityonhand}/{memberquantity},0),NVL({memberitem.locationbackordered}/{memberquantity},0),NVL({memberitem.locationquantityavailable}/{memberquantity},0)))
b
battk
03/16/2023, 7:27 PMyou are probably complicating the formula more than you need
battk
03/16/2023, 7:27 PMyou only need to divide the smallest of all 3 quantities by the member quantity
battk
03/16/2023, 7:28 PMyou dont need to do the division 3 times
battk
03/16/2023, 7:38 PMotherwise, you probably want to use LEAST
g
G
03/16/2023, 7:50 PMthanks @battk I had tried least as well however isn't working... LEAST(NVL({memberitem.locationquantityonhand}/{memberquantity},0), NVL({memberitem.locationbackordered}/{memberquantity},0), NVL({memberitem.locationquantityavailable}/{memberquantity},0))
even if I'm trying to get the minimum without the division it isn't populating hence tried min..
LEAST(NVL({memberitem.locationquantityonhand}, 9999999), NVL({memberitem.locationbackordered}, 9999999), NVL({memberitem.locationquantityavailable}, 9999999))
G
03/16/2023, 7:51 PMany help to resolve would be greatly appreciated
b
battk
03/16/2023, 7:53 PMstart smaller
battk
03/16/2023, 7:53 PMget the least of 2 hardcoded numbers
battk
03/16/2023, 7:54 PMthen upgrade to the least of a hardcoded number and locationquantityonhand
battk
03/16/2023, 7:54 PMthen the least of locationquantityonhand and locationbackordered
battk
03/16/2023, 7:54 PMand then all 3
battk
03/16/2023, 7:54 PMthen add the nvls
battk
03/16/2023, 7:55 PMfinally the division
g
G
03/16/2023, 8:23 PMso I've managed LEAST ({memberitem.locationquantityavailable}, {memberitem.locationquantityonhand}, {memberitem.locationquantitybackordered})/{memberquantity} however how can I add nvl and if 0 then populate 0... so for example backordered for this particular item is blank but I would want it to populate 0 in this case...
b
battk
03/16/2023, 8:30 PMyour original solution was to add nvls to every single quantity
battk
03/16/2023, 8:30 PMsame problem as with the division
battk
03/16/2023, 8:30 PMif any of the quantities is actually null, then thats the least
battk
03/16/2023, 8:31 PMso use nvl once at the end
2 Views