[mschuette]

Trying to and need help writing a formula or finding another way to return the minimum value, that is not 0 or empty, of multiple custom fields.

Basically, we have set up a Vendor Quantity Pricing custom form, that has multiple prices for buying in various quantities. Sometimes Price B may be lowest, sometimes Price C. Many are often empty. I'd like to come up with a saved search that returns one value for each record, that searches these multiple prices and finds the lowest that is not 0 or empty.

Any help appreciated!
This is a cached copy. Click here to see the original post.

[mschuette]

I just discovered to use LEAST instead of MIN for a formula in NetSuite, which is why my attempts were failing.

That led to:

Least ({custrecord307},{custrecord13},{custrecord15},{cu strecord17},{custrecord19},{custrecord21},{custrec ord23})

which worked but returned null values, so I came up with this:

Least ((case when {custrecord307} is null or {custrecord307} = 0 then 9999999 else {custrecord307} end),

(case when {custrecord13} is null or {custrecord13} = 0 then 9999999 else {custrecord13} end),

(case when {custrecord15} is null or {custrecord15} = 0 then 9999999 else {custrecord15} end),

(case when {custrecord17} is null or {custrecord17} = 0 then 9999999 else {custrecord17} end),

(case when {custrecord19} is null or {custrecord19} = 0 then 9999999 else {custrecord19} end),

(case when {custrecord21} is null or {custrecord21} = 0 then 9999999 else {custrecord21} end),

(case when {custrecord23} is null or {custrecord23} = 0 then 9999999 else {custrecord23} end))

That works! But if someone knows how to do it more efficiently let me know please!

[michoel]

Slight improvement

Code:
LEAST(
NVL({custrecord307}, 9999999),
NVL({custrecord13}, 9999999),
NVL({custrecord15}, 9999999),
NVL({custrecord17}, 9999999),
NVL({custrecord19}, 9999999),
NVL({custrecord21}, 9999999),
NVL({custrecord23}, 9999999)
)

[mschuette]

Thanks michoel. But I think NVL will just remove and replace null values, not a value of zero, right? For us, we do have a few price fields that have zero in them, so technically are not null, and I wanted to not include them in these search results. But I appreciate the response and for a lot of people I'm sure that would work better.

[khultquist]

This should work and is a little bit cleaner

Code:
Least (
(case when NVL({custrecord307},0) = 0 then 9999999 else {custrecord307} end),
(case when NVL({custrecord13},0) = 0 then 9999999 else {custrecord13} end),
(case when NVL({custrecord15},0) = 0 then 9999999 else {custrecord15} end),
(case when NVL({custrecord17},0) = 0 then 9999999 else {custrecord17} end),
(case when NVL({custrecord19},0) = 0 then 9999999 else {custrecord19} end),
(case when NVL({custrecord21},0) = 0 then 9999999 else {custrecord21} end),
(case when NVL({custrecord23},0) = 0 then 9999999 else {custrecord23} end)
)

[mschuette]

Good call khultquist, thanks!

[Author]

Posts