Nested CASE WHEN Formula(text) in Saved Search Results in NetSuite

Question

I have two fields _courier and _trackingnumber. I would like to create in results nested CASE WHEN formula (text) under the following conditions:

1. the courier is = 'NAME' and
2. if the _trackingnumber of this courier does not start with 0 then add 00 else add _trackingnumber &

I have tried this CASE WHEN option and I realize that AND function does not exist in Functions list in Netsuite

CASE WHEN {_courier} = 'NAME' AND ({_trackingnumber},'0'THEN ('00' || {_trackingnumber}) ELSE ('&' || {_trackingnumber}) END

And I got this error message:

I got this error: ERROR: Invalid Expression

Can you please advise how to fix it?

Answer 1

Try this:

CASE WHEN {_courier} = 'NAME' AND SUBSTR({_trackingnumber},1,1) <> '0' THEN ('00' || {_trackingnumber}) ELSE ('&' || {_trackingnumber}) END