This topic contains 5 replies, has 0 voices, and was last updated by mschuette 7 years, 5 months ago.
-
AuthorPosts
-
May 23, 2017 at 8:25 am #4886
mschuetteTrying 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. -
May 24, 2017 at 6:37 am #4887
mschuetteI 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!
-
May 24, 2017 at 7:19 pm #4888
michoelSlight improvement
Code:
LEAST(
NVL({custrecord307}, 9999999),
NVL({custrecord13}, 9999999),
NVL({custrecord15}, 9999999),
NVL({custrecord17}, 9999999),
NVL({custrecord19}, 9999999),
NVL({custrecord21}, 9999999),
NVL({custrecord23}, 9999999)
) -
May 25, 2017 at 6:32 am #4889
mschuetteThanks 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.
-
May 25, 2017 at 7:41 am #4890
khultquistThis 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)
) -
May 25, 2017 at 8:37 am #4891
mschuetteGood call khultquist, thanks!
-
AuthorPosts
You must be logged in to reply to this topic.