This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Hi, is there a way to get COUNT = 1 if the `systemnotes.newvalue` field has 'Closed Won' regardless of the number of time it appears, in my saved search formula?

Not sure your use case, but DISTINCT() can work

That will give you one instance of the value for whatever field you put in the (). Can probably use that in conjunction with whatever else you're looking to achieve (criteria, results, etc.)

`CASE WHEN {systemnotes.newvalue} = ('Closed Won') THEN 1 ELSE 0 END` this is my function in the results and I am summing them. What I want to achieve: 1 if the systemnotes.newvalue has 'Closed Won' no matter how many times in that opp, else 0. However, right now, this is summing up to 5 if an opp has Closed Won 5 times in the system notes.

Why not  Formula (Text) and just return Closed Won, then group that results column?

I want to know how many opportunites had Closed Won an grouping it wont give me the number of opps that had closed won at some point

I want to check if my "systemnotes.newvalue array" has 'Closed Won', if it does assign 1 for the entire opp, else 0. I dont know if thats possible.

Can your search use another field to get that number, like do a Count on Document Number?

Just made a quick one, which when grouped will only count each Document Number once, even though a few of them appeared multiple times as there was more than one instance of 'Closed Won' in the system notes. Not sure if this helps, but sometimes I find you have to approach things from a different angle when working with saved searches.

That seemed to work fine for the Closed Won ones, but I also had to create a similar report for the ones containing prosposal, quote and demo, and it didnt work

I had criteria set to only show Closed Won, for my example. If you're making separate saved searches for each status, you'd have to update the criteria formula and the results formula to be specific to which status you're looking at.

Yup, I did that I used the IN operator to set my criteria to select all of those, but for some reason it was only picking quote. Quite possibly the latest of the updates.

Do you know why NS does not have Formula(currency) in the criteria section? I tried to emulate your criteria for currency but coudln't find it.

I'm not sure, but you should be able to use numeric formula if you're looking to set criteria based on an amount/rate field. Or just use the field itself

I found a way to do it without using Formulas at all. So basically, I set my Criteria to be 'Closed Won' And used the summary criteria to pull the maximum date of new value = 'Closed Won'. It did the trick for me.

Many paths to the top of the mountain, glad you found a way that works!

<@UAPHBR6TS> if I had to use DISTINCT() where would I use it, in the field or the value?