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_projectoutlined})
WHEN ‘PS TS 60 Percent Project Completion Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_projectprogress})
WHEN ‘PS TS 19 Days Project GoLive Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_satisfywithprod})
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_projectoutlined})
WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 60 Percent Project Completion Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_projectprogress})
WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 19 Days Project GoLive Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_satisfywithprod})
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_projectoutlined})
WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 60 Percent Project Completion Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_projectprogress})
WHEN {custrecord_his_svcssurv_survey_type} = ‘PS TS 19 Days Project GoLive Survey’ THEN TO_NUMBER({custrecord_his_svcs_surv_satisfywithprod})
ELSE 0
END
At this point I have no idea what the issue could be, please help
Thanks!!!