CODEWITHSUNDEEP
netsuite
jrichmeier
jrichmeier

Reputation: 1

NetSuite Saved Search Case Statement

I'm running into an an invalid expression when running this formula in a saved search for NetSuite.

The code should return a future date based on whatever the {entity} name is.

(In this case {entity} is the customer name on a sales order.)

The field type is: Formula(Date)

CASE WHEN {entity} = "Google" THEN {trandate} + 3 
WHEN {entity} = "Stack Overflow" THEN {trandate} + 8 
WHEN {entity} = "O'Malley's" THEN {trandate} + 2 
ELSE {trandate} + 4 
END

Am I missing something?

Upvotes: 0

Views: 5211

Answers (1)

Krypton
Krypton

Reputation: 5256

You need to use a single quote for SQL string data, rather than a double quote as you have. Then, to escape the single quotes within the string O'Malley's you need to double up the single quotes. A double single quote, if you will.

So your formula will become:

CASE WHEN {entity} = 'Google' THEN {trandate} + 3 
WHEN {entity} = 'Stack Overflow' THEN {trandate} + 8 
WHEN {entity} = 'O''Malley''s' THEN {trandate} + 2 
ELSE {trandate} + 4 
END

Or, even better for this 'case' (forgive me), you could use a simple case expression, rather than a searched case expression:

CASE {entity} WHEN 'Google' THEN {trandate} + 3 
WHEN 'Stack Overflow' THEN {trandate} + 8 
WHEN 'O''Malley''s' THEN {trandate} + 2 
ELSE {trandate} + 4 
END

Upvotes: 1

Related Questions