[DanDeHaven]

I've been trying every different conceivable construct for a CASE statement in a Saved Search, yet every time I include more than one WHEN argument I get the dreaded "ERROR: Invalid Expression". The statement is looking to see what the "Survey Type" is and based on that value it should return the value from the different related fields. All of the potential return expressions are different fields yet they can only contain the value 1 to 7.

I created these as Formula(Numeric) saved search fields.

Here was my first attempt.

CASE {custrecord_his_svcssurv_survey_type}

WHEN 'PS TS Project Kickoff Survey' THEN TO_NUMBER({custrecord_his_svcs_surv_projectoutline d})

WHEN ‘PS TS 60 Percent Project Completion Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_projectprogres s})

WHEN ‘PS TS 19 Days Project GoLive Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_satisfywithpro d})

ELSE 0

END

Also tried it this way

CASE

WHEN {custrecord_his_svcssurv_survey_type} = 'PS TS Project Kickoff Survey' THEN TO_NUMBER({custrecord_his_svcs_surv_projectoutline d})

WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 60 Percent Project Completion Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_projectprogres s})

WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 19 Days Project GoLive Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_satisfywithpro d})

ELSE 0

END

Also tried to just remove the TO_NUMBER() part but that doesn't work either.

Then tried to super simplify it with this:

CASE {custrecord_his_svcssurv_survey_type}

WHEN 'PS TS Project Kickoff Survey' THEN 1

WHEN ‘PS TS 60 Percent Project Completion Survey’ THEN 2

WHEN ‘PS TS 19 Days Project GoLive Survey’ THEN 3

ELSE 0

END

Nor this (which should work):

CASE

WHEN {custrecord_his_svcssurv_survey_type} = 'PS TS Project Kickoff Survey' THEN 1

WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 60 Percent Project Completion Survey’ THEN 2

WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 19 Days Project GoLive Survey' THEN 3

ELSE 0

END

I Also included a WHEN to catch potential NULL values, which there are none, but that didn't help either.

CASE

WHEN {custrecord_his_svcssurv_survey_type} IS NULL THEN 0

WHEN {custrecord_his_svcssurv_survey_type} = 'PS TS Project Kickoff Survey' THEN TO_NUMBER({custrecord_his_svcs_surv_projectoutline d})

WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 60 Percent Project Completion Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_projectprogres s})

WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 19 Days Project GoLive Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_satisfywithpro d})

ELSE 0

END

At this point I have no idea what the issue could be, please help

Thanks!!!
This is a cached copy. Click here to see the original post.

[DanDeHaven]

I got this working, the problem I believe is because I wrote the formulas in Word and pasted them into NetSuite. I noticed the single quotes looked different than the ones in NetSuite and believe that is was simply not recognizing the character. Has anyone else ever experienced issues pasting formulas from Word? Is there a better App to build formulas in that pastes properly? I prefer to use something that allows for proper formatting but doesn't adversely affect them in NetSuite.

[Stephanie.Polk]

Hi DanDeHaven,

For simple CASE statements I typically stick within NetSuite's interface in order to build the statement. However, for complex CASE statements I use Sublime Text. This allows me to see the statement or code in either plain-text or a specific syntax. It keeps it more organized than the NetSuite interface and I have never had an issue with the formatting when pasting it over to NetSuite.

I hope this helps. Please let me know if there is anything else I can assist with.

Have a nice day,

Stephanie Lowry

NetSuite Certified Administrator

Aminian Business Services

NetSuite Support: support@aminian.com

(949) 407-8417 | (888) 800-5207 x 17

[Author]

Posts