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.