CODEWITHSUNDEEP
searchformulanetsuite
w3bguy
w3bguy

Reputation: 2250

Using a case when result for comparison in another case when in NetSuite

I am trying to run an equation on a saved search in NetSuite that uses a formula, and a field.

I can run this and I get the correct value:

CASE WHEN {systemnotes.context}='UI' AND {systemnotes.field}='Date Closed' THEN {systemnotes.newvalue} END

And, this returns the correct value:

{startdate}

But, I can not figure out how to say that if the first case does not return a null value, and the second one is not null then give me the difference between the two. I was trying something like this (in many different variations:

CASE WHEN [(CASE WHEN {systemnotes.context}='UI' AND {systemnotes.field}='Date Closed' THEN {systemnotes.newvalue} END) IS NOT NULL AND {startdate} IS NOT NULL] THEN
{CASE WHEN {systemnotes.context}='UI' AND {systemnotes.field}='Date Closed' THEN {systemnotes.newvalue} END)-{startdate}

END

Every time I run it I get invalid expression. I've Googled around adn con not seem to find anything that discusses this type of operation, though.

Upvotes: 0

Views: 3434

Answers (1)

Mike Robbins
Mike Robbins

Reputation: 3297

The formula below worked for me. I couldn't tell what recordtype you were running against so I just added a custom date field to the case record called Test Date Field. Just replace the field name with yours and it should work.

The key is that {systemnotes.newvalue} returns text so you need to wrap that result in TO_DATE() to convert it. Then you can subtract {startdate} from it.

Your column type should be Formula(numeric).

case when 
(CASE WHEN {systemnotes.context}='UI' AND {systemnotes.field}='Test Date Field' THEN {systemnotes.newvalue} END is not null and {startdate} is not null)
then (TO_DATE(CASE WHEN {systemnotes.context}='UI' AND {systemnotes.field}='Test Date Field' THEN {systemnotes.newvalue} END) - {startdate}) END

Upvotes: 1

Related Questions