```CASE WHEN {department} ='Sales : External Sales...
# generald
DonkeyBot
08/04/2023, 8:35 AM Copy code
CASE WHEN {department} ='Sales : External Sales' THEN '19'
ELSE WHEN {department} ='Directorate' THEN '14' ELSE WHEN {department} ='Finance : Facilities' THEN '16'
ELSE WHEN {department} ='Finance : QHSE' THEN '16' ELSE WHEN {department} ='IT' THEN '18'
ELSE WHEN {department} ='Sales' THEN '1'
ELSE WHEN {department} ='Service' THEN '2'
ELSE WHEN {department} ='Sales' THEN '1'
ELSE WHEN {department} ='Telecoms : Telecoms Sales' THEN '11'
ELSE NULL ENDc
Chris Dillon
08/04/2023, 8:40 AMYou need an END for each when – I think you should CASE WHEN each time as well, it's the only way I can get these to work reliably.
https://stackoverflow.com/questions/18217618/netsuite-custom-formula-field-using-a-case-statement-with-multiple-when-conditio
d
DonkeyBot
08/04/2023, 8:43 AMThanks, I actually also asked BARD / GPT 4 and I got a working snippet of code back, pretty neat here it is
👍 1
DonkeyBot
08/04/2023, 8:44 AM Copy code
CASE WHEN {department} = 'Sales : External Sales' THEN 19 WHEN {department} = 'Directorate' THEN 14 WHEN {department} = 'Finance : Facilities' THEN 16 WHEN {department} = 'Finance : QHSE' THEN 16 WHEN {department} = 'IT' THEN 18 WHEN {department} = 'Sales' THEN 1 WHEN {department} = 'Service' THEN 2 ELSE NULL ENDDonkeyBot
08/04/2023, 8:45 AMSeemed I was wrong to wrap the THEN return in ''
c
CD
08/04/2023, 9:01 AMIt was the multitude of ELSE that was the main problem
s
Stuart Anderton
08/04/2023, 9:49 AMLots of superfluous ELSEs in your original
k
Karina
08/04/2023, 3:23 PMELSE NULL is not right, give some value instead of null, like ‘0’
Karina
08/04/2023, 3:24 PMChatGPT has ways to go LOL
m
michoel
08/06/2023, 11:29 PMThis is a good use case for DECODE over CASE WHEN.. also ELSE NULL is superfluous
2 Views